Data Replication with Multiple Extracts and Multiple Replicats with Integrated Capture Mode Using Oracle GoldenGate 12c

Version 2

    Data Replication with Multiple Extracts and Multiple Replicats with Integrated Capture Mode Using Oracle GoldenGate 12c

     

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

     

    Introduction

     

    Oracle GoldenGate 12c supports to configure Multiple Parallel Replicat Processes for improved apply performance at Target Database. Configuring Oracle GoldenGate Extract in Integrated Capture Mode to take advantage of the database LogMiner server functionality and simplify management.

     

    This article is designed to configure Oracle GoldenGate 12c software to perform one-way DML and DDL replication between an Source Database (srcdb) with Multiple Extract Processes, Pump Processes and Multiple Replicat Processes at

    Target Database (tardb)

     

    For this lab, “srcdb” (Oracle 12cR1) will be the Source Database and “tardb” (Oracle 12cR1) will be the Target Database.

             

    Source Database (srcdb)

    Target Database (tardb)

    • Oracle 12c R1  Database (12.1.0.1.0)
    • Oracle  GoldenGate 12c (12.1.2.1.0)
    • Oracle  Enterprise Linux 5.8 (x86-64)
    • Database Name :  srcdb
    • Schema Name :  scott
    • Host Name : ggnode1
    • IP Address:  192.168.56.105
    • Oracle 12c R1  Database (12.1.0.1.0)
    • Oracle  GoldenGate 12c (12.1.2.1.0.)
    • Oracle  Enterprise Linux 5.8 (x86-64)
    • Database Name :  tardb
    • Schema Name :  scott
    • Hostname:  ggnode2
    • IP Address:  192.168.56.106

             

    OGG Processes at  Source Database (srcdb)

    OGG Processes at  Target Database (tardb)

    • Manager: Listening port 15100
    • Extract Process-1: ES1a
    • Extract Process-2: ES1b
    • Extract Process-3: ES1c
    • Pump Process-1: PS1a
    • Pump Process-2: PS1b
    • Pump Process-3: PS1c
    • Manager: Listening port 15200
    • Replicat Process-1: RS1a
    • Replicat Process-2: RS1b
    • Replicat Process-3: RS1c

     

    Series of Steps involved @Source Database (srcdb):

     

    • 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 database (srcdb)
    • Create and configure Manger, Multiple Extract processes, Multiple Pump processes for bi-directional between Oracle 12c database (srcdb) and Oracle 12c database (tardb)
    • Enabling supplemental logging to ensure the correct data is replicated to the target database (tardb).
    • Run the database in ARCHIVELOG mode
    • Enabling force logging mode
    • Enabling supplemental logging
    • Configuring the Streams pool size for integrated capture.

     

    Step-1: Login as ‘oracle’ user in Source Database Server (srcdb) and install and login as ‘ogguser’configure subdirs for Oracle GoldenGate 12c

    Multi-replicant GG screen.jpg

    Multi-replicant GG screen 2.jpg

    [oracle@ggnode1 12g]$ ./ggsci

    Oracle GoldenGate Command Interpreter for Oracle

    Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO

    Linux, x64, 64bit (optimized), Oracle 12c on Aug  7 2014 10:21:34

    Operating system character set identified as UTF-8.Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

     

    GGSCI (ggnode1.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

     

    Log into source database server (srcdb) as ‘sysdba’

     

    [oracle@ggnode1 ~]$ source 12c.env
    [oracle@ggnode1 ~]$ sqlplus /nolog
    SQL*Plus: Release 12.1.0.1.0 Production on Sat Jun 20 13:46:03 2015
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.

     

    SQL> connect sys/oracle@srcdb as sysdba
    Connected.

     

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

    Tablespace created.

     

    SQL> CREATE USER ogguser IDENTIFIED BY oracle DEFAULT TABLESPACE GOLDENGATE TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
    User created.

     

    SQL> GRANT CONNECT TO ogguser;
    Grant succeeded.

     

    SQL> GRANT DBA TO ogguser;
    Grant succeeded.

     

    SQL> GRANT UNLIMITED TABLESPACE TO ogguser;
    Grant succeeded.

     

    SQL> GRANT SELECT ANY DICTIONARY TO ogguser;
    Grant succeeded.

     

    SQL> alter database force logging;
    Database altered.

     

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

     

    SQL> ALTER SYSTEM SWITCH LOGFILE;

    System altered.

     

    SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN,LOG_MODE FROM V$DATABASE;

     

    SUPPLEME            LOG_MODE
    --------            -----------

    YES                 ARCHIVELOG

     

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

     

    Note: When using Extract in integrated capture mode, an area of Oracle memory called the Streams pool must be configured in the System Global Area (SGA) of the database.

     

    SQL> sho parameter streams

     

    NAME                       TYPE               VALUE
    ------------------         -----------        ------
    streams_pool_size          big integer        0

     

    SQL> alter system set streams_pool_size=256M scope=both;
    System altered.

     

    SQL> sho parameter streams

     

    NAME                        TYPE                 VALUE
    -----------------           -----------          -----
    streams_pool_size           big integer          256M

     

    SQL> connect scott/oracle@srcdb
    Connected.


    SQL> select tname from tab;

     

    TNAME 

    ------------
    DEPT
    EMP
    BONUS
    SALGRADE

     

    Login to Oracle GoldenGate 12c in Source Database Server (srcdb) and configure multiple extract processes (es1a, es1b & es1c) and Pump Processes (ps1a, ps1b & ps1c)


    GGSCI (ggnode1.oracle.com) 2> dblogin userid ogguser, password oracle

    Successfully logged into database.

     

    Register extract processes ES1a, ES1 & ES1c with separate trail files.

    Create multiple pump processes (PS1a, PS1b & PS1c) for transforming trail files to target database (tardb).

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 4> register extract es1a database
    Extract ES1A successfully registered with database at SCN 4341495.

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 5> add extract es1a, integrated tranlog, begin now
    EXTRACT added.

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 6> add exttrail ./dirdat/ya, extract es1a, megabytes 10
    EXTTRAIL added.

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 7> register extract es1b database
    Extract ES1B successfully registered with database at SCN 4360660.

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 8> add extract es1b, integrated tranlog, begin now
    EXTRACT added.

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 9> add exttrail ./dirdat/yb, extract es1b, megabytes 10
    EXTTRAIL added.

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 10> register extract es1c database
    Extract ES1C successfully registered with database at SCN 4374333.

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 11> add extract es1c, integrated tranlog, begin now
    EXTRACT added.

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 12> add exttrail ./dirdat/yc, extract es1c, megabytes 10
    EXTTRAIL added.

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 13> add extract ps1a, exttrailsource ./dirdat/ya
    EXTRACT added.

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 14> add rmttrail ./dirdat/ya, extract ps1a, megabytes 10
    RMTTRAIL added.

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 15> add extract ps1b, exttrailsource ./dirdat/yb
    EXTRACT added.

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 16> add rmttrail ./dirdat/yb, extract ps1b, megabytes 10
    RMTTRAIL added.

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 17> add extract ps1c, exttrailsource ./dirdat/yc
    EXTRACT added.

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 18> add rmttrail ./dirdat/yc extract ps1c, megabytes 10
    RMTTRAIL added.

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 19> edit param es1a
    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 20> view param es1a

    extract es1a
    exttrail ./dirdat/ya
    userid ogguser, password oracle
    cachemgr cachesize 512M
    ddl include all
    ddloptions report
    statoptions resetreportstats
    tranlogoptions excludeuser ogguser
    tranlogoptions integratedparams (max_sga_size 1024, parallelism 1)
    table scott.dept;

     

    Note: Check the Extract ES1a whether it is configured in Integrated Capture Mode (i.e Log Read Checkpoint will show as ‘Oracle Integrated Redo Logs’).

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 49> info extract es1a

     

    EXTRACT    ES1A                    Last Started 2015-06-21 22:51                   Status RUNNING
    Checkpoint Lag                         00:00:01 (updated 00:00:05 ago)
    Process ID                               23996
    Log Read Checkpoint                Oracle Integrated Redo Logs
                                                    2015-06-22 11:06:55
                                                    SCN 0.5652891 (5652891)

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 21> edit param es1b
    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 22> view param es1b

    extract es1b
    exttrail ./dirdat/yb
    userid ogguser, password oracle
    cachemgr cachesize 512M
    ddl include all
    ddloptions report
    statoptions resetreportstats
    tranlogoptions excludeuser ogguser
    tranlogoptions integratedparams (max_sga_size 1024, parallelism 1)

    table scott.dept;

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 23> edit param es1c
    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 24> view param es1c

    extract es1c
    exttrail ./dirdat/yc
    userid ogguser, password oracle
    cachemgr cachesize 512M
    ddl include all
    ddloptions report
    statoptions resetreportstats
    tranlogoptions excludeuser ogguser
    tranlogoptions integratedparams (max_sga_size 1024, parallelism 1)
    table scott.dept;

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 25> edit param ps1a
    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 26> view param ps1a

    extract ps1a
    userid ogguser, password oracle
    rmthost ggnode2, mgrport 15200, compress
    rmttrail ./dirdat/ya
    table scott.dept, filter (@range (1,3));

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 27> edit param ps1b
    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 28> view param ps1b

    extract ps1b
    userid ogguser, password oracle
    rmthost ggnode2, mgrport 15200, compress
    rmttrail ./dirdat/yb
    table scott.dept, filter (@range (2,3));

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 29> edit param ps1c
    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 30> view param ps1c

    extract ps1c
    userid ogguser, password oracle
    rmthost ggnode2, mgrport 15200, compress
    rmttrail ./dirdat/yc
    table scott.dept, filter (@range (3,3));

     

    Note: Please note that we can use the option 'Compress'. It uses Zlib Compression and Automatic decompression is performed by the Server Collector on a Target Database System.

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 31> info all
    Program                Status                     Group                      Lag at Chkpt        Time Since Chkpt

                                                                                                                                                                                                                                                                                                

    ProgramStatusGroupLag at ChkptTime Since Chkpt
    MANAGERSTOPPED
    EXTRACTSTOPPEDES1A00:00:0000:05:04
    EXTRACTSTOPPEDES1B00:00:0000:04:28
    EXTRACTSTOPPEDES1C00:00:0000:03:52
    EXTRACTSTOPPEDPS1A00:00:0000:03:17
    EXTRACTSTOPPEDPS1B00:00:0000:03:11
    EXTRACTSTOPPEDPS1C00:00:0000:03:07

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 32> edit param mgr
    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 33> view param mgr

    PORT 15100
    DYNAMICPORTLIST 15510-15520
    PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS

     

    Oracle GoldenGate (OGG) requires key column values to be logged into redo to allow the same updated or deleted rows manipulated on the source database to be found on the target database.
    Add supplemental logging at the schema level using the Oracle GoldenGate command ADD SCHEMATRANDATA.

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 34> add schematrandata scott
    2015-06-21 22:25:22  INFO    OGG-01788  SCHEMATRANDATA has been added on schema scott.
    2015-06-21 22:25:22  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema scott.

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 35> edit param ./GLOBALS
    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 36> view param ./GLOBALS

     

    GGSCHEMA OGGUSER
    ENABLEMONITORING
    CHECKPOINTTABLE GGS_CHECKPOINT

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 39> info all

                                                                                                                                                                                                                                                                                               

    ProgramStatusGroupLag at ChkptTime Since Chkpt
    MANAGERSTOPPED
    EXTRACTSTOPPEDES1A00:00:0000:14:26
    EXTRACTSTOPPEDES1B00:00:0000:13:50
    EXTRACTSTOPPEDES1C00:00:0000:13:14
    EXTRACTSTOPPEDPS1A00:00:0000:12:39
    EXTRACTSTOPPEDPS1B00:00:0000:12:34
    EXTRACTSTOPPEDPS1C00:00:0000:12:29

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 38> start mgr
    Manager started.

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 48> info mgr
    Manager is running (IP port ggnode1.oracle.com.15100, Process ID 23542).

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 40> info all

                                                                                                                                                                                                                                                                                               

    ProgramStatusGroupLag at ChkptTime Since Chkpt
    MANAGERSTOPPED
    EXTRACTSTOPPEDES1A00:00:0000:17:02
    EXTRACTSTOPPEDES1B00:00:0000:16:26
    EXTRACTSTOPPEDES1C00:00:0000:15:50
    EXTRACTSTOPPEDPS1A00:00:0000:15:15
    EXTRACTSTOPPEDPS1B00:00:0000:15:10
    EXTRACTSTOPPEDPS1C00:00:0000:15:05

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 45> info exttrail ./dirdat/ya

     

    Extract Trail:       ./dirdat/ya
    Extract:               ES1A
    Seqno:                8
    RBA:                  8881941
    File Size:            10M

     

    Extract Trail:       ./dirdat/ya
    Extract:              PS1A
    Seqno:               2
    RBA:                 6898892
    File Size:           10M

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 46> info exttrail ./dirdat/yb

     

    Extract Trail:      ./dirdat/yb
    Extract:              ES1B
    Seqno:               8
    RBA:                 9172543
    File Size:           10M

     

    Extract Trail:       ./dirdat/yb
    Extract:               PS1B
    Seqno:                2
    RBA:                  6978307
    File Size:            10M

     

    GGSCI (ggnode1.oracle.com as ogguser@srcdb) 47> info exttrail ./dirdat/yc

     

    Extract Trail:       ./dirdat/yc
    Extract:               ES1C
    Seqno:                8
    RBA:                  5718157
    File Size:            10M

     

    Extract Trail:       ./dirdat/yc
    Extract:               PS1C
    Seqno:                2
    RBA:                  7041522
    File Size:            10M

     

    Series of Steps involved @Target Database (tardb):

     

    • 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 database (tardb)
    • Create and configure Manger, Multiple Replicat Processes in target database (tardb)
    • Enabling supplemental logging to ensure the correct data is replicated
    • Run the database in ARCHIVELOG mode
    • Enabling force logging mode
    • Enabling supplemental logging
    • Configuring the Streams pool size.

     

    Step-2: Login as ‘oracle’ user in Target Database Server (tardb) and install and login as ‘ogguser’configure subdirs for Oracle GoldenGate 12c

    Multi-replicant GG screen 3.jpg

    Multi-replicant GG screen 4.jpg

    [oracle@ggnode2 ~]$ source 12c.env
    [oracle@ggnode2 ~]$ cd $GG
    [oracle@ggnode2 12g]$ ./ggsci

     

    Oracle GoldenGate Command Interpreter for Oracle
    Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
    Linux, x64, 64bit (optimized), Oracle 12c on Aug  7 2014 10:21:34
    Operating system character set identified as UTF-8.
    Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

     

    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

     

    Log into Target Database Server (tardb) as ‘sysdba’ privileges

    [oracle@ggnode2 ~]$ sqlplus /nolog

    SQL*Plus: Release 12.1.0.1.0 Production on Sat Jun 20 13:49:28 2015
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.

     

    SQL> connect sys/oracle@tardb as sysdba
    Connected.

     

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

     

    SQL> CREATE USER ogguser IDENTIFIED BY oracle DEFAULT TABLESPACE GOLDENGATE TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
    User created.

     

    SQL> GRANT CONNECT TO ogguser;
    Grant succeeded.

     

    SQL> GRANT DBA TO ogguser;
    Grant succeeded.

     

    SQL> GRANT UNLIMITED TABLESPACE TO ogguser;

    Grant succeeded.

     

    SQL> GRANT SELECT ANY DICTIONARY TO ogguser;
    Grant succeeded.

     

    SQL> alter database force logging;
    Database altered.

     

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

     

    SQL> ALTER SYSTEM SWITCH LOGFILE;
    System altered.

     

    SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN,LOG_MODE FROM V$DATABASE;
    SUPPLEME                  LOG_MODE
    -----------------                   ---------------------
    YES                             ARCHIVELOG

     

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

     

    SQL> sho parameter streams
    NAME                          TYPE                VALUE
    ----------------------------        ---------------         --------------
    streams_pool_size         big integer           0

     

    SQL> alter system set streams_pool_size=256M scope=both;
    System altered.

     

    SQL> sho parameter streams


    NAME                              TYPE                  VALUE
    ----------------------------            ----------------          --------------
    streams_pool_size             big integer           256M

     

    SQL> connect scott/oracle@tardb
    Connected.

     

    SQL> select tname from tab;

     

    TNAME
    -------------------
    DEPT
    EMP
    BONUS
    SALGRADE

     

    Login to Oracle GoldenGate 12c in Target Database Server (tardb)

     

    [oracle@ggnode2 12g]$ ./ggsci
    Oracle GoldenGate Command Interpreter for Oracle
    Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
    Linux, x64, 64bit (optimized), Oracle 12c on Aug  7 2014 10:21:34
    Operating system character set identified as UTF-8.
    Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

     

    GGSCI (ggnode2.oracle.com) 2> dblogin userid ogguser, password oracle
    Successfully logged into database.

     

    GGSCI (ggnode2.oracle.com as ogguser@tardb) 4> edit param ./GLOBALS
    GGSCI (ggnode2.oracle.com as ogguser@tardb) 5> view param ./GLOBALS

    GGSCHEMA OGGUSER
    ENABLEMONITORING
    CHECKPOINTTABLE GGS_CHECKPOINT

     

    GGSCI (ggnode2.oracle.com as ogguser@tardb) 6> exit
    [oracle@ggnode2 12g]$ ./ggsci

     

    GGSCI (ggnode2.oracle.com) 1> dblogin userid ogguser, password oracle
    Successfully logged into database.

     

    GGSCI (ggnode2.oracle.com as ogguser@tardb) 1> add checkpointtable
    No checkpoint table specified. Using GLOBALS specification (GGS_CHECKPOINT)...
    Successfully created checkpoint table GGS_CHECKPOINT.

     

    GGSCI (ggnode2.oracle.com as ogguser@tardb) 2> add replicat rs1a, exttrail ./dirdat/ya
    REPLICAT added.

     

    GGSCI (ggnode2.oracle.com as ogguser@tardb) 3> add replicat rs1b, exttrail ./dirdat/yb
    REPLICAT added.

     

    GGSCI (ggnode2.oracle.com as ogguser@tardb) 4> add replicat rs1c, exttrail ./dirdat/yc
    REPLICAT added.

     

    GGSCI (ggnode2.oracle.com as ogguser@tardb) 5> edit param rs1a
    GGSCI (ggnode2.oracle.com as ogguser@tardb) 6> view param rs1a

    replicat rs1a
    userid ogguser, password oracle
    assumetargetdefs
    ddloptions report
    discardfile ./dirout/rs1a.dsc, purge
    map scott.dept, target scott.dept, filter (@range (1,3));

     

    Note: Please note that we can use the option 'Range'. Range function divides a workload into multiple, randomly distributed groups of data.

     

    GGSCI (ggnode2.oracle.com as ogguser@tardb) 47> info replicat rs1a

     

    REPLICAT   RS1A             Last Started 2015-06-21 22:37 Status RUNNING
    Checkpoint Lag                  00:00:00 (updated 00:00:10 ago)
    Process ID                        23194
    Log Read Checkpoint         File ./dirdat/ya000002
                                            2015-06-21 22:58:13.060948  RBA 6898892

     

    GGSCI (ggnode2.oracle.com as ogguser@tardb) 7> edit param rs1b
    GGSCI (ggnode2.oracle.com as ogguser@tardb) 8> view param rs1b

    replicat rs1b
    userid ogguser, password oracle
    assumetargetdefs
    ddloptions report
    discardfile ./dirout/rs1b.dsc, purge
    map scott.dept, target scott.dept, filter (@range (2,3));

     

    GGSCI (ggnode2.oracle.com as ogguser@tardb) 11> edit param rs1c
    GGSCI (ggnode2.oracle.com as ogguser@tardb) 12> view param rs1c

    replicat rs1c
    userid ogguser, password oracle
    assumetargetdefs
    ddloptions report
    discardfile ./dirout/rs1c.dsc, purge
    map scott.dept, target scott.dept, filter (@range (3,3));

     

    GGSCI (ggnode2.oracle.com as ogguser@tardb) 13> edit param mgr
    GGSCI (ggnode2.oracle.com as ogguser@tardb) 14> view param mgr

    PORT 15200
    DYNAMICPORTLIST 15510-15520
    PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS

     

    GGSCI (ggnode2.oracle.com as ogguser@tardb) 15> info all
                                                                                                                                                  

    ProgramStatusGroupLag at ChkptTime Since Chkpt
    MANAGERRUNNING
    REPLICATSTOPPEDRS1A00:00:0000:03:44
    REPLICATSTOPPEDRS1B00:00:0000:03:37
    REPLICATSTOPPEDRS1C00:00:0000:03:26

        

    GGSCI (ggnode2.oracle.com as ogguser@tardb) 24> start mgr
    Manager started.

     

    GGSCI (ggnode2.oracle.com as ogguser@tardb) 46> info mgr
    Manager is running (IP port ggnode2.oracle.com.15200, Process ID 23184).

     

    GGSCI (ggnode2.oracle.com as ogguser@tardb) 25> info all

                                                                                                                                             

    ProgramStatusGroupLag at ChkptTime Since Chkpt
    MANAGERRUNNING
    REPLICATSTOPPEDRS1A00:00:0000:08:45
    REPLICATSTOPPEDRS1B00:00:0000:08:38
    REPLICATSTOPPEDRS1C00:00:0000:08:27

     

    GGSCI (ggnode2.oracle.com as ogguser@tardb) 26> start er *

     

    Sending START request to MANAGER ...
    REPLICAT RS1A starting

     

    Sending START request to MANAGER ...
    REPLICAT RS1B starting

     

    Sending START request to MANAGER ...
    REPLICAT RS1C starting

     

    GGSCI (ggnode2.oracle.com as ogguser@tardb) 27> info all

                                                                                                                                             

    ProgramStatusGroupLag at ChkptTime Since Chkpt
    MANAGERRUNNING
    REPLICATRUNNINGRS1A00:00:0000:00:01
    REPLICATRUNNINGRS1B00:00:0000:00:01
    REPLICATRUNNINGRS1C00:00:0000:00:01

     

    Login to Oracle GoldenGate 12c in Source Database Server (srcdb) and using ‘scott’ user enter transactions in ‘dept’ table

     

    SQL> select count(*) from dept;

     

    COUNT(*)
    -----------------
    99913

     

    SQL> declare
    deptno         number(6);
    dname         varchar2(14);
    loc               varchar2(13);
    begin
        for i in 100000..500000 loop
            insert into dept values (i,'ORACLE','REDWOOD');
            commit;
        end loop;
    end;
    /
    PL/SQL procedure successfully completed.

     

    SQL> select count(*) from dept;


    COUNT(*)

    ---------------
    499914

     

    Note: Change loop value for more transactions and execute once again

     

    Check the synchronization of transactions in Source Database (srcdb) and Target database (tardb). Check the table rows in 'dept' table in 'scott' user in Source Database (srcdb) and Target Database (tardb).

    Multi-replicant srcdb screen.jpg

     

    Check the Trail files location with sizes at Source database (srcdb)

    Multi-replicant srcdb screen2.jpg

     

    Check the Trail files location with sizes at Target database (tardb). We have used compressed option for trail files in target database.

    Multi-replicant srcdb screen3.jpg

    Check the statistics of multiple extract processes in Source Database (srcdb)

     

    Checking the statistics for extract process (ES1a)

    Multi-replicant srcdb screen4.jpg

     

    Checking the statistics for extract process (ES1b)

    Multi-replicant srcdb screen5.jpg

     

    Checking the statistics for extract process (ES1c)

    Multi-replicant srcdb screen6.jpg

     

    Check the statistics of Multiple Pump Processes in Source Database (srcdb)

    Multi-replicant srcdb screen7.jpg

    Check the statistics of multiple replicat processes in Target Database (tardb)

    Multi-replicant srcdb screen8.jpg

    Summary: Configured Multiple Replicat processes in Target Database for improved apply performance. Configured Oracle GoldenGate Extract in Integrated Capture Mode to take advantage of the database LogMiner server functionality and simplify management. Oracle recommends to first configure a single Replicat process and monitor apply lag and performance. Adding more Replicat processes should only be carried out when performance of a single Replicat causes unacceptable apply latency.

     

    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