Oracle Golden Gate one way DDL Replication step by step

Oracle Golden Gate one way DDL/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 file Discription:

[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 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 target 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) For DLL replication we need to run below sql scripts.

----Run all sql scripts by sys user

SQL> @/u01/app/oracle/product/gg/marker_setup.sql

SQL> @/u01/app/oracle/product/gg/ddl_setup.sql

SQL> @/u01/app/oracle/product/gg/role_setup.sql

SQL> grant GGS_GGSUSER_ROLE to ggate;

SQL> @/u01/app/oracle/product/gg/ddl_enable.sql

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

CheckpointTable oggadm1.oggchkpt

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

5)create check point table on source and target

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

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

DDL INCLUDE ALL

TABLE scott.*;

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 esalesaa, Megabytes 5

EXTTRAIL added.

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

DDL INCLUDE ALL

TABLE scott.*;

GGSCI (gg1.example.com) 10>Add Extract psalesab, 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 psalesab, 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

8) Create replicat 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

DDL INCLUDE ALL

DDLERROR DEFAULT IGNORE

MAP scott.*, TARGET scott.*;

GGSCI (gg2.example.com) 32>Add Replicat rsalesab, 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

9) Start extract process on source database

GGSCI (gg1.example.com) 14> start Extract *

GGSCI (gg1.example.com) 15> info all

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

10) Start replicat process on target database

GGSCI (gg1.example.com) 34> start REPLICAT *

GGSCI (gg1.example.com) 34> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

REPLICAT    RUNNING     REP1        00:00:00      00:00:02

11) Check DLL replication.

- Source side

SQL> conn scott/tiger

Connected.

SQL> create table t1 (id number);

Table created.

--target side

SQL> conn scott/tiger

Connected.

SQL> select count(*) from t1;

  COUNT(*)

----------

         0

12) Data replication

---On source side..

SQL> insert into t1 values(12);

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

  COUNT(*)

----------

         1

SQL>

---On target side

SQL> select count(*) from t1;

  COUNT(*)

----------

         1

-----------------------END--------------------------------------

     

 

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

Oracle Golden Gate installation step by step..

--- Oracle Golden Gate installation step by step..

-server information 

OS Version: 
[oracle@gg1 ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.4 (Tikanga)
[oracle@gg1 ~]$

Hostname:node1.example.com
Ip Address:192.168.0.100

1) create seprate folder for oracle GG_HOME

[oracle@gg1 gg]$ mkdir /u01/app/oracle/product/gg

[oracle@gg1 gg]$ chown -R oracle:oinstall /u01

[oracle@gg1 gg]$ chmod 775 /u01


2)copy oracle Golden Gate S/W to GG_HOME (/u01/app/oracle/product/gg)

-unzip oracle golden gate s/w

[oracle@gg1 gg]$tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

3)set oracle GG home parameter in user bash_profile,add below line in .bash_profile

[oracle@gg1 gg] vi .bash_profile

GG_HOME=/u01/app/oracle/product/gg; export GG_HOME

[oracle@gg1 gg] . .bash_profile

4) configure oracle GG 

[oracle@gg1 gg] cd $GG_HOME
[oracle@gg1 ~]$ pwd
/u01/app/oracle/product/gg

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


-create subdirectory

GGSCI (gg1.example.com) 1>create subdirs

Creating subdirectories under current directory /u01/app/oracle/product/gg

Parameter files                /u01/app/oracle/product/gg/dirprm: already exists
Report files                   /u01/app/oracle/product/gg/dirrpt: already exists
Checkpoint files               /u01/app/oracle/product/gg/dirchk: already exists
Process status files           /u01/app/oracle/product/gg/dirpcs: already exists
SQL script files               /u01/app/oracle/product/gg/dirsql: already exists
Database definitions files     /u01/app/oracle/product/gg/dirdef: already exists
Extract data files             /u01/app/oracle/product/gg/dirdat: already exists
Temporary files                /u01/app/oracle/product/gg/dirtmp: already exists
Stdout files                   /u01/app/oracle/product/gg/dirout: already exists

Note:- already I configured GG because of that it is showing directories are exists.


                --------------------END---------------