Oracle Golden Gate unidirectional DML Replication step by step
OS Description
Source os:
[oracle@gg1 gg]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.4 (Tikanga)
Ip address: 192.168.0.100
Hostname: gg1.example.com
Target OS:
[oracle@gg2 gg]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.4 (Tikanga)
Ip address: 192.168.0.200
Hostname: gg2.example.com
Database Description
Source Database:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SID : DB11G
Tnsname.ora file Description:
[oracle@gg1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DB11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg1.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)
)
)
Target=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg2.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)
)
)
Target Database:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SID: DB11G
tnsname.ora file Description:
[oracle@gg2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DB11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg2.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)
)
)
Sourc =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg1.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)
)
)
Oracle Golden Gate configuration
--Source and targate database
1) Check oracle database LOG_MODE is set to ARCHIVELOG.
[oracle@gg1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 22 07:34:36 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG
SQL>
---The result should be ARCHIVELOG. If it is, then skip to the next step.
If the result is NOARCHIVELOG, then do the following:
SQL> SELECT log_mode FROM v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL> shutdown immediate
SQL> startup mount
SQL>ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG
SQL>
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
YES YES
----The results should both be YES. If it is, then skip to the next step.
If either result is NO, then do the following:
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
NO NO
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
YES YES
SQL>CREATE USER ggate IDENTIFIED BY ggate;
SQL> GRANT dba TO ggate;
2) Create check point table on both source and target.
On source
[oracle@gg1 gg]$ vi GLOBALS
CheckpointTable oggadm1.oggchkpt
On target
[oracle@gg2 gg]$ vi GLOBALS
Checkpoint Table oggadm1.oggchkpt
3) Create manager process on both source and target.
On source
[oracle@gg1 gg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (gg1.example.com) 1> Edit Param mgr
PORT 7809
DYNAMICPORTLIST 7810-7820
GGSCI (gg1.example.com) 3> Info mgr
Manager is DOWN!
GGSCI (gg1.example.com) 3> start mgr
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
On target
[oracle@gg2 gg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (gg2.example.com) 1> Edit Param mgr
PORT 7809
DYNAMICPORTLIST 7810-7820
GGSCI (gg2.example.com) 3> Info mgr
Manager is DOWN!
GGSCI (gg2.example.com) 3> start mgr
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
4) create check point table on source and targat
On source
GGSCI (gg2.example.com) 4>dblogin userid ggate, Password ggate
GGSCI (gg1.example.com) 5> Add CheckpointTable
On target
GGSCI (gg2.example.com) 4>dblogin userid ggate, Password ggate
GGSCI (gg2.example.com) 5> Add CheckpointTable
5) Create extract process
On target Database
GGSCI (gg1.example.com) 6>Edit Param EX1
EXTRACT ex1
USERID ggate, PASSWORD ggate
EXTTRAIL /u01/app/oracle/product/gg/dirdat/ex
TABLE scott.emp;
GGSCI (gg1.example.com) 7> Add Extract EX1, TranLog, Begin Now
EXTRACT added.
GGSCI (gg1.example.com) 8> Add ExtTrail ./u01/app/oracle/product/gg/dirdat/ex, Extract EX1, Megabytes 5
EXTTRAIL added.
6) Create data pump process
On target database
GGSCI (gg1.example.com) 9> Edit Param DP1
EXTRACT dp1
USERID ggate, PASSWORD ggate
RMTHOST gg2.example.com, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/rt
TABLE scott.emp;
GGSCI (gg1.example.com) 10>Add Extract DP1, ExtTrailSource /u01/app/oracle/product/gg/dirdat/rt
Extract added.
GGSCI (gg1.example.com) 11>Add RmtTrail /u01/app/oracle/product/gg/dirdat/rt, Extract DP1, Megabytes 5
RMTTRAIL added.
GGSCI (gg1.example.com) 12> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DP1 00:00:00 00:00:05
EXTRACT STOPPED EX1 00:00:00 00:00:00
GGSCI (gg1.example.com) 13>Info ExtTrail *
Extract Trail: /u01/app/oracle/product/gg/dirdat/rt
Extract: DP1
Seqno: 1
RBA: 1285
File Size: 5M
Extract Trail: /u01/app/oracle/product/gg/dirdat/ex
Extract: EX1
Seqno: 1
RBA: 1119
File Size: 5M
7) Create replicate process
On target database
GGSCI (gg2.example.com) 32> edit param REP1
REPLICAT rep1
USERID ggate, PASSWORD ggate
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/product/gg/discards, PURGE
MAP scott.emp, TARGET scott.emp;
GGSCI (gg2.example.com) 32>Add Replicat REP1, ExtTrail /u01/app/oracle/product/gg/dirdat/rt
REPLICAT added.
GGSCI (gg2.example.com) 33> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP1 00:00:00 00:00:02
8) Start extract process on source database
GGSCI (gg1.example.com) 14> start Extract *
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 00:00:00
EXTRACT RUNNING EX1 00:00:00 00:00:06
9) Start replicate process on target database
GGSCI (gg1.example.com) 34> start REPLICAT *
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:02
10) Testing Data
On source database
SQL> conn scott/*****
Connected.
SQL> select count(*) from emp;
COUNT(*)
----------
10
SQL>
on target database
SQL> select count(*) from emp;
COUNT(*)
----------
13
--deleting data from source
SQL> delete from emp where EMPNO=7844;
1 row deleted.
SQL> commit;
Commit complete.
---on target database
SQL> select count(*) from emp;
COUNT(*)
----------
12
SQL> delete from emp where EMPNO=7844;
0 rows deleted.
SQL>
-----------------------END--------------------------------------
OS Description
Source os:
[oracle@gg1 gg]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.4 (Tikanga)
Ip address: 192.168.0.100
Hostname: gg1.example.com
Target OS:
[oracle@gg2 gg]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.4 (Tikanga)
Ip address: 192.168.0.200
Hostname: gg2.example.com
Database Description
Source Database:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SID : DB11G
Tnsname.ora file Description:
[oracle@gg1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DB11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg1.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)
)
)
Target=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg2.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)
)
)
Target Database:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SID: DB11G
tnsname.ora file Description:
[oracle@gg2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DB11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg2.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)
)
)
Sourc =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg1.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)
)
)
Oracle Golden Gate configuration
--Source and targate database
1) Check oracle database LOG_MODE is set to ARCHIVELOG.
[oracle@gg1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 22 07:34:36 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG
SQL>
---The result should be ARCHIVELOG. If it is, then skip to the next step.
If the result is NOARCHIVELOG, then do the following:
SQL> SELECT log_mode FROM v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL> shutdown immediate
SQL> startup mount
SQL>ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG
SQL>
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
YES YES
----The results should both be YES. If it is, then skip to the next step.
If either result is NO, then do the following:
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
NO NO
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
YES YES
SQL>CREATE USER ggate IDENTIFIED BY ggate;
SQL> GRANT dba TO ggate;
2) Create check point table on both source and target.
On source
[oracle@gg1 gg]$ vi GLOBALS
CheckpointTable oggadm1.oggchkpt
On target
[oracle@gg2 gg]$ vi GLOBALS
Checkpoint Table oggadm1.oggchkpt
3) Create manager process on both source and target.
On source
[oracle@gg1 gg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (gg1.example.com) 1> Edit Param mgr
PORT 7809
DYNAMICPORTLIST 7810-7820
GGSCI (gg1.example.com) 3> Info mgr
Manager is DOWN!
GGSCI (gg1.example.com) 3> start mgr
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
On target
[oracle@gg2 gg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (gg2.example.com) 1> Edit Param mgr
PORT 7809
DYNAMICPORTLIST 7810-7820
GGSCI (gg2.example.com) 3> Info mgr
Manager is DOWN!
GGSCI (gg2.example.com) 3> start mgr
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
4) create check point table on source and targat
On source
GGSCI (gg2.example.com) 4>dblogin userid ggate, Password ggate
GGSCI (gg1.example.com) 5> Add CheckpointTable
On target
GGSCI (gg2.example.com) 4>dblogin userid ggate, Password ggate
GGSCI (gg2.example.com) 5> Add CheckpointTable
5) Create extract process
On target Database
GGSCI (gg1.example.com) 6>Edit Param EX1
EXTRACT ex1
USERID ggate, PASSWORD ggate
EXTTRAIL /u01/app/oracle/product/gg/dirdat/ex
TABLE scott.emp;
GGSCI (gg1.example.com) 7> Add Extract EX1, TranLog, Begin Now
EXTRACT added.
GGSCI (gg1.example.com) 8> Add ExtTrail ./u01/app/oracle/product/gg/dirdat/ex, Extract EX1, Megabytes 5
EXTTRAIL added.
6) Create data pump process
On target database
GGSCI (gg1.example.com) 9> Edit Param DP1
EXTRACT dp1
USERID ggate, PASSWORD ggate
RMTHOST gg2.example.com, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/rt
TABLE scott.emp;
GGSCI (gg1.example.com) 10>Add Extract DP1, ExtTrailSource /u01/app/oracle/product/gg/dirdat/rt
Extract added.
GGSCI (gg1.example.com) 11>Add RmtTrail /u01/app/oracle/product/gg/dirdat/rt, Extract DP1, Megabytes 5
RMTTRAIL added.
GGSCI (gg1.example.com) 12> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DP1 00:00:00 00:00:05
EXTRACT STOPPED EX1 00:00:00 00:00:00
GGSCI (gg1.example.com) 13>Info ExtTrail *
Extract Trail: /u01/app/oracle/product/gg/dirdat/rt
Extract: DP1
Seqno: 1
RBA: 1285
File Size: 5M
Extract Trail: /u01/app/oracle/product/gg/dirdat/ex
Extract: EX1
Seqno: 1
RBA: 1119
File Size: 5M
7) Create replicate process
On target database
GGSCI (gg2.example.com) 32> edit param REP1
REPLICAT rep1
USERID ggate, PASSWORD ggate
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/product/gg/discards, PURGE
MAP scott.emp, TARGET scott.emp;
GGSCI (gg2.example.com) 32>Add Replicat REP1, ExtTrail /u01/app/oracle/product/gg/dirdat/rt
REPLICAT added.
GGSCI (gg2.example.com) 33> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP1 00:00:00 00:00:02
8) Start extract process on source database
GGSCI (gg1.example.com) 14> start Extract *
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 00:00:00
EXTRACT RUNNING EX1 00:00:00 00:00:06
9) Start replicate process on target database
GGSCI (gg1.example.com) 34> start REPLICAT *
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:02
10) Testing Data
On source database
SQL> conn scott/*****
Connected.
SQL> select count(*) from emp;
COUNT(*)
----------
10
SQL>
on target database
SQL> select count(*) from emp;
COUNT(*)
----------
13
--deleting data from source
SQL> delete from emp where EMPNO=7844;
1 row deleted.
SQL> commit;
Commit complete.
---on target database
SQL> select count(*) from emp;
COUNT(*)
----------
12
SQL> delete from emp where EMPNO=7844;
0 rows deleted.
SQL>
-----------------------END--------------------------------------
It was an excellent Blog to see from you which is very useful. Thank you so much for gathering all this information about Oracle Goldengate, it’s very clever and will be extremely helpful for all people.
ReplyDelete