• Arun Kumar

Configure Golden Gate Initial Load and Change Sync

In this article we will configure initial load along with change sync inside Oracle Golden Gate.


What is initial load?
Initial load is a process of extracting data records from the source database and loading those records onto the target database. Initial load is a data migration process that is performed only once.


Create Sample Table


Let us create EMP table from SCOTT.EMP for FOX user

On Proddb:
==========
sqlplus / as sysdba
Create table fox.emp as select * from scott.emp;
SQL> alter table fox.emp add primary key ("EMPNO");

On the target, just create the EMP table without any data into it. Generate the FOX.EMP table DDL command

On Proddb:
==========
set heading off;
set echo off;
Set pages 999;
set long 90000;
select dbms_metadata.get_ddl('TABLE','EMP','FOX') from dual;

In the above output, change FOX to TOM and execute the output of above command on target ggdev.


Configure Change Sync


First we will have to configure change sync for FOX.EMP table.


Connect to database via Golden Gate:

On proddb:
==========
cd $GG_HOME
./ggsci
GGSCI> dblogin userid ogg, password ogg
Successfully logged into database.

Add table level supplemental logging via Golden Gate:

On GGPROD:
==========
GGSCI> add trandata FOX.EMP
Logging of supplemental redo data enabled for table FOX.EMP.
TRANDATA for scheduling columns has been added on table 'FOX.EMP'.

Create GG Extract Process:

On GGPROD:
==========
GGSCI> ADD EXTRACT PFOXE1, INTEGRATED TRANLOG, BEGIN NOW
EXTRACT (Integrated) added.
GGSCI> register extract PFOXE1 database

Create local trail file for extract process

GGSCI> add exttrail /u01/app/oracle/product/gg/dirdat/pf, extract PFOXE1

Create parameter file for extract process:

GGSCI> edit param PFOXE1
EXTRACT PFOXE1
USERID ogg, PASSWORD ogg
EXTTRAIL /u01/app/oracle/product/gg/dirdat/pf
TABLE FOX.EMP;

Create GG DP Process:

GGSCI> Add extract PFOXD1, EXTTRAILSOURCE /u01/app/oracle/product/gg/dirdat/pf

Create remote trail file for extract process:

GGSCI> Add rmttrail /u01/app/oracle/product/gg/dirdat/rf, extract PFOXD1

Create parameter file for data pump process:

GGSCI> edit param PFOXD1
EXTRACT PFOXD1
USERID ogg, PASSWORD ogg
RMTHOST ggdev, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/rf
TABLE FOX.EMP;

Create GG Replicate on target:

On GGDEV:
=========
GGSCI> dblogin userid ogg, password ogg
GGSCI> add replicat DFOXR1, integrated exttrail /u01/app/oracle/product/gg/dirdat/rf

Create parameter file for replicat on target

GGSCI> edit param DFOXR1
REPLICAT DFOXR1
USERID ogg, PASSWORD ogg
ASSUMETARGETDEFS
MAP FOX.EMP TARGET TOM.EMP;

Start manager on source and target:

On GGPROD:
==========
GGSCI> start mgr
On GGDEV:
=========
GGSCI> start mgr


Configure Initial Load


Now, we need to configure initial load extract and replicat. Add initial load Extract on source

On Proddb:
==========
GGSCI> ADD EXTRACT INITLE, SOURCEISTABLE

Edit parameter file for initial load extract

GGSCI> EDIT PARAM INITLE
EXTRACT INITLE
userid ogg, password ogg
RMTHOST ggdev, mgrport 7809
RMTTASK REPLICAT, GROUP INITLR
TABLE FOX.EMP;

Add initial load Replicat on target

On Devdb:
=========
GGSCI> ADD REPLICAT INITLR, SPECIALRUN

Edit parameter file for initial load replicat

GGSCI> EDIT PARAM INITLR
REPLICAT INITLR
userid ogg, password ogg
ASSUMETARGETDEFS
MAP FOX.EMP, TARGET TOM.EMP;


Start Initial Load & Change Sync


First start the change sync extract and data pump on source. This will start capturing changes while we perform the initial load. Do not start replicat at this point

On proddb:
==========
GGSCI> start PFOXE1
GGSCI> start PFOXD1

Now start the initial load extract. Remember, this will automatically start the initial load replicat on target

On proddb:
==========
GGSCI> start INITLE
GGSCI> INFO INITLE
EXTRACT INITLE Last Started 2016-01-11 15:59 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table FOX.EMP
 2016-01-11 15:59:57 Record 14
Task SOURCEISTABLE
 

Verify on target if all the 14 records have been loaded on target table or not

On Devdb:
=========
sqlplus / as sysdba
select * from tom.emp;

Now start the change sync replicat

On Devdb:
=========
GGSCI> start DFOXR1

Note: At this stage, you can delete the initial load extract and replicat process as they are no longer needed.


If you get below error while starting the initial load extract:

2017-09-18 12:23:40 ERROR OGG-01201 Error reported by MGR : Access denied.
2017-09-18 12:23:40 ERROR OGG-01668 PROCESS ABENDING.

Add below line to ggdev mgr

ACCESSRULE, PROG *, IPADDR *, ALLOW

GGSCI> refresh mgr


Real-Time Initial Load Process


The real time initial load is little different than our previous initial load activity.

Let us create DEPT table from SCOTT.DEPT for FOX user

On Proddb: 
========== 
sqlplus / as sysdba
Create table fox.dept as select * from scott.dept; 
 
SQL> alter table fox.dept add primary key ("DEPTNO"); 
 

On the target, just create the DEPT table without any data into it. Generate the FOX.DEPT table DDL command

On Proddb:
==========
set heading off;
set echo off;
Set pages 999;
set long 90000;
select dbms_metadata.get_ddl('TABLE','DEPT','FOX') from dual; 

In the above output, change FOX to TOM and execute the output of above command on target ggdev.


Configure Change Sync


Connect to database via Golden Gate:

On proddb:
==========
cd $GG_HOME ./ggsci 
 
GGSCI> dblogin userid ogg, password ogg
Successfully logged into database. 

Add table level supplemental logging via Golden Gate:

On GGPROD:
==========
GGSCI> add trandata FOX.DEPT 
 
Logging of supplemental redo data enabled for table FOX.DEPT. TRANDATA for scheduling columns has been added on table 'FOX.DEPT'.
 

Create GG Extract Process:

On GGPROD:
==========
GGSCI> ADD EXTRACT PFOXE2, INTEGRATED TRANLOG, BEGIN NOW
EXTRACT (Integrated) added. 
 
GGSCI> register extract PFOXE2 database 
 

Create local trail file for extract process

GGSCI> add exttrail /u01/app/oracle/product/gg/dirdat/p2, extract PFOXE2 

Create parameter file for extract process:


GGSCI> edit param PFOXE2

GGSCI> edit param PFOXE2 
 
EXTRACT PFOXE2
USERID ogg, PASSWORD ogg
EXTTRAIL /u01/app/oracle/product/gg/dirdat/p2
TABLE FOX.DEPT;

Create GG DP Process:

GGSCI> Add extract PFOXD2, EXTTRAILSOURCE /u01/app/oracle/product/gg/dirdat/p2 

Create remote trail file for extract process:

GGSCI> Add rmttrail /u01/app/oracle/product/gg/dirdat/r2, extract PFOXD2 

Create parameter file for data pump process:


GGSCI> edit param PFOXD2 
 
EXTRACT PFOXD2
USERID ogg, PASSWORD ogg
RMTHOST ggdev, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/r2
TABLE FOX.DEPT; 

Create GG Replicate on target:

On GGDEV:
=========
GGSCI> dblogin userid ogg, password ogg
GGSCI> add replicat DFOXR2, integrated exttrail /u01/app/oracle/product/gg/dirdat/r2 

Create parameter file for replicat on target

GGSCI> edit param DFOXR2 
 
REPLICAT DFOXR2
USERID ogg, PASSWORD ogg
ASSUMETARGETDEFS
MAP FOX.DEPT TARGET TOM.DEPT;

Start manager on source and target:

On GGPROD:
==========
GGSCI> start mgr 
 
On GGDEV:
=========
GGSCI> start mgr


Configure Initial Load


Add initial load Extract on source

On Proddb:
==========
GGSCI> ADD EXTRACT INITLE2, SOURCEISTABLE 

Edit parameter file for initial load extract

GGSCI> EDIT PARAM INITLE2 
 
EXTRACT INITLE2
userid ogg, password ogg
RMTHOST ggdev, mgrport 7809
RMTTASK REPLICAT, GROUP INITLR2
TABLE FOX.DEPT;

Add initial load Replicat on target

On Devdb:
=========
GGSCI> ADD REPLICAT INITLR2, SPECIALRUN 

Edit parameter file for initial load replicat

GGSCI> EDIT PARAM INITLR2 
 
REPLICAT INITLR2
userid ogg, password ogg
ASSUMETARGETDEFS
MAP FOX.DEPT, TARGET TOM.DEPT;


Start Initial Load & Change Sync


First start the change sync extract and data pump on source. This will start capturing changes while we perform the initial load. Do not start replicat at this point

On proddb:
==========
GGSCI> start PFOXE2
GGSCI> start PFOXD2 

At this stage capture the database SCN number. We will start the replicate on target from this SCN onwards

On proddb:
==========
SQL> select current_scn from v$database;

Let us make an update into DEPT table. This update will be captured by both Initial load and also change capture. Later we will analyze how GG handles conflicts

On proddb:
==========
sqlplus fox/fox
update dept set loc='INDIA' where deptno=30;
commit; 

Now start the initial load extract. Remember, this will automatically start the initial load replicat on target

On proddb:
==========
GGSCI> start INITLE2 
 
GGSCI> INFO INITLE2 
 
EXTRACT    INITLE    Last Started  2016-01-11 15:59  Status STOPPED Checkpoint Lag       Not Available
Log Read Checkpoint  Table FOX.DEPT                      
                     2016-01-11 15:59:57  Record 4
Task                 SOURCEISTABLE 
                     

Verify on target if all the 4 records have been loaded on target table or not

On Devdb:
=========
sqlplus / as sysdba
select * from tom.dept;

Let us make some changes to DEPT table and if everything goes well, we must see this change after starting replicat

On proddb:
==========
sqlplus fox/fox
update dept set loc='US' where deptno=40;
commit; 

Now start the change sync replicat

On Devdb:
=========
GGSCI> start DFOXR2, aftercsn <Initial_load_scn>

Note: At this stage, you can delete the initial load extract and replicat process as they are no longer needed.


Recent Posts

See All

Install Oracle 12c Golden Gate on Oracle Linux

In this article we will look at the steps to install oracle 12c Golden Gate on Oracle Enterprise Linux 6.5. The steps involved are: Virtual Machine Setup Install Oracle 12c Database Install Oracle 12c

Single Table Replication – Same Schema

In this article we will be looking at single table replication using Oracle Golden Gate where the schema name is same on both source and target database. Create Sample Table Create Fox user on both pr

Single Table Replication – Different Schema

In this article we will be looking at single table replication using Oracle Golden Gate where the schema name is different on source and target but table structure remains same. Create Sample Table Cr

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis