Configure Goldengate DDL replication between Oracle Databases
Environment Used for the excersie:
Source database : SRC
Target Database : TRGT
Listener Port : 1521
Source Schema : ggsender
Target Schema : ggreceiver
Source Manager Port : 7809
Target Manager Port : 7809
GG_HOME=/opt/oracle/gg/12.1.2 –> You may refer my earlier post for installation steps
ORACLE_HOME=/app/oracle/product/12.1.0
ORACLE Version : 12.1.0
GoldenGate Version : 12.1.2
Section A: Prepare Source and Target Database for replication
Step 1: Create Oracle Net services for source and target databases
Source TNS:
SRC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ggddltest.home.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME=SRC)
)
)
Target TNS:
TRGT =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ggddltest.home.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME=SRC)
)
)
Verify TNS Ping
[oracle@ggddltest 12.1.2]$ tnsping SRC
TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 09-AUG-2014 02:27:08
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/app/oracle/product/12.1.0/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ggddltest.home.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME=SRC)))
OK (0 msec)
[oracle@ggddltest 12.1.2]$ tnsping TRGT
TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 09-AUG-2014 02:27:11
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/app/oracle/product/12.1.0/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ggddltest.home.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME=TRGT)))
OK (0 msec)
Step 2: Enable Database Force Logging in source database
SQL:SRC >select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
SQL:SRC >alter database force logging;
Database altered.
SQL:SRC >select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
Step 3: Enable supplemental logging in source database
SQL:SRC >select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
NO
SQL:SRC >alter database add supplemental log data;
Database altered.
SQL:SRC >select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
YES
Step 4: Create a GoldenGate user for source and target database
Source:
SQL:SRC >create tablespace gg_data datafile '+DATA' size 100m;
Tablespace created.
SQL:SRC >create user ggate identified by ggate default tablespace gg_data;
User created.
SQL:SRC >grant connect,resource, DBA to ggate;
Grant succeeded.
Target:
SQL:TRGT >create tablespace gg_data datafile '+DATA' size 100m;
Tablespace created.
SQL:TRGT >create user ggate identified by ggate default tablespace gg_data;
User created.
SQL:TRGT >grant connect,resource, DBA to ggate;
Grant succeeded.
Step 5: Execute necessary GoldenGate scripts to enable DDL replication in source and target database
Source:
[oracle@ggddltest ~]$ cd $GG_HOME
[oracle@ggddltest 12.1.2]$ pwd
/opt/oracle/gg/12.1.2
[oracle@ggddltest 12.1.2]$ sqlplus
SQL*Plus: Release 12.1.0.1.0 Production on Sat Aug 9 00:23:43 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL:SRC >@marker_setup.sql
-- Enter Oracle GoldenGate schema name:ggate
SQL:SRC >@ddl_setup.sql
-- Enter Oracle GoldenGate schema name:ggate
SQL:SRC >@role_setup.sql
-- Enter GoldenGate schema name:ggate
SQL:SRC >GRANT GGS_GGSUSER_ROLE TO ggate;
Grant succeeded.
SQL:SRC >@ddl_enable.sql
Trigger altered.
Target:
[oracle@ggddltest 12.1.2]$ cd $GG_HOME
[oracle@ggddltest 12.1.2]$ pwd
/opt/oracle/gg/12.1.2
[oracle@ggddltest 12.1.2]$ sqlplus
SQL*Plus: Release 12.1.0.1.0 Production on Sat Aug 9 00:26:40 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL:TRGT >@marker_setup.sql
-- Enter Oracle GoldenGate schema name:ggate
SQL:TRGT >@ddl_setup.sql
-- Enter Oracle GoldenGate schema name:ggate
SQL:TRGT >@role_setup.sql
-- Enter GoldenGate schema name:ggate
SQL:TRGT >GRANT GGS_GGSUSER_ROLE TO ggate;
Grant succeeded.
SQL:TRGT >@ddl_enable.sql
Trigger altered.
Step 6: Create the source and target database Schemas which are to be replicated
Source:
SQL:SRC >create user ggsender identified by ggsender;
User created.
SQL:SRC >grant connect,resource,unlimited tablespace to ggsender;
Grant succeeded.
Target:
SQL:TRGT >create user ggreceiver identified by ggreceiver;
User created.
SQL:TRGT >grant connect,resource,unlimited tablespace to ggreceiver;
Grant succeeded.
Step 7: Link the LD_LIBARY_PATH to point to GG_HOME and $ORACLE_HOME/lib
[oracle@ggddltest 12.1.2]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$GG_HOME
[oracle@ggddltest 12.1.2]$ echo $LD_LIBRARY_PATH
/app/oracle/product/12.1.0/lib:/opt/oracle/gg/12.1.2
Section B: Configure replication in GoldenGate
Step 1: Create and start GoldenGate Manager process
GGSCI (ggddltest.home.com) >edit params mgr
-- Port used for extract and replicate process communication ---
-- Manager process manages all the other GoldenGate processes ----
PORT 7809
GGSCI (ggddltest.home.com) >view params mgr
PORT 7809
GGSCI (ggddltest.home.com) > start mgr
Manager started.
GGSCI (ggddltest.home.com) > info manager
Manager is running (IP port ggddltest.home.com.7809, Process ID 7827).
Step 2: Add an extract (Capture) process for source database
GGSCI (ggddltest.home.com) >add extract xtct_s, tranlog, begin now
EXTRACT added.
GGSCI (ggddltest.home.com) >edit params xtct_s
-- Process group ---
extract xtct_s
--- Source database login info ----
userid ggate@SRC, password ggate
--- Required to support ASM ----
TRANLOGOPTIONS DBLOGREADER
--- Target Host and Manager Port for communication ----
rmthost ggddltest.home.com, mgrport 7809
--- Target Host trail files location ----------
rmttrail /opt/oracle/gg/12.1.2/dirdat/TRGT/rt
--- DDL Support --
ddl include mapped objname ggsender.*
--- Objects to be replicated ------
TABLE ggsender.*;
GGSCI (ggddltest.home.com) >view params xtct_s
extract xtct_s
userid ggate@SRC, password ggate
TRANLOGOPTIONS DBLOGREADER
rmthost ggddltest.home.com, mgrport 7809
rmttrail /opt/oracle/gg/12.1.2/dirdat/TRGT/rt
ddl include mapped objname ggsender.*
TABLE ggsender.*;
GGSCI (ggddltest.home.com) >add rmttrail /opt/oracle/gg/12.1.2/dirdat/TRGT/rt, extract xtct_s
RMTTRAIL added.
Step 3: Add Replicat (Delivery) Process for target database
GGSCI (ggddltest.home.com) >add replicat rpct_t, exttrail /opt/oracle/gg/12.1.2/dirdat/TRGT/rt, checkpointtable ggate.SRC_ckpt
REPLICAT added.
GGSCI (ggddltest.home.com) >dblogin userid ggate@TRGT, password ggate
Successfully logged into database.
GGSCI (ggddltest.home.com) >add checkpointtable ggate.SRC_ckpt
Successfully created checkpoint table ggate.SRC_ckpt.
GGSCI (ggddltest.home.com) >edit params rpct_t
-- Replicat process group ---
replicat rpct_t
-- Target database login info ---
userid ggate@TRGT, password ggate
-- Assuming same structure for source and target tables ---
assumetargetdefs
-- DDL Support ---
DDL
--- Mapping source and target schema for replication ---
MAP ggsender.*, TARGET ggreceiver.*;
GGSCI (ggddltest.home.com) >view params rpct_t
replicat rpct_t
userid ggate@TRGT, password ggate
assumetargetdefs
DDL
MAP ggsender.*, TARGET ggreceiver.*;
Step 4: Start Replication
GGSCI (ggddltest.home.com) >start extract XTCT_S
Sending START request to MANAGER ...
EXTRACT XTCT_S starting
GGSCI (ggddltest.home.com) >start replicat RPCT_T
Sending START request to MANAGER ...
REPLICAT RPCT_T starting
GGSCI (ggddltest.home.com) >info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING XTCT_S 00:00:00 00:00:00
REPLICAT RUNNING RPCT_T 00:00:00 00:00:05
Step 5: Verify replication is working
Source:
SQL:SRC >conn ggsender/ggsender@SRC
Connected.
SQL:SRC >create table test_ggsender
2 (
3 id number(3),
4 name varchar(10)
5 );
Table created.
SQL:SRC >insert into test_ggsender values (100,'abc');
1 row created.
SQL:SRC >commit ;
SQL:SRC >conn ggreceiver/ggreceiver@TRGT
Connected.
Target
SQL:TRGT >select table_name from user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
TEST_ggsender
SQL:TRGT >select * from TEST_ggsender;
ID NAME
---------- ----------
100 abc
ORACLE REFERENCE : https://blogs.oracle.com/imc/entry/oracle_goldengate_configuring_ddl_replicationhttps://blogs.oracle.com/imc/entry/oracle_goldengate_configuring_ddl_replication
Other reference : https://blogs.oracle.com/imc/entry/oracle_goldengate_configuring_ddl_replication
No comments:
Post a Comment