GOLDEN GATE configuration from schema to schema replication within the same database

Here i am going to show how to replicate schema1 to schema2 using ORACLE goldengate only DML replication.(in the same database)

Prerequisites: Oracle Goledengate software is assumed to be installed
               Manager process started(if not check the bottom of post how to start manager process)
               TNS entries are there for ASM and database (if not check the bottom of post)
               supplemental logging is enabled at database level(if not check the bottom of post)
source schema: SCHEMA1
destination schema: SCHEMA2
HOST NAME:subhendb01
DATABASE NAME:ORCL

-- create golden_data tablespace
create tablespace ggate_data datafile size 100m autoextend on;
-- create golden gate user i.e. ggate_schema1
create user ggate_schema1 identified by <pwd>
temporary tablespace TEMP
default tablespace ggate_data
quota unlimited on ggate_data;

grant CREATE SESSION to ggate_schema1;
grant ALTER SESSION to ggate_schema1;
grant RESOURCE to ggate_schema1;
grant SELECT ANY DICTIONARY to ggate_schema1;
grant EXECUTE on DBMS_FLASHBACK to ggate_schema1;


--Encrypt passwords for GGate user and ASM user for extract parameter file

--Create encrypted password for user ggate_schema1

GGSCI (subhendb01.***.***.****.***) 39> encrypt password <pwd>
No key specified, using default key...

Encrypted password:  AACAAAAAAAAAAAJABDJBJFYAFAVBTEYGXFLIVHDDGANENIWA
--- Use it for the USERID line in the parameter file

Create encrypted password for ASM user

GGSCI (subhendb01.***.***.****.***) 15> encrypt password <pwd>

No key specified, using default key...

Encrypted password:  AACAAAAAAAAAAAJAIEJANCHETDCCSJLCUCUEPFYGGJBFJGIH

--- Use it for the TRANLOGOPTIONS ASMUSER line in the parameter file


--Configure 'continuous' EXTRACT group

[oracle@subhendb01 11.1_11g]$ . oraenv
ORACLE_SID = [oracle] ? ORCL_1
The Oracle base for ORACLE_HOME=/oracle/app/database/11.2.0.3/dbhome_1 is /oracle/app
[oracle@subhendb01 11.1_11g]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Linux, x64, 64bit (optimized), Oracle 11 on Jul 28 2010 13:13:42

Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.



GGSCI (subhendb01.***.***.****.***) 1>ADD EXTRACT EXT_schema1, TRANLOG, THREADS 3, BEGIN NOW

GGSCI (subhendb01.***.***.****.***) 2>ADD EXTTRAIL ./dirdat/ep, EXTRACT EXT_schema1

GGSCI (subhendb01.***.***.****.***) 3> edit params EXT_SCHEMA1

...............
EXTRACT EXT_schema1
SETENV (ORACLE_HOME = "/oracle/app/database/11.2.0.3/dbhome_1")
SETENV (ORACLE_SID = "ORCL_1")
USERID ggate_schema1, PASSWORD AACAAAAAAAAAAAJABDJBJFYAFAVBTEYGXFLIVHDDGANENIWA, &
ENCRYPTKEY DEFAULT
TRANLOGOPTIONS ASMUSER sys@+ASM, ASMPASSWORD AACAAAAAAAAAAAHAWAEJQDPACDHGNHSE, &
ENCRYPTKEY DEFAULT
TRANLOGOPTIONS PURGEORPHANEDTRANSACTIONS
DISCARDFILE ./discard/ext_schema1_discard.dsc, append, megabytes 10
GETUPDATEBEFORES
NOCOMPRESSDELETES
RMTHOST subhendb01, mgrport 7840
rmttrail ./dirdat/ep
table NWHADS_schema1.*;


--For replication schema NWHADS_schema2
--Create GGATE_DATA tablespace and  ggate_rep_schema2 user, if not exists

create user ggate_rep_schema2 identified by <pwd>
temporary tablespace TEMP
default tablespace ggate_data
quota unlimited on ggate_data;

grant CREATE SESSION to ggate_rep_schema2;
grant ALTER SESSION to ggate_rep_schema2;
grant RESOURCE to ggate_rep_schema2;
grant SELECT ANY DICTIONARY to ggate_rep_schema2;
grant EXECUTE on DBMS_FLASHBACK to ggate_rep_schema2;


--Create a checkpoint table, if not already exists

-- Create global parameter file with the table name

GGSCI (subhendb01.***.***.****.***) 3> edit params ./GLOBALS
……
CHECKPOINTTABLE ggate_schema1.cp_table
CHECKPOINTTABLE ggate_rep_schema2.cp_table
…..

GGSCI (subhendb01.***.***.****.***) 4>DBLOGIN USERID ggate_schema1, PASSWORD <pwd>
GGSCI (subhendb01.***.***.****.***) 4>ADD CHECKPOINTTABLE ggate_schema1.cp_table

… Successfully created checkpoint table ggate_schema1.cp_table.

GGSCI (subhendb01.***.***.****.***) 4>DBLOGIN USERID ggate_rep_schema2, PASSWORD <pwd>
GGSCI (subhendb01.***.***.****.***) 4>ADD CHECKPOINTTABLE ggate_rep_schema2.cp_table


… Successfully created checkpoint table ggate_rep_schema2.cp_table.


--Create the replicated schema tablespace and owner

grant CREATE SESSION to NWHADS_schema2;
grant DEBUG CONNECT SESSION to NWHADS_schema2;
grant CREATE TABLE to NWHADS_schema2;
grant CREATE VIEW to NWHADS_schema2;
grant CREATE SYNONYM to NWHADS_schema2;
grant CREATE SEQUENCE to NWHADS_schema2;
grant CREATE TRIGGER to NWHADS_schema2;
grant CREATE TYPE to NWHADS_schema2;
grant CREATE PROCEDURE to NWHADS_schema2;
grant CREATE LIBRARY to NWHADS_schema2;
grant CREATE DATABASE LINK to NWHADS_schema2;



--Configure 'continuous' REPLICAT group

--GGSCI (subhendb01.***.***.****.***) 1> ADD REPLICAT REP_schema2, EXTTRAIL ./dirdat/ep, BEGIN NOW
edit params REP_schema2
……………………………………
REPLICAT REP_schema2
SETENV (ORACLE_HOME = "/oracle/app/database/11.2.0.3/dbhome_1")
SETENV (ORACLE_SID = "ORCL_1")
USERID ggate_rep_schema2, PASSWORD AACAAAAAAAAAAAJABDJBJFYAFAVBTEYGXFLIVHDDGANENIWA, &
ENCRYPTKEY DEFAULT
ASSUMETARGETDEFS
EXTTRAIL ./dirdat/ep
DBOPTIONS  DEFERREFCONST
INSERTALLRECORDS
GETUPDATEBEFORES
REPERROR (DEFAULT, ABEND)
DDLERROR DEFAULT IGNORE
discardfile ./discard/rep_schema1_discard.dsc, append, megabytes 10
map NWHADS_schema1.*, target NWHADS_schema2.*;
………………………


GGSCI (subhendb01.***.***.****.***) 4> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT_SCHEMA1    00:00:00      00:00:03
REPLICAT    RUNNING     REP_SCHEMA2    00:00:00      00:00:08


=========================
start of manager process
==========================

--  Check ports are free, e.g.:

netstat -a|grep " 78[4-7][0-9] "

./ggsci

EDIT PARAMS MGR    

PORT 7840
DYNAMICPORTLIST 7841-7870
AUTOSTART EXTRACT *
AUTOSTART REPLICAT *
AUTORESTART EXTRACT *
AUTORESTART REPLICAT *
PURGEOLDEXTRACTS ./DIRDAT/*, USECHECKPOINTS, MINKEEPDAYS 5

--save and exit

-- Test MGR restart

 START MGR
 STOP MGR
 START MGR
……………………………………
Manager started.

==================================
Configuration of tnsnames.ora file
==================================

The ASM service entry must be present in tnsnames.ora in the DB Oracle home:
………………………………………
+ASM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = subhendb01v.***.***.****.***)(PORT = 1621))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = +ASM)
    )
  ) ………………………………………
Verify TNS connectivity

sqlplus sys/<pwd>@+ASM as sysdba

The source database service entry must be present in tnsnames.ora in the DB Oracle home:
………………………………………
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = subhendbscan.***.***.****.***)(PORT = 1621))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  ) ………………………………………….

Verify TNS connectivity

sqlplus sys/<pwd>@ORCL as sysdba

=============================================
ENABLE SUPPLEMENTAL LOGGING AT DATABASE LEVEL
=============================================


As sys or system : enable minimal supplemental logging at the database level. This is
required to process updates to primary keys and chained rows.

--- Check status

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM GV$DATABASE;

SUPPLEME
--------
NO
NO
NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

--- To start the supplemental logging, switch the log files on all instances

SQL> alter system archive log current;

System altered.

--- Verify

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM GV$DATABASE;

SUPPLEME
--------
YES
YES
YES