Oracle GoldenGate Bidirectional Replication Setup
Oracle GoldenGate Bidirectional Replication Setup.
1. Scenario 2: Bidirectional replication
Summary of the software / configuration items would be as given in below table.
| SNo | Software/Configuration Item | Value @ Site A | Value @ Site B |
| 1 | GoldenGate software location | /u01/app/oracle/product/ogg1 | /u01/app/oracle/product
/ogg2 |
| 2 | GoldenGate OS user ID/password | oracle/oracle123 | Oracle/oracle123 |
| 3 | GoldenGate database user ID/password | Gg1_owner/gg1_owner | Gg2_owner/gg1_owner |
| 4 | GoldenGate Version | 11.2.1.0.1 | 11.2.1.0.1 |
| 5 | Oracle Virtual box version | 4.3.16 | 4.3.16 |
| 6 | Host operating version | Windows | Windows |
| 7 | Manager port | 7809 | 7810 |
| 8 | Linux OS/release | OL 6 | OL 6 |
| Oracle os user | oracle | oracle | |
| 9 | ORACLE_HOME | /u01/app/oracle/product
/11.2.1 |
/u01/app/oracle/product
/11.2.1 |
| 10 | Database name | IND | US |
| 11 | Database Release | Oracle 11.2.0.1.0 | Oracle 11.2.0.1.0 |
| 12 | Schema | POS_IN | POS_US |
| extract name | Ext_IN | Ext_US | |
| 13 | Extract trail filename | /u01/app/oracle/product
/ogg1/dirdat/BI_DIR/LI |
/u01/app/oracle/product
/ogg2/dirdat/BI_DIR/LU |
| Datapump name | Pump_IN | Pump_us | |
| 14 | Remote trail filename | /u01/app/oracle/product
/ogg1/dirdat/BI_DIR/RU |
/u01/app/oracle/product
/ogg2/dirdat/BI_DIR/RI |
| 15 | Replicat name | Rep_IN | Rep_US |
Let us look at a simple example to illustrate GoldenGate’s Active-Active Replication with an introduction to Conflict Detection and Resolution.
Let’s call the two sites we are going to use for Active-Active replication as Site A and Site B.
Site A — IND — short form — IN
Site B — USA — short form — US
On Site A we will have the following groups created
- Extract – ext_IN
- Data Pump – pump_IN
- Replicat – rep_IN
On Site B we will have the following groups created
- Extract – ext_US
- Data Pump – pump_US
- Replicat – rep_US
On Site A we have the following trails set up
- LI – local extract trail which will be written by ext_IN
- RU – remote trail which will be processed by data pump extract group pump_US.( Site B)
From Site B this will be shipped over the network to Site A
On Site B we have the following trails set up
- LU – local extract trail which will be written by ext_US
- RI– remote trail which will be processed by data pump extract group pump_IN.(Site A)
From Site A this will be shipped over the network to Site B
1.1. Database objects creation at Site A and Site B
On Site A
Add Supplemental logging
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Goldengate owner creation
SQL>create tablespace ggs_data datafile ‘/u01/app/oracle/oradata/IND/ggs_data_001.dbf’ size 200m;
create user gg1_owner identified by gg1_owner default tablespace ggs_data temporary tablespace temp;
Grant connect, resource to gg1_owner;
grant select any dictionary, select any table to gg1_owner;
grant create table to gg1_owner;
grant flashback any table to gg1_owner;
grant execute on dbms_flashback to gg1_owner;
grant execute on utl_file to gg1_owner;
grant create any table to gg1_owner;
grant insert any table to gg1_owner;
grant update any table to gg1_owner;
grant delete any table to gg1_owner;
grant drop any table to gg1_owner;
grant alter any table to gg1_owner;
On site B
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
create tablespace ggs_data datafile ‘/u01/app/oracle/oradata/US/ggs_data_001.dbf’ size 100m;
Goldengate owner Creation
create user gg2_owner identified by gg2_owner default tablespace ggs_data temporary tablespace temp;
grant connect,resource to gg2_owner;
grant select any dictionary, select any table to gg2_owner;
grant create table to gg2_owner;
grant flashback any table to gg2_owner;
grant execute on dbms_flashback to gg2_owner;
grant execute on utl_file to gg2_owner;
grant create any table to gg2_owner;
grant insert any table to gg2_owner;
grant update any table to gg2_owner;
grant delete any table to gg2_owner;
grant drop any table to gg2_owner;
grant alter any table to gg2_owner;
###########################################
Create the following objects on both databases (Site A and Site B)
Site A
create user POS_IN identified by POS_IN default tablespace users temporary tablespace temp;
grant connect,resource to POS_IN;
grant unlimited tablespace to POS_IN;
create user POS_US identified by POS_US default tablespace users temporary tablespace temp;
grant connect,resource to POS_US;
grant unlimited tablespace to POS_US;
create table POS_IN.inventory
(prod_id number,
prod_category varchar2(20),
qty_in_stock number,
last_dml timestamp default systimestamp);
create table POS_US.inventory
(prod_id number,
prod_category varchar2(20),
qty_in_stock number,
last_dml timestamp default systimestamp);
SQL> alter table POS_IN.inventory add constraint pk_inventory primary key (prod_id) ;
Table altered.
alter table POS_US.inventory add constraint pk_inventory primary key (prod_id) ;
SQL> grant all on POS_IN.inventory to gg1_owner;
Grant succeeded.
grant all on POS_US.inventory to gg2_owner;
CREATE OR REPLACE TRIGGER POS_IN.INVENTORY_CDR_TRG
BEFORE UPDATE
ON POS_IN.INVENTORY
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
IF SYS_CONTEXT (‘USERENV’, ‘SESSION_USER’) != ‘GG1_OWNER’
THEN
:NEW.LAST_DML := SYSTIMESTAMP;
END IF;
END;
/
Trigger created.
CREATE OR REPLACE TRIGGER POS_US.INVENTORY_CDR_TRG
BEFORE UPDATE
ON POS_US.INVENTORY
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
IF SYS_CONTEXT (‘USERENV’, ‘SESSION_USER’) != ‘GG2_OWNER’
THEN
:NEW.LAST_DML := SYSTIMESTAMP;
END IF;
END;
/
1.2. GoldenGate Installation on both Sites
Install GG version 11.2.1.0.1 as given in the below .
GOLDENGATE HOME: /u01/app/oracle/product/ogg1
[oracle@ol6-GG-sys1 ogg1]$ pwd
/u01/app/oracle/product/ogg1
[oracle@ol6-GG-sys1 ogg1]$ unzip
ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[oracle@ol6-GG-sys1 ogg1]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
Create subdirectories
./ggsci
GGSCI>create subdir
Repeat the GG installation on second GG_HOME
GOLDENGATE HOME: /u01/app/oracle/product/ogg2
/u01/app/oracle/product/ogg2
[oracle@ol6-GG-sys1 ogg1]$ pwd
/u01/app/oracle/product/ogg2
[oracle@ol6-GG-sys1 ogg1]$ unzip
ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[oracle@ol6-GG-sys1 ogg1]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
Create subdirectories
./ggsci
GGSCI>create subdir
1.3. Create GLOBAL file on Both Sites
Add GGSSCHEMA and CHECKPOINTTABLE in GLOBALS file in both golden gate homes.
##############################################
On site A
[oracle@ol6-GG-sys1 ogg1]$ cat GLOBALS
GGSCHEMA GG1_OWNER
CHECKPOINTTABLE GG1_OWNER.CHKPTTAB
On Site B
[oracle@ol6-GG-sys1 ogg2]$ cat GLOBALS
GGSCHEMA GG2_OWNER
CHECKPOINTTABLE GG2_OWNER.CHKPTTAB
1.4. Connecting to GoldenGate and create checkpointtable
GGSCI (ol6-GG-sys1.localdomain) 3> dblogin userid gg1_owner@IND ,password gg1_owner
Successfully logged into database.
GGSCI (ol6-GG-sys1.localdomain) 4> add checkpointtable
No checkpoint table specified, using GLOBALS specification (GG1_OWNER.CHKPTTAB)…
Successfully created checkpoint table GG1_OWNER.CHKPTTAB.
GGSCI (ol6-GG-sys1.localdomain) 5>
###############################################################################################
GGSCI (ol6-GG-sys1.localdomain) 1> dblogin userid gg2_owner@US,password gg2_owner
Successfully logged into database.
GGSCI (ol6-GG-sys1.localdomain) 3> add checkpointtable
No checkpoint table specified, using GLOBALS specification (GG2_OWNER.CHKPTTAB)…
Successfully created checkpoint table GG2_OWNER.CHKPTTAB.
GGSCI (ol6-GG-sys1.localdomain) 4>
1.5. Add TRANDATA on both sites
On both Site A and Site B, add trandata
#########################################################
GGSCI (ol6-GG-sys1.localdomain) 14> dblogin userid gg1_owner@IND password gg1_owner
Successfully logged into database.
GGSCI (ol6-GG-sys1.localdomain) 15> add trandata POS_IN.inventory cols (prod_category,qty_in_stock, last_dml)
Logging of supplemental redo data enabled for table POS_IN.INVENTORY.
GGSCI (ol6-GG-sys1.localdomain) 16> info trandata POS_IN.inventory
Logging of supplemental redo log data is enabled for table POS_IN.INVENTORY.
Columns supplementally logged for table POS_IN.INVENTORY: PROD_ID, PROD_CATEGORY, QTY_IN_STOCK, LAST_DML.
GGSCI (ol6-GG-sys1.localdomain) 17>
################################################################################
GGSCI (ol6-GG-sys1.localdomain) 18> dblogin userid gg2_owner@US password gg2_owner
Successfully logged into database.
GGSCI (ol6-GG-sys1.localdomain) 19> add trandata POS_US.inventory cols (prod_category,qty_in_stock, last_dml)
Logging of supplemental redo data enabled for table POS_US.INVENTORY.
GGSCI (ol6-GG-sys1.localdomain) 20> info trandata POS_US.inventory
Logging of supplemental redo log data is enabled for table POS_US.INVENTORY.
Columns supplementally logged for table POS_US.INVENTORY: PROD_ID, PROD_CATEGORY, QTY_IN_STOCK, LAST_DML.
GGSCI (ol6-GG-sys1.localdomain) 21>
1.6. Setup verification on both sites
Verify the Database connectivity to users :gg1_owner,gg2_owner,pos_in,pos_us
Verify the Goldengate home for subdirectories.
Verify tables / objects in pos_in,pos_us schemas.
1.7. DDL Replication
Setup DDL replication on both sites as shown in section 3.1
1.8. Create GoldenGate processes
1.8.1. Configure MGR on site A and B
##################################3
GGSCI> shell date
GGSCI> edit params mgr
PORT 7809
GGSCI>
1.8.2. Create GoldenGate Extract on IND
Create the extract (EXT_IN) and data pump (PUMP_IN) on Site A (INDIA)
####################################################################
GGSCI > add extract ext_IN tranlog begin now
EXTRACT added.
GGSCI > add exttrail /u01/app/oracle/product/ogg1/dirdat/BI_DIR/LI extract ext_IN
EXTTRAIL added.
GGSCI > edit params ext_IN
EXTRACT ext_IN
USERID gg1_owner, PASSWORD gg1_owner
EXTTRAIL /u01/app/oracle/product/ogg1/dirdat/BI_DIR/LI
TRANLOGOPTIONS EXCLUDEUSER gg1_owner
TABLE POS_IN.inventory,
GETBEFORECOLS (
ON UPDATE KEYINCLUDING (prod_category,qty_in_stock, last_dml),
ON DELETE KEYINCLUDING (prod_category,qty_in_stock, last_dml));
1.8.3. GoldenGate Pump on IND
GGSCI (ol6-GG-sys1.localdomain) 55> add extract pump_IN exttrailsource /u01/app/oracle/product/ogg1/dirdat/BI_DIR/LI
EXTRACT added.
GGSCI (ol6-GG-sys1.localdomain) 57> add rmttrail /u01/app/oracle/product/ogg2/dirdat/BI_DIR/RI extract pump_in
RMTTRAIL added.
GGSCI > edit params pump_IN
EXTRACT pump_IN
USERID gg1_owner, PASSWORD gg1_owner
RMTHOST localhost, MGRPORT 7810, TCPBUFSIZE 100000
RMTTRAIL /u01/app/oracle/product/ogg2/dirdat/BI_DIR/RI
PASSTHRU
TABLE POS_IN.inventory;
1.8.4. GoldenGate Replicat on US
On site A add replicat (REP_IN)
#############################################
GGSCI > add replicat rep_IN exttrail /u01/app/oracle/product/ogg1/dirdat/BI_DIR/RU
REPLICAT added.
GGSCI > edit params rep_IN
REPLICAT rep_IN
ASSUMETARGETDEFS
USERID gg1_owner@IND, PASSWORD gg1_owner
DISCARDFILE /u01/app/oracle/product/ogg1/discard_IN1.txt, append,
MAP POS_US.inventory, TARGET POS_IN.inventory;
1.8.5. Create GoldenGate Extract on IND
Create the extract (EXT_US) and data pump (PUMP_US) on Site B
###################################################################
GGSCI> EDIT params mgr
PORT 7810
USERID gg2_owner@US,PASSWORD gg2_owner
GGSCI>
GGSCI > add extract ext_US tranlog begin now
EXTRACT added.
GGSCI > add exttrail /u01/app/oracle/product/ogg2/dirdat/BI_DIR/LU extract ext_US
EXTTRAIL added.
GGSCI > edit params ext_US
EXTRACT ext_US
USERID gg2_owner@US, PASSWORD gg2_owner
EXTTRAIL /u01/app/oracle/product/ogg2/dirdat/BI_DIR/LU
TRANLOGOPTIONS EXCLUDEUSER gg2_owner
TABLE POS_US.inventory,
GETBEFORECOLS (
ON UPDATE KEYINCLUDING (prod_category,qty_in_stock, last_dml),
ON DELETE KEYINCLUDING (prod_category,qty_in_stock, last_dml));
1.8.6. Create GoldenGate Pump on US
GGSCI > add extract pump_US exttrailsource /u01/app/oracle/product/ogg2/dirdat/BI_DIR/LU
EXTRACT added.
GGSCI > add rmttrail /u01/app/oracle/product/ogg1/dirdat/BI_DIR/RU extract pump_US
RMTTRAIL added.
GGSCI > edit params pump_US
EXTRACT pump_US
USERID gg2_owner@US, PASSWORD gg2_owner
RMTHOST localhost, MGRPORT 7810, TCPBUFSIZE 100000
RMTTRAIL /u01/app/oracle/product/ogg1/dirdat/BI_DIR/RU
PASSTHRU
TABLE POS_US.inventory;
1.8.7. Create Replicat on US
On site B add replicat (REP_NW)
########################################
GGSCI> add replicat rep_US exttrail /u01/app/oracle/product/ogg2/dirdat/BI_DIR/RI
REPLICAT added.
GGSCI 10> edit params rep_US
REPLICAT rep_US
ASSUMETARGETDEFS
USERID gg2_owner@US, PASSWORD gg2_owner
DISCARDFILE /u01/app/oracle/product/ogg2/discard_US1.txt, append,
MAP POS_IN.inventory, TARGET POS_US.inventory;
1.8.8. Start GoldenGate processes on both Sites.
Start the Extract and Data Pump process on Site A
###################################################################
GGSCI> start mgr
GGSCI > start extract ext_IN
GGSCI > start extract pump_IN
GGSCI 32> info extract ext_IN
GGSCI 34> info all
##########################################################
Start the Extract and Data Pump process on Site B
GGSCI 22> start extract ext_US
GGSCI 23> start extract pump_US
GGSCI 24> info all
On Site A start the Replicat process REP_IN
GGSCI 38> start replicat rep_IN
GGSCI 39> status replicat rep_IN
On Site B start the Replicat process REP_US
#######################################################
GGSCI 26> start replicat rep_US
GGSCI 27> status replicat rep_US
1.8.9. Verify extract, pump, and replicat processes on both sites
GGSCI 39> status replicat rep_IN
GGSCI 27> status replicat rep_US
1.9. Verifying Bidirectional Replication
1.9.1. DML
Insert
Update
Delete
DML
##########
insert into POS_IN.inventory values (104444,’DVD’,10,sysdate);
insert into POS_IN.inventory values (10100,’DVD’,10,sysdate);
insert into POS_US.inventory values (10101,’DVD’,10,sysdate);
insert into POS_US.inventory values (2,’DVD’,10,sysdate);
insert into POS_US.inventory values (3,’DVD’,10,sysdate);
insert into POS_IN.inventory values (5,’DVD’,10,sysdate);
insert into POS_IN.inventory values (9,’DVD’,10,sysdate);
insert into POS_IN.inventory values (99,’DVD’,10,sysdate);
update POS_IN.inventory set QTY_IN_STOCK=50 where QTY_IN_STOCK=5;
update POS_US.inventory set QTY_IN_STOCK=90 where QTY_IN_STOCK=9;
delete from POS_IN.inventory where QTY_IN_STOCK=50;
delete from POS_US.inventory where QTY_IN_STOCK=99;
insert into POS_IN.inventory values (9,’DVD’,10,sysdate);
insert into POS_IN.inventory values (99,’DVD’,10,sysdate);
insert into POS_IN.inventory values (11,’DVD’,10,sysdate);
insert into POS_US.inventory values (44,’DVD’,10,sysdate);
insert into epm_NR.inventory values (55,’DVD’,10,sysdate);
insert into epm_NR.inventory values (56,’DVD’,10,sysdate);
insert into epm_NW.inventory values (57,’DVD’,10,sysdate);
################################################################################
INSERT a row from Site A
SQL> select name from v$database;
SQL> insert into POS_IN.inventory values (100,’TV’,100,sysdate);
1 row created.
SQL> commit;
Commit complete.
Check if row is replicated on Site B
SQL> select name from v$database;
SQL> select * from POS_IN.inventory;
PROD_ID PROD_CATEGORY QTY_IN_STOCK LAST_DML
———- ——————– ———— ———
100 TV 100 22-MAR-13
#################################################333
From Site B now INSERT another record
SQL> insert into POs_US.inventory values (101,’DVD’,10,sysdate);
1 row created.
SQL> commit;
Commit complete.
From Site A check if the replication has taken place
SQL> select * from POS_IN.inventory;
PROD_ID PROD_CATEGORY QTY_IN_STOCK LAST_DML
———- ——————– ———— ———
100 TV 100 22-MAR-13
101 DVD 10 22-MAR-13
################################################
1.9.2. DDL
DDL
Adding a column
SQL> alter table pos_in.inventory add (xyz number);
Table altered.
Dropping a column
SQL> alter table pos_us.inventory drop column xyz;
Table altered.
See Also: