Oracle Golden Gate unidirectional DML Replication step by step

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--------------------------------------