Autonomous Database + GoldenGate + Python

  • Multitenants Database 19c on-premises .
  • Download an dumy csv Data-Set “Map of Pizza Restaurants” from kaggle web .
  • Python to create table and load csv (yes I know there is simpler way to do that but I like Python a lot and I’m also big fan of jupyter-lab).
  • Autonomous Database 18c in Oracle Cloud
  • Golden Gate on premises with Extract and Replicat , both of processes on premises ( yess… Replicat to Autonomous is on premises ;). Remember Autonomous Database is preconfigured for GoldenGate , you don’t need to do anything there, just unlock a user command.
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 READ WRITE NO
8 ORCLPDB2 READ WRITE NO
  • (OP) configure Golden Gate (I didn’t show the software download and installation because it’s easy and you would get bored)
task = Enable supplemental log  ALTER SESSION SET CONTAINER="CDB$ROOT";
select log_mode from v$database;
select supplemental_log_data_min, force_logging from v$database;
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;
Alter database add supplemental log data;
Alter database force logging;
task = Create user for OGG on pdb create user C##GGADMIN identified by Welcome1;
alter user C##GGADMIN identified by Welcome1;
grant connect, resource to C##GGADMIN;
EXEC dbms_goldengate_auth.grant_admin_privilege('C##GGADMIN',container=>'all');
grant dba to c##ggadmin container=all;
  • (OP) create the user
task = Create user on pdb owner of table ALTER SESSION SET CONTAINER="ORCLPDB2";
CREATE USER source_repl IDENTIFIED BY Welcome1;
grant create session, resource, create view, create table to source_repl;
create tablespace users datafile size 100M;ALTER USER source_repl DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP";
ALTER USER source_repl QUOTA UNLIMITED ON USERS;
  • From Autonomous web page download connection file (it’s useful for tnsnames and wallet)
  • (ATP) Test the connection to ATP
[oracle@localhost admin]$ sqlplus admin@autorest_medium
  • (ATP)Unlock the ggadmin user (as I told you before, this is the only command you need to run ; GoldenGate is preconfigured on Autonomous Database …. it’s autonomous :-) )
task = unlock user on Autonomous 
alter user ggadmin account unlock;
alter user ggadmin identified by DataBase##20;
  • (OP) configure OGG Manager process like below
task = configure Manager and GLOBALSGGSCI (localhost.localdomain) 7> view params mgrPORT 7819
Dynamicportlist 7704-7760
GGSCI (localhost.localdomain) 11> view params ./GLOBALSALLOWOUTPUTDIR /home/oracle/OGG/dirdat
  • (OP) configure Wallet and credentialstore
task = configure Credentialstoreadd credentialstorealter credentialstore add user C##GGADMIN@ORCLCDB password Welcome1  alias ggadmin_sourcealter credentialstore add user ggadmin@autorest_medium password DataBase##20 alias ggadmin_targettask = test  Credentialstore
dblogin useridalias ggadmin_source
dblogin useridalias ggadmin_target
  • (OP) Configure and start Extract
task = configure Extract process (with DDL)GGSCI (localhost.localdomain) 6> view params extopextract extop
setenv (ORACLE_SID=ORCLCDB)
useridalias ggadmin_source
DDL INCLUDE ALL
exttrail ./dirdat/ad
reportcount every 2 minutes, rate
table ORCLPDB2.source_repl.*;
task = add extract process
add schematrandata ORCLPDB2.source_repl allcols
add extract EXTOP tranlog, integrated tranlog, begin now
ADD EXTTRAIL ./dirdat/ad, EXTRACT EXTOP
#unregister extract extop, database
register extract extop, database container(ORCLPDB2)
task = start extract process
start extop
  • (OP) Configure and start Replicat prosess
task = Configure replicat Process (DDL includeGGSCI (localhost.localdomain) 20> view params repatpreplicat repatp
useridalias ggadmin_target
DDL include ALL
map ORCLPDB2.source_repl.*, target admin.*, colmap(usedefaults);
batchsql
task = add and start replicat processadd replicat repatp, exttrail ./dirdat/ad, nodbcheckpoint
start repatp
[
{
"id": 1,
"nome_database": "ORCLPDB2",
"username": "source_repl",
"password": "Welcome1",
"connect_string": "192.168.xx.xx:1521/orclpdb2",
"versione": "v19"
}
]
  • (OP) check the Goldengate statistics
stats REPATPGGSCI (localhost.localdomain) 8> stats REPATPSending STATS request to REPLICAT REPATP ...Start of Statistics at 2019-08-06 12:21:08.DDL replication statistics:*** Total statistics since replicat started     ***
Operations 2.00
Mapped operations 2.00
Unmapped operations 0.00
Other operations 0.00
Excluded operations 0.00
Errors 0.00
Retried errors 0.00
Discarded errors 0.00
Ignored errors 0.00
Replicating from ORCLPDB2.SOURCE_REPL.PIZZA_DATA to HWJ4BDEW25QZBPA_AUTOREST.ADMIN.PIZZA_DATA:*** Total statistics since 2019-08-06 12:21:01 ***
Total inserts 9999.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 9999.00
*** Daily statistics since 2019-08-06 12:21:01 ***
Total inserts 9999.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 9999.00
*** Hourly statistics since 2019-08-06 12:21:01 ***
Total inserts 9999.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 9999.00
*** Latest statistics since 2019-08-06 12:21:01 ***
Total inserts 9999.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 9999.00
End of Statistics.
  • (ATP) and finally check number of rows on ATP Autonomous database
MBPdilbindiit:DB_TEST$ sqlplus Admin@autorest_medium
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
SQL> select count(*) from PIZZA_DATA;COUNT(*)
----------
9999
cat parametri_conn_ATP.json
[
{
"id": 1,
"nome_database": "ATP",
"username": "Admin",
"password": "DataBase##20",
"connect_string": "autorestx_medium",
"versione": "v18 Autonomous"
}
]

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store