OGG bi-directional replication for Oracle DB

Overview of an Active-Active Configuration

Oracle GoldenGate supports an active-active bi-directional configuration, where there are two systems with identical sets of data that can be changed by application users on either system. Oracle GoldenGate replicates transactional data changes from each database to the other to keep both sets of data current.

In a bi-directional configuration, there is a complete set of active Oracle GoldenGate processes on each system. Data captured by an Extract process on one system is propagated to the other system, where it is applied by a local Replicat process.

This configuration supports load sharing. It can be used for disaster tolerance if the business applications are identical on any two peers. Bidirectional synchronization is supported for all database types that are supported by Oracle GoldenGate.

Oracle GoldenGate supports active-active configurations for:

·       DB2 on z/OS, LUW, and IBM i

·       MySQL

·       Oracle

·       SQL Server

·       Teradata

Oracle GoldenGate supports DDL replication in an Oracle active-active configuration. DDL support is available for Oracle Database, MySQL, and Teradata databases.

Preventing Data Looping

In a bidirectional configuration, SQL changes that are replicated from one system to another must be prevented from being replicated back to the first system. Otherwise, it moves back and forth in an endless loop, as in this example:

1. A user application updates a row on system A.
2. Extract extracts the row on system A and sends it to system B.
3. Replicat updates the row on system B.
4. Extract extracts the row on system B and sends it back to system A.
5. The row is applied on system A (for the second time).
6. This loop continues endlessly.

To prevent data loopback, you may need to provide instructions that:

• prevent the capture of SQL operations that are generated by Replicat, but enable the capture of SQL operations that are generated by business applications if they contain objects that are specified in the Extract parameter file.
• identify local Replicat transactions, in order for the Extract process to ignore them.

Preventing the Capture of Replicat Transactions (Oracle)

To prevent the capture of SQL that is applied by Replicat to an Oracle database, there are different options depending on the Extract capture mode:

·       When Extract is in classic or integrated capture mode, use the TRANLOGOPTIONS parameter with the EXCLUDETAGtag option. This parameter directs the Extract process to ignore transactions that are tagged with the specified redo tag.

·       When Extract is in classic capture mode, use the Extract TRANLOGOPTIONS parameter with the EXCLUDEUSER or EXCLUDEUSERID option to exclude the user name or ID that is used by Replicat to apply the DDL and DML transactions. Multiple EXCLUDEUSER statements can be used. The specified user is subject to the rules of the GETREPLICATES or IGNOREREPLICATES parameter.

Identifying Replicat Transactions (Oracle)

There are multiple ways to identify Replicat transaction in an Oracle environment. When Replicat is in classic or integrated mode, you use the following parameters:

• Use DBOPTIONS with the SETTAG option in the Replicat parameter file. Replicat tags the transactions being applied with the specified value, which identifies those transactions in the redo stream. The default SETTAG value is 00. Valid values are a single TAG value consisting of hexadecimal digits.

• Use the TRANLOGOPTIONS parameter with the EXCLUDETAG option in the Extract parameter file. The logmining server associated with that Extract excludes redo that is tagged with the SETTAG value.

·       By using SETTAG and EXCLUDETAG you have more flexibility on how you configure replication between various databases.  For Oracle Database 12c container databases this is the only option to avoid data looping.

The following shows how SETTAG can be set in the Replicat parameter file:

dboptions settag 0935

The following shows how EXCLUDETAG can be set in the Extract parameter file:

tranlogoptions excludetag 0935

If you are excluding multiple tags, each must have a separate TRANLOGOPTIONS EXCLUDETAG statement specified.

You can also use the transaction name or userid of the Replicat user to identify Replicat transactions. You can choose which of these to ignore when you configure Extract.

Configuration for DDL replication

After Oracle GoldenGate 12.1.2, you no longer need to run the SQL scripts such as marker_setup.sql and ddl_setup.sql to set up the DDL replication. Using the GoldenGate Integrated Capture for Oracle Database, the native DDL replication is very easy to setup.

You need to use DDL INCLUDE to specify what DDL operations you need to capture. The following example includes ALL the DDL operations. To enable replication of the newly added tables, we can also add the DDLOPTIONS ADDTRANDATA in the parameter file or run ADD SCHEMATRANDATA in GGCSI.

ddl include all

ddloptions addtrandata, report

Creating an Active-Active configuration

The figure shows Oracle GoldenGate Configuration for Active-active Synchronization. In below example, the configuration would be deployed between Oracle 12c pdbs. We will configure pdbsrc as primary system, pdbtgt as secondary system.

Prerequisites

Open Archive Log:

SQL>startup mount;

SQL>alter database archivelog;

SQL>alter database open;

Open supplemental log data and force logging：

SQL>SELECT supplemental_log_data_min, force_logging FROM v$database;

SQL>alter database add supplemental log data;

SQL>alter database force logging;

SQL>ALTER SYSTEM switch logfile;

Enabling Oracle GoldenGate in the Database:

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

Create OGG Users

SQL> startup

SQL> alter PLUGGABLE database all open;

SQL>create user c##oggadmin identified by oracle;

SQL>GRANT DBA to c##oggadmin container=all;

SQL> exec dbms_goldengate_auth.grant_admin_privilege(‘c##oggadmin’,container=>’ALL’);

SQL> alter session set container=pdbsrc;

SQL> create user west identified by oracle;

SQL> grant connect,resource to west;

SQL> alter user west quota unlimited on users;

SQL> alter session set container=pdbtgt;

SQL> create user east identified by oracle;

SQL> grant connect,resource to east;

SQL> alter user east quota unlimited on users;

Configuration from Primary System to Secondary System (pdbsrc to pdbtgt)

Configure the Extract Process (pdbsrc)

GGSCI (orcl) 59> view param etwest

extract etwest

userid c##oggadmin,password oracle

exttrail ./dirdat/ew

ddl include all

TranLogOptions Excludetag 00

sourcecatalog pdbsrc

table west.*;

GGSCI (orcl) 1> add extract etwest,integrated tranlog, begin now

GGSCI (orcl) 2> add exttrail ./dirdat/ew, extract etwest, megabytes 20

GGSCI (orcl) 5> dblogin userid c##oggadmin, password oracle

GGSCI (orcl as c##oggadmin@disdb/CDB$ROOT) 6> register extract etwest database container(pdbsrc)

GGSCI (orcl as c##oggadmin@disdb/CDB$ROOT) 7> add schematrandata pdbsrc.west

Configure the Data Pump Process (pdbsrc)

GGSCI (orcl) 60> view param pmwest

extract pmwest

passthru

rmthost orcl,mgrport 7909

rmttrail ./dirdat/rw

table pdbsrc.west.*;

GGSCI (orcl) 3> add extract pmwest, exttrailsource ./dirdat/ew

GGSCI (orcl) 4> add rmttrail ./dirdat/rw, extract pmwest, megabytes 20

Configure the Replicat Process (pdbtgt)

GGSCI (orcl) 61> view param rpeast

replicat rpeast

userid c##oggadmin@pdbsrc, password oracle

discardfile rpeast.dsc, append, megabytes 10

ddl include all

map pdbtgt.east.*, target pdbsrc.west.*;

GGSCI (orcl) 8> add replicat rpeast, integrated ,exttrail ./dirdat/re

Configuration from Secondary System to Primary System (pdbtgt to pdbsrc)

Configure the Extract Process (pdbtgt)

GGSCI (orcl) 135> view param eteast

extract eteast

userid c##oggadmin,password oracle

exttrail ./dirdat/ee

ddl include all

TranLogOptions Excludetag 00

sourcecatalog pdbtgt

table east.*;

GGSCI (orcl) 2> add extract eteast,integrated tranlog, begin now

GGSCI (orcl) 3> add exttrail ./dirdat/ee, extract eteast, megabytes 20

GGSCI (orcl) 6> dblogin userid c##oggadmin, password oracle

GGSCI (orcl as c##oggadmin@disdb/CDB$ROOT) 7> register extract eteast database container(pdbtgt)

GGSCI (orcl as c##oggadmin@disdb/CDB$ROOT) 8> add schematrandata pdbtgt.east

Configure the Data Pump Process (pdbtgt)

GGSCI (orcl) 136> view param pmeast

extract pmeast

passthru

rmthost orcl,mgrport 7809

rmttrail ./dirdat/re

table pdbtgt.east.*;

GGSCI (orcl) 1> add replicat rpwest, integrated ,exttrail ./dirdat/rw

GGSCI (orcl) 4> add extract pmeast, exttrailsource ./dirdat/ee

Configure the Replicat Process (pdbsrc)

GGSCI (orcl as c##oggadmin@disdb/CDB$ROOT) 138> view param rpwest

replicat rpwest

userid c##oggadmin@pdbtgt, password oracle

discardfile rpwest.dsc, append, megabytes 10

ddl include all

map pdbsrc.west.*, target pdbtgt.east.*;

GGSCI (orcl) 5> add rmttrail ./dirdat/re, extract pmeast, megabytes 20