Bi-Directional Replication with Pluggable Database (PDB) in Multitenant Database Using Oracle GoldenGate 12c

Version 2

    Bi-Directional Replication with Pluggable Database (PDB) in Multitenant Database Using Oracle GoldenGate 12c

     

    Yenugula Venkata RaviKumar (Oracle ACE & Oracle Certified Master) & Nassyam Basha (Oracle ACE Director, Author & Oracle Certified Master)

     

    Introduction

     

    Oracle GoldenGate functionality in a multitenant Container Database (cdb) methodology works for single instance, cluster instance databases. The functions will change when you are working with Extract Process, Replicat process in a pluggable database (pdb) of container database (cdb) environments.

     

    A multitenant, container database will have hold on redo/archive logs, so the same redo stream will share by all Pluggable Databases in that Container Database.

     

    Some of the crucial points to keep in mind when dealing with Oracle GoldenGate and Oracle Database 12c multitenant architecture are:

     

    • One Extract process can be configured to capture changes from multiple Pluggable Databases (PDBs) in a Container Database.
    • We have to use Integrated Capture Mode, a log mining server is involved and this is only accessible from the root container (CDB$ROOT).
    • We have to connect as a common user to attach to the log mining server.
    • SOURCECATALOG  enables us to use the earlier Schema.Table type naming convention.
    • Replicat process can only connect and apply to one Pluggable Database (PDB).

     

    This article explains to configure Oracle GoldenGate (OGG) software to perform Bi-directional replication between Source Container Database (db1) with Pluggable database (db1pdb) and Target Container Database (db2) with Pluggable database (db2pdb) using Oracle 12c and Oracle GoldenGate 12c.

     

    For this exercise, the details given below:

     

           

    Source Container Database (db1)

    Target Container Database (db2)

    1. Oracle Enterprise Linux 5.8 (x86-64)
          2. Oracle 12c R1 Database (12.1.0.1.0)        ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
          3. Oracle GoldenGate 12c (12.1.2.0.0)
          GOLDENGATE_HOME=/u01/app/ogg/12g
          4. Container Database Name: db1
          5. Schema Name: scott
          6. Hostname: ggnode1
          7. IP Address: 192.168.56.105
          8. Pluggable Database: db1pdb
          9. Listener: 1521

    1.   Oracle Enterprise Linux 5.8 (x86-64)
          2. Oracle 12c R1 Database (12.1.0.1.0)      ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
          3. Oracle GoldenGate 12c (12.1.2.0.0)
          GOLDENGATE_HOME=/u01/app/ogg/12g
          4. Container Database Name: db2
          5. Schema Name: scott
          6. Hostname: ggnode2
          7. IP Address: 192.168.56.106
          8. Pluggable Database: db2pdb
          9. Listener: 1521

     

    Architecture of Bi-Directional between Pluggable Databases (PDBs) in Container database (CDB):

     

    Bi-Dir Replat PDB Image1.jpg

     

    Oracle GoldenGate 12c processes configured in Source Container Database and Target Container Database:

           

    Oracle GoldenGate Processes at
    Source Database

    Oracle GoldenGate Processes at
    Target Database

    • Base Directory: /u01/app/ogg/12g
    • Manager: Listening port 7809
    • Extract Process-1: ESCOTT
    • Extract Process-2: PSCOTT
    • Replicat Process: RS01
    • Base Directory: /u01/app/ogg/12g
    • Manager: Listening port 7810
    • Extract Process-1: ES01
    • Extract Prcoess-2: PS01
    • Replicat Process: RSCOTT

    Series of Steps involved @Source Container Database (db1):

     

    • Installed Oracle 12c R1 database software as a ‘oracle’ user in the following directory

           ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1

    • Installing Oracle GoldenGate 12c software as a ‘oracle’ user in the following directory

           GOLDENGATE_HOME=/u01/app/ogg/12g

    • Configuring, creating subdirs for Oracle GoldenGate 12c in Source Container Database (db1)
    • Create and configure Manger, Extract processes, Replicat process for Bi-directional between Pluggable Databases (db1pdb & db2pdb) of Container Databases (db1 & db2).
    • Create Container Database (db1) with pluggable database (db1pdb) with advanced option in DBCA with sample schemas.

    Bi-Dir Replat PDB Image2.jpg

     

    • Use NETMGR create database services option in LISTENER for db1,db1pdb,db2 & db2pdb
    • Use NETMGR services option create for db1,db1pdb,db2 & db2pdb

     

    Installing Oracle GoldenGate 12c as ‘oracle’ user in source database (db1)

     

    Select the option “Oracle GoldenGate for Oracle Database 12c (506.0 MB)”

    Bi-Dir Replat PDB Image3.jpg

    Define the path for Oracle GoldenGate: /u01/app/ogg/12g

    Bi-Dir Replat PDB Image4.jpg

    Successfully installed Oracle GoldenGate 12c software binaries for Oracle 12c database in Source Container Database (db1).

     

    Configuring Oracle GoldenGate 12c in Source Container Database (db1)

     

    Run ggsci command to log into the GoldenGate command line interface (GGSCI)


    [oracle@ggnode1 ~]$ source 12c.env
    [oracle@ggnode1 ~]$ cd $GG
    [oracle@ggnode1 12g]$ pwd
    /u01/app/ogg/12g

     

    [oracle@ggnode1 12g]$ ./ggsci
    Oracle GoldenGate Command Interpreter for Oracle
    Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
    Linux, x64, 64bit (optimized), Oracle 12c on Sep 25 2013 02:33:54
    Operating system character set identified as UTF-8.

    Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

     

    The following command will create subdirectories required for Oracle GoldenGate environment


    GGSCI (ggnode1.oracle.com) 2> CREATE SUBDIRS
    Creating subdirectories under current directory /u01/app/ogg/12g

     

    Parameter files                                       /u01/app/ogg/12g/dirprm: created
    Report files                                            /u01/app/ogg/12g/dirrpt: created
    Checkpoint files                                     /u01/app/ogg/12g/dirchk: created
    Process status files                                /u01/app/ogg/12g/dirpcs: created
    SQL script files                                      /u01/app/ogg/12g/dirsql: created
    Database definitions files                        /u01/app/ogg/12g/dirdef: created
    Extract data files                                    /u01/app/ogg/12g/dirdat: created
    Temporary files                                       /u01/app/ogg/12g/dirtmp: created
    Credential store files                               /u01/app/ogg/12g/dircrd: created
    Masterkey wallet files                             /u01/app/ogg/12g/dirwlt: created
    Dump files                                             /u01/app/ogg/12g/dirdmp: created
    GGSCI (ggnode1.oracle.com) 2> exit

     

    Log into Source Container Database (db1) as a 'sysdba' and complete the following requirements:

     

    • Check the database archive log mode.
    • Create GoldenGate tablespace
    • Create GoldenGate user ‘c##ogguser’
    • Assign privileges for ‘c##ogguser’ user
    • Add database level supplemental logging
    • Connect to pluggable database (db1pdb) and assign privileges to 'scott' user

     

    [oracle@ggnode1 12g]$ sqlplus /nolog

    SQL*Plus: Release 12.1.0.1.0 Production on Tue Jun 9 14:18:27 2015 Copyright (c) 1982, 2013, Oracle.  All rights reserved.

     

    SQL> connect sys/oracle@192.168.56.105:1521/db1 as sysdba

    Connected.

     

    SQL> CREATE TABLESPACE GOLDENGATE DATAFILE '/u01/app/oracle/oradata/db1/goldengate01.dbf' SIZE 100M AUTOEXTEND ON;
    Tablespace created.

     

    SQL> CREATE USER c##ogguser IDENTIFIED BY oracle container=all;
    User created.

     

    SQL> GRANT CONNECT TO c##ogguser container=all;
    Grant succeeded.

     

    SQL> GRANT DBA TO c##ogguser container=all;
    Grant succeeded.

     

    SQL> GRANT UNLIMITED TABLESPACE TO c##ogguser container=all;
    Grant succeeded.

     

    SQL> grant create session to c##ogguser;
    Grant succeeded.

     

    SQL> GRANT SELECT ANY DICTIONARY TO c##ogguser container=all;
    Grant succeeded.

     

    SQL> grant create session to c##ogguser container=all;

    Grant succeeded.

     

    SQL> grant alter session to c##ogguser container=all;
    Grant succeeded.

     

    SQL> alter user c##ogguser default tablespace users container=all;
    User altered.

     

    SQL> alter user c##ogguser quota unlimited on users container=all;
    User altered.

     

    SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
    Database altered.

     

    SQL> ALTER SYSTEM SWITCH LOGFILE;
    System altered.

     

    SQL> ALTER DATABASE FORCE LOGGING;
    Database altered.

     

    SQL> ALTER SYSTEM SWITCH LOGFILE;
    System altered.

     

    SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, LOG_MODE,FORCE_LOGGING FROM v$database;

     

    SUPPLEME      LOG_MODE          FORCE_LOGGING
    ------------------      --------------------         -----------------------------
    YES                 ARCHIVELOG       YES

    Bi-Dir Replat PDB Image5.jpg

    Grant privileges to the user ‘C##OGGUSER’ in Source Container Database and create table in 'scott' user.

     

    SQL> exec dbms_goldengate_auth.grant_admin_privilege('C##OGGUSER',container=> 'all')
    PL/SQL procedure successfully completed.

     

    SQL> connect sys/oracle@192.168.56.105:1521/db1pdb as sysdba
    Connected.

     

    SQL> alter user scott identified by oracle account unlock;
    User altered.

     

    SQL> connect scott/oracle@192.168.56.105:1521/db1pdb
    Connected.

     

    SQL> create table inventory (
    prod_id number,
    prod_category varchar2(20),
    qty_in_stock number,
    last_dml timestamp default systimestamp);
    Table created.

     

    SQL> alter table inventory add constraint pk_inventory primary key (prod_id);
    Table altered.

     

    SQL> grant all on inventory to c##ogguser;
    Grant succeeded.

     

    Log into Oracle GoldenGate 12c in source container database (db1) and Extract Process (escott) and Pump Process (pscott)

     

    [oracle@ggnode1 ~]$ source 12c.env
    [oracle@ggnode1 ~]$ cd $GG
    [oracle@ggnode1 12g]$ pwd
    /u01/app/ogg/12g

     

    GGSCI (ggnode1.oracle.com) 4> dblogin userid c##ogguser@db1pdb, password oracle
    Successfully logged into database DB1PDB.

     

    GGSCI (ggnode1.oracle.com) 5> ADD SCHEMATRANDATA SCOTT ALLCOLS

     

    2015-05-28 15:43:13  INFO    OGG-01788  SCHEMATRANDATA has been added on schema SCOTT.
    2015-05-28 15:43:13  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema SCOTT.
    2015-05-28 15:43:13  INFO    OGG-01977  SCHEMATRANDATA for all columns has been added on schema SCOTT.

     

    GGSCI (ggnode1.oracle.com) 6> dblogin userid c##ogguser@db1, password oracle
    Successfully logged into database CDB$ROOT.

     

    GGSCI (ggnode1.oracle.com) 8> register extract escott database container (db1pdb)
    Extract ESCOTT successfully registered with database at SCN 1754059.

     

    GGSCI (ggnode1.oracle.com) 9> ADD EXTRACT escott INTEGRATED TRANLOG, BEGIN NOW
    EXTRACT added.

     

    GGSCI (ggnode1.oracle.com) 10> ADD EXTTRAIL ./dirdat/lt EXTRACT escott
    EXTTRAIL added.

     

    GGSCI (ggnode1.oracle.com) 11> ADD EXTRACT pscott EXTTRAILSOURCE ./dirdat/lt BEGIN NOW
    EXTRACT added.

     

    GGSCI (ggnode1.oracle.com) 12> ADD RMTTRAIL ./dirdat/rt EXTRACT pscott
    RMTTRAIL added.

     

    GGSCI (ggnode1.oracle.com) 13> edit param mgr
    GGSCI (ggnode1.oracle.com) 14> view param mgr
    PORT 7809

     

    Note the use of the parameter SOURCECATALOG in the extract parameter file.

     

    SOURCECATALOG specifies a default container in an Oracle multitenant container database for subsequent TABLE or SEQUENCE statements. Enables the use of legacy two-part naming convention (schema.object) where three-part names otherwise would be required for those databases.

     

    GGSCI (ggnode1.oracle.com) 10> view param escott
    EXTRACT escott
    USERID C##OGGUSER@db1, PASSWORD oracle
    LOGALLSUPCOLS
    UPDATERECORDFORMAT COMPACT
    EXTTRAIL ./dirdat/lt
    SOURCECATALOG db1pdb
    TABLE scott.*;

     

    GGSCI (ggnode1.oracle.com) 11> view param pscott

    EXTRACT pscott
    USERID C##OGGUSER@db1, PASSWORD oracle
    RMTHOST ggnode2, MGRPORT 7810
    RMTTRAIL ./dirdat/rt
    SOURCECATALOG db1pdb
    TABLE scott.*;

     

    GGSCI (ggnode1.oracle.com) 9> info all

                                                                                                     

    ProgramStatusGroupLag at ChkptTime Since Chkpt
    MANAGERRUNNING
    EXTRACTSTARTINGESCOTT00:00:0000:26:21
    EXTRACTRUNNINGPSCOTT00:00:0000:00:03

     

     

    GGSCI (ggnode1.oracle.com) 10> info all

     

                                                                                                     

    ProgramStatusGroupLag at ChkptTime Since Chkpt
    MANAGERRUNNING
    EXTRACTRUNNINGESCOTT00:13:0100:00:00
    EXTRACTRUNNINGPSCOTT00:00:0000:00:03

     

    Installing Oracle GoldenGate 12c as ‘oracle’ user in Target Container Database (db2)

     

    Series of Steps involved @Target Container Database (db2):

     

    • Installed Oracle 12c R1 database software as a ‘oracle’ user in the following directory

           ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1

    • Installing Oracle GoldenGate 12c software as a ‘oracle’ user in the following directory

           GOLDENGATE_HOME=/u01/app/ogg/12g

    • Configuring, creating subdirs for Oracle GoldenGate 12c in Target Container database (db2)
    • Create and configure Manger, Extract Processes, Replicat process for Bi-directional between Pluggable Databases (db1pdb & db2pdb) of Container Databases (db1 & db2).
    • Create container database (db2) with pluggable database (db2pdb) with advanced option in DBCA with sample schemas.

    Bi-Dir Replat PDB Image6.jpg

    • Use NETMGR create database services option in LISTENER for db2, db2pdb, db1 & db1pdb.
    • Use NETMGR services option create for Container Databases and Pluggable Databases (db2, db2pdb, db1 & db1pdb).

     

    Select the option “Oracle GoldenGate for Oracle Database 12c”

    Bi-Dir Replat PDB Image7.jpg

    Select Software Location u01/app/ogg/12g

    Bi-Dir Replat PDB Image8.jpg

    Successfully installed Oracle GoldenGate 12c software binaries for Oracle 12c database in Target Container Database (db2).

     

    [oracle@ggnode2 ~]$ source 12c.env
    [oracle@ggnode2 ~]$ cd $GG
    [oracle@ggnode2 12g]$ pwd
    /u01/app/ogg/12g

     

    Run ggsci command to log into the GoldenGate command line interface (GGSCI)

    [oracle@ggnode2 12g]$ ./ggsci

    Oracle GoldenGate Command Interpreter for Oracle
    Version 12.1.2.0.0 17185003
    OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
    Linux, x64, 64bit (optimized), Oracle 12c on Sep 25 2013 02:33:54
    Operating system character set identified as UTF-8.
    Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

     

    The following command will create subdirectories required for Oracle GoldenGate environment


    GGSCI (ggnode2.oracle.com) 1> create subdirs
    Creating subdirectories under current directory /u01/app/ogg/12g

     

    Parameter files                                     /u01/app/ogg/12g/dirprm: already exists
    Report files                                          /u01/app/ogg/12g/dirrpt: created
    Checkpoint files                                   /u01/app/ogg/12g/dirchk: created
    Process status files                              /u01/app/ogg/12g/dirpcs: created
    SQL script files                                     /u01/app/ogg/12g/dirsql: created
    Database definitions files                       /u01/app/ogg/12g/dirdef: created
    Extract data files                                  /u01/app/ogg/12g/dirdat: created
    Temporary files                                    /u01/app/ogg/12g/dirtmp: created
    Credential store files                            /u01/app/ogg/12g/dircrd: created
    Masterkey wallet files                          /u01/app/ogg/12g/dirwlt: created
    Dump files                                          /u01/app/ogg/12g/dirdmp: created
    GGSCI (ggnode2.oracle.com) 2> exit

     

    Log into target database, check archive log mode and create user ‘c##ogguser’ with required privileges as ‘sysdba’

     

    • Login to Target Container Database (db2) as a ‘sysdba’
    • Check the database archive log mode.
    • Create GoldenGate tablespace
    • Create GoldenGate user ‘c##ogguser’
    • Assign privileges for ‘c##ogguser’ user
    • Add database level supplemental logging
    • Connect to pluggable database (db2pdb) and assign privileges to 'scott' user

     

    [oracle@ggnode2 12g]$ sqlplus /nolog

    SQL*Plus: Release 12.1.0.1.0 Production on Tue Jun 9 14:34:18 2015
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.

     

    SQL> connect sys/oracle@192.168.56.106:1521/db2 as sysdba
    Connected.

     

    SQL> CREATE TABLESPACE GOLDENGATE DATAFILE '/u01/app/oracle/oradata/db2/goldengate01.dbf' SIZE 100M AUTOEXTEND ON;
    Tablespace created.

     

    SQL> CREATE USER c##ogguser IDENTIFIED BY oracle container=all;
    User created.

     

    SQL> GRANT CONNECT TO c##ogguser container=all;
    Grant succeeded.

     

    SQL> GRANT DBA TO c##ogguser container=all;
    Grant succeeded.

     

    SQL> GRANT UNLIMITED TABLESPACE TO c##ogguser container=all;
    Grant succeeded.

     

    SQL> GRANT SELECT ANY DICTIONARY TO c##ogguser container=all;
    Grant succeeded.

     

    SQL> grant create session to c##ogguser container=all;
    Grant succeeded.

     

    SQL> grant alter session to c##ogguser container=all;
    Grant succeeded.

     

    SQL> alter user c##ogguser default tablespace users container=all;
    User altered.

     

    SQL> alter user c##ogguser quota unlimited on users container=all;
    User altered.

     

    SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
    Database altered.

     

    SQL> ALTER SYSTEM SWITCH LOGFILE;
    System altered.

     

    SQL> ALTER DATABASE FORCE LOGGING;
    Database altered.

     

    SQL> ALTER SYSTEM SWITCH LOGFILE;
    System altered.

     

    SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, LOG_MODE,FORCE_LOGGING FROM v$database;

     

    SUPPLEME            LOG_MODE             FORCE_LOGGING
    -------------------           ---------------------           -----------------------------
    YES                       ARCHIVELOG          YES

     

    Grant privileges to the user ‘C##OGGUSER’ in target container database and create table in 'scott' user.

     

    SQL> exec dbms_goldengate_auth.grant_admin_privilege('C##OGGUSER',container=> 'all');

    PL/SQL procedure successfully completed.

     

    SQL> connect sys/oracle@192.168.56.106:1521/db2pdb as sysdba
    Connected.

     

    SQL> alter user scott identified by oracle account unlock;
    User altered.

     

    SQL> connect scott/oracle@192.168.56.106:1521/db2pdb
    Connected.

     

    SQL> create table inventory (
    prod_id number,
    prod_category varchar2(20),
    qty_in_stock number,
    last_dml timestamp default systimestamp);
    Table created.

     

    SQL> alter table inventory add constraint pk_inventory primary key (prod_id);
    Table altered.

     

    SQL> grant all on inventory to c##ogguser;
    Grant succeeded.

     

    Login into Oracle GoldenGate 12c in Target Database Server and add Replicat Process (rscott) and Manager Process (mgr)

     

    [oracle@ggnode2 ~]$ source 12c.env
    [oracle@ggnode2 ~]$ cd $GG
    [oracle@ggnode2 12g]$ pwd
    /u01/app/ogg/12g

     

    GGSCI (ggnode2.oracle.com) 3> dblogin userid c##ogguser@db2pdb, password oracle
    Successfully logged into database DB2PDB.

     

    GGSCI (ggnode2.oracle.com) 4> ADD SCHEMATRANDATA SCOTT ALLCOLS

     

    2015-05-28 15:43:18  INFO    OGG-01788  SCHEMATRANDATA has been added on schema SCOTT.
    2015-05-28 15:43:18  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema SCOTT.
    2015-05-28 15:43:18  INFO    OGG-01977  SCHEMATRANDATA for all columns has been added on schema SCOTT.

     

    GGSCI (ggnode2.oracle.com) 5> dblogin userid c##ogguser@db2, password oracle
    Successfully logged into database CDB$ROOT.

     

    GGSCI (ggnode2.oracle.com) 6> ADD REPLICAT rscott INTEGRATED EXTTRAIL ./dirdat/rt
    REPLICAT (Integrated) added.

     

    GGSCI (ggnode2.oracle.com) 7> edit param rscott

    GGSCI (ggnode2.oracle.com) 8> view param rscott

    REPLICAT rscott
    DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
    USERID C##OGGUSER@db2pdb, PASSWORD oracle
    ASSUMETARGETDEFS
    MAP db1pdb.scott.*, TARGET db2pdb.scott.*;

     

    GGSCI (ggnode2.oracle.com) 7> edit param mgr
    GGSCI (ggnode2.oracle.com) 15> view param mgr
    PORT 7810

     

    GGSCI (ggnode2.oracle.com) 8> start mgr
    Manager started.

     

    GGSCI (ggnode2.oracle.com) 9> start replicat rscott
    Sending START request to MANAGER ...
    REPLICAT RSCOTT starting

     

    GGSCI (ggnode2.oracle.com) 10> info all

                                                          

    ProgramStatusGroupLag at ChkptTime Since Chkpt
    MANAGERRUNNING
    REPLICATSTARTINGESCOTT00:00:0000:01:08

     

    GGSCI (ggnode2.oracle.com) 11> info all

                                                          

    ProgramStatusGroupLag at ChkptTime Since Chkpt
    MANAGERRUNNING
    REPLICATRUNNINGESCOTT00:00:0000:00:07

     

    Login into Source Database Server (Pluggable Database- db1pdb) as a scott user and enter transactions and check the transactions whether target pluggable database (db2pdb) received or not.

       Bi-Dir Replat PDB Image9.jpg

    Converting from Uni-Directional to Bi-Directional in Pluggable Databases between db1pdb and db2pdb in Multi-tenant environment

     

    Login to Oracle GoldenGate environment for target Container database server (db2) and add extract process (es01), Pump Process (ps01) for Bi-directional replication.

     

    GGSCI (ggnode2.oracle.com) 4> dblogin userid c##ogguser@db2, password oracle
    Successfully logged into database CDB$ROOT.

     

    GGSCI (ggnode2.oracle.com) 14> register extract es01 database container(db2pdb)
    Extract ES01 successfully registered with database at SCN 1751950.

     

    GGSCI (ggnode2.oracle.com) 15> add extract es01 integrated tranlog, begin now
    EXTRACT added.

     

    GGSCI (ggnode2.oracle.com) 16> add exttrail ./dirdat/ea extract es01
    EXTTRAIL added.

     

    GGSCI (ggnode2.oracle.com) 17> add extract ps01 exttrailsource ./dirdat/ea begin now
    EXTRACT added.

     

    GGSCI (ggnode2.oracle.com) 18> add rmttrail ./dirdat/pa extract ps01
    RMTTRAIL added.

     

    GGSCI (ggnode2.oracle.com) 19> edit param es01
    GGSCI (ggnode2.oracle.com) 20> view param es01

    extract es01
    userid c##ogguser@db2, password oracle
    LOGALLSUPCOLS
    UPDATERECORDFORMAT COMPACT
    EXTTRAIL ./dirdat/ea
    SOURCECATALOG db2pdb
    TABLE scott.*;

     

    GGSCI (ggnode2.oracle.com) 21> edit param ps01
    GGSCI (ggnode2.oracle.com) 22> view param ps01

    EXTRACT ps01
    userid c##ogguser@db2, password oracle
    rmthost ggnode1, mgrport 7809
    rmttrail ./dirdat/pa
    SOURCECATALOG db2pdb
    TABLE scott.*;

     

    GGSCI (ggnode2.oracle.com) 23> info all

                                                                                                                                   

    ProgramStatusGroupLag at ChkptTime Since Chkpt
    MANAGERRUNNING
    EXTRACTSTOPPEDES0100:00:0000:01:55
    EXTRACTSTOPPEDPS0100:00:0000:01:28
    REPLICATRUNNINGRSCOTT00:00:0000:00:05

     

    GGSCI (ggnode2.oracle.com) 24> start es01
    Sending START request to MANAGER ...
    EXTRACT ES01 starting

     

    GGSCI (ggnode2.oracle.com) 25> start ps01
    Sending START request to MANAGER ...
    EXTRACT PS01 starting

     

    GGSCI (ggnode2.oracle.com) 26> info all

                                                                                                                                        

    ProgramStatusGroupLag at ChkptTime Since Chkpt
    MANAGERRUNNING
    EXTRACTSTARTINGES0100:00:0000:02:08
    EXTRACTRUNNINGPS0100:00:0000:01:41
    REPLICATRUNNINGRSCOTT00:00:0000:00:08

     

    GGSCI (ggnode2.oracle.com) 27> info all

                                                                                                                                        

    ProgramStatusGroupLag at ChkptTime Since Chkpt
    MANAGERRUNNING
    EXTRACTRUNNINGES0100:00:0600:00:08
    EXTRACTRUNNINGPS0100:00:0000:00:03
    REPLICATRUNNINGRSCOTT00:00:0000:00:05

     

    Note: Since Extract Process (es01) is running in Integrated Capture Mode it requires extra startup time.

    Login to Oracle GoldenGate environment for Source Container Database Server (db1) and add replicat process (rs01) for Bi-directional replication.

     

    GGSCI (ggnode1.oracle.com) 22> dblogin userid c##ogguser@db1, password oracle
    Successfully logged into database CDB$ROOT.

     

    GGSCI (ggnode1.oracle.com) 23> ADD REPLICAT rs01 INTEGRATED EXTTRAIL ./dirdat/pa
    REPLICAT (Integrated) added.

     

    GGSCI (ggnode1.oracle.com) 24> edit param rs01    
    GGSCI (ggnode1.oracle.com) 25> view param rs01

    replicat rs01
    DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
    userid c##ogguser@db1pdb, password oracle
    ASSUMETARGETDEFS
    map db2pdb.scott.*, target db1pdb.scott.*;

     

    GGSCI (ggnode1.oracle.com) 26> start rs01
    Sending START request to MANAGER ...
    REPLICAT RS01 starting

     

    GGSCI (ggnode1.oracle.com) 27> info all

                                                                                                                                        

    ProgramStatusGroupLag at ChkptTime Since Chkpt
    MANAGERRUNNING
    EXTRACTRUNNINGEPSCOTT00:00:0600:00:03
    EXTRACTRUNNINGPSCOTT00:00:0000:00:04
    REPLICATRUNNINGRS0100:00:0000:00:46

     

    Login into Pluggable Database- db2pdb of Target Container Database (db2) as a 'scott' user and enter the transactions and check the transactions whether Source pluggable database (db1pdb) received or not.

    Bi-Dir Replat PDB Image10.jpg

    Check statistics for all the processes in Source & Target Container Database Servers

     

    Source Container Database (db1):

    GGSCI (ggnode1.oracle.com) 31> stats escott, total
    GGSCI (ggnode1.oracle.com) 32> stats pscott, total
    GGSCI (ggnode1.oracle.com) 33> stats rs01, total

     

    Target Container Database (db2):

    GGSCI (ggnode2.oracle.com) 31> stats es01, total
    GGSCI (ggnode2.oracle.com) 32> stats ps01, total
    GGSCI (ggnode2.oracle.com) 33> stats rscott, total

     

    Summary: Oracle GoldenGate works similar in Multi-tenant environments, Single instance & Cluster instances provided we have to add additional extract processes & replicat processes based on replication type whether uni-directional or bi-directional approach.

     

    About the Authors

     

    Nassyam Basha CKPT

     

    Nassyam Basha is a Database Administrator, Oracle ACE Director. He holds a Master's Degree in Computer Applications from the University of Madras. He started learning with dBase,FoxPro, and has participated in several projects with FoxPro before he started working with Oracle database technologies in 2006. He is an Oracle 11g Certified Master, Exadata Implementation Specialist and having good knowledge in Oracle technologies such as Data Guard, RMAN, RAC, Exadata. He actively participates in Oracle-related forums such as OTN and also contributes to many Oracle-Lists. He maintains an Oracle technology-related blog, and he co-authored the book "Oracle Data Guard 11gR2 administration beginners guide" for PACKT publications. He is a frequent contributor to OTN in many languages and is a moderator on the Oracle Community Platform. He is speaker @AIOUG, OTN , IOUG, SANGAM and he is co-founder of oraworld team( www.oraworld-team.com ). Learn more from his profile at CKPT

     

    YV Ravi Kumar LaserSoft

     

    YV RaviKumar is an Oracle ACE and Oracle Certified Master (OCM) with 17 years of experience in Banking, Financial Services and Insurance (BFSI) vertical and played various roles like Senior Database Architect and Production DBA. He is also OCP in Oracle 8i, 9i, 10g,11g &12c and Certified in Golden Gate, RAC, Performance Tuning & Oracle Exadata. He continuously motivates many DBAs and helps the Oracle Community by publishing his tips/ideas/suggestions/solutions in his blog. He has written 35 OTN articles on Oracle Exadata, Oracle RAC and Oracle Golden Gate for OTN-Spanish, OTN-Portuguese and OTN-English and 17 articles for TOAD World, 2 Articles for UKOUG, 3 Articles for OTech Magazine and 2 Articles for Redgate. He is a member of the All India Oracle User Group (AIOUG) and a frequent Oracle speaker in @OTN, AIOUG, Sangam and IOUG. He designed, architected and implemented Core Banking System (CBS) Database for Central Banks of two countries - India & Mahe, Seychelles. He is a Co-Founder of OraWorld (www.oraworld.com). Learn more from his profile at LaserSoft