top of page
DBA Genesis Logo

Configure Golden Gate Initial Load and Change Sync

Step-by-step guide for configuring initial load and change synchronization.

Oracle Golden Gate 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.

configure golden gate initial load and change sync - database


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 in 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 Golden Gate Initial Load


Now, we need to configure golden gate 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

view manager parameter file golden gate


Real-Time Initial Load Process


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

real time initial load process golden gate

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.


Become a top notch dba with DBA Genesis
Start your DBA career today
bottom of page