In this
document, we will see the bi-directional replication using Oracle Golden Gate
12 on Oracle 12c database.
In OGG bi
directional replication, we need to understand which objects are supported and
which are not.
a. Primary Key
A Primary Key is used to identify and resolve conflicts. Make sure every
table to be included in the replication has a Primary Key.
b. Sequences are not supported in
Bi-Directional Replication. We must deploy one of the methods below to overcome
it.
i. ODD/EVEN
ii. RANGE
iii.CONCATENATE
c. Trigger causes uniqueness issues. We
must do the following to overcome them.
i. We must disable the triggers on the target or use OGG to suppress
them.
ii. Specify the DBOPTIONS SUPPRESSTRIGGERS parameter in Extract
parameter file when using GoldenGate version 10.2.0.4 or 11.2.0.2 or 12.1.2.1
d. Data Looping
In a Bi-Directional replication configuration, each side processes
transaction actively. For Insert transactions it can cause uniqueness issues
and for updates it can spawn into an infinite loop.
To avoid data looping specify the following parameters should be used in
Extract process, on both databases.
TRANLOGOPTIONS EXCLUDEUSER <GG_USER>
OR
TRANLOGOPTIONS EXCLUDEUSER <oracle_uid>
e. TRUNCATE Table
Truncate table operations are not detected by data looping. We must
truncate tables only in one database. We can accomplish it by one of the
following ways:
i. Control user access using privileges so that they can truncate tables
only in one direction.
OR
ii. Specify the parameter “GETTRUNCATE”
from source to target and “IGNORETRUNCATE”
from target to source.
f.
LAG
Oracle GoldenGate is an Asynchronous solution. It is possible that there
may be LAG at times depending on the transaction volume or network issue or
when processes are down. This LAG can cause data inconsistencies. To avoid this
situation make sure that there is very little or no LAG or have proper SLAs.
g. OGG CONFLICTS
Conflicts are very common and born to happen in Bi-Directional
Replication as Oracle GoldenGate is an Asynchronous solution.
We will see the following four different conflicts in OGG replication.
i. CONFLICT FOR INSERTS
ii. CONFLICT FOR UPDATES
iii. CONFLICT FOR DELETES
iv. CONFLICT FOR UPDATE/DELETE
h. Timestamp Column
A Primary Key alone is NOT sufficient to handle conflicts. We must use
another column or combination of columns to handle conflicts. A Timestamp
column stores the commit time of the DML. This column can be populated with the
help of Application or a Trigger. We will make use of timestamp column along
with the Primary Key column to identify and resolve conflicts.
Make sure every table part of replication have a column with timestamp
or date data type.
i. Conflict Detection And Resolution
Start with OGG version 11.2, Oracle has provided built-in CDRs. These
built-in CDRs can be used in OGG 11.2.
We will use “RESOLVECONFLICT”
parameter of MAP statement to resolve conflicts.
3.
Limitations
Ø Bi-Directional Replication works
only on Windows, UNIX/Linux.
Ø CDR works with numeric,
date/timestamp colums and char/varchar2 only.
Ø BATCHSQL is not supported in
Bi-Directional Replication.
Ø LOB , Abstract data type (ADT) and User
Defined data type (UDT) are NOT supported with CDR.
4. ENVIRONMENT DETAILS
Source
Server IP: 10.10.1.10
Database Name: ggsource
Database Version: 12.1.0.2.1
GG version: 12.1.2.1.0
Target
Server IP : 10.10.1.20
Database Name: ggtarget
Database Version: 12.1.0.2.1
GG version: 12.1.2.1.0
1. Login to edelivery.oracle.com Select
the product and version.
2.
Download GoldenGate V 12.1.2.1.0
3. On both source and target servers,
create directories to store the Golden
gate.
[oracle@TEST01 u02]$ mkdir –p /u02/app/oracle/product/gg
[oracle@TEST01 u02]$ chmod 775 /u02/app/oracle/product/gg
[oracle@TEST02 u02]$ mkdir –p /u02/app/oracle/product/gg
[oracle@TEST02 u02]$ chmod 775 /u02/app/oracle/product/gg
4. Transfer and unzip the installable
downloaded.
[oracle@TEST01 u02]$ cd /u02/app/oracle/product/gg
[oracle@TEST01 gg]$ unzip V46695.zip
[oracle@TEST02 u02]$ cd /u02/app/oracle/product/gg
[oracle@TEST02 gg]$ unzip V46695.zip
5.
Untar the tar file.
6. Create a directory for placing the
discard files.
[oracle@TEST01 gg]$ mkdir discard
[oracle@TEST02 gg]$ mkdir discard
7. Set up the path for Golden gate in
.bash_profile
export
LD_LIBRARY_PATH=/u02/app/oracle/product/12.1.0/gg:$ORACLE_HOME/lib
export ORACLE_SID=ggsource
export PATH
export GG=/u02/app/oracle/product/gg
export
LD_LIBRARY_PATH=/u02/app/oracle/product/12.1.0/gg:$ORACLE_HOME/lib
export ORACLE_SID=ggtarget
export PATH
export GG=/u02/app/oracle/product/gg
8. Now login to Golden gate and create
subdirectories on both source and target servers.
[oracle@TEST02 gg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Aug 7 2014 10:21:34
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights
reserved.
GGSCI (TEST02) 1> create subdirs;
GGSCI (TEST02) 1> show all;
Parameter settings:
SET SUBDIRS ON
SET DEBUG OFF
Current directory: /u02/app/oracle/product/gg
Using subdirectories for all process files
Editor: vi
Reports (.rpt)
/u02/app/oracle/product/gg/dirrpt
Parameters (.prm)
/u02/app/oracle/product/gg/dirprm
Replicat Checkpoints (.cpr) /u02/app/oracle/product/gg/dirchk
Extract Checkpoints (.cpe)
/u02/app/oracle/product/gg/dirchk
Process Status (.pcs)
/u02/app/oracle/product/gg/dirpcs
SQL Scripts (.sql)
/u02/app/oracle/product/gg/dirsql
Database Definitions (.def)
/u02/app/oracle/product/gg/dirdef
Dump files (.dmp)
/u02/app/oracle/product/gg/dirdmp
Masterkey wallet files (.wlt)
/u02/app/oracle/product/gg/dirwlt
Credential store files (.crd)
/u02/app/oracle/product/gg/dircrd
9. Now, let us prepare database for
Golden Gate replication.
Login to the
database with user having ALTER SYSTEM
privileges;
a) We need to put the database in archivelog mode by starting the
database in mount mode.
SQL>
startup mount
ORACLE
instance started.
Total System
Global Area 1660944384 bytes
Fixed
Size 2925072 bytes
Variable
Size 989859312 bytes
Database
Buffers 654311424 bytes
Redo
Buffers 13848576 bytes
Database
mounted.
b) SQL> alter database archivelog;
Database altered.
c) Open the database
SQL> alter
database open;
Database
altered.
d) SQL> alter database add supplemental log data;
Database
altered.
e) Now, we need to ensure redo and archive logs are having supplemental
log data.
f) Now, switch the log files.
SQL> alter
system switch logfile;
g) Confirm from the database whether supplemental logging is enabled.
SQL> select
SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
YES
h) The o/p should be either YES or IMPLICIT.
10.
Create tablespace and user for
Golden Gate administrator user.
create
tablespace ggtest datafile '/u02/app/oracle/oradata/ggsource/ggtest01.dbf' size
10M autoextend on next 5M;
create user
gg_admin identified by gg_admin default tablespace ggtest;
11.
Give necessary privileges to
the user in order to replicate particular table.
grant
connect,resource to gg_admin;
grant select
any table,select any dictionary to gg_admin;
grant create
table to gg_admin;
grant execute
on dbms_flashback to gg_admin;
grant execute
on utl_file to gg_admin;
grant insert on
<schema>.<table_name> to gg_admin;
( insert privileges to the table to be replicated)
12. Go to Golden Gate Installed location
(in our scenario /u02/app/oracle/product/gg) and then run the following Golden
Gate inbuild scripts for creating all necessary objects to support DDL
replication.
SQL>@marker_setup.sql
SQL>@DDL_setup.sql
SQL>@GGS/role_setup.sql
SQL>grant GGS_GGSUSER_ROLE to gg_admin;
SQL>@DDL_enable.sql
SQL> @marker_setup.sql
Marker setup script
You will be prompted for the name of a schema for the Oracle GoldenGate
database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:GG_ADMIN
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GG_ADMIN
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
SQL> @ddl_setup.sql
Oracle GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication...
You will be prompted for the name of a schema for the Oracle GoldenGate
database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled.
For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:gg_admin
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Checking for sessions that are holding locks on Oracle Golden Gate
metadata tables ...
Check complete.
Using GG_ADMIN as a Oracle GoldenGate schema name.
Working, please wait ...
DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GG_ADMIN
CLEAR_TRACE STATUS:
Line/pos
Error
----------------------------------------
-----------------------------------------------------------------
No errors
No errors
CREATE_TRACE STATUS:
Line/pos Error
----------------------------------------
-----------------------------------------------------------------
No errors
No errors
TRACE_PUT_LINE STATUS:
Line/pos
Error
----------------------------------------
-----------------------------------------------------------------
No errors
No errors
INITIAL_SETUP STATUS:
Line/pos
Error
----------------------------------------
-----------------------------------------------------------------
No errors
No errors
DDLVERSIONSPECIFIC PACKAGE STATUS:
Line/pos
Error
----------------------------------------
-----------------------------------------------------------------
No errors
No errors
DDLREPLICATION PACKAGE STATUS:
Line/pos
Error
----------------------------------------
-----------------------------------------------------------------
No errors
No errors
DDLREPLICATION PACKAGE BODY STATUS:
Line/pos
Error
---------------------------------------- -----------------------------------------------------------------
No errors
No errors
DDL IGNORE TABLE
-----------------------------------
OK
DDL IGNORE LOG TABLE
-----------------------------------
OK
DDLAUX PACKAGE STATUS:
Line/pos
Error
----------------------------------------
-----------------------------------------------------------------
No errors
No errors
DDLAUX PACKAGE BODY STATUS:
Line/pos Error
----------------------------------------
-----------------------------------------------------------------
No errors
No errors
SYS.DDLCTXINFO PACKAGE STATUS:
Line/pos Error
----------------------------------------
-----------------------------------------------------------------
No errors
No errors
SYS.DDLCTXINFO PACKAGE BODY
STATUS:
Line/pos
Error
----------------------------------------
-----------------------------------------------------------------
No errors
No errors
DDL HISTORY TABLE
-----------------------------------
OK
DDL HISTORY TABLE(1)
-----------------------------------
OK
DDL DUMP TABLES
-----------------------------------
OK
DDL DUMP COLUMNS
-----------------------------------
OK
DDL DUMP LOG GROUPS
-----------------------------------
OK
DDL DUMP PARTITIONS
-----------------------------------
OK
DDL DUMP PRIMARY KEYS
-----------------------------------
OK
DDL SEQUENCE
-----------------------------------
OK
GGS_TEMP_COLS
-----------------------------------
OK
GGS_TEMP_UK
-----------------------------------
OK
DDL TRIGGER CODE STATUS:
Line/pos
Error
----------------------------------------
-----------------------------------------------------------------
No errors
No errors
DDL TRIGGER INSTALL STATUS
-----------------------------------
OK
DDL TRIGGER RUNNING STATUS
------------------------------------------------------------------------------------------------------------------------
ENABLED
STAYMETADATA IN TRIGGER
------------------------------------------------------------------------------------------------------------------------
OFF
DDL TRIGGER SQL TRACING
------------------------------------------------------------------------------------------------------------------------
0
DDL TRIGGER TRACE LEVEL
------------------------------------------------------------------------------------------------------------------------
0
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/product/12.1.0/dbhome_1/rdbms/log/ggs_ddl_trace.log
Analyzing installation status...
VERSION OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
SQL> @role_setup.sql
GGS Role setup script
This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the
params.sql script to change the gg_role parameter to the preferred name. (Do
not run the script.)
You will be prompted for the name of a schema for the GoldenGate
database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:gg_admin
Wrote file role_setup_set.txt
PL/SQL procedure successfully completed.
Role setup script complete
Grant this role to each user assigned to the Extract, GGSCI, and Manager
processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO <loggedUser>
where <loggedUser> is the user assigned to the GoldenGate
processes.
-------
SQL> GRANT GGS_GGSUSER_ROLE TO
gg_admin;
Grant succeeded.
SQL> @ddl_enable.sql
Trigger altered.
13. Now, to set up bi-directional
replication, we need the following.
On Site GGSOURCE, we will have the following groups created
• Extract – ext1
• Data Pump – dpump1
• Replicat – rep1
On Site GGTARGET, we will have the following groups created
• Extract – ext2
• Data Pump – dpump2
• Replicat – rep2
On Site GGSOURCE, we have the following trails set up
• aa – local extract trail which will be written to by ext1
• ab – remote trail which will be processed by data pump extract group
dpump1. This will be shipped over the network to Site GGTARGET
On Site GGTARGET, we have the following trails set up
• ac – local extract trail which will be written to by ext2
• ad – remote trail which will be processed by data pump extract group
dpump2. This will be shipped over the network to Site GGSOURCE
14. Now, we need to find the tables
which need to be replicated. For this, we will create a user GGBI and create a
table under GGBI.
On source and target, create a user as below.
SQL> create user ggbi identified by ggbi;
On Source and target database, create a table GGEMP under GGBI.
SQL> create table ggemp
(emp_id number,
emp_name_name varchar2(20),
mgr number,
last_dml timestamp default systimestamp);
SQL> alter
table ggemp add constraint pk_ggemp primary key (emp_id) ;
Table altered.
SQL> grant
all on ggemp to gg_admin;
Grant
succeeded.
SQL> CREATE
OR REPLACE TRIGGER GGEMP_CDR_TRG
BEFORE UPDATE
or insert
ON GGBI.GGEMP
REFERENCING NEW
AS New OLD AS Old
FOR EACH ROW
BEGIN
IF SYS_CONTEXT
('USERENV', 'SESSION_USER') != 'GG_ADMIN'
THEN
:NEW.LAST_DML
:= SYSTIMESTAMP;
END IF;
END;
/
15.
Create the extract (EXT1) and
data pump (DPUMP1) on Site GGSOURCE
GGSCI (TEST01)
5> add extract ext1 tranlog begin now
EXTRACT added.
GGSCI (TEST01)
6> add exttrail /u02/app/oracle/product/gg/dirdat/aa extract ext1
EXTTRAIL added.
GGSCI (TEST01)
8> add extract dpump1 exttrailsource /u02/app/oracle/product/gg/dirdat/aa
EXTRACT added.
GGSCI (TEST01)
9> add rmttrail /u02/app/oracle/product/gg/dirdat/ab extract dpump1
RMTTRAIL added.
GGSCI (TEST01)
10> edit params ext1
EXTRACT ext1
USERID
gg_admin, PASSWORD gg_admin
EXTTRAIL
/u02/app/oracle/product/gg/dirdat/aa
TRANLOGOPTIONS
EXCLUDEUSER gg_admin
TABLE
ggbi.ggemp,
GETBEFORECOLS (
ON UPDATE
KEYINCLUDING (emp_name_name,mgr,last_dml),
ON DELETE
KEYINCLUDING (emp_name_name,mgr,last_dml));
GGSCI (TEST01)
11> edit params dpump1
EXTRACT dpump1
USERID
gg_admin, PASSWORD gg_admin
RMTHOST 10.10.1.20,
MGRPORT 7809, TCPBUFSIZE 100000
RMTTRAIL
/u02/app/oracle/product/gg/dirdat/ab
PASSTHRU
TABLE
ggbi.ggemp;
16.
On GGTARGET site, add replicat
(REP2)
GGSCI (TEST02)
1> add replicat rep2 exttrail /u02/app/oracle/product/gg/dirdat/ab
REPLICAT added.
GGSCI (TEST02)
2> edit params rep2
REPLICAT rep2
ASSUMETARGETDEFS
USERID
gg_admin, PASSWORD gg_admin
DISCARDFILE
/u02/app/oracle/product/gg/discard/discardrep2.txt, append,
MAP ggbi.ggemp,
TARGET ggbi.ggemp;
17.
On GGTARGET, create the extract
(EXT2) and data pump (DPUMP2)
GGSCI (TEST02)
3> add extract ext2 tranlog begin now
EXTRACT added.
GGSCI (TEST02)
4> add exttrail /u02/app/oracle/product/gg/dirdat/ac extract ext2
EXTTRAIL added.
GGSCI (TEST02)
5> add extract dpump2 exttrailsource /u02/app/oracle/product/gg/dirdat/ac
EXTRACT added.
GGSCI (TEST02)
6> add rmttrail /u02/app/oracle/product/gg/dirdat/ad extract dpump2
RMTTRAIL added.
GGSCI (TEST02)
7> edit params ext2
EXTRACT ext2
USERID
gg_admin, PASSWORD gg_admin
EXTTRAIL
/u02/app/oracle/product/gg/dirdat/ac
TRANLOGOPTIONS
EXCLUDEUSER gg_admin
TABLE
ggbi.ggemp,
GETBEFORECOLS (
ON UPDATE KEYINCLUDING
(emp_name_name,mgr,last_dml),
ON DELETE
KEYINCLUDING (emp_name_name,mgr,last_dml));
GGSCI (TEST02)
8> edit params dpump2
EXTRACT dpump2
USERID
gg_admin, PASSWORD gg_admin
RMTHOST 10.10.1.10,
MGRPORT 7809, TCPBUFSIZE 100000
RMTTRAIL /u02/app/oracle/product/gg/dirdat/ad
PASSTHRU
TABLE
ggbi.ggemp;
18.
On GGSOURCE site, add replicat
(REP1)
GGSCI (TEST01)
8> add replicat rep1 exttrail /u02/app/oracle/product/gg/dirdat/ad
REPLICAT added
GGSCI (TEST02)
9> edit params rep1
REPLICAT rep1
ASSUMETARGETDEFS
USERID
gg_admin, PASSWORD gg_admin
DISCARDFILE
/u02/app/oracle/product/gg/discard/discardrep1.txt, append,
MAP ggbi.ggemp,
TARGET ggbi.ggemp;
19.
On both GGSOURCE and GGTARGET
sites , add trandata .
GGSCI (TEST01)
12> dblogin userid gg_admin,password
gg_admin
Successfully
logged into database.
GGSCI (TEST01
as gg_admin@ggsource) 14> add
trandata ggbi.ggemp cols(emp_name_name,mgr,last_dml)
Logging of
supplemental redo data enabled for table GGBI.GGEMP.
TRANDATA for
scheduling columns has been added on table 'GGBI.GGEMP'.
GGSCI (TEST01
as gg_admin@ggsource) 15> info
trandata ggbi.ggemp
Logging of
supplemental redo log data is enabled for table GGBI.GGEMP.
Columns
supplementally logged for table GGBI.GGEMP: EMP_ID, EMP_NAME_NAME, LAST_DML,
MGR.
GGSCI (TEST02)
10> dblogin userid gg_admin,password gg_admin
Successfully
logged into database.
GGSCI (TEST02
as gg_admin@ggtarget) 11> add trandata ggbi.ggemp cols(emp_name_name,mgr,last_dml)
Logging of
supplemental redo data enabled for table GGBI.GGEMP.
TRANDATA for
scheduling columns has been added on table 'GGBI.GGEMP'.
GGSCI (TEST02
as gg_admin@ggtarget) 12> info
trandata ggbi.ggemp
Logging of
supplemental redo log data is enabled for table GGBI.GGEMP.
Columns
supplementally logged for table GGBI.GGEMP: EMP_ID, EMP_NAME_NAME, LAST_DML,
MGR.
20.
Start the Extract and Data Pump
process in GGSOURCE
GGSCI (TEST01
as gg_admin@ggsource) 16> start
extract ext1
Sending START
request to MANAGER ...
EXTRACT EXT1
starting
GGSCI (TEST01
as gg_admin@ggsource) 17> start
extract dpump1
Sending START
request to MANAGER ...
EXTRACT DPUMP1
starting
GGSCI (TEST01
as gg_admin@ggsource) 18> info
extract ext1
EXTRACT EXT1
Last Started 2014-11-17
23:35 Status RUNNING
Checkpoint
Lag 00:00:00 (updated 00:00:04 ago)
Process ID 30100
Log Read
Checkpoint Oracle Redo Logs
2014-11-17 23:35:50 Seqno 299, RBA 21294592
SCN 0.5422658 (5422658)
GGSCI (TEST01
as gg_admin@ggsource) 19> info
extract dpump1
EXTRACT DPUMP1
Last Started 2014-11-17 23:35
Status RUNNING
Checkpoint
Lag 00:00:00 (updated 00:00:07 ago)
Process ID 30109
Log Read
Checkpoint File
/u02/app/oracle/product/gg/dirdat/aa000000
First Record RBA 0
GGSCI (TEST01
as gg_admin@ggsource) 20> info all
Program Status
Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING
DPUMP1 00:00:00 00:00:00
EXTRACT RUNNING
EXT1 00:00:00 00:00:01
REPLICAT STOPPED
REP1 00:00:00 00:00:44
21.
Start the Extract and Data Pump
process in GGTARGET
GGSCI (TEST02
as gg_admin@ggtarget) 13> start extract ext2
Sending START
request to MANAGER ...
EXTRACT EXT2
starting
GGSCI (TEST02
as gg_admin@ggtarget) 14> start extract dpump2
Sending START
request to MANAGER ...
EXTRACT DPUMP2
starting
GGSCI (TEST02
as gg_admin@ggtarget) 15> info all
Program Status
Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING
DPUMP2 00:00:00 00:13:38
EXTRACT RUNNING
EXT2 00:00:00 00:00:00
REPLICAT STOPPED
REP2 00:00:00 00:17:21
22.
On GGSOURCE, start the replicat
(REP1) process.
GGSCI (TEST01
as gg_admin@ggsource) 23> start replicat rep1
Sending START
request to MANAGER ...
REPLICAT REP1
starting
GGSCI (TEST01
as gg_admin@ggsource) 24> info all
Program Status
Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING
DPUMP1 00:00:00 00:00:08
EXTRACT RUNNING
EXT1 00:00:00 00:00:06
REPLICAT RUNNING
REP1 00:00:00 00:00:04
23.
On GGTARGET , add
checkpointtable and start the replicat
(REP2) process .
GGSCI (TEST02
as gg_admin@ggtarget) 22> add
checkpointtable gg_admin.ckptab
Successfully
created checkpoint table gg_admin.ckptab.
GGSCI (TEST02
as gg_admin@ggtarget) 23> start replicat rep2
Sending START
request to MANAGER ...
REPLICAT REP2
starting
GGSCI (TEST02
as gg_admin@ggtarget) 24> info all
Program Status
Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING
DPUMP2 00:00:00 00:00:07
EXTRACT RUNNING
EXT2 00:00:00 00:00:05
REPLICAT RUNNING
REP2 00:00:00 00:00:02
Test Scenario 1
On GGSOURCE, inserted a row and found
that the row is replicated on GGTARGET.
SQL> select name from v$database;
NAME
---------
GGSOURCE
SQL> insert into ggemp values
(1,'goldengate',100,sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from ggemp;
EMP_ID EMP_NAME_NAME
MGR LAST_DML
---------- --------------------
----------
---------------------------------------------------------------------------
1 goldengate 100
17-NOV-14 11.50.24.659428 PM
SQL> select name from v$database;
NAME
---------
GGTARGET
SQL> select * from ggemp;
EMP_ID EMP_NAME_NAME
MGR LAST_DML
---------- --------------------
----------
---------------------------------------------------------------------------
1 goldengate 100
17-NOV-14 11.50.24.659428 PM
Test Scenario 2
On GGTARGET, we are inserting a row ,
this row should be replicated on GGSOURCE.
SQL>
insert into ggemp values (2,'oracle',101,sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from ggemp;
EMP_ID EMP_NAME_NAME
MGR LAST_DML
---------- --------------------
----------
---------------------------------------------------------------------------
1 goldengate 100
17-NOV-14 11.50.24.659428 PM
2 oracle 101
17-NOV-14 11.50.55.909332 PM
SQL> select name from v$database;
NAME
---------
GGSOURCE
SQL> select * from ggemp;
EMP_ID EMP_NAME_NAME
MGR LAST_DML
---------- --------------------
----------
---------------------------------------------------------------------------
1 goldengate 100
17-NOV-14 11.50.24.659428 PM
2 oracle 101
17-NOV-14 11.50.55.909332 PM
CONFLICT DETECTION and RESOLUTION
In the above steps, we have finished
setting up normal Bi-directional replication. Now, we need to set up CONFLICT
DETECTION and various resolution methods for the same.
To summarize, we have the following in
GGSOURCE.
I.
Extract Process EXT1
II.
Data Pump Process DPUMP1
III.
Replicat Process REP1
On GGTARGET, we have
I.
Extract Process EXT2
II.
Data pump Process DPUMP2
III.
Replicat Process REP2
For CDR, we need to use following
parameters in Golden Gate.
TRANLOGOPTIONS
EXCLUDEUSER <GGUSER> - To prevent
capture of SQL that is applied by Replicat to other database in Bi-directional
replication. This will avoid all transactions generated by GGUSER.
GETBEFORECOLS – This parameter
is used in Extract file for specific columns to capture the before image we
want to capture and written to trail file upon update or delete operation.
KEYINCLUDING
– It specifies to capture before image of the
primary key and also the specified column/s
On Replicat side, we need 2 parameters,
COMPARECOLS – It specifies the columns
that replicat uses to deted update or delete conflicts.
RESOLVECONFLICT – This will be used in MAP statement to different resolutions for
different conflict resolutions.
Ø In our setup, we have 2 databases ( GGSOURCE and GGTARGET )and we
need to have the changes to be replicated from both the sites.
Ø Data Pump and Extract Trail file setup remains same as per the setup
for Active-Active replication without CDR.
On GGSOURCE
1.
The extract (EXT1) parameter
file will be follows.
GGSCI (TEST01)
5> edit params ext1
EXTRACT ext1
USERID
gg_admin, PASSWORD gg_admin
EXTTRAIL
/u02/app/oracle/product/gg/dirdat/aa
TRANLOGOPTIONS
EXCLUDEUSER gg_admin
TABLE
ggbi.ggemp,
GETBEFORECOLS (
ON UPDATE
KEYINCLUDING (emp_name_name,mgr,last_dml),
ON DELETE
KEYINCLUDING (emp_name_name,mgr,last_dml));
2.
The Replicat process on GGSOURCE
server will be as follows.
GGSCI (TEST01)
9> edit params rep1
REPLICAT rep1
ASSUMETARGETDEFS
USERID
gg_admin, PASSWORD gg_admin
DISCARDFILE
/u02/app/oracle/product/gg/discard/discardrep1.txt, append,
MAP
ggbi.ggemp, TARGET ggbi.ggemp,
COMPARECOLS
(ON UPDATE ALL, ON DELETE ALL),
RESOLVECONFLICT
(UPDATEROWEXISTS, (DEFAULT, USEMAX (DML_TIMESTAMP))),
RESOLVECONFLICT
(INSERTROWEXISTS, (DEFAULT, USEMAX (DML_TIMESTAMP))),
RESOLVECONFLICT
(DELETEROWEXISTS, (DEFAULT, IGNORE)),
RESOLVECONFLICT
(UPDATEROWMISSING, (DEFAULT, OVERWRITE)),
RESOLVECONFLICT
(DELETEROWMISSING, (DEFAULT, DISCARD));
On GGTARGET
3.
The extract (ext2) parameter
file will be as follows.
GGSCI (TEST02)
2> edit params ext2
EXTRACT ext2
USERID
gg_admin, PASSWORD gg_admin
EXTTRAIL
/u02/app/oracle/product/gg/dirdat/ac
TRANLOGOPTIONS
EXCLUDEUSER gg_admin
TABLE
ggbi.ggemp,
GETBEFORECOLS (
ON UPDATE
KEYINCLUDING (emp_name_name,mgr,last_dml),
ON DELETE
KEYINCLUDING (emp_name_name,mgr,last_dml));
4.
Replicat process on GGTARGET
database will be as follows.
GGSCI (TEST02)
3> edit params rep2
REPLICAT rep2
ASSUMETARGETDEFS
USERID
gg_admin, PASSWORD gg_admin
DISCARDFILE
/u02/app/oracle/product/gg/discard/discardrep2.txt, append,
MAP ggbi.ggemp,
TARGET ggbi.ggemp,
COMPARECOLS (ON
UPDATE ALL, ON DELETE ALL),
RESOLVECONFLICT
(UPDATEROWEXISTS, (DEFAULT, USEMAX (DML_TIMESTAMP))),
RESOLVECONFLICT
(INSERTROWEXISTS, (DEFAULT, USEMAX (DML_TIMESTAMP))),
RESOLVECONFLICT
(DELETEROWEXISTS, (DEFAULT, IGNORE)),
RESOLVECONFLICT
(UPDATEROWMISSING, (DEFAULT, OVERWRITE)),
RESOLVECONFLICT
(DELETEROWMISSING, (DEFAULT, DISCARD));
Now, we have
implemented Bi-directional replication with conflict detection and the updated
row should have latest value using DML_TIMESTAMP and in case of any row
missing, then overwrite or any row is missing, it should discard while
deleting.
Test Scenario 3
Let us update the row from both GGSOURCE
and GGTARGET with different values and see the latest values are updated.
On GGTARGET, I am updating the MGR
column from 106 to 108 and in GGSOURCE, I am updating from 106 to 107 at same
time, the values in both the database should be 108 as GGTARGET is having
latest timestamp.
On GGSOURCE,
SQL>
select * from ggemp;
EMP_ID EMP_NAME_NAME
MGR LAST_DML
---------- --------------------
---------- ---------------------------------------------------------------------------
1 goldengate 100
17-NOV-14 11.50.24.659428 PM
2 oracle 102
18-NOV-14 05.14.21.405412 PM
3 ggtest 106
18-NOV-14 06.06.54.695195 PM
SQL> update ggemp set mgr=108 where
emp_id=3;
1 row updated.
SQL> commit;
Commit complete.
On GGTARGET
SQL> select * from ggemp;
EMP_ID EMP_NAME_NAME MGR LAST_DML
----------
-------------------- ---------- ---------------------------------------------------------------------------
1 goldengate 100 17-NOV-14 11.50.24.659428
PM
2 oracle 102 18-NOV-14
05.14.21.405412 PM
3 ggtest 106 18-NOV-14 06.06.54.695195 PM
SQL> update
ggemp set mgr=108 where emp_id=3;
1 row updated.
SQL>
commit;
Commit
complete.
Result
Now, the
output of GGSOURCE and GGTARGET shows the following.
SQL> select
* from ggemp;
EMP_ID EMP_NAME_NAME MGR LAST_DML
----------
-------------------- ----------
---------------------------------------------------------------------------
1 goldengate 100 17-NOV-14 11.50.24.659428
PM
2 oracle 102 18-NOV-14 05.14.21.405412 PM
3 ggtest 108 18-NOV-14
06.07.43.392792 PM
Test
Scenario 4
Now, I am
going to delete same record from both the sites.
a. Initially, I am deleting from GGSOURCE and GGTARGET at same time and
commiting.
On GGSOURCE
SQL> select * from ggemp;
EMP_ID EMP_NAME_NAME MGR LAST_DML
---------- -------------------- ----------
---------------------------------------------------------------------------
1 goldengate 100 17-NOV-14 11.50.24.659428
PM
4 ggoracle 110 18-NOV-14
06.42.20.953194 PM
3 ggtest 108 18-NOV-14
06.07.43.392792 PM
SQL> delete from ggemp where emp_id=4;
1 row deleted.
SQL> commit;
Commit complete.
On GGTARGET
SQL>
delete from ggemp where emp_id=4;
1
row deleted.
SQL>
commit;
Now, since GGSOURCE timestamp is proceeded earlier and deleted the
record, the other delete statement from GGTARGET got discarded.
This can be seen in the trail file.
Operation: 3 Error: 1403
Operation failed at seqno 0 rba 3340
Discarding record on CDR DISCARD resolution
Record not found
Aborted
delete from GGBI.GGEMP to GGBI.GGEMP (target format)...
*
EMP_ID = 4
000000: 34
|4 |
EMP_NAME_NAME = ggoracle
000000: 67 67 6f 72 61 63 6c 65
|ggoracle |
