Simple Golden Gate Setup Example

I have 2 tables I want to create a replication for in Oracle to SQL Server:

DataDarling.GG_TEST

DataDarling.GG_TEST2

For this I need setup these things on these 2 environments:

• OracleDB [Oracle]
• Eggtest.oby
• Eggtest.prm
• ggtstcdc.oby
• ggtstcdc.prm
• dggtstcc.oby
• dggtstcc.prm

• SQLDB  [SQL Server]
• Create empty tables without indexes
• Load tables from OracleDB using SPECIALRUN command

Script Detail:

Eggtest.oby:

add extract eggtest, sourceistable

start extract eggtest

Eggtest.prm:

EXTRACT EGGTEST

USERID ggate, PASSWORD ggate

RMTHOST target.host.com, MGRPORT 7809

RMTTASK replicat, GROUP RGGTEST

TABLE DataDarling.GG_TEST;

TABLE DataDarling.GG_TEST2;

Rggtest.oby - never start this manually.  Golden Gate on the Oracle side triggers this job.

add replicat RGGTEST, specialrun

Rggtest.prm

REPLICAT rggtest

TARGETDB SBL_RPT, userid gguser, password gguser

SOURCEDEFS F:\Target_System\goldengate\dirdef\gg_test.def

DISCARDFILE F:\Target_System\goldengate\dirrpt\rggtest.dsc, purge

MAP DataDarling.GG_TEST, TARGET DBO.GG_TEST;

MAP DataDarling.GG_TEST2, TARGET DBO.GG_TEST2;

Ggtstcdc.oby:

delete extract ggtstcdc

add extract ggtstcdc tranlog, THREADS 3, BEGIN NOW

add exttrail /sbl_backups/ggate/ggtest/cdc/c1 extract ggtstcdc, megabytes 1 -- This is to allow for a small table size

ggtstcdc.prm:

EXTRACT ggtstcdc

SETENV (ORACLE_SID=oracledb1)

USERID ggate, PASSWORD ggate

TRANLOGOPTIONS DBLOGREADER, DBLOGREADERBUFSIZE  2597152,ASMBUFSIZE 28000

EXTTRAIL /sbl_backups/ggate/ggtest/cdc/c1

TABLE DataDarling.GG_TEST, KEYCOLS (ROW_ID);

TABLE DataDarling.GG_TEST2, KEYCOLS (ROW_ID);

dggtstcc.oby

delete extract dggtstcc

add extract dggtstcc exttrailsource /sbl_backups/ggate/ggtest/cdc/c1

add rmttrail F:\Target_System\Goldengate\ggtest\cdc\c1 extract dggtstcc, megabytes 1

dggtstcc.prm

EXTRACT dggtstcc

USERID ggate, PASSWORD ggate

RMTHOST target.host.com, MGRPORT 7809, COMPRESS, COMPRESSTHRESHOLD 1000

RMTTRAIL F:\Target_System\Goldengate\ggtest\cdc\c1

PASSTHRU

TABLE DataDarling.*;

rggtstcc.oby

add replicat RGGTSTCC, exttrail F:\Target_System\Goldengate\ggtest\cdc\c1

 rggtstcc.prm

REPLICAT rggtstcc

TARGETDB SBL_RPT, userid gguser, password gguser

SOURCEDEFS F:\Target_System\goldengate\dirdef\gg_test.def

DISCARDFILE F:\Target_System\goldengate\ggtest\dirrpt\rggtstcc.dsc, purge

REPORTCOUNT EVERY 2 MINUTES, RATE

HANDLECOLLISIONS

MAP DataDarling.GG_TEST, TARGET DBO.GG_TEST, KEYCOLS (ROW_ID);

MAP DataDarling.GG_TEST2, TARGET DBO.GG_TEST2, KEYCOLS (ROW_ID);

Order of execution:

On Linux (Oracle) Host inside ggsci

1. Obey diroby/ggtstcdc.oby

2. Obey diroby/eggtest.oby

3. (Check that the job completed and the data loaded on SQL Server Side)

4. Obey dggtstcc.oby

On Windows (SQL Server) Host inside ggsci:

1. Obey diroby/rggtstcc.oby

2. Check that the change files are being applied to the database.