Golden Gate Bidirectional Replication.
In this Replication each databases contains an identical data structure and user can change the data on either database at the same time and are replicated to the other database.
For Bidirection Replication we need Golden Gate components as follows:
1. Local Extract should be running on each Source database server.
2. Data Pump should be running on each Source database server.
3. Replicat should be running on each Target database server.
In my Previous blog I have created unidirectional DDL Replication, where in I have created Extract and data pump on Source database server and replicat on Target database server. Please go through the below link for the same.
http://dbarohit.blogspot.in/2013/07/golden-gate-ddl-replication.html
Now to make this post short I'll do the additional steps for configuring bidirectional replication.
At Source server.
a. Exclude the Transactions for Bidirectional Replication in Extract parameter.
GGSCI (source.cybage.com) 11> edit params SRC_EXT
extract src_ext
userid gger, password ggerexttrail dirdat/l1
ddl include mapped
tranlogoptions excludeuser gger
table SCOTT.*;
b. Data Pump for the Extract
GGSCI (source.cybage.com) 2> edit params src_dpmp
extract src_dpmp
passthru
rmthost 172.28.29.61, mgrport 7809
rmttrail dirdat/l2
table SCOTT.*;
c. Replicat over Source database server.
GGSCI (source.cybage.com) 12> edit params SRC_REP
replicat src_rep
userid gger, password gger
handlecollisions
assumetargetdefs
map SCOTT.*, target SCOTT.*;
Save the file.
GGSCI (source.cybage.com) 7> edit params ./GLOBALS
ggschema gger
checkpointtable gger.chkpt
Save the file.
GGSCI (source.cybage.com) 9> dblogin userid gger password gger
Successfully logged into database.
GGSCI (source.cybage.com) 10> add checkpointtable gger.chkpt
Successfully created checkpoint table GGER.CHKPT.
GGSCI (source.cybage.com) 11> info checkpointtable gger.chkpt
Checkpoint table GGER.CHKPT created 2013-07-26 22:37:27.
GGSCI (source.cybage.com) 12> add replicat src_rep, exttrail dirdat/l4, checkpointtable gger.chkpt
REPLICAT added.
GGSCI (source.cybage.com) 13> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING SRC_DPMP 00:00:00 00:00:05
EXTRACT RUNNING SRC_EXT 00:00:00 00:00:08
REPLICAT STOPPED SRC_REP 00:00:00 00:00:06
GGSCI (source.cybage.com) 14> start SRC_REP
Sending START request to MANAGER ...
REPLICAT SRC_REP starting
GGSCI (source.cybage.com) 15> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING SRC_DPMP 00:00:00 00:00:04
EXTRACT RUNNING SRC_EXT 00:00:00 00:00:06
REPLICAT RUNNING SRC_REP 00:00:00 00:00:03
At Target server.
SQL> @marker_setup.sql
Marker setup 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:gger
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGER
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
SQL> @ddl_setup.sql
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 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 GoldenGate schema name:gger
You will be prompted for the mode of installation.
To install or reinstall DDL replication, enter INITIALSETUP
To upgrade DDL replication, enter NORMAL
Enter mode of installation:INITIALSETUP
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 GGER as a GoldenGate schema name, INITIALSETUP as a mode of installation.
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 GGER
DDLORA_GETTABLESPACESIZE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
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 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
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/sdb1/db1/trace/ggs_ddl_trace.log
Analyzing installation status...
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:gger
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 gger;
Grant succeeded.
GGSCI (target.cybage.com) 37> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING TAR_REP 00:00:00 00:00:04
Replicat is already configured which is as similar as unidirectional setup configured in my previous post.
Now I'll configure Extract and Data pump at Target database .
GGSCI (target.cybage.com) 2> edit params tar_ext
extract tar_ext
userid gger, password gger
exttrail dirdat/l3
ddl include mapped
tranlogoptions excludeuser gger
table SCOTT.*;
Save the file.
GGSCI (target.cybage.com) 4> add extract tar_ext, tranlog, begin now
EXTRACT added.
GGSCI (target.cybage.com) 5> add exttrail dirdat/l3, extract tar_ext
EXTTRAIL added.
GGSCI (target.cybage.com) 9> start tar_ext
Sending START request to MANAGER ...
EXTRACT TAR_EXT starting
GGSCI (target.cybage.com) 10> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING TAR_EXT 00:00:00 00:00:05
REPLICAT RUNNING TAR_REP 00:00:00 00:00:06
GGSCI (target.cybage.com) 11> edit params tar_dpmp
extract tar_dpmp
passthru
rmthost 172.28.29.56, mgrport 7809
rmttrail dirdat/l4
table SCOTT.*;
Save the file.
GGSCI (target.cybage.com) 12> add extract tar_dpmp, exttrailsource dirdat/l3
EXTRACT added.
GGSCI (target.cybage.com) 13> add rmttrail dirdat/l4, extract tar_dpmp
RMTTRAIL added.
GGSCI (target.cybage.com) 14> start tar_dpmp
Sending START request to MANAGER ...
EXTRACT TAR_DPMP starting
GGSCI (target.cybage.com) 15> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING TAR_DPMP 00:00:00 00:00:43
EXTRACT RUNNING TAR_EXT 00:00:00 00:00:05
REPLICAT RUNNING TAR_REP 00:00:00 00:00:09
Now the Golden Gate Bidirectional replication configuration is done.
Lets Test the Extract and Replication Test on both the Source and Target database server.
For this I'll create the test table on both the servers and will check on each other.
At Source.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
EXT TABLE
GG TABLE
REP TABLE
SALGRADE TABLE
TEST TABLE
8 rows selected.
SQL> create table cyb as
2 select * from emp;
Table created.
SQL> insert into cyb
2 select * from emp;
14 rows created.
SQL> select count(*) from cyb;
COUNT(*)
----------
28
SQL> conn /as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
At Target.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
CYB TABLE
DEPT TABLE
EMP TABLE
EXT TABLE
GG TABLE
REP TABLE
SALGRADE TABLE
TEST TABLE
9 rows selected.
SQL> select count(*) from cyb;
COUNT(*)
----------
28
Lets create the table on Target database server and check the same on Source server.
SQL> create table sal as
2 select * from test;
Table created.
SQL> insert into sal
2 select * from test;
14 rows created.
SQL> select count(*) from sal;
COUNT(*)
----------
28
SQL> conn /as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
At Source .
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
CYB TABLE
DEPT TABLE
EMP TABLE
EXT TABLE
GG TABLE
REP TABLE
SAL TABLE
SALGRADE TABLE
TEST TABLE
10 rows selected.
SQL> select count(*) from sal;
COUNT(*)
----------
294
Thanks for your time. Cheers :)
I cannot see replicat for target database why?
ReplyDelete