Category Archives: GoldenGate

Oracle GoldenGate

Oracle Golden Gate – OGG-01028 Incompatible record

Intro

The incompatible record error is one I have seen a couple of times now with DB2 as the source and it seems to hing around something that occurs on weekends when they take the system down for maintenance.  I have yet to pinpoint the cause, but this workaround does get everything back up and running.

 

Symptoms

From the report file:

Switching to next trail file ./dirdat/qt000141 at 2014-10-20 09:14:57 due to EOF, with current RBA 99999985
Opened trail file ./dirdat/qt000141 at 2014-10-20 09:14:57


Source Context :
  SourceModule            : [er.processloop]
  SourceID                : [/scratch/aime1/adestore/views/aime1_adc4150267/oggcore/OpenSys/src/app/er/processloop.cpp]
  SourceFunction          : [process_extract_loop]
  SourceLine              : [874]
  ThreadBacktrace         : [7] elements
                          : [/apps/oracle/ggs/libgglog.so(CMessageContext::AddThreadContext()+0x1e) [0x7fc89734bc3e]]
                          : [/apps/oracle/ggs/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x2cc) [0x7fc89734498c]]
                          : [/apps/oracle/ggs/libgglog.so(_MSG_ERR_ER_GENERIC_FAILURE(CSourceContext*, char const*, CMessageFactory::MessageDisposition)+0x31) [0x7fc8
9732f839]]
                          : [/apps/oracle/ggs/replicat(process_extract_loop()+0x31f1) [0x53e581]]
                          : [/apps/oracle/ggs/replicat(main+0x732) [0x54faa2]]
                          : [/lib64/libc.so.6(__libc_start_main+0xfd) [0x3e5ce1ed1d]]
                          : [/apps/oracle/ggs/replicat(__gxx_personality_v0+0x332) [0x4c233a]]

2014-10-20 09:15:17  ERROR   OGG-01028  Incompatible record (101) in ./dirdat/qt000141, rba 1155 (getting header).

 

Fix

Use the logdump utility to gather the required info from the trail file.

 

> cd $GG_HOME
> ./logdump

Oracle GoldenGate Log File Dump Utility for Oracle
Version 11.2.1.0.7 16934304 OGGCORE_11.2.1.0.7_PLATFORMS_130709.1600.1

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



Logdump 22 >log to incompat_record.log
--- Session log incompat_record.log opened 2014/10/20 09:31:32.434.370 ---
Logdump 23 >open /apps/oracle/ggs/dirdat/qt000141
Current LogTrail is /apps/oracle/ggs/dirdat/qt000141
Logdump 24 >ghdr on
Logdump 25 >pos 1155
Reading forward from RBA 1155
Logdump 26 >n
Bad record found at RBA 1155, format 5.50 Header token)
 8004 8647                                         | ...G
Logdump 27 >sfh prev
Scanned    1155 bytes and did not find a header
Bad record found at RBA 1155, format 5.50 Header token)
 8004 8647                                         | ...G
Logdump 28 >sfh next
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :   119  (x0077)   IO Time    : 2014/10/18 00:26:10.455.904
IOType     :    15  (x0f)     OrigNode   :   255  (xff)
TransInd   :     .  (x01)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :          0       AuditPos   : 58745476396002
Continued  :     N  (x00)     RecCount   :     1  (x01)

2014/10/18 00:26:10.455.904 FieldComp            Len   119 RBA 1158
Name: schema_name.table_name
After  Image:                                             Partition 4      m
 0000 000a 0000 0000 0000 0335 dba7 0001 000a 0000 | ...........5........
 0000 0000 0000 0001 0021 0034 0000 c4e3 d3f0 f0f1 | .........!.4........
 c2f3 4040 4040 4040 4040 4040 4040 4040 4040 4040 | ..@@@@@@@@@@@@@@@@@@
 4040 4040 4040 4040 4040 4040 4040 4040 4040 4040 | @@@@@@@@@@@@@@@@@@@@
 4040 4040 0022 001f 0000 f2f0 f1f4 60f1 f060 f1f8 | @@@@."........`..`..
 7af0 f07a f2f6 7af5 f54b f8f7 f0f8 f5f4 f0f0 f0   | z..z..z..K.........

Logdump 29 >exit


Since the issue is with the header of the file, I went to the next record instead of the previous one.  Looks like RBA 1158 is good, and doing a count from here works so the rest of the file is good.  So, let’s alter the replicat to this next good record and start it up.

 

> ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.7 16934304 OGGCORE_11.2.1.0.7_PLATFORMS_130709.1600.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jul 18 2013 07:04:28

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

GGSCI > info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    ABENDED     DB_REP    56:32:07      00:35:28

GGSCI > info *

REPLICAT   DB_REP  Last Started 2014-10-20 10:05   Status ABENDED
Checkpoint Lag       57:39:50 (updated 00:02:38 ago)
Log Read Checkpoint  File ./dirdat/qt000140
                     2014-10-18 00:26:09.793226  RBA 99981632


GGSCI > alter replicat DB2P_REP, extseqno 141, extrba 1158
REPLICAT altered.

GGSCI > start replicat DB_REP

Sending START request to MANAGER ...
REPLICAT DB_REP starting

 

After starting the replicat, normal processing of records started again.

 

References

 

How to Recover from an OGG-01028 Incompatible Record If the Trail Is Not Corrupt (Doc ID 1507462.1)

Oracle GoldenGate : DB2 to Oracle

This article will demonstrate heterogeneous replication using OGG from DB2 to Oracle.

1. Pre-requisites

OGG is installed on source and target systems.

2. Environment

DB2 on Mainfram Z/OS
Oracle Database : 11.2.0.3

DB2 to Oracle

3. Configuration on Source Server ( DB2 )

A. Parameter Files

Place all parameter files in the $GG_HOME/dirprm directory.

 

Extract Capture

> cat cdb2.prm

extract cdb2
--trailcharsetascii
sourcedb db2t, userid ggcap, password ggcap
exttrail ./dirdat/lt
transmemory directory(./dirtmp, 450m, 450m),transallsources 450m
table user.table1;

 

Extract Data Pump

> cat pdb2.prm

extract pdb2
rmthost target_server, mgrport 7809
rmttrail ./dirdat/rt
passthru
table user.table1;

 

Extract for Initial Load

> cat ildb2.prm

extract ildb2
sourceistable
sourcedb db2t, userid ggcap, password ggcap
rmthost target_server, mgrport 7809, tcpbufsize 1048576, tcpflushbytes 1048576
rmtfile ./dirdat/la, megabytes 2048, maxfiles 999
TABLE user.table1;

 

B. Add Trandata

 

cd $GG_HOME

> ./ggsci

GGSCI> DBLogin sourcedb db2t, userid ggcap, password ggcap</strong>
GGSCI> add trandata user.table1
GGSCI> exit

 

C. Source Table Definitions

 

> cd $GG_HOME/dirdef

> cat defgen_db2t.prm
DEFSFILE dirdef/db2t_source_tabs.def, PURGE
sourcedb db2t, userid ggcap, password ggcap
TABLE user.table1;

> cd $GG_HOME

>./defgen paramfile </strong><strong>./dirdef/defgen_db2t.prm NOEXTATTR

 

When done, copy the output file (dirdef/db2t_source_tabs.def) over to the target database server and put it in the $GG_HOME/dirdef directory.

 

D. Create and Start the Extract Processes

 

> ./ggsci

GGSCI> DBLogin sourcedb db2t, userid ggcap, password ggcap
GGSCI> add extract cdb2 , tranlog DB2T.BSDS01 , begin now
GGSCI> add exttrail ./dirdat/lt, extract cdb2
GGSCI> add extract pdb2, exttrailsource ./dirdat/lt
GGSCI> add rmttrail ./dirdat/rt, extract pdb2
GGSCI> start extract cdb2
GGSCI> start extract pdb2

 

E. Create and Start the Initial Load Capture Process

 

GGSCI> add extract ildb2 , sourceistable
GGSCI> start extract ildb2

 

3. Configuration on Target Server ( Oracle)

A. Parameter Files

Place all parameter files in the $GG_HOME/dirprm directory.

 

Replicat

> cat rdb2.prm

REPLICAT RDB2
SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0.3/dbhome_1")
SETENV (ORACLE_SID="ORA11G")
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8") 
USERID ggrep, PASSWORD ggrep 
DiscardRollover at 23:00
ReportRollover at 23:00 
ReportCount Every 5 Minutes, Rate
StatOptions ReportFetch 
DiscardFile ./dirrpt/RDB2.dsc, Append, Megabytes 100M
HandleCollisions
SOURCEDEFS ./dirdef/db2t_source_tabs.def
DbOptions SuppressTriggers
DbOptions DeferRefConst
MAP user.table1, table1 user.table1;

 

Initial load Replicat

> cat ilrdb2.prm

replicat ilrdb2
END RUNTIME
SETENV(ORACLE_HOME="/u01/app/oracle/product/11.2.0.3/dbhome_1")
SETENV(ORACLE_SID="ORA11G")
SETENV(NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
UserID ggrep, Password ggrep
SOURCEDEFS ./dirdef/db2t_source_tabs.def
DISCARDFILE ./dirrpt/ilrdb2.dsc, Purge
MAP user.table1, TARGET user.table1;

 

B. Initial load Steps – File to Replicat

After the initial load process from DB2 has completed, then continue with the configuration of the initial load replicat.

Configure and Start the Initial-Load Replicat

> cd $GG_HOME

>./ggsci

GGSCI> DBLogin UserID ggrep, Password ggrep
GGSCI> add replicat ilrdb2, ExtTrail ./dirdat/aa, CheckPointTable ggrep.ggs_checkpoint
GGSCI> start replicat ilrdb2

 

After the initial-load replicat is complete, continue on to the next step.
 

Configure and Start the Change Synchronization Replicat

> cd $GG_HOME

>./ggsci

GGSCI> add replicat rdb2, ExtTrail ./dirdat/rt, CheckPointTable ggrep.ggs_checkpoint
GGSCI> start extract rdb2

 

Monitor the replicat for any errors and the current time and position of the records it is applying.

Next, verify that the replicat has passed the point where the initial-load replicat stopped. You can compare both time and the RBA by looking in the rdb2.rpt report file and the current position of the change synchronization replication as shown by the “info rdb2” command.

Turn Off Handle Collisions

> cd $GG_HOME

>./ggsci

GGSCI> send replicat rdb2, nohandlecollisions

 

Also, edit the parameter file and comment out the HANDLECOLLISIONS line so that the next time the replicat is restarted, this parameter will not take effect.

GGSCI> edit params rdb2

REPLICAT RDB2
SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0.3/dbhome_1")
SETENV (ORACLE_SID="ORA11G")
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8") 
USERID ggrep, PASSWORD ggrep 
DiscardRollover at 23:00
ReportRollover at 23:00 
ReportCount Every 5 Minutes, Rate
StatOptions ReportFetch 
DiscardFile ./dirrpt/RDB2.dsc, Append, Megabytes 100M
--HandleCollisions
SOURCEDEFS ./dirdef/db2t_source_tabs.def
DbOptions SuppressTriggers
DbOptions DeferRefConst
MAP user.table1, table1 user.table1;

GoldenGate 12 – Classic, Coordinated and Integrated Replicat

I. New Replicat Modes Availalble in GolgenGate 12.1

Prior to the release of version 12.1, the replicat applied transactions to the target database serially.  To achieve parallelism, tables could be split across multiple replicats to get data applied quicker and reduce lag.  Drawbacks to this approach was the manual configuration of multiple replicats, trail files, and the limitation of not be able to split tables that had foreign key relationships across replicats.

The new Coordinated and Integrated Replicats can help you achieve transactions being applied in parallel on the target database without having to manually split out tables into multiple replicats.

Let’s take a look at the difference in the replicat modes.

A. Classic Replicat

Classic Replicat

Applies SQL serially.

Must be used if the database version is < 11.2.0.4

B. Coordinated Replicat

Coordinated Replicat

The configured replicat is called the coordinator.  It starts additional replicats based on number of threads specified.  The replicat name is limited to 5 characters and the threaded replicats will have this name appended with a 3 digit number ( RGGMA, RGGMA001, RGGMA002, … ).  The coordinator sends SQL to the threads, which can be applied in parallel.  The SQL is applied in committed order.

C. Integrated Replicat

Integrated Replicat

  • Constructs logical change records (LCR) that represent source database DML transactions (in committed order). DDL is applied directly by Replicat.  Transactions can be applied in parallel.
  •  Attaches to a background process in the target database known as a database inbound server by means of a lightweight streaming interface
  •  Transmits the LCRs to the inbound server, which applies the data to the target database

II. Configuration

Let’s setup one extract that will feed a heartbeat table into the three different replicat types. Here a diagram.

GG12 Replicat Modes

GG12 Replicat Modes

A. Parameter Files

Integrated Capture

EXTRACT cgga
SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0.4")
SETENV (ORACLE_SID="ORA11204")
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID c##ogg, PASSWORD ogg
TRANLOGOPTIONS MININGUSER c##ogg, MININGPASSWORD ogg
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 200, parallelism 1)
LOGALLSUPCOLS
DISCARDFILE ./dirrpt/CGGA.dsc, APPEND Megabytes 100M
INCLUDE ./dirprm/gg_hb_capture.mac
EXTTRAIL ./dirdat/GA
#gg_hb_capture ();

Pump

EXTRACT pgga
SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0.4")
SETENV (ORACLE_SID="ORA11204")
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID c##ogg, PASSWORD ogg
DISCARDFILE ./dirrpt/PGGA.dsc, APPEND Megabytes 100M
RMTHOST db1.vm.com, MGRPORT 7812
INCLUDE ./dirprm/gg_hb_pump.mac
--
RMTTRAIL ./dirdat/a1
NOPASSTHRU
#gg_hb_pump ();
--
RMTTRAIL ./dirdat/a2
NOPASSTHRU
#gg_hb_pump ();
--
RMTTRAIL ./dirdat/a3
NOPASSTHRU
#gg_hb_pump ();

Classic Replicat

REPLICAT rgga1
SETENV (ORACLE_HOME="/u01/app/oracle/product/12.1.0.1")
SETENV (ORACLE_SID="ORA12101")
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
UserId c##ogg@pdbogg, Password ogg
ASSUMETARGETDEFS
DBOPTIONS NOSUPPRESSTRIGGERS
DBOPTIONS DEFERREFCONST
DISCARDFILE ./dirrpt/RGGA1.dsc, append, megabytes 100
INCLUDE ./dirprm/gg_hb_classic_replicat.mac
#gg_hb_classic_replicat ();

Coordinated Replicat

REPLICAT rgga2
SETENV (ORACLE_HOME="/u01/app/oracle/product/12.1.0.1")
SETENV (ORACLE_SID="ORA12101")
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
UserId c##ogg@pdbogg, Password ogg
ASSUMETARGETDEFS
DBOPTIONS NOSUPPRESSTRIGGERS
DBOPTIONS DEFERREFCONST
DISCARDFILE ./dirrpt/RGGA2.dsc, append, megabytes 100
INCLUDE ./dirprm/gg_hb_coordinated_replicat.mac
#gg_hb_coordinated_replicat (1);

Integrated Replicat

REPLICAT rgga3
SETENV (ORACLE_HOME="/u01/app/oracle/product/12.1.0.1")
SETENV (ORACLE_SID="ORA12101")
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
UserId c##ogg@pdbogg, Password ogg
ASSUMETARGETDEFS
DBOPTIONS NOSUPPRESSTRIGGERS
DBOPTIONS DEFERREFCONST
DISCARDFILE ./dirrpt/RGGA3.dsc, append, megabytes 100
INCLUDE ./dirprm/gg_hb_integrated_replicat.mac
#gg_hb_integrated_replicat ();

Macros

$ cat gg_hb_classic_replicat.mac
MACRO #gg_hb_classic_replicat
BEGIN
MAP ggmon.ggmon_heartbeat, TARGET ggmon.classic_heartbeat, &amp;
 COLMAP (USEDEFAULTS &amp;
 , SOURCE_COMMIT = @GETENV ('GGHEADER', 'COMMITTIMESTAMP') &amp;
 , TARGET_COMMIT = @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &amp;
 , CAP_SOURCE_COMMIT = @TOKEN('CAP_SOURCE_COMMIT') &amp;
 , CAPGROUP = @TOKEN ('CAPGROUP') &amp;
 , CAPTIME = @TOKEN ('CAPTIME') &amp;
 , PMPGROUP = @TOKEN ('PMPGROUP') &amp;
 , PMPTIME = @TOKEN ('PMPTIME') &amp;
 , REPGROUP = @GETENV ('GGENVIRONMENT', 'GROUPNAME') &amp;
 , REPTIME = @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &amp;
 );
MAP ggmon.ggmon_heartbeat, TARGET ggmon.classic_heartbeat_history, &amp;
 INSERTALLRECORDS &amp;
 , COLMAP (USEDEFAULTS &amp;
 , SOURCE_COMMIT = @GETENV ('GGHEADER', 'COMMITTIMESTAMP') &amp;
 , TARGET_COMMIT = @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &amp;
 , CAP_SOURCE_COMMIT = @TOKEN('CAP_SOURCE_COMMIT') &amp;
 , CAPGROUP = @TOKEN ('CAPGROUP') &amp;
 , CAPTIME = @TOKEN ('CAPTIME') &amp;
 , PMPGROUP = @TOKEN ('PMPGROUP') &amp;
 , PMPTIME = @TOKEN ('PMPTIME') &amp;
 , REPGROUP = @GETENV ('GGENVIRONMENT', 'GROUPNAME') &amp;
 , REPTIME = @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &amp;
 );
END;

$ cat gg_hb_coordinated_replicat.mac
MACRO #gg_hb_coordinated_replicat
PARAMS (#thread)
BEGIN
MAP ggmon.ggmon_heartbeat, TARGET ggmon.coordinated_heartbeat, THREAD (#thread) &amp;
 , &amp;
 COLMAP (USEDEFAULTS &amp;
 , SOURCE_COMMIT = @GETENV ('GGHEADER', 'COMMITTIMESTAMP') &amp;
 , TARGET_COMMIT = @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &amp;
 , CAP_SOURCE_COMMIT = @TOKEN('CAP_SOURCE_COMMIT') &amp;
 , CAPGROUP = @TOKEN ('CAPGROUP') &amp;
 , CAPTIME = @TOKEN ('CAPTIME') &amp;
 , PMPGROUP = @TOKEN ('PMPGROUP') &amp;
 , PMPTIME = @TOKEN ('PMPTIME') &amp;
 , REPGROUP = @GETENV ('GGENVIRONMENT', 'GROUPNAME') &amp;
 , REPTIME = @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &amp;
 );
MAP ggmon.ggmon_heartbeat, TARGET ggmon.coordinated_heartbeat_history, THREAD (#thread) &amp;
 INSERTALLRECORDS &amp;
 , COLMAP (USEDEFAULTS &amp;
 , SOURCE_COMMIT = @GETENV ('GGHEADER', 'COMMITTIMESTAMP') &amp;
 , TARGET_COMMIT = @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &amp;
 , CAP_SOURCE_COMMIT = @TOKEN('CAP_SOURCE_COMMIT') &amp;
 , CAPGROUP = @TOKEN ('CAPGROUP') &amp;
 , CAPTIME = @TOKEN ('CAPTIME') &amp;
 , PMPGROUP = @TOKEN ('PMPGROUP') &amp;
 , PMPTIME = @TOKEN ('PMPTIME') &amp;
 , REPGROUP = @GETENV ('GGENVIRONMENT', 'GROUPNAME') &amp;
 , REPTIME = @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &amp;
 );
END;

$ cat gg_hb_integrated_replicat.mac
MACRO #gg_hb_integrated_replicat
BEGIN
MAP ggmon.ggmon_heartbeat, TARGET ggmon.integrated_heartbeat, &
  COLMAP (USEDEFAULTS &
  , SOURCE_COMMIT = @GETENV ('GGHEADER', 'COMMITTIMESTAMP') &
  , TARGET_COMMIT = @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &
  , CAP_SOURCE_COMMIT = @TOKEN('CAP_SOURCE_COMMIT') &
  , CAPGROUP = @TOKEN ('CAPGROUP') &
  , CAPTIME = @TOKEN ('CAPTIME') &
  , PMPGROUP = @TOKEN ('PMPGROUP') &
  , PMPTIME = @TOKEN ('PMPTIME') &
  , REPGROUP = @GETENV ('GGENVIRONMENT', 'GROUPNAME') &
  , REPTIME =  @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &
  );
MAP ggmon.ggmon_heartbeat, TARGET ggmon.integrated_heartbeat_history, &
  INSERTALLRECORDS &
  , COLMAP (USEDEFAULTS &
  , SOURCE_COMMIT = @GETENV ('GGHEADER', 'COMMITTIMESTAMP') &
  , TARGET_COMMIT = @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &
  , CAP_SOURCE_COMMIT = @TOKEN('CAP_SOURCE_COMMIT') &
  , CAPGROUP = @TOKEN ('CAPGROUP') &
  , CAPTIME = @TOKEN ('CAPTIME') &
  , PMPGROUP = @TOKEN ('PMPGROUP') &
  , PMPTIME = @TOKEN ('PMPTIME') &
  , REPGROUP = @GETENV ('GGENVIRONMENT', 'GROUPNAME') &
  , REPTIME =  @DATE ('YYYY-MM-DD HH:MI:SS.FFFFFF','JTS',@GETENV ('JULIANTIMESTAMP')) &
  );
END;

B. Creating Extract and Replicats

Oracle 11.2.0.4 Database

DBLogin UserId c##ogg, Password ogg
MiningDBLogin UserId c##ogg, Password ogg
REGISTER EXTRACT cgga DATABASE
ADD    SchemaTranData ggmon

ADD EXTRACT cgga, INTEGRATED TRANLOG, BEGIN NOW, DESC "***** Integrated Capture *****"
ADD EXTTRAIL ./dirdat/GA, EXTRACT cgga

ADD EXTRACT pgga, EXTTRAILSOURCE ./dirdat/GA, desc "***** Pump *****"
ADD RMTTRAIL ./dirdat/a1, EXTRACT pgga
ADD RMTTRAIL ./dirdat/a2, EXTRACT pgga
ADD RMTTRAIL ./dirdat/a3, EXTRACT pgga


GGSCI (db1.vm.com) 9> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     CGGA        00:00:10      00:00:02
Description "***** Integrated Capture *****"
EXTRACT     RUNNING     PGGA        00:00:00      00:00:08
Description "***** Pump *****"

Oracle 12.1.0.1 Database

DBLogin UserId c##ogg, Password ogg
MiningDBLogin UserId c##ogg, Password ogg
DBLogin UserId c##ogg@pdbogg, Password ogg
-- Classic Replicat
ADD REPLICAT rgga1, ExtTrail ./dirdat/a1, checkpointtable pdbogg.c##ogg.checkpoint, desc "***** Classic Replicat *****"
-- Coordinated Replicat
ADD REPLICAT rgga2, Coordinated MaxThreads 2, ExtTrail ./dirdat/a2, checkpointtable pdbogg.c##ogg.checkpoint, desc "***** Coordinated Replicat *****"
-- Integrated Replicat
ADD REPLICAT rgga3, Integrated, ExtTrail ./dirdat/a3, desc "***** Integrated Replicat *****"


GGSCI (db1.vm.com) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     RGGA1       00:00:00      00:00:07
Description "***** Classic Replicat *****"
REPLICAT    RUNNING     RGGA2       00:00:00      00:00:07
Description "***** Coordinated Replicat *****"
REPLICAT    RUNNING     RGGA3       00:00:00      00:00:05
Description "***** Integrated Replicat *****"

C. Push Data Though via Heartbeat Table

After starting up the heartbeat table, we can query to see the data being replicated across each replicat type. The extract and replicat names can be seen in the output. Note that name of the coordinated replicat group has the three digit number appended to the original name.

Please keep in mind that this configuration was done on a small virtual machine, so the lag times are a bit high, but this blog was only about the functionality of the different replicat types.

GGMON:pdbogg> @all_rep_lag.sql
***********************************************************
Heartbeat
***********************************************************

REPLICAT    CAP                                 LAST              CAP    PMP    REP  TOTAL
TYPE        DB NAME  CAPGROUP PMPGROUP REPGROUP UPDATE            LAG    LAG    LAG    LAG
----------- -------- -------- -------- -------- -------------- ------ ------ ------ ------
Classic     ORA11204 CGGA     PGGA     RGGA1    03/06 02:42:31    5.9    5.3    2.3   13.5
Coordinated ORA11204 CGGA     PGGA     RGGA2001 03/06 02:42:31    5.9    5.3    3.0   14.2
Integrated  ORA11204 CGGA     PGGA     RGGA3    03/06 02:42:31    5.9    5.3   12.6   23.8

***********************************************************
Heartbeat History
***********************************************************

                                                   AVG    AVG    AVG    AVG
REPLICAT    CAP                                    CAP    PMP    REP  TOTAL
TYPE        DB NAME  CAPGROUP PMPGROUP REPGROUP    LAG    LAG    LAG    LAG
----------- -------- -------- -------- -------- ------ ------ ------ ------
Classic     ORA11204 CGGA     PGGA     RGGA1       3.6    5.4    4.1   13.2
Coordinated ORA11204 CGGA     PGGA     RGGA2001    3.6    5.4    2.5   11.6
Integrated  ORA11204 CGGA     PGGA     RGGA3       3.6    5.4   10.0   19.1

A copy of the all_rep_lag.sql script can be found here .

GoldenGate 12 Users, Privileges and Security on Multitenant DBs

I. Users and Privileges

With the release of the latest versions of GG and database, there have been many modifications that require changes in how users are created, logins and the privileges granted.

A. Database Users

If the source database is a multitenant container database, the Extract user must be a common user and must log into the root container.

One extract capture process can capture from multiple pluggable databases to a single trail.

A replicat process can apply transactions to only one pluggable database.

B. Privileges

Click the following link to see a list of privileges required for a GoldenGate user on a 11.2.0.4 or greater database.

Users Privileges for GolgenGate Users for DB 11.2.0.4 or Higher

CREATE USER c##ogg IDENTIFIED BY ogg
DEFAULT TABLESPACE ogg_tbsp
TEMPORARY TABLESPACE temp
PROFILE DEFAULT ACCOUNT UNLOCK
CONTAINER=ALL;

ALTER USER c##ogg DEFAULT ROLE ALL CONTAINER=ALL;
ALTER USER c##ogg QUOTA UNLIMITED ON ogg_tbsp CONTAINER=ALL;

GRANT CREATE SESSION TO c##ogg CONTAINER=ALL;
GRANT CONNECT TO c##ogg CONTAINER=ALL;
GRANT RESOURCE TO c##ogg CONTAINER=ALL;
GRANT ALTER SYSTEM TO c##ogg CONTAINER=ALL;
GRANT DBA TO c##ogg CONTAINER=ALL;
GRANT SELECT ANY TRANSACTION TO c##ogg CONTAINER=ALL;
GRANT ALTER ANY TABLE TO c##ogg CONTAINER=ALL;

BEGIN
 dbms_goldengate_auth.grant_admin_privilege
 (
 grantee => 'C##OGG',
 privilege_type => '*',
 grant_select_privileges => TRUE,
 do_grants => TRUE,
 container => 'ALL'
 );
END;
/

C. Logins

1. Capture DB Login

DBLogin UserId c##ogg, Password ogg

2. Pluggable DB Login

DBLogin UserId c##ogg@pdbogg, Password ogg

II. Security

A. Credential Store

The credential store is used to maintain encrypted database passwords, associating an alias with the userid.

ADD CREDENTIALSTORE

ALTER CREDENTIALSTORE ADD USER c##ogg PASSWORD ogg ALIAS ogg_user

ALTER CREDENTIALSTORE ADD USER c##ogg@pdbogg PASSWORD ogg ALIAS pdbogg_user

B. Logins

1. Capture DB Login

DBLOGIN UserIdAlias ogg_user

2. Pluggable DB Login

DBLOGIN UserIdAlias pdbogg_user

Mapping problem with compressed update record – Key column missing from update

Here’s an example of an update statement failing on the target database on a table without a primary or unique key.

Since there are no keys defined on the table, the replicat will consider all the columns as key columns.

The update statement that was processed by the extract only logged the column values that were changed along with the columns defined by the keycols parameter in the table statement, in this case the objectid column.

The replicat ABENDed since it did not have values for allt he columns. Here’s the discard file which show the specifics on the columns and the error message.

Oracle GoldenGate Delivery for Oracle process started, group RWPC discard file opened: 2013-09-08 11:21:51

Key column ROV_RNAME (354) is missing from update on table WPC.WPCTBL
Key column A_RCPT_DATE (355) is missing from update on table WPC.WPCTBL
Key column A_SCAN_TIME (356) is missing from update on table WPC.WPCTBL
Key column DOCTYPE (357) is missing from update on table WPC.WPCTBL
Key column ORIGDATE (358) is missing from update on table WPC.WPCTBL
Key column STRACKING (359) is missing from update on table WPC.WPCTBL
Key column M_IND (360) is missing from update on table WPC.WPCTBL
Key column NCOB (361) is missing from update on table WPC.WPCTBL
Key column RET_CBT (362) is missing from update on table WPC.WPCTBL
Key column M_COUNTER (363) is missing from update on table WPC.WPCTBL
Key column IS_DOC (364) is missing from update on table WPC.WPCTBL
Missing 11 key columns in update for table WPC.WPCTBL.
Current time: 2013-09-08 11:21:53
Discarded record from action ABEND on error 0

Aborting transaction on /u01/app/oracle/product/golden_gate/dirdat/wP beginning at seqno 76 rba 695700
                         error at seqno 76 rba 973932
Problem replicating WPC.WPCTBL to WPC.WPCTBL
Mapping problem with compressed update record (target format)...
*
CATEGORYID = 3000
OBJECTID = 1892683845

Doing some research on My Oracle Support turned up the following two notes that were used to come up with a solution.

Replicat Abending With Mapping Error and discard file shows Missing Key Columns (Doc ID 1276538.1)
Can I Use A Wildcard In For All Of The Tables Except One so that I may describe it separately? (Doc ID 970419.1)

Two parameters were put in the replicat parameter file to workaround the issue.

DYNAMICRESOLUTION - enables fast process startup when there are numerous tables specified in TABLE or MAP statements

WILDCARDRESOLVE IMMEDIATE - objects are processed at startup

Here’s a snippet of the replicat parameter file with the new mappings. The replicat is able to process the transaction after the change since the KEYCOLS parameter specified objectid to be the table’s key column. Hence, GG updates the columns in the trail, leaving those mentioned before in the discard file unchanged.

> cat rwpc.prm 
REPLICAT rwpc
...
-- ------------------------------------------------------------------
-- mappings
-- ------------------------------------------------------------------
WILDCARDRESOLVE IMMEDIATE
MAP WPC.WPCTBL, TARGET WPC.WPCTBL, KEYCOLS (objectid);
DYNAMICRESOLUTION
MAPEXCLUDE WPC.WPCTBL;
MAP WPC.*, TARGET WPC.*;

GoldenGate Process Memory Usage

From the GG Installation documentation:

Each Extract and Replicat process needs approximately 25-55 MB of memory, or more depending on the size of the transactions and the number of concurrent transactions.

So let’s see some example of how much memory is being used. I wrote a short script to show the memory usage by using “ps” and have two examples, one on Linux and one on Solaris.

The first example ( on Linux ) shows the usage of GoldenGate extract and replicat processes configured to process a heartbeat table, so not very much activity.

The second example ( on Solaris ) show the usage of GoldenGate processes on a live system with lots of activity.

Due to differences on how ps behaves between Linux and Solaris, I display either the group name itself, or the parameter file of the group, which contains the group name. The unix script can be found at the bottom of this blog.

Here’s the example running on Linux. This configuration does not have very many transactions.


> ./gg_memory_usage.sh
OSNAME = Linux

#####################################
# Individual extract Process Usage #
#####################################
48.3516 MB CGGMONX
48.6094 MB CGGMONY
31.6055 MB PGGMONX
31.6055 MB PGGMONY

#####################################
#   Total extract Process Usage    #
#####################################
Number of processes      = 4
AVG Memory usage/process = 40.043 MB
Total memory usage       = 160.172  MB

#####################################
# Individual replicat Process Usage #
#####################################
29.8398 MB RGGMONX
29.832 MB RGGMONY

#####################################
#   Total replicat Process Usage    #
#####################################
Number of processes      = 2
AVG Memory usage/process = 29.8359 MB
Total memory usage       = 59.6719  MB



Here’s the example running on Solaris. This configuration has processed over 11,000 transactions per second at peak times, but normally runs around 5000 transactions per second on average.


oracle@sadcsim1db1[CSOTADB1] $ ./gg_memory_usage.sh
OSNAME = SunOS

#####################################
# Individual extract Process Usage #
#####################################
7565.41 MB /ggs/dirprm/e1.prm
5435.1 MB /ggs/dirprm/e2.prm
160.758 MB /ggs/dirprm/p1.prm
156.758 MB /ggs/dirprm/p2.prm
164.758 MB /ggs/dirprm/p3.prm
160.758 MB /ggs/dirprm/p4.prm
160.758 MB /ggs/dirprm/p5.prm
156.758 MB /ggs/dirprm/p6.prm
164.758 MB /ggs/dirprm/p7.prm
160.758 MB /ggs/dirprm/p8.prm
160.758 MB /ggs/dirprm/p9.prm
156.758 MB /ggs/dirprm/p10.prm
164.758 MB /ggs/dirprm/p11.prm
160.758 MB /ggs/dirprm/p12.prm
160.758 MB /ggs/dirprm/p13.prm
156.758 MB /ggs/dirprm/p14.prm
164.758 MB /ggs/dirprm/p15.prm
160.758 MB /ggs/dirprm/p16.prm

#####################################
#   Total extract Process Usage    #
#####################################
Number of processes      = 18
AVG Memory usage/process = 865.147 MB
Total memory usage       = 15572.6  MB

#####################################
# Individual replicat Process Usage #
#####################################
148.688 MB /ggs/dirprm/r1.prm
144.688 MB /ggs/dirprm/r2.prm
144.688 MB /ggs/dirprm/r3.prm
148.688 MB /ggs/dirprm/r4.prm
144.688 MB /ggs/dirprm/r5.prm
144.688 MB /ggs/dirprm/r6.prm
144.688 MB /ggs/dirprm/r7.prm
144.688 MB /ggs/dirprm/r8.prm
144.688 MB /ggs/dirprm/r9.prm
144.688 MB /ggs/dirprm/r10.prm
144.688 MB /ggs/dirprm/r11.prm
144.688 MB /ggs/dirprm/r12.prm
148.688 MB /ggs/dirprm/r13.prm
144.688 MB /ggs/dirprm/r14.prm
144.688 MB /ggs/dirprm/r15.prm
148.688 MB /ggs/dirprm/r16.prm
144.688 MB /ggs/dirprm/r17.prm
144.688 MB /ggs/dirprm/r18.prm
144.688 MB /ggs/dirprm/r19.prm
144.688 MB /ggs/dirprm/r20.prm
144.688 MB /ggs/dirprm/r21.prm
144.688 MB /ggs/dirprm/r22.prm
144.688 MB /ggs/dirprm/r23.prm
144.688 MB /ggs/dirprm/r24.prm
148.688 MB /ggs/dirprm/r25.prm
144.688 MB /ggs/dirprm/r26.prm
144.688 MB /ggs/dirprm/r27.prm
148.68 MB /ggs/dirprm/r28.prm
144.68 MB /ggs/dirprm/r29.prm
144.68 MB /ggs/dirprm/r30.prm
144.68 MB /ggs/dirprm/r31.prm
144.68 MB /ggs/dirprm/r32.prm
144.68 MB /ggs/dirprm/r33.prm
144.68 MB /ggs/dirprm/r34.prm
144.68 MB /ggs/dirprm/r35.prm
144.68 MB /ggs/dirprm/r36.prm

#####################################
#   Total replicat Process Usage    #
#####################################
Number of processes      = 36
AVG Memory usage/process = 145.352 MB
Total memory usage       = 5232.68  MB

Big difference! If you have a busy system, make sure you have memory. You’ll probably have to do testing since the memory requirements will vary depending on both the number of transactions and the size of the transactions.

Here’s the script :


#!/bin/bash

###############################
# determine the OS type
###############################
OSNAME=`uname`

case "$OSNAME" in
  "SunOS")
    echo "OSNAME = $OSNAME"
    ;;
  "Linux")
    echo "OSNAME = $OSNAME"
    ;;
  "*")
    echo "This script has not been verified on $OSNAME"
    exit 1
    ;;
esac

###############################
# set the temp file
###############################
TMPFILE=/tmp/pmem.tmp
if [ -f $TMPFILE ]
then
  rm -f $TMPFILE
fi

################################
# loop over the gg process types
################################
PROCESSES="extract replicat"

for PROCESS in $PROCESSES
do
  FLAG=""
  FLAG=`ps -ef | grep $PROCESS`
  if [ -z "FLAG" ]
  then
    echo "No $PROCESS processes found"
  else
    echo
    echo "#####################################"
    echo "# Individual $PROCESS Process Usage #"
    echo "#####################################"
    case "$OSNAME" in
      "Linux")
        ps -C $PROCESS -O rss > $TMPFILE
        cat $TMPFILE | grep $PROCESS | awk '{print $2/1024, "MB", $12}' | sort -k 2
        ;;
      "SunOS")
        ps -efo vsz,uid,pid,ppid,pcpu,args | grep -v grep | grep $PROCESS > $TMPFILE
        cat $TMPFILE | grep $PROCESS | awk '{print $1/1024, "MB", $8}' | sort -k 2
        ;;
      "*")
        echo "This script has not been verified on $OSNAME"
        exit 1
        ;;
    esac
    rm -f $TMPFILE

    echo
    echo "#####################################"
    echo "#   Total $PROCESS Process Usage    #"
    echo "#####################################"
    case "$OSNAME" in
      "Linux")
        ps -C $PROCESS -O rss > $TMPFILE
        cat $TMPFILE | grep $PROCESS | awk '{count ++; sum=sum+$2; } END \
          { print "Number of processes      =",count; \
          print "AVG Memory usage/process =",sum/1024/count, "MB"; \
          print "Total memory usage       =", sum/1024,  " MB"}'
        ;;
      "SunOS")
        ps -efo vsz,uid,pid,ppid,pcpu,comm | grep -v grep | grep $PROCESS > $TMPFILE
        cat $TMPFILE | awk '{count ++; sum=sum+$1; } END \
          { print "Number of processes      =",count; \
          print "AVG Memory usage/process =",sum/1024/count, "MB"; \
          print "Total memory usage       =", sum/1024,  " MB"}'
        ;;
      "*")
        echo "This script has not been verified on $OSNAME"
        exit 1
        ;;
    esac
    rm -f $TMPFILE
  fi
done

exit 0

GoldenGate Extract Pump process keeps ABENDing – OGG-01031

I encountered an odd situation recently where the extract pump processes kept ABENDing. The report file listed the OGG-01031 error message.

GGSCI (exadb04.new.fastrieve.com) 23> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     CBOA        00:00:00      00:00:02    
EXTRACT     RUNNING     CGGMONX     00:00:01      00:00:05    
EXTRACT     ABENDED     PBOA        00:00:00      01:46:40    
EXTRACT     ABENDED     PGGMONX     00:00:00      01:43:14    
REPLICAT    RUNNING     RGGMONY     00:00:00      00:03:24    


GGSCI (exadb04.new.fastrieve.com) 1> view report pggmonx


***********************************************************************
                 Oracle GoldenGate Capture for Oracle
    Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
   Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:42:16
 
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.


                    Starting at 2013-08-14 16:15:12
***********************************************************************
.
.
.

2013-08-14 16:15:28  ERROR   OGG-01031  There is a problem in network communication, a remote file problem,
encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. (Reply received
is Unable to open file "./dirdat/gX000017" (error 11, Resource temporarily unavailable)).

I wasn’t able to find very much on this issue, but I did run across MOS note 1318980.1, and it turned out to have the fix in my case. By renaming the associated $GG_HOME/dirchk/*.cps and $GG_HOME/dirchk/*.cpe files, I was able to get the processes running again. Note that in this case the $GG_HOME/dirchk directory resides on an NFS mount. Apparently this type of error can occur on shared file systems causing the $GG_HOME/dirchk entries to be non-updatable.

Extract/Replicat Shows Already Running When Started and Already Stopped When Stopped from GGSCI, OGG-01031, Reply received is Unable to open file (Doc ID 1318980.1)

[exadb04:oracle:FTRVXP4] /u01/app/oracle/product/golden_gate/dirchk 
> ls -lhF
total 696K
-rw-rw-r-- 1 oracle dba  72K Aug 14 16:28 PGGMONX.cpe
-rw-rw-r-- 1 oracle dba 2.2K Aug 14 16:27 PGGMONX.cps

[exadb04:oracle:FTRVXP4] /u01/app/oracle/product/golden_gate/dirchk 
> mv PGGMONX.cpe PGGMONX.cpe_new

[exadb04:oracle:FTRVXP4] /u01/app/oracle/product/golden_gate/dirchk 
> mv PGGMONX.cps PGGMONX.cps_new

[exadb04:oracle:FTRVXP4] /u01/app/oracle/product/golden_gate/dirchk 
> ls -lhF
total 696K
-rw-rw-r-- 1 oracle dba  72K Aug 14 16:28 PGGMONX.cpe_new
-rw-rw-r-- 1 oracle dba 2.2K Aug 14 16:27 PGGMONX.cps_new

[exadb04:oracle:FTRVXP4] /u01/app/oracle/product/golden_gate/dirchk 
> mv PGGMONX.cpe_new PGGMONX.cpe

[exadb04:oracle:FTRVXP4] /u01/app/oracle/product/golden_gate/dirchk 
> mv PGGMONX.cps_new PGGMONX.cps

[exadb04:oracle:FTRVXP4] /u01/app/oracle/product/golden_gate/dirchk 
> ls -lhF
total 696K
-rw-rw-r-- 1 oracle dba  72K Aug 14 16:28 PGGMONX.cpe
-rw-rw-r-- 1 oracle dba 2.2K Aug 14 16:27 PGGMONX.cps

After restart the processes, everything was caught up in a couple of minutes.

GGSCI (exadb04.new.fastrieve.com) 11> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     CBOA        00:00:07      00:00:04    
Description "***** Capture for Bank of America *****"
EXTRACT     RUNNING     CGGMONX     00:02:21      00:00:01    
Description "***** Capture for GGMON X *****"
EXTRACT     RUNNING     PBOA        00:00:00      00:00:05    
EXTRACT     RUNNING     PGGMONX     00:00:00      00:00:08    
Description "***** Pump for GGMON X *****"
REPLICAT    RUNNING     RGGMONY     00:00:00      00:00:02    

GoldenGate Initial-Load Direct Load

This article will demostrate how to perform a OGG Initial Load using the Direct Load technique.

Other Initial Load techniques and their advantages and disadvantages can be found here.

Pre-requisites

OGG is installed on source and target systems

Environment

OGG : 11.2.1.0.1
Database : 11.2.0.3
Linux : OEL 6 ( 64 bit )

Direct Load Steps

1. Configure the initial-load processes

Extract


GGSCI> ADD EXTRACT icdl, SOURCEISTABLE

GGSCI> INFO EXTRACT *, TASKS

EXTRACT    ICDL      Initialized   2013-05-23 16:20   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE


cat ./dirprm/icdl.prm

EXTRACT icdl
-- --------------------------------------------------
-- environment
-- --------------------------------------------------
SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")
SETENV (ORACLE_SID="GGDB1")
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID ggate, PASSWORD ggate
-- --------------------------------------------------
RMTHOST gglab1n2, mgrport 7809
RMTTASK REPLICAT, GROUP irdl
TABLE soe.inventories;
TABLE soe.product_descriptions;

Replicat


GGSCI> ADD REPLICAT irdl, SPECIALRUN

GGSCI> INFO REPLICAT *, TASKS

REPLICAT   IRDL      Initialized   2013-05-23 16:21   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:11 ago)
Log Read Checkpoint  Not Available
Task                 SPECIALRUN


cat ./dirprm/irdl.prm

REPLICAT irdl
-- --------------------------------------------------
-- environment 
-- --------------------------------------------------
SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")
SETENV (ORACLE_SID="GGDB2")
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID ggate, PASSWORD ggate
-- --------------------------------------------------
-- options
-- --------------------------------------------------
ASSUMETARGETDEFS
DBOPTIONS SUPPRESSTRIGGERS
-- --------------------------------------------------
-- files
-- --------------------------------------------------
DISCARDFILE ./dirrpt/irdl.dsc, PURGE
-- --------------------------------------------------
-- mappings
-- --------------------------------------------------
MAP soe.inventories , TARGET soe.inventories;
MAP soe.product_descriptions , TARGET soe.product_descriptions;

2. Configure the change-synchronization processes

This is the normal replication methods, so I will not cover them here to keep things short. You can see example of this in some of my other posts.

3. Start the change-synchronization Extract on the Source site

GGSCI> start extract csoea

4. Start the Initial Load Extract

GGSCI (gglab1n1) > START EXTRACT icdl

GGSCI (gglab1n1) > INFO EXTRACT *, TASKS

EXTRACT    ICDL      Last Started 2013-05-23 16:22   Status RUNNING
Checkpoint Lag       Not Available
Log Read Checkpoint  Table SOE.INVENTORIES
                     2013-05-23 16:22:50  Record 14970
Task                 SOURCEISTABLE

Note that both the extract and replicat processes are now running. This is a result of the “RMTTASK” parameter in the extract parameter file.

GGSCI (gglab1n2)> INFO REPLICAT *, TASKS

REPLICAT   IRDL      Initialized   2013-05-23 16:21   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:02:16 ago)
Log Read Checkpoint  Not Available
Task                 SPECIALRUN

5. Check the report on both the source and target systems

Monitoring the report files will show you what tables are being processed and when the load is complete.

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


Report at 2013-05-23 18:47:03 (activity since 2013-05-23 16:22:32)

Output to irdl:

From Table SOE.INVENTORIES:
       #                   inserts:    900667
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0
From Table SOE.PRODUCT_DESCRIPTIONS:
       #                   inserts:      1000
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0
...

You can also use the info command to check the status.

GGSCI (gglab1n1) > INFO EXTRACT *, TASKS

EXTRACT    ICDL      Last Started 2013-05-23 16:22   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table SOE.PRODUCT_DESCRIPTIONS
                     2013-05-23 18:47:01  Record 1000
Task                 SOURCEISTABLE


GGSCI (gglab1n2) > INFO REPLICAT *, TASKS

REPLICAT   IRDL      Initialized   2013-05-23 16:21   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:08:29 ago)
Log Read Checkpoint  Not Available
Task                 SPECIALRUN

6. Once you verify that the intial load completes, start change synchronization REPLICAT on the Target system

GGSCI> start replicat rsoeas

Monitor the replicat for any errors and the current time and position of the records it is applying.

Next, verify that the change synchronization replicat has passed the point where the initial-load replicat stopped.

7. Turn off HANDLECOLLISIONS

SEND REPLICAT rsoeas, NOHANDLECOLLISIONS

Lastly, edit the replicat parameter file and remove HANDLECOLLISIONS so that it is not enabled next time the replicat starts.

GoldenGate Initial-Load Trail to Replicat

This article will demostrate how to perform a OGG Initial Load using the Trail to Replicat technique.

Other Initial Load techniques and their advantages and disadvantages can be found here.

Pre-requisites

OGG is installed on source and target systems

Environment

OGG : 11.2.1.0.1
Database : 11.2.0.3
Linux : OEL 6 ( 64 bit )

Trail to Replicat Steps

1. Configure the initial-load processes

Extract


SOURCEISTABLE
-- --------------------------------------------------
-- environment
-- --------------------------------------------------
SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")
SETENV (ORACLE_SID="GGDB1")
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID ggate, PASSWORD ggate
-- --------------------------------------------------
RMTHOST gglab1n2, mgrport 7809
rmtfile /u01/app/oracle/product/golden_gate/dirdat/il, maxfiles 3, megabytes 2000, PURGE
TABLE soe.*;

Replicat


REPLICAT irttr
-- --------------------------------------------------
-- environment
-- --------------------------------------------------
SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")
SETENV (ORACLE_SID="GGDB2")
SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID ggate, PASSWORD ggate
-- --------------------------------------------------
-- options
-- --------------------------------------------------
ASSUMETARGETDEFS
DBOPTIONS SUPPRESSTRIGGERS
--DBOPTIONS DEFERREFCONST
-- --------------------------------------------------
-- files
-- --------------------------------------------------
DISCARDFILE ./dirrpt/irttr.dsc, PURGE
-- --------------------------------------------------
-- mappings
-- --------------------------------------------------
MAP soe.* , TARGET soe.*;

2. Configure the change-synchronization processes

This is the normal replication methods, so I will not cover them here to keep things short. You can see example of this in some of my other posts.

3. Start the change-synchronization Extract

GGSCI> DBLOGIN USERID ggate, PASSWORD password ggate

GGSCI> FLUSH SEQUENCE soe.*

GGSCI> start extract csoea

4. Start the initial-load extract

extract paramfile ./dirprm/icttr.prm reportfile ./dirrpt/icttr.rpt

You should observe the following:

* the collector starting on target as seen in the ggserr.log

* the trail files in dirdat directory on target ( $GG_HOME/dirdat/il* )

* report file in dirrpt directory on source

Monitor the icttr.rpt report file for any errors.

5. Start the initial-load replicat on the target environment

Since we are using normal trail files here, the replicat can be started w/o having to wait on the initial-load extract to complete.

add replicat irttr, exttrail ./dirdat/il, nodbcheckpoint
replicat paramfile ./dirprm/irttr.prm reportfile ./dirrpt/irttr.rpt

Monitor the report file for the initial-load replicat and wait until it the intiial-load extract is complete and the replicat is at the EOF with no more records to process. If there are no errors, then you can continue on to the next step.

However, I hit errors here. The following is from the irttr.rpt file, the info command, and the ggserr.log respectively.

...
Reading ./dirdat/il000000, current RBA 3564957, 33000 records

Report at 2013-05-22 21:50:21 (activity since 2013-05-22 21:49:31)

From Table SOE.INVENTORIES to SOE.INVENTORIES:
       #                   inserts:     33000
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0


Last log location read:
     FILE:      ./dirdat/il000000
     SEQNO:     0
     RBA:       3564957
     TIMESTAMP: 2013-05-22 21:29:46.677112
     EOF:       NO
     READERR:   0


2013-05-22 21:50:21  ERROR   OGG-01668  PROCESS ABENDING.
...
GGSCI (gglab1n2.enkitec.com) 2> info *

REPLICAT   IRTTR     Last Started 2013-05-22 21:49   Status ABENDED
Checkpoint Lag       00:20:34 (updated 15:45:19 ago)
Log Read Checkpoint  File ./dirdat/il000000
                     2013-05-22 21:29:46.677112  RBA 3565065
2013-05-22 21:32:18  INFO    OGG-00995  Oracle GoldenGate Delivery for Oracle, irttr.prm:  REPLICAT IRTTR starting.
2013-05-22 21:32:18  INFO    OGG-03035  Oracle GoldenGate Delivery for Oracle, irttr.prm:  Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
2013-05-22 21:32:18  INFO    OGG-01815  Oracle GoldenGate Delivery for Oracle, irttr.prm:  Virtual Memory Facilities for: COM
    anon alloc: mmap(MAP_ANON)  anon free: munmap
    file alloc: mmap(MAP_SHARED)  file free: munmap
    target directories:
    /u01/app/oracle/product/golden_gate/dirtmp.
2013-05-22 21:32:18  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, irttr.prm:  REPLICAT IRTTR started.
2013-05-22 21:33:02  ERROR   OGG-00446  Oracle GoldenGate Delivery for Oracle, irttr.prm:  CSN not found in supplemental file /u01/app/oracle/product/golden_gate/dirchk/IRTTR.cps.
2013-05-22 21:33:02  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, irttr.prm:  PROCESS ABENDING.

This turns out to be a know bug in OGG Version 11.2.1.0.1 and later. Unfortunately, the fix will not be available until 11.2.1.0.4.

Check out the follow note for more details.

OGG v11.2 Replicat fails OGG-00868 OCI Error ORA-01400: cannot insert NULL into
(“OGG_ADMIN”.”CHECKPOINT_LOX”.”LOG_CMPLT_CSN”) , ERROR OGG-00446 CSN not found in
supplemental file /u01/app/oracle/goldengate/dirchk/RPBSINI.cps. [ID 1468424.1]

So I leave off here waiting for the future version.

——————————————————————————-

However, if I were able to continue, there are only two more steps.

6. Start the change-synchronization Replicat

GGSCI> start replicat rsoeas

7. Turn off HANDLECOLLISIONS

SEND REPLICAT rsoeas, NOHANDLECOLLISIONS

Lastly, edit the replicat parameter file and remove HANDLECOLLISIONS so that it is not enabled next time the replicat restarts.

GoldenGate Initial-Load File to Replicat

This article will demostrate how to perform a OGG Initial Load using the File to Replicat technique.

Other Initial Load techniques and their advantages and disadvantages can be found here
.

Pre-requisites

OGG is installed on source and target systems

Environment

OGG : 11.2.1.0.1
Database : 11.2.0.3
Linux : OEL 6 ( 64 bit )

File to Replicat Steps

1. Configure the initial-load processes

Extract


SOURCEISTABLE
 -- --------------------------------------------------
 -- environment
 -- --------------------------------------------------
 SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")
 SETENV (ORACLE_SID="GGDB1")
 SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
 USERID ggate, PASSWORD ggate
 -- --------------------------------------------------
 RMTHOST gglab1n2, mgrport 7809
 RMTFILE /u01/app/oracle/product/golden_gate/dirdat/ilCSOE.DAT, PURGE
 TABLE soe.inventories;
 TABLE soe.product_descriptions;

Replicat


SPECIALRUN
 END RUNTIME
 -- --------------------------------------------------
 -- environment
 -- --------------------------------------------------
 SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")
 SETENV (ORACLE_SID="GGDB2")
 SETENV (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
 USERID ggate, PASSWORD ggate
 -- --------------------------------------------------
 -- options
 -- --------------------------------------------------
 ASSUMETARGETDEFS
 DBOPTIONS SUPPRESSTRIGGERS
 -- --------------------------------------------------
 -- files
 -- --------------------------------------------------
 EXTFILE /u01/app/oracle/product/golden_gate/dirdat/ilCSOE.DAT
 DISCARDFILE /u01/app/oracle/product/golden_gate/dirrpt/ilCSOE.dsc, PURGE
 -- --------------------------------------------------
 -- mappings
 -- --------------------------------------------------
 MAP soe.inventories , TARGET soe.inventories;
 MAP soe.product_descriptions , TARGET soe.product_descriptions;

2. Configure the change-synchronization processes

This is the normal replication methods, so I will not cover them here to keep things short. You can see example of this in some of my other posts.

3. Start the change-synchronization Extract

GGSCI> DBLOGIN USERID ggate, PASSWORD password ggate

GGSCI> FLUSH SEQUENCE soe.*

GGSCI> start extract csoea

4. Start the initial-load extract

extract paramfile ./dirprm/icsoea.prm reportfile ./dirrpt/icsoea.rpt

You should observe the following:

* the collector starting on target as seen in the ggserr.log

* the file ilCSOE.DAT in dirdat directory on target

* report file in dirrpt directory on source

When I first tried this on the entire schema, errors were reported in both the source and target ggserr logs.


2013-05-22 16:06:17  WARNING OGG-01223  Oracle GoldenGate Collector for Oracle:  Error 0 (Success) getting position in /u01/app/oracle/product/golden_gate/dirdat/ilCSOE.DAT.

2013-05-22 16:07:51  ERROR   OGG-01033  There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. (Remote file used is /u01/app/oracle/product/golden_gate/dirdat/ilCSOE.DAT, reply received is Error 0 (Success) getting position in /u01/app/oracle/product/golden_gate/dirdat/ilCSOE.DAT).

This turns out to be a limitation of this technique where the remote file is limited in size to 2 GB.

I then reduced my dataset down to two tables in order to perform the this intial-load.
I also did a test with the “Trail to Replicat” initial-load technique which can be used with datasets greater than 2GB.

Wait until the extract completes running and review the resulting report file for any errors.

5. Start the initial-load replicat on the target environment

replicat paramfile ./dirprm/irsoea.prm reportfile ./dirrpt/irsoea.rpt

Monitor the report file for the initial-load replicat and wait until it completes. If there are no errors, then you can continue on to the next step.

6. Start the change-synchronization Replicat

GGSCI> start replicat rsoeas

Monitor the replicat for any errors and the current time and position of the records it is applying.

Next, Verify that the change synchronization replicat has passed the point where the initial-load replicat stopped. You can compare both time and the RBA by looking in the irsoea.rpt report file and the current position of the change synchronization replication as shown by the info command.

...
Reading /u01/app/oracle/product/golden_gate/dirdat/ilCSOE.DAT, current RBA 97596089, 901667 records

Report at 2013-05-22 18:49:25 (activity since 2013-05-22 18:44:17)

From Table SOE.INVENTORIES to SOE.INVENTORIES:
       #                   inserts:    900667
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0
From Table SOE.PRODUCT_DESCRIPTIONS to SOE.PRODUCT_DESCRIPTIONS:
       #                   inserts:      1000
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0

Last log location read:
     FILE:      /u01/app/oracle/product/golden_gate/dirdat/ilCSOE.DAT
     RBA:       97596089
     TIMESTAMP: 2013-05-22 18:41:55.063284
...
GGSCI (gglab1n2.enkitec.com) 4> info *

REPLICAT   RSOEAS    Last Started 2013-05-22 18:44   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint  File /u01/app/oracle/product/golden_gate/dirdat/As000001
                     2013-05-22 19:49:32.242676  RBA 97596089

7. Turn off HANDLECOLLISIONS

SEND REPLICAT rsoeas, NOHANDLECOLLISIONS

Lastly, edit the replicat parameter file and remove HANDLECOLLISIONS so that it is not enabled next time the replicat restarts.