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.
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;
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:
./sql -nolog @update.sql
(⎈ |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 184.108.40.206.0 - Production Version 220.127.116.11.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!! 🙂