Autonomous Database + GoldenGate + Python
As known the autonomous database completely revolutionizes the way how to use a database.
Oracle Autonomous Database can be provisioned and running in minutes providing high level, high value database service that is self-driving, self-repairing, self-securing and continually optimized based on the real-time workload.
Moreover, it is possible to retrive the data by Api Rest using ORDS or APEX and it is easily understandable how useful it is for example in a microservices scenario.
There are also SQL developers Web tool to facilitate the way to queries and manage the data schema.
Now there is also performance hub to monitor the resource, but don’t forget that Auto Scaling is now available for Autonomous database. OCI Events Integration is also available to make DevOps even easier.
Anyway in my scenario the Autonomous Database is the place where the data confluence from different heterogeneous databases and from other types of sources, distributed in different locations , because Autonomous Database is the perfect place to analyse this huge amount of data.
The solution here is Oracle GoldenGate .
Oracle GoldenGate offer a High Availability integrate solution to build the stream from all disparate data across the enterprise quickly and without impacting performance.
With Oracle GoldenGate you can have Real-time data integration with Oracle and non-Oracle databases and other source , Enhanced Security with password, Trail and data encryption,ETL and JMS integration, Transformations and Replication in heterogeneous environments like a flat file or like kafka topics .
In order to simulate the real case in a small poc I decided to use the following component :
- 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.
From now on I will use the acronyms to indicate the place where I run the command
(OP) = on-premises
(ATP) = Autonomous Transaction Processing Cloud Service
Ok let me start
(OP) I will use ORCLPDB2 pluggable database for local database (v 19.3)
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 ALLmap ORCLPDB2.source_repl.*, target admin.*, colmap(usedefaults);
batchsqltask = add and start replicat processadd replicat repatp, exttrail ./dirdat/ad, nodbcheckpoint
start repatp
Now it’s time to Create sample Table and Load data using python
Ok 9999 records on “pizza_data” table on premise
As you could see in a python code I used file “parametri_conn_db.json” to get info about connection
[
{
"id": 1,
"nome_database": "ORCLPDB2",
"username": "source_repl",
"password": "Welcome1",
"connect_string": "192.168.xx.xx:1521/orclpdb2",
"versione": "v19"
}
]Now I need to check on GoldenGate process if all rows are processed
- (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.00Replicating 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.00End 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.0SQL> select count(*) from PIZZA_DATA;COUNT(*)
----------
9999
Seems all ok , the data on Autonomous Database are ready to be get some information about pizza :-) normalize data use some ML algorithm or something else….
cat parametri_conn_ATP.json
[
{
"id": 1,
"nome_database": "ATP",
"username": "Admin",
"password": "DataBase##20",
"connect_string": "autorestx_medium",
"versione": "v18 Autonomous"
}
]but this is another story…
I hope it’s useful and … have fun
Ciao.
Luca
