Active – Active (Bi-Directional) Replication using Oracle Golden Gate 12c

Since it was announced in Early 2010 that Oracle GoldenGate will be the RoadMap for Replication for Oracle Database, The adoption of GoldenGate over Streams has been quite Rapid. The complexity of Streams has intimidated many adopters, specially for Multi-Master or Peer-to-Peer Replication. GoldenGate not only makes MultiMaster Replication easy but is so full of options and topologies that anybody who has used Golden Gate would most likely never go back to Oracle Streams.

In the Example below we will create a Bi-Directional Model where both the Tables have same name in different databases and can be used for INSERT’ing transactions. The CR( Conflict Resolution) is handled by GG itself.

You can download the latest version of GoldenGate from OTN >> http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

We will use the Sample Table EMP in Sample Schema SCOTT . Before we begin, the assumption is that you have installed and created Oracle Database 12c in both Site A and Site B for creating the replication, (This setup was done on Windows platform, there isn’t much difference between the Linux and Windows setup)

Site A : GGPRIM

————————

Host OS: Windows 2008R2 X64

DB Version: Oracle 12c 12.1.0.2

GoldenGate Version : 12.1.2

Site A : GGSTANDBY

————————-

Host OS: Windows 2008R2 X64

DB Version: Oracle 12c 12.1.0.2

GoldenGate Version : 12.1.2

With 12c the GoldenGate installation is GUI based. GG12c will need to be installed on both Sites and ensure that both databases are in archive log mode.

1. Enable Supplemental Logging
** Do Below Steps in Both Databases **

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
NO

SQL> alter database add supplemental log data;
Database altered.

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
YES

SQL> create user ggate identified by ggate;

SQL> grant dba to ggate;
SQL> conn scott/scott

2. At SITE A, Create the extract (EXT1) and data pump (DPUMP1) —

Site A Home Directory of GG : “D:\app\sql_admin\product\12.1.2\oggcore_1”

add extract ext1 tranlog begin now

add exttrail D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\aa extract ext1

add extract dpump1 exttrailsource D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\aa

— The below Directory is remote directory on other Database Server —

ADD RMTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ab, EXTRACT DPUMP1

edit params ext1

EXTRACT ext1
USERID ggate, PASSWORD ggate
EXTTRAIL D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\aa
TRANLOGOPTIONS EXCLUDEUSER ggate
TABLE scott.emp;

edit params dpump1

EXTRACT dpump1
USERID ggate, PASSWORD ggate
RMTHOST 172.21.104.49, MGRPORT 7809, TCPBUFSIZE 100000
RMTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ab
PASSTHRU
TABLE scott.emp;

3. On SITE B Add Replicat (REP2) —

Site A Home Directory of GG : “F:\app\sql_admin\product\12.1.2\oggcore_1”

ggsci> dblogin userid ggate
password : ******

ADD CHECKPOINTTABLE ggate.ggschkpt

exit

ggsci>

add replicat rep2 exttrail F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ab checkpointtable ggate.ggschkpt
REPLICAT added
.

edit params rep2

REPLICAT rep2
ASSUMETARGETDEFS
USERID ggate, PASSWORD ggate
DISCARDFILE F:\app\sql_admin\product\12.1.2\oggcore_1\discard.txt, append,
MAP scott.emp, TARGET scott.emp;

Create the extract (EXT2) and data pump (DPUMP2) on Site B

add extract ext2 tranlog begin now
EXTRACT added.

add exttrail F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ac extract ext2
EXTTRAIL added.

add extract dpump2 exttrailsource F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ac
EXTRACT added.

— Below Directory is Remote Directory of Other Database Server —

add rmttrail D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ad, extract dpump2
RMTTRAIL added.

edit params ext2

EXTRACT ext2
USERID ggate, PASSWORD ggate
EXTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ac
TRANLOGOPTIONS EXCLUDEUSER ggate
TABLE scott.emp;

edit params dpump2

EXTRACT dpump2
USERID ggate, PASSWORD ggate
RMTHOST 172.20.4.13, MGRPORT 7809, TCPBUFSIZE 100000
RMTTRAIL D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ad
PASSTHRU
TABLE scott.emp;

4. On SITE A, Add replicat (REP1) —

ggsci> dblogin userid ggate
password : ******

ADD CHECKPOINTTABLE ggate.ggschkpt

exit

ggsci>

add replicat rep1 exttrail D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ad checkpointtable ggate.ggschkpt
REPLICAT added.

edit params rep1

REPLICAT rep1
ASSUMETARGETDEFS
USERID ggate, PASSWORD ggate
DISCARDFILE D:\app\sql_admin\product\12.1.2\oggcore_1\discard.txt, append,
MAP scott.emp, TARGET scott.emp;

5. On both SITE A AND SITE B, add trandata, this steps will be done on both sites —

dblogin userid ggate password ggate
Successfully logged into database.

add trandata scott.emp

info trandata scott.emp

exit

6. At SITE A, Start the Extract and Data Pump process —

start mgr

start extract ext1
start extract dpump1
info extract ext1
info all

7. SITE B, Start the Extract and Data Pump process on Site B —

start extract ext2
start extract dpump2
info all

— SITE A —

start replicat rep1

status replicat rep1

— SITE B —

start replicat rep2

status replicat rep2

8. — ERROR in ggserr when starting extract —

If you get below error in starting extract on both sites “Operation not supported because enable_goldengate_replication is not set to true.” then set the parameter enable_goldengate_replication=true

— On Both Sites A and B —

sqlplus "/as sysdba"

alter system set enable_goldengate_replication=true scope=spfile;

Restart both the databases since it is a static parameter(this parameter is only in 11.2.0.4 onwards and in 12c)
Now restart the extract process on both Sites

start extract ext1

start extract ext2

— If you get Directory Level Errors, check on Both sides the RMTTRAIL is pointing to Remote Directory of other server respectively and Remove any Firewall Service blocking the ports servers on both —

-- In Site A --
ADD RMTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ab, EXTRACT DPUMP1

-- In Site B --
ADD RMTTRAIL D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ad, EXTRACT DPUMP2

Do this and Start the extract dpump1 and dpump2, replicat rep1 and rep2 in Both sites A and B

9. — Testing The Active Active Replication —

on Site A

Insert into SCOTT.EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
Values
(1111, 'SHADAB', 'ANALYST', 7566, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
3000, 20);

commit;

Check Record in Site b

On Site B

Insert into SCOTT.EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
Values
(1112, 'Yusuf', 'ANALYST', 7566, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
3000, 20);

commit;

Check Record in Site A

10.
————————————–
|SUMMARY OF DEFINITIONS ON BOTH SITES |
————————————–

There is a slight modification in definitions below, I created another table called tb_table_1 (code below) on both sites to demonstrate you can add as many tables as you want or even the full schema using * wildcard.

create table Tb_table_1(
user_id number primary key,
field_1 varchar2(30)
);

—- SITE A DEFINITIONS —

edit param mgr

PORT 7809

edit param ext1

EXTRACT ext1
USERID ggate, PASSWORD ggate
EXTTRAIL D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\aa
TRANLOGOPTIONS EXCLUDEUSER ggate
TABLE scott.emp;
TABLE scott.tb_table_1;

edit param dpump1

EXTRACT dpump1
USERID ggate, PASSWORD ggate
RMTHOST 172.21.104.49, MGRPORT 7809, TCPBUFSIZE 100000
RMTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ab
PASSTHRU
TABLE scott.emp;
TABLE scott.tb_table_1;

edit param rep1

REPLICAT rep1
ASSUMETARGETDEFS
USERID ggate, PASSWORD ggate
DISCARDFILE D:\app\sql_admin\product\12.1.2\oggcore_1\discard.txt, append,
MAP scott.emp, TARGET scott.emp;
MAP scott.tb_table_1, TARGET scott.tb_table_1;
TABLE scott.tb_table_1;

—- SITE B DEFINITIONS —

edit param mgr

PORT 7809

edit param ext2

EXTRACT ext2
USERID ggate, PASSWORD ggate
EXTTRAIL F:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ac
TRANLOGOPTIONS EXCLUDEUSER ggate
TABLE scott.emp;
TABLE scott.tb_table_1;

edit param dpump2

EXTRACT dpump2
USERID ggate, PASSWORD ggate
RMTHOST 172.20.4.13, MGRPORT 7809, TCPBUFSIZE 100000
RMTTRAIL D:\app\sql_admin\product\12.1.2\oggcore_1\dirdat\ad
PASSTHRU
TABLE scott.emp;
TABLE scott.tb_table_1;

edit param rep2

REPLICAT rep2
ASSUMETARGETDEFS
USERID ggate, PASSWORD ggate
DISCARDFILE F:\app\sql_admin\product\12.1.2\oggcore_1\discard.txt, append,
MAP scott.emp, TARGET scott.emp;
MAP scott.tb_table_1, TARGET scott.tb_table_1;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s