GoldenGate Replication between Pluggable Databases

Source :-

OS:- ORHEL7 DB 19C
======================================================
Source DB :- 19C 2 NODE RAC RACDB / PDB Source / Schema Perro / host rac1
Target DB :- 19C Single Node RAC GGTRG / PDB Target / Schema Perro / host rac2
======================================================

[root@rac2 ~]# ps -ef |grep pmon
oracle 1498 1 0 Jan24 ? 00:00:05 ora_pmon_RACDB2
oracle 2445 1 0 Jan24 ? 00:00:04 ora_pmon_GGTRG
grid 6141 1 0 Jan24 ? 00:00:04 asm_pmon_+ASM2
grid 7000 1 0 Jan24 ? 00:00:04 apx_pmon_+APX2

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.

*********************************

Configure Golden Gate for CDB/PDB

*********************************

1. Classic mode of extract and replicat is not supported in multitenant configurations

2. Extract needs to configured at CDB level,container database (CDB$ROOT)

3. Replicat needs to configured at PDB level only 

4. we need to create common user in CDB$ROOT on source side 

5. We need to create common user in CDB$ROOT on target side 

6. On target side golden user needs to connect to PDB to apply the changes 

7. Hence each replicat can only replicate to one PDB 

Assuming we have golden gate installed on source and taget we will proceed further 

Prepare Environment

We have a running Oracle 19c instance multitenant container RACDB with one PDB SOURCE and GoldenGate 19c installed.

[root@rac1 ~]#ps -ef |grep pmon
oracle 2428 1 0 Jan24 ? 00:00:05 ora_pmon_RACDB1
grid 6817 1 0 Jan24 ? 00:00:04 asm_pmon_+ASM1
grid 7358 1 0 Jan24 ? 00:00:04 apx_pmon_+APX1

We have target environment , CDB GGTRG & PDB TARGET

[root@rac2 ~]# ps -ef |grep pmon
oracle 1498 1 0 Jan24 ? 00:00:05 ora_pmon_RACDB2
oracle 2445 1 0 Jan24 ? 00:00:04 ora_pmon_GGTRG
grid 6141 1 0 Jan24 ? 00:00:04 asm_pmon_+ASM2
grid 7000 1 0 Jan24 ? 00:00:04 apx_pmon_+APX2

-------------------------------------

Database Prerequisites on source side

-------------------------------------

. oraenv
RACDB1

sqlplus / as sysdba

------------------------------

1. Enable Supplemental logging 

------------------------------

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING FROM v$database;

--------------------------------

2. Enable database force logging 

--------------------------------

SQL> ALTER DATABASE FORCE LOGGING;

SQL> ALTER SYSTEM SWITCH LOGFILE;

-------------------------------------------

3. Enable goldengate_replication parameter

------------------------------------------- 

SQL> alter system set enable_goldengate_replication=true scope=both ;

-------------------------------------

4. Create common User for Golden Gate 

-------------------------------------

create user c##golden identified by welcome1 default tablespace users

temporary tablespace temp container=all;

5. Grant permission to ogg user 

-------------------------------

grant dba TO  c##golden CONTAINER=all;

exec dbms_goldengate_auth.grant_admin_privilege(' c##golden',container=>'all');

---------------------------------
6. Create credentialstore for PDB
---------------------------------

./ggsci
add credentialstore
GGSCI (patoracle) 2> alter credentialstore add user c##golden@RACDB alias goldcontainer1 Password: Credential store altered. 

GGSCI (patoracle) 3> alter credentialstore add user c##golden@Source alias goldtaller1 

Password: Credential store altered. 

GGSCI (rac1) 7> info credentialstore

Reading from credential store:

Default domain: OracleGoldenGate

  Alias: goldsource1

  Userid: c##golden@source

  Alias: goldcontainer1

  Userid: c##golden@racdb

  Alias: goldtarget1

  Userid: c##golden@target

----------------------------------------

7. Configure Extract to capture from PDB

----------------------------------------

Extract :-


GGSCI (rac1) 9> edit param EXTPATO
EXTRACT extpato
SETENV (ORACLE_SID=RACDB1)
SETENV (ORACLE_HOME=/u008/app/oracle/product/19.3.0/dbhome_1)
USERIDALIAS goldcontainer1
LOGALLSUPCOLS
TRANLOGOPTIONS USE_ROOT_CONTAINER_TIMEZONE
DDL INCLUDE MAPPED SOURCECATALOG source
EXTTRAIL /golden/dirdat/tr
TABLE source.perro.*;


--------------------
8. Add Extract Group
--------------------

ADD EXTRACT EXTPATO , INTEGRATED TRANLOG, BEGIN NOW
ADD EXTTRAIL /golden/dirdat/tr , EXTRACT EXTPATO , MEGABYTES 500

---------------------------------

9. Register Extract with database

---------------------------------

./ggsci

dblogin useridalias goldsource1

REGISTER EXTRACT EXTPATO, DATABASE CONTAINER (SOURCE)

-------------------------

10. Configure Pump Extract 

-------------------------

Pump:-

GGSCI (rac1) 10> edit param EXTPATOP
extract EXTPATOP
rmthost 192.168.56.20, mgrport 7809
PASSTHRU
NOTCPSOURCETIMER
-- Path and Name of remote trail on target server
rmttrail /golden/dirdat/tr
TABLE source.perro.*;

--------------------------------

11. Add trandata at schema level

--------------------------------

GGSCI (rac1 as c##golden@RACDB/CDB$ROOT) 15> add schematrandata source.perro

--------------------------

12. Add pump process group 

--------------------------

ADD EXTRACT EXTPATOP, EXTTRAILSOURCE /golden/dirdat/tr

ADD RMTTRAIL /golden/dirdat/tr,  EXTRACT EXTPATOP, megabytes 500

--------------------------

13. Start extract and Pump 

--------------------------

./ggsci 

GGSCI (rac1) 11> info all

start EXTPATO
start EXTPATOP

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING EXTPATO 00:00:00 00:00:09
EXTRACT RUNNING EXTPATOP 00:00:00 00:00:02

==========================================================

==========================================================

Target side
-------------------------------------
Database Prerequisites on Target side
-------------------------------------

-------------------------------------
1. Create common User for Golden Gate
----------------------
create user c##golden identified by welcome1 default tablespace users
temporary tablespace temp container=all;

-------------------------------

2. Grant permission to ogg user 

-------------------------------

grant dba TO c##golden CONTAINER=all;

exec dbms_goldengate_auth.grant_admin_privilege('c##golden',container=>'all');

-------------------------------------------

3. Enable goldengate_replication parameter

------------------------------------------- 

SQL> alter system set enable_goldengate_replication=true scope=both ;

---------------------------------

4. Create credentialstore for PDB 

---------------------------------

GGSCI (rac1 ) 4> 

add credentialstore

alter credentialstore add user c##golden@target alias goldtarget1

Password:

---------------------

5. Configure replicat 

---------------------

We are planning to replicat to ora_pmon_GGTRG for perro schema in target PDB

---------------------------------

ggsci> edit params repato

REPLICAT repato

SETENV (ORACLE_SID=GGTRG)

SETENV (ORACLE_HOME=/u008/app/oracle/product/19.3.0/dbhome_1)

SETENV (TNS_ADMIN=/u008/app/oracle/product/19.3.0/dbhome_1/network/admin)

DBOPTIONS INTEGRATEDPARAMS(parallelism 2)

USERIDALIAS goldtarget1

--TRANLOGOPTIONS USE_ROOT_CONTAINER_TIMEZONE

ASSUMETARGETDEFS

DDL INCLUDE mapped

--EXTTRAIL /golden/dirdat/tr

MAP source.perro.*, TARGET target.perro.*;

---------------------

6. Add replicat group

---------------------

GGSCI >  ADD REPLICAT repato INTEGRATED EXTTRAIL /golden/dirdat/tr

-----------------

7. Start replicat 

-----------------

ggsci> start repato 

GGSCI (rac2.databasexpert.com) 52> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPATO 00:00:00 00:00:07

********************

Test the replication

******************** 

sqlplus /nolog

SQL> conn perro@taller;

Enter password:

Connected.

SQL> create table t1 (i2 int, c2 varchar(20), d2 timestamp);

SQL>  alter table t1  add (constraint i2_pk primary key (i2) );

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;

===============

Verify the replication,  extract , pump and replicat has process the transaction ......