LATEST TOPICS

Configure Goldengate DDL replication between Oracle Databases

Environment Used for the excersie:

Source database : LABPA
Target Database : LABPABR

Listener Port : 1521

Source Schema : sender
Target Schema : receiver

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:

LABPA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = labserver.home.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME=LABPA)
)
)

Target TNS:

LABPABR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = labserver.home.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME=LABPA)
)
)

Verify TNS Ping

[oracle@labserver 12.1.2]$ tnsping labpa

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 = labserver.home.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME=LABPA)))
OK (0 msec)

[oracle@labserver 12.1.2]$ tnsping labpabr

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 = labserver.home.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME=LABPABR)))
OK (0 msec)

 

Step 2: Enable Database Force Logging in source database

SQL:labpa >select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
NO

SQL:labpa >alter database force logging;

Database altered.

SQL:labpa >select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

 

Step 3: Enable supplemental logging in source database

SQL:labpa >select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME
--------
NO

SQL:labpa >alter database add supplemental log data;

Database altered.

SQL:labpa >select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME
--------
YES

 

Step 4: Create a GoldenGate user for source and target database

 

Source:

SQL:labpa >create tablespace gg_data datafile '+DATA' size 100m;

Tablespace created.

SQL:labpa >create user ggate identified by ggate default tablespace gg_data;

User created.

SQL:labpa >grant connect,resource, DBA to ggate;

Grant succeeded.

Target:

SQL:labpabr >create tablespace gg_data datafile '+DATA' size 100m;

Tablespace created.

SQL:labpabr >create user ggate identified by ggate default tablespace gg_data;

User created.

SQL:labpabr >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@labserver ~]$ cd $GG_HOME
[oracle@labserver 12.1.2]$ pwd
/opt/oracle/gg/12.1.2
[oracle@labserver 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:labpa >@marker_setup.sql

-- Enter Oracle GoldenGate schema name:ggate

SQL:labpa >@ddl_setup.sql
-- Enter Oracle GoldenGate schema name:ggate

SQL:labpa >@role_setup.sql

-- Enter GoldenGate schema name:ggate

SQL:labpa >GRANT GGS_GGSUSER_ROLE TO ggate;

Grant succeeded.

SQL:labpa >@ddl_enable.sql

Trigger altered.

Target:

[oracle@labserver 12.1.2]$ cd $GG_HOME
[oracle@labserver 12.1.2]$ pwd
/opt/oracle/gg/12.1.2
[oracle@labserver 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:labpabr >@marker_setup.sql

-- Enter Oracle GoldenGate schema name:ggate

SQL:labpabr >@ddl_setup.sql

-- Enter Oracle GoldenGate schema name:ggate

SQL:labpabr >@role_setup.sql

-- Enter GoldenGate schema name:ggate

SQL:labpabr >GRANT GGS_GGSUSER_ROLE TO ggate;

Grant succeeded.

SQL:labpabr >@ddl_enable.sql

Trigger altered.

 

Step 6: Create the source and target database Schemas which are to be replicated

 

Source:

SQL:labpa >create user sender identified by sender;

User created.

SQL:labpa >grant connect,resource,unlimited tablespace to sender;

Grant succeeded.

Target:

SQL:labpabr >create user receiver identified by receiver;

User created.

SQL:labpabr >grant connect,resource,unlimited tablespace to receiver;

Grant succeeded.

 

Step 7: Link the LD_LIBARY_PATH to point to GG_HOME and $ORACLE_HOME/lib

[oracle@labserver 12.1.2]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$GG_HOME
[oracle@labserver 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 (labserver.home.com) >edit params mgr
-- Port used for extract and replicate process communication ---

-- Manager process manages all the other GoldenGate processes ----

PORT 7809

GGSCI (labserver.home.com) >view params mgr

PORT 7809

GGSCI (labserver.home.com) > start mgr
Manager started.
GGSCI (labserver.home.com) > info manager

Manager is running (IP port labserver.home.com.7809, Process ID 7827).

 

Step 2: Add an extract (Capture) process for source database

GGSCI (labserver.home.com) >add extract xtct_s, tranlog, begin now
EXTRACT added.
GGSCI (labserver.home.com) >edit params xtct_s
-- Process group ---
extract xtct_s
--- Source database login info ----
userid ggate@labpa, password ggate
--- Required to support ASM ----
TRANLOGOPTIONS DBLOGREADER
--- Target Host and Manager Port for communication ----
rmthost labserver.home.com, mgrport 7809
--- Target Host trail files location ----------
rmttrail /opt/oracle/gg/12.1.2/dirdat/labpabr/rt
--- DDL Support --
ddl include mapped objname sender.*
--- Objects to be replicated ------
TABLE sender.*;

GGSCI (labserver.home.com) >view params xtct_s

extract xtct_s
userid ggate@labpa, password ggate
TRANLOGOPTIONS DBLOGREADER
rmthost labserver.home.com, mgrport 7809
rmttrail /opt/oracle/gg/12.1.2/dirdat/labpabr/rt
ddl include mapped objname sender.*
TABLE sender.*;
GGSCI (labserver.home.com) >add rmttrail /opt/oracle/gg/12.1.2/dirdat/labpabr/rt, extract xtct_s

RMTTRAIL added.

 

Step 3: Add Replicat (Delivery) Process for target database

GGSCI (labserver.home.com) >add replicat rpct_t, exttrail /opt/oracle/gg/12.1.2/dirdat/labpabr/rt, checkpointtable ggate.labpa_ckpt
REPLICAT added.
GGSCI (labserver.home.com) >dblogin userid ggate@labpabr, password ggate
Successfully logged into database.

GGSCI (labserver.home.com) >add checkpointtable ggate.labpa_ckpt

Successfully created checkpoint table ggate.labpa_ckpt.
GGSCI (labserver.home.com) >edit params rpct_t
-- Replicat process group ---
replicat rpct_t
-- Target database login info ---
userid ggate@labpabr, password ggate
-- Assuming same structure for source and target tables ---
assumetargetdefs
-- DDL Support ---
DDL
--- Mapping source and target schema for replication ---
MAP sender.*, TARGET receiver.*;
GGSCI (labserver.home.com) >view params rpct_t

replicat rpct_t
userid ggate@labpabr, password ggate
assumetargetdefs
DDL
MAP sender.*, TARGET receiver.*;

 

Step 4: Start Replication

GGSCI (labserver.home.com) >start extract XTCT_S

Sending START request to MANAGER ...
EXTRACT XTCT_S starting
GGSCI (labserver.home.com) >start replicat RPCT_T

Sending START request to MANAGER ...
REPLICAT RPCT_T starting
GGSCI (labserver.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:labpa >conn sender/sender@labpa
Connected.
SQL:labpa >create table test_sender
 2 (
 3 id number(3),
 4 name varchar(10)
 5 );

Table created.

SQL:labpa >insert into test_sender values (100,'abc');

1 row created.

SQL:labpa >commit ;

SQL:labpa >conn receiver/receiver@labpabr
Connected.

Target

SQL:labpabr >select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
TEST_SENDER

SQL:labpabr >select * from TEST_SENDER;

ID          NAME
---------- ----------
100         abc

 

GoldenGate Replication is now in place !!!

 

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed