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;
