News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Oracle GoldenGate on Oracle Cloud Marketplace (Part 3)

  • Posted by Gavin Soorma
  • On April 19, 2020
  • 0 Comments

In this example, we will perform replication from an Oracle 12c Release 1 database hosted on GEN 1 OCI Classic environment to an Oracle 19c Autonomous (ATP) database using Oracle GoldenGate on Cloud Marketplace.

In the earlier parts of this post (Part 1 and Part 2) , we have discussed how to configure Oracle GoldenGate on Cloud Marketplace to have connectivity to both the source as well as target environments as well as the steps required to prepare and configure the source and target databases for GoldenGate replication using Microservices Architecture.

Create User and Table on Source 12c Database (OCI Classic)

SQL> alter session set container=pdb1;

Session altered.

SQL> create user test_mig identified by Dreamliner787##;

User created.

SQL> grant create session,create table to test_mig;

Grant succeeded.

SQL> alter user test_mig default tablespace users;

User altered.

SQL> alter user test_mig quota unlimited on users;

User altered.

SQL> conn test_mig/Dreamliner787##@pdb1

Connected.

SQL>  create table myobjects as select object_id, object_name,object_type from all_objects where 1=2;

Table created.

SQL> alter table myobjects add constraint pk_myobjects primary key (object_id);

Table altered.

Create User and Table on Target 19c Database (ATP)

-bash-4.2$ sqlplus admin/Dreamliner787##@gsatp_high

SQL*Plus: Release 19.0.0.0.0 – Production on Wed Apr 1 05:13:10 2020

Version 19.3.0.0.0

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

Last Successful login time: Wed Apr 01 2020 01:45:29 +00:00

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

Version 19.5.0.0.0

SQL> create user test_mig identified by Dreamliner787## ;

User created.

SQL> grant create table, create session to test_mig;

Grant succeeded.

SQL> alter user test_mig quota unlimited on data;

User altered.

SQL> conn test_mig/Dreamliner787##@gsatp_high

Connected.

SQL> create table myobjects as select object_id, object_name,object_type from all_objects where 1=2;

Table created.

SQL>  alter table myobjects add constraint pk_myobjects primary key (object_id);

Table altered.

Create the Checkpoint Table

 
 

 
 

 
 

 
 

 Create Integrated Extract

  • Use the credential which connects to the common user in the source OCI Classic container database
  • Enter the trail file location
  • The Extract is registered with the PDB1 database

 
 

 
 

 
 

 We have to include either the SOURCECATALOG parameter or prefix the schema with the pluggable database name which is PDB1 in this case 

 
 

 

Create Non-Integrated Replicat

  • Autonomous Database currently only supports non-Integrated Replicat and non-Integrated Parallel Replicat
  • The credentials should be the one which connects to ggadmin user in the ATP database

 
 

 
 

 
 

 
 

 Execute DML transaction on source 12c OCI Classic database
 

[oracle@db12c ~]$ sqlplus test_mig/Dreamliner787##@pdb1

SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 1 05:49:11 2020

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

Last Successful login time: Wed Apr 01 2020 05:42:43 +00:00

Connected to:

Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> insert into myobjects

  select object_id, object_name,object_type from all_objects ;

74766 rows created.

SQL> commit;

Commit complete.

Verify Extract has captured the changes on source

 Verify Replicat has applied changes on target
 

-bash-4.2$ sqlplus test_mig/Dreamliner787##@gsatp_high

SQL*Plus: Release 19.0.0.0.0 – Production on Wed Apr 1 05:52:36 2020

Version 19.3.0.0.0

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

Last Successful login time: Wed Apr 01 2020 05:48:29 +00:00

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

Version 19.5.0.0.0

SQL> select count(*) from myobjects;

  COUNT(*)

———-

     74766

 
 

 View statistics via Performance Metrics Server

 

0 Comments

Leave Reply

Your email address will not be published. Required fields are marked *