GoldenGate Replication between Pluggable Databases
Introduction
We are going to setup a GoldenGate replication with Integrated Extract and Integrated Replicat between two Pluggable databases PDB within a Multitenant Container CDB.
First we are going to clone the Pluggable database and then setup GoldenGate for maintaining both databases in sync.
Prepare Environment
We have a running Oracle 19c instance multitenant container ORCLCDB with one PDB taller and GoldenGate 19c installed.
Prepare Database for Replication
GoldenGate requires to setup the following parameter to enable replication:
[oracle@patoracle ~]$ sqlplus / as sysdba
SQL> alter system set ENABLE_GOLDENGATE_REPLICATION=true scope=both;
System altered.
also is mandatory to have supplemental log data and force logging activated:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;
SUPPLEME FORCE_LOGGING
-------- ---------------------------------------
YES YES
then create the LogMiner dictionary:
SQL> exec dbms_logmnr_d.build( options => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
PL/SQL procedure successfully completed.
and finally we must have enough undo retention time, we are going to set this to one day:
SQL> alter system set undo_retention = 86400 scope=both;
System altered.
Create GoldenGate User
Create a database user for GoldenGate with proper privileges:
patoracle ~ $ sqlplus / as sysdba
SQL> create USER c##golden IDENTIFIED BY **** container=all;
User created.
SQL> grant RESOURCE, CONNECT to c##golden container=all;
Grant succeeded.
SQL> exec dbms_goldengate_auth.grant_admin_privilege('c##golden',container=>'all');
PL/SQL procedure successfully completed.
SQL> grant UNLIMITED TABLESPACE to c##golden container=all;
Grant succeeded.
Add GoldenGate User to CredentialStore
We need to add login information in GoldenGate creating a credentialstore and aliases for container and pluggable databases:
[golden@patoracle golden]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
GGSCI (patoracle) 1> add credentialstore
Credential store created.
GGSCI (patoracle) 2> alter credentialstore add user c##golden@ORCLCDB alias goldcontainer
Password:
Credential store altered.
GGSCI (patoracle) 3> alter credentialstore add user c##golden@taller alias goldtaller
Password:
Credential store altered.
GGSCI (patoracle) 4> alter credentialstore add user c##golden@estudio alias goldestudio
Password:
Credential store altered.
GGSCI (patoracle) 5> info credentialstore
Reading from credential store:
Default domain: OracleGoldenGate
Alias: goldcontainer
Userid: c##golden@ORCLCDB
Alias: goldtaller
Userid: c##golden@taller
Alias: goldestudio
Userid: c##golden@estudio
Add Trandata for our Schema
We want to activate trandata for the schema we need to replicate, to do so we need to connect to the pluggable database:
GGSCI (patoracle) 1> DBLOGIN USERIDALIAS goldtaller
Successfully logged into database TALLER.
GGSCI (patoracle as c##golden@ORCLCDB/TALLER) 2> ADD SCHEMATRANDATA taller.perro
2020-05-13 13:40:31 INFO OGG-01788 SCHEMATRANDATA has been added on schema "perro".
2020-05-13 13:40:31 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema "perro".
2020-05-13 13:40:31 INFO OGG-10154 Schema level PREPARECSN set to mode NOWAIT on schema "perro".
2020-05-13 13:40:34 INFO OGG-10471 ***** Oracle Goldengate support information on table PERRO.T1 *****
Oracle Goldengate support native capture on table PERRO.T1.
Oracle Goldengate marked following column as key columns on table PERRO.T1: I1.
Take Snapshot of the Database
Finally we need a snapshot of our pluggable database for the initial point of the cloning-replication:
[oracle@patoracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 13 13:01:52 2020
Version 19.3.0.0.0
SQL> alter session set container = taller;
Session altered.
SQL> alter pluggable database taller close immediate;
Pluggable database altered.
SQL> alter pluggable database taller open read only;
Pluggable database altered.
SQL> alter pluggable database SNAPSHOT taller_snap_20200513;
Pluggable database altered.
SQL> select con_name, snapshot_name, snapshot_scn from cdb_pdb_snapshots ;
CON_NAME SNAPSHOT_NAME SNAPSHOT_SCN
---------- ------------------------- ------------
TALLER TALLER_SNAP_20200513 2308750
SQL> alter pluggable database taller close immediate;
Pluggable database altered.
SQL> alter pluggable database taller open;
Pluggable database altered.
Write down the SCN of this snapshot, we will be using it as the start point of replication.
Create Integrated Extract
Extract Parameters
In GoldenGate create the parameter file defining name, oracle variables, login alias, extrail and tables to extract:
[golden@patoracle golden]$ ./ggsci
GGSCI (patoracle) 1> edit params extpato
EXTRACT extpato
SETENV (ORACLE_SID='ORCLCDB')
SETENV (ORACLE_HOME = '/opt/oracle/product/19c/dbhome_1')
USERIDALIAS goldcontainer
LOGALLSUPCOLS
DDL INCLUDE MAPPED SOURCECATALOG taller
EXTTRAIL /golden/dirdat/tr
TABLE taller.perro.*;
Add Extract in GoldenGate
We will add the Integrated Extract beginning at the SCN from the pluggable database snapshot:
GGSCI (patoracle) 2> ADD EXTRACT extpato, INTEGRATED TRANLOG, SCN 2308750
EXTRACT (Integrated) added.
GGSCI (patoracle) 3> ADD EXTTRAIL /golden/dirdat/tr, EXTRACT extpato
EXTTRAIL added.
Register Extract in the Database
We need to register our Integrated Extract to a SCN before our snapshot time where the LogMiner dictionary begun:
SQL> SELECT max(first_change#) as extract_scn from v$archived_log
where dictionary_begin = 'YES' and first_change# < 2308750 ;
EXTRACT_SCN
-----------
2190137
then connect to the multitenant container and register the Extract to the dictionary SCN and specifying the source pluggable database:
GGSCI (patoracle) 4> DBLOGIN USERIDALIAS goldcontainer
Successfully logged into database CDB$ROOT.
GGSCI (patoracle as c##golden@ORCLCDB/CDB$ROOT) 5> REGISTER EXTRACT extpato DATABASE CONTAINER (taller) SCN 2190137
2020-05-13 13:38:44 INFO OGG-02003 Extract EXTPATO successfully registered with database at SCN 2190502.
Create Integrated Replicat
Clone the Database
Once everything is setup in the source side, we begin in the target side by cloning the pluggable taller database into a new estudio pluggable database using the snapshot taller_snap_20200513:
[oracle@patoracle ~]$ sqlplus / as sysdba
SQL> CREATE PLUGGABLE DATABASE estudio FROM taller
USING SNAPSHOT TALLER_SNAP_20200513
STORAGE (MAXSIZE 2G)
DEFAULT TABLESPACE datos
CREATE_FILE_DEST = '/oradata/pdbs/estudio/'
2 3 4 5 6 ;
Pluggable database created.
SQL> alter pluggable database estudio open;
Pluggable database altered.
Add Checkpoint Table
When the new database is created we add a checkpointtable for replication control:
[golden@patoracle golden]$ ./ggsci
GGSCI (patoracle) 1> DBLOGIN USERIDALIAS goldcontainer
Successfully logged into database CDB$ROOT.
GGSCI (patoracle as c##golden@ORCLCDB/CDB$ROOT) 2> ADD CHECKPOINTTABLE estudio.c##golden.checkpointable
Successfully created checkpoint table estudio.c##golden.checkpointable.
then add the table name to the GLOBALS file:
GGSCI (patoracle) 1> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE estudio.c##golden.checkpointable
Replicat Parameters
In GoldenGate create the parameter file defining name, login alias, and the table mapping from source to target:
GGSCI (patoracle) 1> edit params repato
REPLICAT repato
DBOPTIONS INTEGRATEDPARAMS(parallelism 2)
USERIDALIAS goldestudio
ASSUMETARGETDEFS
DDL INCLUDE ALL
MAP taller.perro.*, TARGET estudio.perro.*;
Add Replicat in GoldenGate
We will add the Integrated Replicat specifying the source extrail and telling to start at the beginning of the first trail file. Also specify the checkpoint table:
GGSCI (patoracle) 2> ADD REPLICAT repato, integrated, EXTTRAIL /golden/dirdat/tr, EXTSEQNO 0, EXTRBA 0, CHECKPOINTTABLE estudio.c##golden.checkpointable
REPLICAT (Integrated) added.
Test Replication
Add Data in the Source
Before beginning the replication we add some data so it be captured by the Extract and Replicat when they start:
[oracle@patoracle ~]$ sqlplus /nolog
SQL> conn perro@taller;
Enter password:
Connected.
SQL> insert into t1 values (100,'Before Replication',current_timestamp);
1 row created.
Start Replication
Let’s start replication and review the Extract and Replicat report files to check data was captured and mapped.
Start the Extract and validate is running:
GGSCI (patoracle) 1> start extract extpato
Sending START request to MANAGER ...
EXTRACT EXTPATO starting
GGSCI (patoracle) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTPATO 00:00:00 00:00:05
REPLICAT STOPPED REPATO 00:00:00 00:06:49
and then check the Extract output in the dirrpt directory:
[golden@patoracle dirrpt]$ more EXTPATO.rpt
...
2020-05-13 14:39:11 INFO OGG-06508 Wildcard MAP (TABLE) resolved (entry taller.perro.*): TABLE "TALLER"."PERRO"."T1".
2020-05-13 14:39:11 WARNING OGG-02180 Table TALLER.PERRO.T1 will use legacy trail format to support parameter LOGALLSUPCOLS.
2020-05-13 14:39:11 INFO OGG-06509 Using the following key columns for source table TALLER.PERRO.T1: I1.
we can see the data is being captured!
Start the Replicat and validate is running:
GGSCI (patoracle) 3> start repato
Sending START request to MANAGER ...
REPLICAT REPATO starting
GGSCI (patoracle) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTPATO 00:00:00 00:00:05
REPLICAT RUNNING REPATO 00:00:00 00:00:09
and check the Replicat output in the dirrpt directory:
[oracle@patoracle ~]$ cd dirrpt
[golden@patoracle dirrpt]$ more REPATO.rpt
...
2020-05-13 14:46:24 INFO OGG-06506 Wildcard MAP resolved (entry taller.perro.*): MAP "TALLER"."PERRO"."T1", TARGET estudio.perro."T1".
2020-05-13 14:46:29 INFO OGG-02756 The definition for table TALLER.PERRO.T1 is obtained from the trail file.
2020-05-13 14:46:29 INFO OGG-06511 Using following columns in default map by name: I1, C2, D3.
2020-05-13 14:46:29 INFO OGG-06510 Using the following key columns for target table ESTUDIO.PERRO.T1: I1.
we can see the data is being mapped!
Validate the Replication
Connect to source pluggable database and generate more data and objects:
[oracle@patoracle ~]$ sqlplus /nolog
SQL> conn perro@taller;
Enter password:
Connected.
SQL> insert into t1 values (101,'After Replication',current_timestamp);
1 row created.
SQL> create table t2 (i2 int, c2 varchar(20), d2 timestamp);
Table created.
SQL> alter table t2 add (constraint i2_pk primary key (i2) );
Table altered.
SQL> insert into t2 values (1,'New Table',current_timestamp);
1 row created.
SQL> commit;
then validate data and objects have been replicated to the target pluggable database:
[oracle@patoracle ~]$ sqlplus /nolog
SQL> conn perro@estudio
Enter password:
Connected.
SQL> select count(*) from t1;
COUNT(*)
----------
6
SQL> select * from t1 where i1 >= 100 order by 1;
I1 C2 D3
---------- -------------------- ------------------------------
100 Before Replication 13-MAY-20 14.11.20.045306 PM
101 After Replication 13-MAY-20 14.32.17.829542 PM
SQL> select * from t2 ;
I2 C2 D2
---------- -------------------- ------------------------------
1 New Table 13-MAY-20 14.35.22.792098 PM
we can see the data and objects were replicated!
Conclusion
We validated we can make a clone of our pluggable database and maintain it up to date using GoldenGate replication. The process is similar to a classic non multitenant environment, we just need to take care of the container and pluggable database particularities.