Oracle Goldengate 11g Initial Load Using Direct Load Method.

This article focuses on initial synchronization of source and
target databases by using Golden Gate initial direct load method for the
purpose of zero downtime DML replication.


Prerequisites:

1) Target Tables should be empty. Otherwise you can get duplicate row errors.
2) Disable Foreign Key and Check Constraints. They can be enabled later on
   when the initial load is complete.
3) (Optional) Remove indexes from target tables as they may slow down the
   initial loading process.
4) To use handlecollisions function, each target table must have a
   primary or unique key. If that's not possible use the KEYCOLS 
   option of the TABLE and MAP parameters. Even if that is not possible
   then quiesce your database for the duration of intial load.


Schema to be replicated "SCOTT"

1) Install GoldenGate on both the machines.

2) Disable Foreign key and check constraints and triggers on target tables.

SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,status from dba_constraints where owner='SCOTT';

CONSTRAINT_NAME                C TABLE_NAME                     STATUS
------------------------------ - ------------------------------ --------
FK_DEPTNO                      R EMP                            ENABLED
PK_DEPT                        P DEPT                           ENABLED
PK_EMP                         P EMP                            ENABLED



SQL> alter table emp disable constraint fk_deptno;

Table altered.


3) Configure and start extract process on source.

4) configure and start Data Pump process on source.

5) Configure Initial load extract and replicat processes.


On Source

GGSCI (canada.example.com) 15> edit param iniload1


extract iniload1
userid ggs_admin, password ggs_admin
rmthost newyork, mgrport 7980
rmttask replicat, group iniload2
table SCOTT.*;

#RMTTASK - Instructs the manager process on the target system to 
#start the initial-load replicat process without manual intervention
#as a one time task.

GGSCI (canada.example.com) 18> add extract iniload1, sourceistable
EXTRACT added.

#SOURCEISTABLE - Tells the extract process to extract records directly
from the source tables rather than from the redo logs.

On Target

GGSCI (newyork.example.com) 5> edit param iniload2

replicat iniload2
userid ggs_admin, password ggs_admin
assumetargetdefs
map SCOTT.*, target SCOTT.*;

GGSCI (newyork.example.com) 9> add replicat iniload2, specialrun
REPLICAT added.

#SpecialRun - Runs as a onetime process with distinct beginning and end,
#which is different from continuous processing as it does not have a 
#specific end point.


On Source

Start initial load extract process on source. The replicat process
 on target will start automatically.

GGSCI (canada.example.com) 19> start extract iniload1

Sending START request to MANAGER ...
EXTRACT INILOAD1 starting


GGSCI (canada.example.com) 30> info extract iniload1

EXTRACT    INILOAD1  Last Started 2012-11-13 21:48   Status RUNNING
Checkpoint Lag       Not Available
Log Read Checkpoint  Table SCOTT.DEPT
                     2012-11-13 21:48:32  Record 1
Task                 SOURCEISTABLE


GGSCI (canada.example.com) 31> view report iniload1


2012-11-13 21:48:27  INFO    OGG-01017  Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.

***********************************************************************
                 Oracle GoldenGate Capture for Oracle
     Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100 
   Linux, x86, 32bit (optimized), Oracle 11g on Oct  7 2011 15:32:42
 
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.


                    Starting at 2012-11-13 21:48:27
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Tue Aug 18 15:51:54 EDT 2009, Release 2.6.18-164.el5
Node: canada.example.com
Machine: i686
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 7671

Description: 

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
extract iniload1
userid ggs_admin, password *********
rmthost newyork, mgrport 7980
rmttask replicat, group iniload2
table SCOTT.*;
TABLEWildcard  resolved (entry SCOTT.*):
  table SCOTT.BONUS;

2012-11-13 21:48:27  WARNING OGG-00869  No unique key is defined for table BONUS. All viable columns will b
e used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

Using the following key columns for source table SCOTT.BONUS: ENAME, JOB, SAL, COMM.
TABLEWildcard  resolved (entry SCOTT.*):
  table SCOTT.DEPT;

Using the following key columns for source table SCOTT.DEPT: DEPTNO.
TABLEWildcard  resolved (entry SCOTT.*):
  table SCOTT.EMP;

Using the following key columns for source table SCOTT.EMP: EMPNO.
TABLEWildcard  resolved (entry SCOTT.*):
  table SCOTT.SALGRADE;

2012-11-13 21:48:27  WARNING OGG-00869  No unique key is defined for table SALGRADE. All viable columns wil
l be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

Using the following key columns for source table SCOTT.SALGRADE: GRADE, LOSAL, HISAL.

CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE:                         64K
CACHESIZE:                                2G
CACHEBUFFERSIZE (soft max):               4M
CACHEPAGEOUTSIZE (normal):                4M
PROCESS VM AVAIL FROM OS (min):        2.87G
CACHESIZEMAX (strict force to disk):   2.64G

Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Database Language and Character Set:
NLS_LANG environment variable specified has invalid format, default value will be used.
NLS_LANG environment variable not set, using default value AMERICAN_AMERICA.US7ASCII.
NLS_LANGUAGE     = "AMERICAN" 
NLS_TERRITORY    = "AMERICA" 
NLS_CHARACTERSET = "WE8MSWIN1252" 

Warning: your NLS_LANG setting does not match database server language setting.
Please refer to user manual for more information.

Processing table SCOTT.BONUS

Processing table SCOTT.DEPT

Processing table SCOTT.EMP

Processing table SCOTT.SALGRADE

***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************


Report at 2012-11-13 21:48:50 (activity since 2012-11-13 21:48:27)

Output to iniload2:

From Table SCOTT.DEPT:
       #                   inserts:         4
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0
From Table SCOTT.EMP:
       #                   inserts:        14
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0
From Table SCOTT.SALGRADE:
       #                   inserts:         5
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0


REDO Log Statistics
  Bytes parsed                    0
  Bytes output                 3473





6) Enable all the constraints and triggers on the target.


SQL> alter table emp enable constraint fk_deptno;

Table altered.

7) Configure and start Replicat process on target.