How to configure GoldenGate on 11gR2RAC

Oracle11gR2RAC and GoldenGate are popular Oracle products. After simple learning and reading documents, we believe that the single-node GoldenGate can be used for data replication.

Oracle 11gR2 RAC and GoldenGate are popular products in Oracle. After simple learning and reading documents, we believe that the single-node GoldenGate can be used for data replication.

Oracle 11gR2 RAC and GoldenGate are popular products in Oracle. After a simple study and reading of the document, it is not very difficult to configure a single-node GoldenGate for data replication, however, for the replication from the RAC system to the RAC System Using GoldenGate, there are still some configuration techniques and policy settings. A problem occurred some time ago: assuming that the source and target nodes are two node RAC systems, how can we ensure that the replicate will automatically switch when the target node fails? In fact, if you understand the working mechanism of GG and Resource Management of RAC, the solution to the problem will be very clear.

Today, we will introduce the complete configuration steps of OGG (Oracle GoldenGate abbreviation, the same below) on 11gR2 RAC, and briefly talk about how to solve the problem in the above scenario.

Phase 1: Download OGG (refer to the previous single-node copy example and do not go into details)
OGG's

Phase 2: OGG Installation

1) log on to any node in the source RAC system and create a shared directory for OGG on ACFS, such as/cloudfs/goldengate.

2) decompress the OGG installation package to the/cloudfs/goldengate directory.

3) set the environment variables for OGG to work, such

Export LIBRARY_PATH =/cloudfs/goldengate: $ ORACLE_HOME/lib: $ LD_LIBRARY_PATH

4) Start ggsci and create a directory, then perform necessary settings to start the manager

$ Ggsci

GGSCI> create subdirs

(Optional, support for DDL/Sequence)
Create and edit the parameter file for GLOBALS:

GGSCI> edit params./GLOBALS
Add this line to GLOBALS parameter file:

GGSCHEMA ggs

NOTE: 'ggs 'is the example OGG user and will be used in the rest of this document.

GGSCI> edit params mgr

Add the following lines to Manager parameter file:

PORT 7809
Autostart er *
Autorestart er *

GGSCI> START mgr

5) Repeat steps 1-4 above on the target end. Pay attention to the use of the directory name. We will use/mycloudfs/goldengate on the target end to differentiate them.

Stage 3: Prepare the source and target RAC Databases

1) Create OGG user 'ggs 'on both the source and target database, connect to database using SQL * Plus as SYSDBA:

SQL> CREATE USER ggs IDENTIFIED BY ggs;
SQL> GRANT CONNECT, RESOURCE, DBA TO ggs;

2) (optional, add Oracle sequence replication support) On both source and target database, go to OGG directory and run this SQL, enter OGG user 'ggs 'as prompted:
SQL> @ sequence. SQL

3) Enable supplemental logging on source ODA database:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
SQL> ALTER SYSTEM SWITCH LOGFILE;

(Optional) Add Oracle DDL replication support
4) On the source system, go to OGG directory, connect to database using SQL * Plus as SYSDBA.

SQL> GRANT EXECUTE ON utl_file TO ggs;

5 On the source system, run the following script, provide OGG user 'ggs 'as prompted.

SQL> @ marker_setup. SQL
SQL> @ ddl_setup. SQL

NOTE: enter 'initialsetup 'when prompted for the mode of installation.

SQL> @ role_setup. SQL
SQL> @ ddl_enable. SQL
SQL> @ ddl_pin ggs

NOTE: 'ggs 'here is the OGG user.

Stage 4: configure the source extract group

1) Issue the following command to log on to the database.
GGSCI> dblogin userid ggs, PASSWORD ggs

2) Create a primary Extract group 'myext ':

GGSCI> add extract myext, TRANLOG, begin now, THREADS 2
NOTE: THREADS value is the number of your RAC instances.

3) Create a local trail. The primary Extract writes to this trail, and the data-pump Extract reads it.

GGSCI> add exttrail/cloudfs/goldengate/dirdat/et, EXTRACT myext
NOTE: 'et' is the example trail identifier for Extract 'myext '.

4) Create and edit the parameter file for Extract 'myext ':

GGSCI> edit params myext
Add following lines to this parameter file:

EXTRACT myext
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1 ")
USERID ggs @ ggdb, PASSWORD ggs
TRANLOGOPTIONS DBLOGREADER
THREADOPTIONS maxcommitpropagationdelay20000
EXTTRAIL/cloudfs/goldengate/dirdat/et
DYNAMICRESOLUTION
DDL INCLUDE ALL
TABLE hr .*;

NOTE 1: make sure the SQL * Net connection string 'ggdb' works.
NOTE 2: 'hr' is the example schema which will be synchronized to the target system.

Phase 5: Configure data pump extract group on the source end

1) Create a data pump group 'mydump ':
GGSCI> add extract mypump, EXTTRAILSOURCE/cloudfs/goldengate/dirdat/et, BEGIN now

2) Specify a remote trail that will be created on the target system.

GGSCI> add rmttrail/mycloudfs/goldengate/dirdat/rt, EXTRACT mypump
NOTE: 'rt 'is the example trail identifier for Extract 'mydump', and use the target OGG directory'/mycloudfs/goldengate' here.

3) Create and edit the parameter file for Extract 'mydump ':
GGSCI> edit params mypump

Add following lines to this parameter file:

EXTRACT mypump
RMTHOST rac12box-scan, MGRPORT 7809
RMTTRAIL/mycloudfs/goldengate/dirdat/rt
PASSTHRU
TABLE hr .*;

NOTE: RMTHOST is the target host. If you also prefer to set up HA on the target system, specify the VIP for your target system as RMTHOST,
Otherwise just use the IP address/hostname of your target system.

4) Start Extract 'myext 'and 'mypump ':

GGSCI> START myext
GGSCI> START mypump

5) Check the status of OGG processes:
GGSCI> info all

Stage 6: Configure Replicat group on the target end

1) Create a Replicat group 'rept', which reads trails from Extract 'mydump ':

GGSCI> add replicat rept, EXTTRAIL/mycloudfs/goldengate/dirdat/rt, nodbcheckpoint

2) Create and edit the parameter file for Replicat 'rept ':

GGSCI> edit params rept
Add following lines to this parameter file, assume the same ORACLE_HOME and target database 'ggdb' as in source ODA environment:

REPLICAT rept
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1 ")
USERID ggs @ ggdb, PASSWORD ggs
ASSUMETARGETDEFS
HANDLECOLLISIONS
REPERROR (DEFAULT, DISCARD)
DDLERROR DEFAULT DISCARD
DDLOPTIONS REPORT
DISCARDFILE/mycloudfs/goldengate/repsz. dsc, append, megabytes 100
MAP hr. *, TARGET hr .*;

NOTE: make sure the SQL * Net connection string 'ggdb' works.

3) Start Replicat 'rept ':

GGSCI> START rept

4) Check the status of OGG processes:
GGSCI> info all

Stage 7: Verify the Goldengate Function

1) Log on to source database as user 'hr', do some simple DDL and DML operations.

2) Check the data change has been captured by Extract on source system:

GGSCI> STATS myext

GGSCI> STATS mypump

3) Log on to target system as oracle user, check the status of Replicat 'rept ':

GGSCI> STATS rept

4) Compare the output and make sure data change is synchronized.

5) (optional, for further HA setup) Stop OGG on source system:

GGSCI> STOP myext

GGSCI> STOP mypump

GGSCI> STOP mgr

6) (optional, for further HA setup) Stop OGG on target system:

GGSCI> STOP rept

GGSCI> STOP mgr

For more information about HA settings, see the next section.