Oracle Golden Gate Initial Data Load between Source and Target

by Snehashish Ghosh

Scenario


 OS – Windows 7 32 bit

Oracle Database 11g R2

OGG ogg112101

Download OGG from oracle using the following link:

http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

We shall replicate data from one schema of a oracle database to another schema in the same oracle database.

Source schema name – sender

Destination schema name – receiver

 Install Oracle Golden Gate


1. Unzip the Oracle Golden Gate installation file ogg112101_ggs_Windows_x86_ora11g_32bit.zip to d:\ogg folder.

             gg_ins1

 

 

 

 

2.  Open a command prompt,go to golden gate installation folder and run create subdirs.

 d:\ogg>ggsci

 Oracle Golden Gate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230

Windows (optimized), Oracle 11g on Apr 23 2012 04:52:28

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

GGSCI (LIN41000510) 1> create subdirs

Creating subdirectories under current directory d:\ogg

Parameter files                d:\ogg\dirprm: already exists

Report files                   d:\ogg\dirrpt: created

Checkpoint files               d:\ogg\dirchk: created

Process status files           d:\ogg\dirpcs: created

SQL script files               d:\ogg\dirsql: created

Database definitions files     d:\ogg\dirdef: created

Extract data files             d:\ogg\dirdat: created

Temporary files                d:\ogg\dirtmp: created

Stdout files                   d:\ogg\dirout: created

Create another new directory manually named discard to keep discarded record during data replication.

Steps to perform data replication using Oracle Golden Gate


 1. Execute the following ogg provided demo table creation and data insertion file by logging into source schema.

D:\ogg\ demo_more_ora_create.sql

D:\ogg\ demo_more_ora_insert.sql

c:\OGG>sqlplus sender/[email protected]

SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 17 17:26:28 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @C:\OGG\demo_ora_create.sql

 Table dropped.

Table created.

Table dropped.

Table created.

SQL> @C:\OGG\demo_ora_insert.sql

1 row created.

1 row created.

1 row created.

1 row created.

Commit complete.

SQL> disc

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 2. Execute the following ogg provided demo table creation file by logging into target schema.

SQL> conn receiver/[email protected]

Connected.

SQL> @C:\OGG\demo_ora_create.sql

Table dropped.

Table created.

Table dropped.

Table created.

SQL> disc

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 3.      Set the environment variable

 d:\ogg>set ORACLE_SID=PRE

 d:\ogg>set ORACLE_HOME=C:\oracle\product\11.2.0\dbhome_2

 4.     Create manager process

EDIT PARAMS MGR

PORT 7809

Start manager

gg_ins2

 

 

 

 

 

 

 

 5.     Create extract process

GGSCI > add extract iniext,sourceistable

 6.  Create replicat process

GGSCI > add replicat inirep,specialrun

7.      Create extract parameter file

GGSCI > edit params iniext

— Extract parameter file to capture TCUSTORD

— and TCUSTMER initial data for Replicat

EXTRACT INIEXT

USERID [email protected], PASSWORD password

RMTHOST localhost, MGRPORT 7809

RMTTASK REPLICAT, GROUP INIREP

TABLE sender.TCUSTMER;

TABLE sender.TCUSTORD;

gg_ins3

 

 

 

 

 8.   Create replicat parameter file

GGSCI > edit params inirep

— REPLICAT parameter file to replicate initial changes

— for TCUSTMER.

REPLICAT INIREP

ASSUMETARGETDEFS

DISCARDFILE D:\ogg\discard\tcustmer.dsc, PURGE

USERID [email protected], PASSWORD password

MAP sender.*, TARGET receiver.*;

 

gg_ins4

 

 

 

 

 

 9.   Start Manager, Extract & Replicat process

 GGSCI > start manager

 

gg_ins5

 

 

 

 

 

 

 

 

 

 

GGSCI> start extract iniext

Processing table SENDER.TCUSTMER

Processing table SENDER.TCUSTORD

***********************************************************************

*                   ** Run Time Statistics **                         *

***********************************************************************

Report at 2013-04-17 21:09:00 (activity since 2013-04-17 21:08:50)

 

Output to INIREP:

 

From Table SENDER.TCUSTMER:

#                   inserts:         2

#                   updates:         0

#                   deletes:         0

#                  discards:         0

From Table SENDER.TCUSTORD:

#                   inserts:         2

#                   updates:         0

#                   deletes:         0

#                  discards:         0

 REDO Log Statistics

Bytes parsed                    0

Bytes output                  578

 10.     Confirm data has been replicated into database target schema

 SQL> select * from receiver.tcustmer;

 CUST NAME                           CITY                 ST

—- —————————— ——————– —

WILL BG SOFTWARE CO.                SEATTLE              WA

JANE ROCKY FLYER INC.               DENVER               CO

 SQL> select * from receiver.tcustord;

 CUST ORDER_DAT PRODUCT_   ORDER_ID PRODUCT_PRICE PRODUCT_AMOUNT TRANSACTION_ID

—- ——— ——– ———- ————- ————– ————–

WILL 30-SEP-94 CAR             144         17520              3            100

JANE 11-NOV-95 PLANE           256        133300              1            100

Conclusion


 To configure Oracle Golden Gate Initial Load of tables the following high level steps are performed:

  1. Configure manager
  2. Create Extract process
  3. Create Extract Parameter File
  4. Create Replicat Process
  5. Create Replicat Parameter File
  6. Start manager, Extract and Replicat process

Sometime the following error message is encountered after we run extract process

2013-04-17 20:47:29  ERROR   OGG-00664  OCI Error during OCIServerAttach (status

= 12154-ORA-12154: TNS:could not resolve the connect identifier specified).

2013-04-17 20:47:29  ERROR   OGG-01668  PROCESS ABENDING.

To overcome the error set the Oracle_SID,ORACLE_HOME environment variables properly.

Check whether tnsping to database working properly. Then restart manager and start extract again.

One comment to Oracle Golden Gate Initial Data Load between Source and Target

  • syed hassan  says:

    hi

    your document is very helpful, but I stuck in this “OGG-00664 OCI Error during OCIServerAttach (status

    = 12154-ORA-12154: TNS:could not resolve the connect identifier specified).

    2013-04-17 20:47:29 ERROR OGG-01668 PROCESS ABENDING” error. I have set the sid and oracle home but not effected? plzzzz help

Leave a reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

*