Test Environment for Migration with Liquibase against Oracle XE

For extensive testing of Database Migration I need an environment with Liquibase and an Oracle Database. So I have built this Virtual Development Environment which can be started, stopped, removed and rebuilt in seconds.

The architecture is based on this blog, here you see the updated overview:

vds docker desktop on windows with xe and liquibase

SQLcl is used for creating and importing the database scripts.
The development happens against the Oracle XE Database.

I want to test the latest version of SQLcl. When you look at Oracle Container Registry and search for sqlcl hopefully you find the same version for your architecture as on the download site.

If you find not the latest SQLcl as Docker image you can build you own Docker Image.

The following file describes all required components for development:

docker-compose.yml

version: '3.8'
services:
  oracle-xe: (1)
    environment:
      - ORACLE_PASSWORD=oracle
    image: gvenzl/oracle-xe:slim-faststart (2)
    ports:
      - "1521:1521"
    restart: unless-stopped
    volumes:
      - oracle-volume:/opt/oracle/oradata (3)

  oracle-sqlcl: (4)
    image: container-registry.oracle.com/database/sqlcl:latest (5)
    volumes:
      - ./sql_scripts:/opt/oracle/sql_scripts/
    stdin_open: true
    tty: true
    links:
      - oracle-xe:oracle-xe (6)
volumes:
  oracle-volume:

1	The first service
2	is started from Gerald Venzl’s latest Oracle XE image, which is optimized for a fast start.
3	The volume persist the database state after the first start.
4	The second service
5	is started from Oracle’s official latest SQLcl image. If you have built your own image, you have to change this here.
6	The SQLcl service contains a link to the Oracle XE service, to allow a connection to this database with this name as the host in the connection string.

Start your virtual development server:

docker compose up --detach

Start your SQLcl session in the container:

docker exec -it project-oracle-sqlcl-1 /opt/oracle/sqlcl/bin/sql /nolog

At least after first start you have to connect as SYS and create all required users with required grants:

connect sys/oracle@oracle-xe:1521/xepdb1 as sysdba
@users_roles_grants.sql

The sript is restartable:

users_roles_grants.sql

declare
   type t_list is
      table of varchar2(30);
   l_list t_list := t_list(
                          'development',
                          'staging',
                          'production'
                    );
   e_user_already_exists exception;
   pragma EXCEPTION_INIT ( e_user_already_exists, -1920 );
begin
   for l_iterator in 1..l_list.COUNT loop
      DBMS_OUTPUT.PUT('Creating user '
                      || l_list(l_iterator)
                      || ': ');
      begin
         execute immediate 'CREATE USER '
                           || l_list(l_iterator)
                           || ' QUOTA UNLIMITED ON users PROFILE DEFAULT IDENTIFIED BY oracle ACCOUNT UNLOCK';
         DBMS_OUTPUT.PUT_LINE('User '
                              || l_list(l_iterator)
                              || ' created');
      exception
         when e_user_already_exists then
            execute immediate 'ALTER USER '
                              || l_list(l_iterator)
                              || ' QUOTA UNLIMITED ON users PROFILE DEFAULT IDENTIFIED BY oracle ACCOUNT UNLOCK';
            DBMS_OUTPUT.PUT_LINE('User '
                                 || l_list(l_iterator)
                                 || ' exists, altered');
      end;
      execute immediate 'GRANT CONNECT, RESOURCE, CREATE VIEW to ' || l_list(l_iterator); (1)
   end loop;
end;
/

1	Wenn creating or updating users at least the grants according to documentation example are required for liquibase. These are independent of further rights for objects to create.

Now you connect to the development schema and run an initialisation script, which simulates development and create some objects.

connect development/oracle@oracle-xe:1521/xepdb1
@init.sql

We create a subdirectory via the shortcut for host command for our application schema and capture the schema objects:

! mkdir development
cd development
liquibase generate-schema -split
! ls -R
.:
controller.xml  function  sequence  table

./function:
get_tab1_count_function.xml

./sequence:
tab1_seq_sequence.xml

./table:
tab1_table.xml

After the generation we can list the generated files from SQLcl too:

! ls -R
.:
controller.xml  function  sequence  table

./function:
get_tab1_count_function.xml

./sequence:
tab1_seq_sequence.xml

./table:
tab1_table.xml

As last test of the environment apply the changes to our staging schema:

connect staging/oracle@oracle-xe:1521/xepdb1

select object_name, object_type from user_objects;

no rows selected

liquibase update -changelog-file controller.xml
...

select object_name, object_type from user_objects;

OBJECT_NAME                     OBJECT_TYPE
_______________________________ ______________
DATABASECHANGELOGLOCK           TABLE
PK_DATABASECHANGELOGLOCK        INDEX
DATABASECHANGELOG_ACTIONS       TABLE
SYS_IL0000075993C00004$$        INDEX
SYS_LOB0000075993C00004$$       LOB
SYS_IL0000075993C00003$$        INDEX
SYS_LOB0000075993C00003$$       LOB
DATABASECHANGELOG_ACTIONS_PK    INDEX
DATABASECHANGELOG_ACTIONS_TRG    TRIGGER
DATABASECHANGELOG_DETAILS        VIEW
DATABASECHANGELOG                TABLE
TAB1_SEQ                         SEQUENCE
TAB1                             TABLE
TAB1_PK                          INDEX
GET_TAB1_COUNT                   FUNCTION

15 rows selected.

You find all sources on GitHub.

That’s it!