Propagating changes between schemas/environments in Oracle database with SQLcl and Liquibase | Part one: manual procedure

SQLcl and Liquibase can work together to easy the propagation of changes between schemas either in the same database instance or in different ones.

COnfiguration

Click on the links above to download and configure SQLcl on your environment. Then configure the connection. For example, to propagate between 2 different ATP instances, download the regional wallet from one of the autonomous instances ( a regional wallet put in tnsnames.ora the connection strings of all your autonomous instances in your tenancy). Then put the content of the wallet in a directory and set

export TNS_ADMIN=<your wallet directory>

Finally edit sqlnet.ora in wallet directory and put the path of the wallet directory

Add to tnsnames.ora other configurations as well in case you want to connect to other Oracle database flavours such as Exadata, on-prem instances, etc.

Example:

PROPAGATION OF CHANGES

Either you want to promote all the schema objects from one source to an empty destination or you want to promote changes made in source because software evolutionand versioning, the following procedure will help you.

The script below provides an easy way to promote changes between schemas. For that purpose create a file named as you wanted, for example update.sql with the following (put your appropriate values in the placeholders in bold):

/* source database schema user */
connect <user>/<password>@<sourceconnstring>
-- creating a working directory
!mkdir <a path in your machine>
cd myversion;
pwd
-- Generates changelogs and controller file for the connected schema
lb genschema;
/* destination database schema user*/
connect <user>/<password>@<destinationconnstring>
pwd
-- Applies the specified change log using the current connection
lb update -changelog controller.xml;
exit;

Example:

connect scott/tiger@atpext_tp
!mkdir myversion
pwd
cd myversion;
lb genschema;
connect scott/tiger@atpext1_tp
pwd
lb update -changelog controller.xml;
exit;

Go to directory where you have put the SQLcl binaries:

pwd
/Users/javiermugueta/sqlcl/bin

Execute

./sql -nolog @update.sql

Example output:

(⎈ |context-chfg2qhw3ya:invictuscore)mbpj:bin javiermugueta$ ./sql -nolog @update.sql

SQLcl: Release 21.1 Production on Thu Apr 29 01:01:46 2021

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Connected.

[Method loadCaptureTable]: 
                 Executing
[Type - TYPE_SPEC]:                          598 ms
[Type - TYPE_BODY]:                           74 ms
[Type - SEQUENCE]:                           126 ms
[Type - DIRECTORY]:                           55 ms
[Type - CLUSTER]:                             66 ms
[Type - TABLE]:                             1725 ms
[Type - MATERIALIZED_VIEW_LOG]:               56 ms
[Type - MATERIALIZED_VIEW]:                   48 ms
[Type - VIEW]:                                72 ms
[Type - REF_CONSTRAINT]:                     644 ms
[Type - DIMENSION]:                           56 ms
[Type - FUNCTION]:                           102 ms
[Type - PROCEDURE]:                          133 ms
[Type - PACKAGE_SPEC]:                       222 ms
[Type - DB_LINK]:                             55 ms
[Type - SYNONYM]:                             65 ms
[Type - INDEX]:                              596 ms
[Type - TRIGGER]:                            241 ms
[Type - PACKAGE_BODY]:                       783 ms
[Type - JOB]:                                 67 ms
                 End
[Method loadCaptureTable]:                  5784 ms
[Method processCaptureTable]:              96324 ms
[Method sortCaptureTable]:                   107 ms
[Method cleanupCaptureTable]:                 38 ms
[Method writeChangeLogs]:                    911 ms


Export Flags Used:

Export Grants		false
Export Synonyms		false

The following are objects that were not able to be parsed correctly.
This means they will not be sorted according to any object dependencies.

package_body_bad_0.xml
Connected.
ScriptRunner Executing: package_body_bad_0.xml::8fc1a703cceabb943cc46f1ed674d006518b90e7::(HR1)-Generated -- DONE

######## ERROR SUMMARY ##################
Errors encountered:0

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

Shortly we’ll explain how to put this together with other automation tasks in a CI/CD pipeline.

That’s all, hope it helps!! 🙂