Wednesday, 22 April 2015

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











     









 


















at

2 comments:

  1. Unknown1 October 2015 at 11:35

    We are providing bestOracle Golden Gate Online training in usa at very reaposable price with Adithyaelearning. We will provide you well expert teacher and environment for your learning.

    ReplyDelete
  2. Keerthi5520 May 2021 at 23:15

    I cannot thank you enough for the blog.Thanks Again. Keep writing.
    teradata training
    oracle bpm training
    angular js training
    sql server dba training
    oracle golden gate training

    ReplyDelete

Subscribe to: Post Comments (Atom)

work on autovacuum postgreSQL parameter

 In This blog, we are discussing the auto vacuum parameter on a small scale. we will understand the below parameters and will see how to mod...