Oracle Golden Gate on RAC

Oracle Golden Gate on RAC

1 Oracle GoldenGate GGSCI Commands

http://docs.oracle.com/goldengate/1212/gg-winux/GWURF/ggsci_commands.htm

RAC

Oracle GoldenGate - Heartbeat process to monitor lag and performance in GoldenGate (Doc ID 1299679.1)

Note 966181.1 Installing GoldenGate For Oracle RAC  

Note 965373.1 Installing And Configuring OGG In A RAC Environment

Note 970876.1 Oracle ASM RAC EXTRACT Hanging  

Note 967390.1 Using Alternative Archived Log Destination On Oracle RAC  

Note 1242965.1 Re-enabling a RAC thread got extract abend with 'begin time .. prior to oldest log in log history.'  

Note 957112.1 Encountered SCN That Is Not Greater Than The Highest SCN Already Processed

Note 987379.1 How to recover from Extract ERROR 180 encountered commit SCN that is not greater than the highest 

Note 1342635.1How the mapping of OGG thread will change when a RAC node is add added or remove removed?

Note 965373.1 Visual Representation of Oracle GoldenGate In An Oracle RAC Environment

Note 1476058.1 Downgrading INTEGRATED Extract to Classic Extract fails on RAC environment

Note 1313703.1 Oracle GoldenGate Best Practices: Oracle GoldenGate high availability using Oracle Clusterware

Note 1267901.1 How to configure GoldenGate extract when adding or removing redo log threads in an Oracle RAC.

Note 1059895.1 Extract Processes Running In An Oracle RAC On Linux Environment

http://www.oracle.com/technetwork/database/features/availability/maa-goldengate-rac-2007111.pdf

http://www.oracle.com/technetwork/middleware/goldengate/overview/ha-goldengate-whitepaper-128197.pdf

http://myoracleproduct.blogspot.in/2013/11/oracle-goldengate-replication-oracle-to.html

http://gavinsoorma.com/category/goldengate/

http://www.oracle.com/technetwork/middleware/goldengate/overview/usingoraclegoldengate11gwithoracle-1539665.pdf


http://www.ahmedbaraka.com/computer/docs/oracle_gg.pdf


http://docs.oracle.com/cd/E22355_01/doc.11111/e21406.pdf

http://docs.oracle.com/goldengate/1212/gg-winux/GLOGD/wu_logdump.htm

http://decipherllc.blogspot.in/2010/08/oracle-goldengate-positioning-read-of.html


http://www.oracle11ggotchas.com/articles/TroubleshootingUsingLOGDUMP.htm


http://deciphercorp.wordpress.com/2010/07/26/oracle-goldengate-positioning-a-read-of-extractreplicat-trail-file-or-oracle-redo-log/

Oracle GoldenGate Best Practices: Oracle GoldenGate high availability using Oracle Clusterware (Doc ID 1313703.1) https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=776281352603689&id=1313703.1&displayIndex=44&_afrWindowMode=0&_adf.ctrl-state=482rov4g1_443  Why Am I Unable To Stop Extract? [ID 971992.1]  Why Do I Get The Error Message "GGS ERROR 501 Extract Read, Error 2 (No Such File Or Directory) Opening Redo Log"? [ID 964722.1]  Can I Minimize Notifications Of Long Running Transactions On Oracle? [ID 972411.1]  OGG Extract RBA Not Moving And LAG Increasing And Appears Hung [ID 964705.1]  Oracle GoldenGate Best Practice - sample parameter files [ID 1321696.1] Oracle GoldenGate Best Practices: Oracle GoldenGate high availability using Oracle Clusterware (Doc ID 1313703.1) Installing GoldenGate For Oracle RAC (Doc ID 966181.1) ============================================================================== Most Common Goldengate Errors and Issues (Doc ID 1354649.1) QUESTIONS AND ANSWERS Note: 966211.1 How To Resync A Single Table With Minimum Impact To Other Tables' Replication?  Note: 966227.1 Troubleshooting TCP/IP Errors In Open Systems  Note: 972954.1 What Causes The 'Bad Column Index(xxxx)' Error In Replicat?  Note: 987379.1 How to recover from Extract ERROR 180 encountered commit SCN that is not greater than the highest SCN already processed  Note: 1112506.1 GoldenGate Extract Abends with "Unable to lock file" Error For Trail File  Note: 1124403.1 When Replicat is Running but Not Applying Records  Note: 1138409.1 EXTRACT / REPLICAT CHECKPOINT RBA IS LARGER THAN LOCAL TRAILFILE SIZE, AFTER SERVER CRASH  Note: 1163073.1 When using rmthost with timeout option, there are orphaned data collector processes on target system  Note: 1266389.1 Oracle GoldenGate and compressed tables  Note: 1270168.1 OGG-01028 Compression Not Supported - when extract processes regular and cluster tables  Note: 1276538.1 Replicat abending with missing key cols  Note: 1280584.1 Where To Find The List Of Supported And Unsupported Oracle Data Types for GoldenGate  Note: 1288562.1 GG Data Pumps Error With OGG-01031 When Upgrading the Target Side Note: 957112.1 Encountered SCN That Is Not Greater Than The Highest SCN Already Processed Note: 1271578.1 How to Handle Tables without Primary Keys or Unique Indexes with Oracle GoldenGate  Note: 957053.1 Recovering From a GG Pump Process Abending With An Incompatible Record Error  Note: 968622.1 Does GoldenGate Support Tables With An UNUSED Column?  Note: 968614.1 Why Does GoldenGate Report The Lag Is Unknown or a Timestamp Mismatch?  Note: 968769.1 GoldenGate Errors That Occur In Teradata Extract Setting  Note: 1163073.1 When using rmthost with timeout option, there are orphaned data collector processes on target system  Note: 964709.1 How To Handle Oracle DDLs (add/drop/resize a Column) 22 19 1  Note: 1300076.1 Usage and considerations for allocation of ports and DYNAMICPORTLIST  Note: 1232303.1 mount options to use when configuring GoldenGate to run on NFS mounted file system  Note: 1298548.1 Extract not accepting CACHESIZE settings  Note: 1300197.1 Logger not logging cobol transactions for GGSLIB with non default AUDCFG location  Note: 1273285.1 How To Troubleshoot Oracle Redo Log Reading Extract Slow Performance Issue comment the following until it is republished Note: 965356.1 GGS Error 150: No Dynamic Ports Available  Note: 1199561.1 Characterset Conversion: Error 160 Bad column length  Note: 1159684.1 Extract abends,mss: missing trailing blocks, unable to queue I/O, I/O beyond file size  Note: 1292874.1 GoldenGate Extract Abends on Startup with Error OGG-01088 Realloc xxx Bytes Failed.  Note: 1271522.1 Oracle GoldenGate (OGG) 11.1>>> .1>>> .0 and Transparent Data Encryption (TDE)  Note: 965270.1 How does the Manager Allocate TCPIP Ports?  Note: 1195995.1 Implementing GoldenGate for DataGuard fail overs  Note: 965373.1 Installing And Configuring GoldenGate In An Oracle RAC Environment  Note: 1276058.1 Oracle GoldenGate Best Practices: Instantiation from an Oracle Source Database  Note: 971264.1 How To Upgrade GGS Objects In A DDL Replication Environment  REFERENCES NOTE:1298548.1 - Extract not accepting CACHESIZE settings NOTE:1300076.1 - Usage and considerations for allocation of ports and DYNAMICPORTLIST NOTE:1300197.1 - Logger not logging cobol transactions for GGSLIB with non default AUDCFG location NOTE:1112506.1 - GoldenGate Extract Abends with "Unable to lock file" Error For Trail File NOTE:1124403.1 - When Replicat is Running but Not Applying Records NOTE:1138409.1 - OGG EXTRACT / REPLICAT CHECKPOINT RBA IS LARGER THAN LOCAL TRAIL SIZE NOTE:966227.1 - OGG Troubleshooting TCP/IP Errors In Open Systems NOTE:1276538.1 - Replicat Abending With Mapping Error and discard file shows Missing Key Columns NOTE:1280584.1 - Where Is The List Of Supported And Unsupported Oracle Data Types (Datatypes) for GoldenGate ? NOTE:1286276.1 - Why Isn't GoldenGate Manager Purging The Trail Files? NOTE:1288562.1 - OGG Data Pumps Error With OGG-01031 When Upgrading the Target Side NOTE:1292874.1 - GoldenGate Extract/Pump/Replicat Abends on Startup with Error OGG-01088 Realloc xxx Bytes Failed. NOTE:965270.1 - How does GoldenGate allocates ports between Source Extract pump and Target Server/collector?  NOTE:965356.1 - OGG GGS Error 150: No Dynamic Ports Available orphan ports server collector NOTE:965373.1 - Visual Representation of Oracle GoldenGate In An Oracle RAC Environment NOTE:966211.1 - How To Resync A Single Table With Minimum Impact To Other Tables' Replication? NOTE:957053.1 - Recovering From a GG Pump Process Abending With An Incompatible Record Error NOTE:957112.1 - OGG Encountered SCN That Is Not Greater Than The Highest SCN Already Processed NOTE:964709.1 - How To Handle Oracle DDLs (add/drop/resize a Column) When not using DDL replication? NOTE:1159684.1 - OGG Extract abends,mss: missing trailing blocks, unable to queue I/O, I/O beyond file size NOTE:1163073.1 - When using rmthost with timeout option, there are orphaned data collector processes on target system NOTE:1195995.1 - Implementing GoldenGate for in conjunction with a planned or unplanned DataGuard fail overs NOTE:1199561.1 - GoldenGate Replicat Characterset Conversion: Error 160 Bad column length NOTE:1232303.1 - Oracle GoldenGate Best Practice: NFS Mount options for use with GoldenGate NOTE:1266389.1 - Oracle GoldenGate: Compressed tables are not supported until OGG v11.2.x Integrated Extract. NOTE:1270168.1 - Ogg-01028 Compression Not Supported - when extract processes regular and cluster tables NOTE:1271522.1 - Oracle GoldenGate (OGG) 11.1.1.0 and Transparent Data Encryption (TDE) NOTE:1271578.1 - How to Handle Tables Without Primary Keys or Unique Indexes With Oracle GoldenGate NOTE:1273285.1 - How To Troubleshoot Oracle Redo Log Reading Extract Slow Performance Issue using TESTMAPPINGSPEED NOTE:1276058.1 - Oracle GoldenGate Best Practices: Instantiation from an Oracle Source Database NOTE:968614.1 - Why Does GoldenGate Report The Lag Is Unknown or a Timestamp Mismatch? NOTE:968622.1 - Does GoldenGate Support Tables With An UNUSED Column? NOTE:968769.1 - GoldenGate Errors That Occur In Teradata Extract Setting NOTE:971264.1 - How to Upgrade Oracle GoldenGate Objects in a DDL Replication Environment? NOTE:972954.1 - What Causes The GGS ERROR 160 Bad Column Index(xxxx) Error in Replicat? NOTE:987379.1 - (VERSION 10 EXTRACT ONLY) How to recover from Extract ERROR 180 encountered commit SCN that is not greater than the highest SCN already processed ======================================================================== DETAILS This Note accumulates those Notes specific to the interaction of Oracle GoldenGate with the Oracle database. ACTIONS Note 1273285.1How to troubleshoot Oracle Redo log reading Note 1060533.1How to trace transaction from trail back to redo log Note 1060593.1Analysis of empty supplemental log key values in trail for an Oracle update Note 1268590.1Checking for Oracle archive log corruption Note 1273285.1How do I troubleshoot Oracle Redo Log Reading Extract Slow Performance  Note 1127476.1Redo Thread: error 13 (Permission denied) opening redo log Note 1054126.1  How do I change the version compatibility to 9.2 in an Oracle Database Note 1060581.1Why is the "select any table" privilege required for extract on an Oracle Partition table Note 1267901.1How to configure GoldenGate extract when adding or removing redo log threads in an Oracle RAC Note 1269274.1Why does GoldenGate require DBA/SYSDBA privileges in extract/replicat Note 1112325.1Deploying Oracle Goldengate to achieve operational reporting for Oracle E-Business Suite Note 1112403.1Deploying Oracle Goldengate to achieve operational reporting for Oracle Siebel CRM Note 1112406.1Deploying Oracle Goldengate to achieve operational reporting for Oracle JD Edwards Note 1114746.1Deploying Oracle GoldenGate to achieve operational reporting for PeopleSoft Note 1276058.1Oracle GoldenGate Best Practices: Instantiation form an Oracle Source Database Note 1298562.1Oracle GoldenGate data base complete database check script (all schemas) Note 972950.1Why do I receive the Error "ORA-12638 Credential Retrieval Failed" Note 1059710.1GoldenGate Extract/Replicat Error in Oracle-UNIX environment Note 1195995.1Implementing GoldenGate for DataGuard fail overs Note 1269855.1Oracle GoldenGate supports reading redo and archive log files stored on Raw devices with source database on AIX OS and also requires TRANLOFOPTIONS RAWDEVICEOFFSET parameter Note 1280235.1Oracle procedure failed with ORA-12801 and ORA-01031 only when OGG DDL trigger is enabled Note 1280584.1Where to find the list of supported and unsupported Oracle Data Types for GoldenGate Note 1297398.1GoldenGate - Error(904, ORA-00904:: Invalid identifier)selecting data in ORAUTIL_getCurrentSCN() Note 972950.1Why do I receive the Error "ORA-12638 Credential Retrieval Failed" Note 1149823.1Extract cannot read database redo (Oracle) or only (SQLServer) logs on Windows 2008 or Windows 7 Note 1206737.1Extract generates a large colume of trail data for Lob updates on Oracle Note 1296567.1Why does extract fail with Error OGG-00665: ORA-22812 Note 1297398.1GOldenGate - Erro r(904, ORA-00904:: Invalid identifier)selecting data in ORAUTIL_getCurrentSCN() Note 972329.1Why do I get the Error message "GG ErrorNote 972946.1Why does extract/replicat on OracleNote 972950.1Why do I receive the Error "ORA-12638 Credential Retrieval Failed" Note 973586.1Extraction of CLOB/LOB data, when do we do a DB fetch Note 987379.1How to recover from extract ERROR @Note 1059890.1  Positioning extract to read from the beginning of an Oracle redo/archive log Note 1059895.1Extract processes running in an Oracle RAC on Linux environment @Note 1059896.1Extract abends with Oracle snapshot too old message Note 1060553.1Extract for Oracle abends with the Error "getting Last rollback item ROWID" @Note 1060577.1  How to enable the trace for DDL Trigger @Note 1063375.1Use threadsNote 1108953.1What does this message mean? "Redo thread online log/oracle @Note 1125265.1  Extract abend with Error numcnv_getnumeric buffer overflow, chained row or others Note 1186935.1  GGS Error 500 could not find archived log for sequence @Note 1192972.1ALO extract hangs on restart an error 22 (invalid argument) occurs Note 1206737.1Extract generates a large column of trail data for Lob updates on Oracle Note 1211806.1OGG extract abends with" cm_obj_pop_last_item: object empty: error 116  Note 1242965.1Reenabling a RAC thread got extract abend with "begin time prior to oldest log in log history Note 1265785.1Extract abended with ORA-01013 when processing DDL record Note 1267048.1Extract abends as a table is considered as compresses only when with DDL parameter Note 1270168.1OGG-01028 Compression not supported - when extract processes regular and cluster tables Note 1273546.1Oracle export hangs when OGG extract DDL trigger enabled Note 1274300.1Extract abended with Error "ORA-00942:table or view does not exist, SQL< select values$ from sys.props$ where name = 'NLS_LANGUAGE' Note 1285734.1Why extract on RAC with DDL parameter, abended on a table that is not included in the parameter list @Note 1291330.1DBLOGREADER extract fails with this versions of ORACLE does not support DBLOGREADER Note 972962.1How do I call an Oracle function within replicat Note 1087305.1How to specify which Oracle database to access from a GoldenGate replicat Note 1116896.1  OGG replicat gets ORA-01873 for interval data on HP-UX Note 1259884.1ERROR OGG-01082 when running a replicat against an Oracle database Note 972411.1  Can I minimize notification of long running transactions on Oracle Note 1054126.1How do I change the version compatibility to 9.2 in an Oracle Database Note 1059352.1How to turn on supplemental logging at the database or the table level  Note 1059362.1How do I determine if supplemental logging is turned ON at the database or the table level @Note 1059412.1How to support Oracle Live Reorg @Note 1059559.1Setting the Undo segment in Oracle @Note 1059565.1What are the MBU and LMN records in Oracle Redo log @Note 1059578.1Replicating Index organized tables (IOT) Note 1059583.1When GoldenGate fetches data from the database on extraction for Oracle Note 1060437.1Why do I get the ora-00001 when doing multi-row PK/unique_key update by single statement in Oracle Note 1060525.1Does GolenGate support the DML statement merge Note 1060533.1How to trace transaction from trail back to redo log Note 1060535.1Using Oracle GoldenGate in an Oracle ASM environment when a non sys user @Note 1060544.1Oracle GoldenGate extract configuration: Connect to Oracle ASM locally without using password Note 1060568.1GGS Error 118 OCI error creating temp LOB Note 1060581.1Why is the "select any table" privilege required for extract on an Oracle Partition table Note 1060794.1How to solve an Ora-02292 error in replicat for a self-referential FK when processing multi-row deletes Note 1060870.1ALTARCHIVELOGDEST Note 1061093.1Extract fails with Error 118 No valid Log File for current Redo sequence xxxx thread Y Note 1063125.1Error message ORA-20782: DDL operation Failed because Recyclebin is turned on Note 1063389.1How to turn-off Oracle Recycle bin Note 1081322.1Configuring Oracle GoldenGate for partitioned Replication Note 1083886.1Can I use the @RANGE Function to split data exactly by a column value Note 1087214.1OCI error ora-01830 in OGG replicat when using sqlexec to call a procedure Note 1265554.1OGG DDL support of ALTER TABLE move or DBMS_REDEFINITION Note 1280235.1Oracle procedure failed with ORA-12801 and ORA-01031 only when OGG DDL trigger is enabled Note 1297811.1 Why do I see a message INFO 220 found Crash Recovery Marker from ========================================================================= GGSCI   -- (Oracle) GoldenGate Software Command Interpreter How to Invoke? C:\GG> ggsci.exe [oracle@prod gg]$ ./ggsci Commands ggsci> HELP [command] [object] ggsci> help GGSCI Command Summary: Object:          Command: SUBDIRS          CREATE ER                   INFO, KILL, LAG, SEND, STATUS, START, STATS, STOP EXTRACT          ADD, ALTER, CLEANUP, DELETE, INFO, KILL, LAG, SEND, START, STATS, STATUS, STOP EXTTRAIL         ADD, ALTER, DELETE, INFO GGSEVT           VIEW MANAGER         INFO, REFRESH, SEND, START, STOP, STATUS MARKER           INFO PARAMS           EDIT, VIEW REPLICAT         ADD, ALTER, CLEANUP, DELETE, INFO, KILL, LAG, SEND, START, STATS, STATUS, STOP REPORT           VIEW RMTTRAIL         ADD, ALTER, DELETE, INFO TRACETABLE     ADD, DELETE, INFO TRANDATA        ADD, DELETE, INFO CHECKPOINTTABLE  ADD, DELETE, CLEANUP, INFO Commands without an object: (Database)       DBLOGIN, LIST TABLES, ENCRYPT PASSWORD (DDL)              DUMPDDL (Miscellaneous)  FC, HELP, HISTORY, INFO ALL, OBEY, SET EDITOR, SHELL, SHOW, VERSIONS, ! For help on a specific command, type HELP [command] [object] Example: HELP ADD REPLICAT Note: You must type the word COMMAND after the ! to display the ! help topic.  i.e.: GGSCI> help ! command ggsci> help add rmttrail ggsci> CREATE SUBDIRS     -- To create default directories within Oracle GoldenGate home directory ggsci> INFO ALL [TASKS | ALLPROCESSES]   -- To display the status of all Manager, Extract, and Replicat processes ggsci> info all Program     Status      Group       Lag       Time Since Chkpt MANAGER     RUNNING EXTRACT     STOPPED     EXTR      00:01:15      00:00:07 REPLICAT    ABENDED     REP       00:00:00      00:00:04 ggsci> info all tasks ggsci> info all allprocesses ggsci> INFO MANAGER   -- To determine whether or not the Manager process is running ggsci> INFO MGR ggsci> INFO MARKER [COUNT number_of_items]  -- To review recently processed markers from a NonStop system ggsci> info marker ggsci> INFO EXTRACT group_name [, SHOWCH [n]] [, DETAIL] [, TASKS | ALLPROCESSES]  -- To display Status of Extract, Approximate Extract lag, Checkpoint information, Process run history ggsci> info extract emp_ext ggsci> info extract cust_ext, detail ggsci> info extract ext*, showch ggsci> info extract *, detail ggsci> info extract hr, tasks ggsci> INFO REPLICAT group_name [, SHOWCH [n]] [, DETAIL] [, TASKS | ALLPROCESSES] -- To display status of Replicat, Approximate replication lag, the trail from which Replicat is reading, Replicat run history, including checkpoints in the trail, Information about the Replicat environment. ggsci> info replicat emp_rep ggsci> info replicat emp_rep, detail ggsci> info replicat prd*, detail, allprocesses ggsci> info replicat *, tasks ggsci> info replicat fin, showch ggsci> INFO EXTTRAIL trail_name  -- To retrieve configuration information for a local trail ggsci> info exttrail * ggsci> info exttrail e:\ogg\dirdat\ex ggsci> INFO RMTTRAIL trail_name  -- To retrieve configuration information for a remote trail ggsci> info rmttrail * ggsci> info rmttrail d:\ogg\dirdat\ex ggsci> INFO TRANDATA user_name.table_names [, OLDFORMAT] [, USETRIGGER]  -- To determine whether logging/replication enabled or not ggsci> info trandata hr.* ggsci> info trandata fin.acct ggsci> INFO CHECKPOINTTABLE [user_name.table_name]  -- To confirm the existence of a checkpoint table and view the date and time that it was created ggsci> info checkpointtable gg_owner.chkpt_table ggsci> INFO TRACETABLE [owner.table]     -- To verify the existence of the specified trace table ggsci> info tracetable ora_trace ggsci> INFO ER group_wildcard_specification   -- To get information on multiple Extract and Replicat groups as a unit ggsci> info ER *ext* ggsci> SHOW   -- To display the Oracle GoldenGate environment ggsci> SHOW ALL ggsci> DBLOGIN {SOURCEDB|TARGETDB dsn | USERID user_name[, PASSWORD password] [SYSDBA] |  SOURCEDB|TARGETDB dsn, USERID user_name[, PASSWORD password] [SYSDBA] [SQLID sqlid]   -- To establish a database connection ggsci> dblogin sourcedb testdb ggsci> dblogin targetdb repldb ggsci> dblogin userid gg ggsci> dblogin userid gg, password oracle ggsci> dblogin userid system@localhost:1521/prod, password 12345678 ggsci> dblogin sourcedb ctdb@host1, userid gg_owner, password ggs sysdba ggsci> ENCRYPT PASSWORD password [ENCRYPTKEY keyname]   -- To encrypt a database login password ggsci> encrypt password oracle143 encryptkey spiderman ggsci> LIST TABLES table_name   -- To list all tables in the database that match the specification ggsci> list tables cust* ggsci> EDIT PARAMS {MGR | ./GLOBALS | group_name | file_name}  -- To create or change a parameter file ggsci> edit params mgr ggsci> edit params ./GLOBALS ggsci> edit params myload ggsci> edit params rep_emp ggsci> edit params e:\gg\dirprm\replp.prm ggsci> VIEW PARAMS {MGR | group_name | file_name}   -- To view the contents of a parameter file ggsci> view params mgr ggsci> view params s_extr ggsci> view params e:\prm\replp.prm ggsci> VIEW GGSEVT                -- To view GoldenGate error log (ggserr.log file) ggsci> VIEW REPORT {group_name[n] | file_name}   -- To view the process report that is generated by Extract or Replicat ggsci> view report ext6 ggsci> view report rep ggsci> view report c:\ogg\dirrpt\orders.rpt ggsci> ADD EXTRACT group_name {, SOURCEISTABLE |, TRANLOG [bsds_name] |, VAM |, EXTFILESOURCE file_name |, EXTTRAILSOURCE trail_name |, VAMTRAILSOURCE VAM_trail_name} {, BEGIN {NOW | yyyy-mm-dd [hh:mi:[ss[.cccccc]]]} |, EXTSEQNO seqno, EXTRBA relative_byte_address |, LOGNUM log_number, LOGPOS byte_offset |, EOF |, LSN value |, EXTRBA relative_byte_address |, PAGE data_page, ROW row | } [, THREADS n] [, PASSIVE] [, PARAMS parameter_file] [, REPORT report_file] [, DESC "description"]   -- To create an Extract group Syntax for an alias Extract: ADD EXTRACT group_name, RMTHOST {host_name | IP_address}, {MGRPORT port} | {PORT port} [, RMTNAME name] [, DESC "description"] ggsci> add extract s_extr, tranlog, begin now ggsci> add extract finext, tranlog, begin now, threads 4 ggsci> add extract fin, tranlog, begin now, passive ggsci> add extract ext_ms, extseqno 111, begin now ggsci> add extract hr_ext, extrba 567890, begin 2012-02-02 12:00:00 ggsci> add extract initload, sourceistable ggsci> add extract pump, exttrailsource /oracle/gg11/dirdat/hr ggsci> add extract fin, vam                        -- VAM - Vendor Access Module ggsci> add extract fin, vamtrailsource /ogg/dirdat/vt ggsci> add extract finA, rmthost host123, mgrport 7810, rmtname fin ggsci> ADD REPLICAT group_name {, SPECIALRUN |, EXTFILE full_path_name |, EXTTRAIL full_path_name} [, BEGIN {NOW | YYYY-MM-DD HH:MM[:SS[.CCCCCC]]} |, EXTSEQNO seqno, EXTRBA rba] [, CHECKPOINTTABLE owner.table | NODBCHECKPOINT] [, PARAMS parameter_file] [, REPORT report_file] [, DESC "description"]   -- To create a Replicat group ggsci> add replicat repl, exttrail C:\OGG10G\dirdat\lt ggsci> add replicat t_rep, exttrail /oracle/gg11/dirdat/lt, checkpointtable gg_owner.checkpoint ggsci> add replicat initload, specialrun ggsci> add replicat sales, exttrail /oracle/gg11/dirdat/lt, nodbcheckpoint ggsci> ADD EXTTRAIL trail_name, EXTRACT group_name [, MEGABYTES n] [, SEQNO n]   -- To create a trail for online processing on local system ggsci> add exttrail /oracle/gg11/dirdat/lt, extract s_extr ggsci> add exttrail C:\OGG10G\dirdat\et, extract emp_ext ggsci> add exttrail c:\ogg\dirdat\fi, extract fin, megabytes 30 ggsci> ADD RMTTRAIL trail_name, EXTRACT group_name [, MEGABYTES n] [, SEQNO n]   -- To create a trail for online processing on remote system ggsci> add rmttrail C:\OGG10G\dirdat\hr, extract extr ggsci> add rmttrail /u01/app/oracle/ogg/dirdat/ms, extract msextr ggsci> add rmttrail /u01/app/oracle/ogg/dirdat/my, extract mysql, megabytes 50 ggsci> ADD TRANDATA user_name.table_names [, COLS (column_list)] [, INCLUDELONG | EXCLUDELONG]  [, LOBSNEVER | LOBSALWAYS | LOBSIFCHANGED | LOBSALWAYSNOINDEX] [, NOKEY] [, OLDFORMAT] [, USETRIGGER]   -- To enable Oracle GoldenGate to acquire the transaction information it needs from the transaction records ggsci> add trandata hr.* ggsci> add trandata emp.employees ggsci> add trandata fin.acct, cols (name, address) ggsci> add trandata fin.acct, nokey, cols (name, pid) ggsci> add trandata fin.acct, lobsalwaysnoindex ggsci> ADD CHECKPOINTTABLE [user_name.table_name]   -- To create a checkpoint table in the target database ggsci> add checkpointtable ggsci> add checkpointtable gg_owner.checkpoint ggsci> ADD TRACETABLE [owner.table]   -- To create a trace table in the Oracle database ggsci> add tracetable ggsci> add tracetable ora_trace ggsci> REGISTER EXTRACT extract_group_name LOGRETENTION ggsci> register extract extr logretention ggsci> START MANAGER    -- To start Manager process ggsci> START MGR ggsci> START * ggsci> START EXTRACT extract_name   -- To start Extract process ggsci> start extract extr ggsci> START REPLICAT group_name [SKIPTRANSACTION | ATCSN csn | AFTERCSN csn]   -- To start Replicat process ggsci> start replicat rep ggsci> start replicat fin, atcsn 5238306       -- commit sequence number (CSN) ggsci> start replicat fin, aftercsn 0X000006B1:0002334D:0004 ggsci> START ER group_wildcard_specification    -- To start multiple Extract and Replicat groups as a unit ggsci> start er *rep* ggsci> STOP MANAGER [!]    -- To stop Manager process ggsci> stop manager ggsci> stop manager !     -- will not ask for confirmation ggsci> STOP EXTRACT extract_name   -- To stop Extract gracefully ggsci> stop extract extr ggsci> STOP REPLICAT replicat_name [!]   -- To stop Replicat gracefully ggsci> stop replicat rep ggsci> STOP ER group_wildcard_specification  -- To stop multiple Extract and Replicat groups as a unit ggsci> stop er *ext* ggsci> STATUS MANAGER    -- To determine whether or not the Manager process is running ggsci> STATUS EXTRACT group_name [, TASKS | ALLPROCESSES]   -- To determine whether or not Extract is running ggsci> status extract extr_hr ggsci> status extract ext*, tasks ggsci> status extract *ext*, allprocesses ggsci> STATUS REPLICAT group_name [, TASKS | ALLPROCESSES]  -- To determine whether or not Replicat is running ggsci> status replicat emp_rep ggsci> status replicat cust_rep, allprocesses ggsci> STATUS ER group_wildcard_specification   -- To check the status of multiple Extract and Replicat groups as a unit ggsci> status er *EX* ggsci> STATS EXTRACT group_name [, statistic] [, TABLE table] [, TOTALSONLY table_specification] [, REPORTFETCH | NOREPORTFETCH] [, REPORTRATE HR|MIN|SEC] [, ... ]  -- To display statistics for one or more Extract group ggsci> stats ext_hr ggsci> stats extract ext ggsci> stats extract ext2 reportrate sec ggsci> stats extract fin, total, daily ggsci> stats extract fin, total, hourly, table acct, reportrate min, reset, reportfetch ggsci> STATS REPLICAT group_name [, statistic] [, TABLE table] [, TOTALSONLY table_specification] [, REPORTDETAIL | NOREPORTDETAIL] [, REPORTRATE HR|MIN|SEC] [, ... ]   -- To display statistics for one or more Replicat groups ggsci> stats rep_hr ggsci> stats replicat fin, total, table acct, reportrate hr, reset, noreportdetail ggsci> STATS ER group_wildcard_specification   -- To get statistics on multiple Extract and Replicat groups as a unit ggsci> stats er ext* ggsci> REFRESH MANAGER  -- not available in Oracle 11g ggsci> SEND MANAGER [CHILDSTATUS [DEBUG]] [GETPORTINFO [DETAIL]] [GETPURGEOLDEXTRACTS]   -- To retrieve the status of the active Manager process or to retrieve dynamic port information as configured in the Manager parameter file ggsci> send manager childstatus ggsci> send manager childstatus debug ggsci> send manager getportinfo ggsci> send manager getportinfo detail ggsci> send manager getpurgeoldextracts ggsci> SEND EXTRACT group_name,  { CACHEMGR {CACHESTATS | CACHEQUEUES | CACHEPOOL} | FORCESTOP | FORCETRANS id [THREAD n] [FORCE] | GETLAG | GETTCPSTATS | LOGEND | REPORT | ROLLOVER | SHOWTRANS [id] [THREAD n] [COUNT n] [DURATION duration_unit] [TABULAR] [FILE file_name [DETAIL]] | SKIPTRANS id [THREAD n] [FORCE] | STATUS | STOP | TLTRACE {DEBUG | OFF | level} [SIZELIMIT size] [DDLINCLUDE | DDL[ONLY]] [FILE] file_name | TRACE[2] {tracefile | OFF} | TRACEINIT | TRANLOGOPTIONS {PURGEORPHANEDTRANSACTIONS | NOPURGEORPHANEDTRANSACTIONS} | TRANLOGOPTIONS TRANSCLEANUPFREQUENCY minutes | VAMMESSAGE "Teradata_command" | VAMMESSAGE {ARSTATS | INCLUDELIST [filter] | EXCLUDELIST [filter]} | VAMMESSAGE OPENTRANS }     -- To communicate with a running Extract process Teradata_command = {"control:terminate" | "control:suspend" | "control:resume" | "control:copy database.table" ggsci> send extract exthr status ggsci> send extract extr, getlag ggsci> send extract group_name tltrace file file_name ddlinclude ggsci> send extract fin, rollover ggsci> send extract fin  stop ggsci> send extract fin, vammessage control:suspend ggsci> send extract fin, tranlogoptions transcleanupfrequency 15 ggsci> send extract fin, showtrans count 10 ggsci> send extract fin, skiptrans 5.17.27634 thread 2 ggsci> SEND REPLICAT group_name, { FORCESTOP | GETLAG | HANDLECOLLISIONS [table_specification] | NOHANDLECOLLISIONS [table_specification] | REPORT [HANDLECOLLISIONS [table_specification]] | STATUS | STOP | TRACE[2] [DDLINCLUDE | DDL[ONLY]] [FILE] file_name | TRACE[2] OFF | TRACEINIT }    -- To communicate with a starting or running Replicat process ggsci> send replicat fin, handlecollisions ggsci> send replicat fin, report handlecollisions fin_* ggsci> send replicat fin, getlag ggsci> SEND ER group_wildcard_specification   -- To send instructions to multiple Extract and Replicat groups as a unit ggsci> send er *ext ggsci> ALTER EXTRACT group_name [, ADD_EXTRACT_attribute] [, THREAD number] [, ETROLLOVER]  -- To change the attributes of an Extract group, To increment a trail to the next file in the sequence ggsci> alter extract fin, begin 2012-02-16 ggsci> alter extract fin, etrollover ggsci> alter extract fin, extseqno 26, extrba 338 ggsci> alter extract accounts, thread 4, begin 2012-03-09 ggsci> alter extract sales, lsn 1234:123:1 ggsci> ALTER REPLICAT group_name ,  [, BEGIN {NOW | YYYY-MM-DD HH:MM[:SS[.CCCCCC]]} |, EXTSEQNO seqno, EXTRBA rba] [, PARAMS parameter_file] [, REPORT report_file] [, DESC "description"]   -- To change the attributes of a Replicat group ggsci> alter replicat fin, begin 2011-09-28 08:08:08 ggsci> alter replicat fin, extseqno 53 ggsci> alter replicat fin, extrba 666 ggsci> ALTER EXTTRAIL trail_name, EXTRACT group_name [, MEGABYTES n]   -- To change the attributes of a trail (on the local system) ggsci> alter exttrail c:\ogg\dirdat\aa, extract fin, megabytes 30 ggsci> ALTER RMTTRAIL trail_name, EXTRACT group_name [, MEGABYTES n]   -- To change the attributes of a trail (on a remote system) ggsci> alter rmttrail c:\ogg\dirdat\et, extract fin, megabytes 25 ggsci> CLEANUP EXTRACT group_name [, SAVE count]   -- To delete run history for specified Extract group ggsci> cleanup extract fin ggsci> cleanup extract e*, save 6 ggsci> CLEANUP REPLICAT group_name [, SAVE count]  -- To delete run history for specified Replicat group ggsci> cleanup replicat fin ggsci> cleanup replicat *, save 10 ggsci> CLEANUP CHECKPOINTTABLE [user_name.table_name]  -- To remove checkpoint records from the checkpoint table ggsci> cleanup checkpointtable ggs.fin_check ggsci> DELETE EXTRACT group_name [!]   -- To delete an Extract group ggsci> delete extract emp_ext ggsci> delete extract emp_ext !              -- will not ask for confirmation ggsci> DELETE REPLICAT group_name [!]   -- To delete a Replicat group ggsci> delete replicat emp_ext ggsci> delete replicat emp_ext !             -- will not ask for confirmation ggsci> DELETE EXTTRAIL trail_name    -- To delete the record of checkpoints associated with a trail on a local system ggsci> delete exttrail /home/ogg/dirdat/et ggsci> DELETE RMTTRAIL trail_name    -- To delete the record of checkpoints associated with a trail on a remote system ggsci> delete rmttrail /home/ogg/dirdat/et ggsci> DELETE TRANDATA user_name.table_names [, OLDFORMAT] [, USETRIGGER]  -- To delete logging/replication data ggsci> delete trandata fin.acct ggsci> delete trandata fin.cust* ggsci> delete trandata emp.hr, usetrigger ggsci> DELETE CHECKPOINTTABLE [user_name.table_name] [!]     -- To drop checkpoint table from database ggsci> delete checkpointtable ggs.fin_check ggsci> DELETE TRACETABLE [owner.table]   -- To delete a trace table ggsci> delete tracetable ora_trace ggsci> KILL EXTRACT group_name    -- To kill an Extract process running in regular or PASSIVE mode ggsci> kill extract fin ggsci> KILL REPLICAT group_name   -- To kill a Replicat process ggsci> kill replicat fin ggsci> KILL ER group_wildcard_specification   -- To forcefully terminate multiple Extract and Replicat groups as a unit ggsci> kill er *x* ggsci> LAG EXTRACT group_name    -- To determine a true lag time between Extract and the datasource ggsci> lag extract ext* ggsci> lag extract * ggsci> LAG REPLICAT group_name   -- To determine a true lag time between Replicat and the trail ggsci> lag replicat myrepl ggsci> lag replicat * ggsci> LAG ER group_wildcard_specification   -- To get lag information on multiple Extract and Replicat groups as a unit ggsci> lag er *ext* ggsci> DUMPDDL [SHOW]   -- To view the data in the Oracle GoldenGate DDL history table ggsci> dumpddl ggsci> HISTORY [n]   -- To view the most recently issued GGSCI commands since the session started ggsci> history       -- by default, shows last 10 commands ggsci> history 30 ggsci> SET EDITOR program_name    -- To change the default text editor for the current session of GGSCI, default editors are Notepad for Windows and vi for UNIX ggsci> set editor wordpad ggsci> VERSIONS   -- To display operating system and database version information ggsci> FC [n | -n | string]   -- To display edit a previously issued GGSCI command and then execute it again ggsci> fc ggsci> fc 9 ggsci> fc -3 ggsci> fc sta FC editor commands i text  -- Inserts text r text  -- Replaces text d        -- Deletes a character replacement_text  -- Replaces the displayed command with the text that we enter on a one-for-one basis. ggsci> SHELL shell_command   -- To execute shell commands from within GGSCI interface ggsci> shell ls -l /u01/app/oracle/gg/dirdat ggsci> shell dir dirprm\* ggsci> shell rm ./dat* ggsci> ! [n | -n | string]    -- To execute previous GGSCI command ggsci> ! ggsci> ! 6    -- To run the command 6 listed in the history ggsci> ! -3 ggsci> ! sta ggsci> help ! command ggsci> OBEY ggsci_script      -- To process a file that contains a list of GoldenGate commands. ggsci> shell more /u01/app/oracle/gg/startup.txt START MANAGER START EXTRACT EXT3 START EXTRACT DPUMP INFO ALL ggsci> OBEY /u01/app/oracle/gg/startup.txt ggsci> OBEY ./mycommands.txt ggsci> EXIT ===================================================================== OGG Encountered SCN That Is Not Greater Than The Highest SCN Already Processed (Doc ID 957112.1) APPLIES TO: Oracle GoldenGate - Version 6.0.0 and later Information in this document applies to any platform. FIX Issue: Extract ERROR 180 encountered commit SCN that is not greater than the highest SCN already processed Solution Overview: This error occurs shortly after a transaction is written to the idle node but does not yet appear in the redo log when the current transaction is processed. This transaction will have a higher SCN than the previous transaction Solution Details: In an Oracle RAC environment, Extract has a coordinator thread. This thread assembles the operations that are generated on each of the RAC nodes into SCN order. Before starting to assemble the transactions, Extract waits for a short time after Oracle writes the transaction to the redo log. If a node has been idle with no transactions, Extract waits the number of milliseconds specified in the Extract parameter THREADOPTIONS MAXCOMMITPROPAGATIONDELAY (default 3 seconds). Waiting ensures that there are no new transactions on the idle node before writing the current transactions to the trail. Possible Reasons for error: -- One of the threads is slower than the other. -- The redo logs are not flushed on time due to latency on Log writes. -- Network issues between Extract and one of the RAC nodes, if Extract is running on a system separate from RAC nodes. -- Long log write times due to a standby configuration, if any. -- Log file I/Os are taking unusually long times to complete. -- Time imperfections between the cluster nodes. All nodes in the RAC cluster must have synchronized system clocks. If Extract is running on a system other than any of the RAC nodes, that system clock must be in sync, too, because we compare the local system's time to the commit timestamp to make critical decisions. For information about synchronizing system clocks, consult www.ntp.org or your systems administrator -- The DB timezone setting is different than the OS timezone setting Case example: The following shows a time sequence and explains what happens at each step in time: TIME WHAT HAPPENED ------- --------- 2008-03-31 14:08:21.964 Starting from this timestamp, thread 2 is reporting that it is at the end of the file (EOF). ...... Thread 2 keeps reporting EOF. 2008-03-31 14:25:33.021 Thread 1 reports it is at EOF 2008-03-31 14:25:33.110 Thread 2 reports it is at EOF 2008-03-31 14:25:34.040 Thread 1 gets the commit record, with timestamp of 2008-03-31 14:10:52. It commits the transaction with an SCN of 1.2943495430. no timestamp... The coordinator thread processes the committed transaction from thread 1, because thread 2 kept reporting EOF for the last 17 minutes. 2008-03-31 14:25:34.188 Thread 2 got an update record and a commit record with timestamp of 2008-03-31 14:10:52. It commits transaction with an SCN of 1.2943495406 2008-03-31 14:25:34.xxx The main thread processes the committed transaction from thread 2, and detects an out of order SCN. Extract abends with this error: 2008-03-31 14:25:34 GGS ERROR 180 encountered commit SCN 1.2943495406 (0001.af722cee) that is not greater than the highest SCN already processed 1.2943495430 (0001.af722d06) Redo Thread 2 (2) xid 29.27.131432 (0x001d.01b.00020168), starting seq.rba 3725.98984464, scn 1.2943495403 (0001.af722ceb), commit seq.rba 3725.99011828 commit timestamp 2008-03-31 14:10:52.000000. In this case, the out of order SCN was caused by the huge gap in the time between when the record was generated to the log buffer and when it was captured by Extract. Troubleshooting steps: -- Check to see if the clocks on different nodes in the same RAC are in sync -- Check to see if there are any unusual time gaps between log data on threads during the SCN error. -- Check how the 'remote'  log files, if any, are shared with the system on which Extract is running (e.g., NFS, SAN, shared SCSI, etc.) and see if there are any noticeable problems there. -- Check if there are messages in the Oracle alert logs, lgwr tracefiles or net logs during the problematic time, to see if they provide any clues to point to one of the 'Possible Reasons' discussed above. -- Check to see if any analysis been done on the I/O performance on the drives that contain the log files and whether they are "real Disk";, NFS, SAN, etc. -- Check to see if the DB timezone is setup correctly.  Issue 'SELECT dbtimezone FROM DUAL', the timezone should match the OS.  For example is the result is "+00:00", that means DB is using GMT/UTC timezone. Recommendation:- The MAXCOMMITPROPAGATIONDELAY parameter can be used to set the delay time well above the max_commit_propogation_delay setting in the database, plus the default extra padding that Extract adds (2000 milliseconds). In Oracle RAC, the max_commit_propogation_delay specifies the maximum amount of time allowed before the system change number (SCN) held in the SGA of an instance is refreshed by the log writer process (LGWR). Units are in hundredths of seconds. To check Oracle's value: Connect as a user with dba privileges and issue: SQL> show parameter max_commit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_commit_propagation_delay integer 0 To set MAXCOMMITPROPAGATIONDELAY : The value of MAXCOMMITPROPAGATIONDELAY must be greater than zero and less than 90000 milliseconds. This is how the line should look in the Extract parameter file: THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 2700 Starting from GGS Version 9.x and above, an additional parameter 'IOLATENCY' can be used if Extract abends with 'encountered SCN XXXXX' too often. IOLATENCY adjusts the delta between the database-configured max commit propogation delay and the internal value that Extract uses. By default IOLATENCY is set to 1.5 seconds  Note: Valid values for IOLATENCY are between 0 and 180000 milliseconds (3 minutes). The combined parameters should look like this: THREADOPTIONS MAXCOMMITPROPAGATIONDELAY IOLATENCY The combination of MAXCOMMITPROPAGATIONDELAY and IOLATENCY can be used to ensure that: 1) the Oracle threads have written their most recent SCN data to the logs 2) the I/O processes have had time to complete, considering the various factors that increase I/O latency, such as hardware contention, file locking, long seek and queue times, etc. The key is in adjusting IOLATENCY and MAXCOMMITPROPAGATIONDELAY parameters to an optimum value based on the frequency of hitting the error. Hint: If the problem happens too often, you can start with high values for IOLATENCY and MAXCOMMITPROPAGATIONDELAY parameters. Once the error stops happening, you can gradually decrease the values to see the SCN number where this error starts appearing again. This would give you an idea of the boundary values specific to that environment. Furthermore, the reason Extract successfully restarts after this error is that, on restart, it re-reads the operations, and this time they are all on disk and can be processed in correct SCN order. The side effect is that Extract rewrites operations into the trail. This may cause the data pump or Replicat to abend with incompatible record errors. Note: From Oracle 10.2, parameter max_commit_propagation_delay has been deprecated. So "SQL> show parameter max_commit NAME TYPE VALUE " is no longer viable for Oracle 10.2 upward. After you adjusted IOLATENCY and MAXCOMMITPROPAGATIONDELAY parameters to really big values, if the Extract still abends and the RAC nodes clocks are in sync, then you might hit bug 13559209.  This bug was introduced in early patch versions of V11.1.1.0.29.   Also, we found bug 13408324 when customer has more than 15 nodes in RAC, it will corrupt some of Extract's internal data structure and cause the flag of THREADOPTIONS IGNORESCNSEQUENCE in-correctly turned on, which will cause Extract keep running while printing out the warning message in the report file.  This could cause downstream data integrity issues as Extract is writing out of order transactions in the trail file. Final Fix of this issue in V11.1.1+ Oracle DB HEARTBEAT is utilized to coordinate the read from multiple nodes to totally eliminate this issue.  This is being tracked in BugDB 10356426, which is implemented for V11.1.1.1, also backported back to V11.1.1.0.x code line.  If you are running V11.1.1.0.x GoldenGate, please request a build that has this fix. The redo compatibility of the db should be 10.2 or higher for the fix to work. Additional causes and Versions: This error is also known to occur in 11.2 code due to reported bug: 13955146. This bug is fixed in v12.1 and also included in OGG versions 11.2.1.0.3+ and 11.1.1.1.5+    ======================================================================= How Can The Checkpoints In The Extract Checkpoint File Be Changed? (Doc ID 964684.1) Recovery Checkpoint (position of oldest unprocessed transaction in the data source): Sequence #: 34381 RBA: 8615440 Timestamp: 2008-08-27 18:51:27.000000 Redo File: /rdbms/oracle/ora1012i/64/oradata/ora1012a/redo01.log Current Checkpoint (position of last record read in the data source): Sequence #: 34381 RBA: 8615936 Timestamp: 2008-08-27 18:51:27.000000 Redo File: /rdbms/oracle/ora1012i/64/oradata/ora1012a/redo01.log Write Checkpoint #1 GGS Log Trail Current Checkpoint (current write position): Sequence #: 0 RBA: 2970 Timestamp: 2008-08-27 18:52:24.196674 Extract Trail: ./dirdat/pj 3) In the ./dirchk directory, there will be a file with a .cpe extension that is the same name as the Extract group. Rename this file to some other name. This is essentially the same as deleting the Extract.  4) Add the Extract with the same name. GGSCI > ADD EXTRACT , TRANLOG, EXTSEQNO , EXTRBA   GGSCI > INFO EXTRACT Note that both the Current and Recovery checkpoint data will be the same 5) Alter the Recovery Checkpoint. GGSCI > ALTER EXTRACT , IOEXTSEQNO , IOEXTRBA GGSCI > INFO EXTRACT   Note that the Current and Recovery checkpoint data should be changed as specified. 6) Add the remote trail (RMTTRAIL) or local trail (EXTTRAIL). GGSCI > ADD RMTTRAIL./dirdat/, METGABYTES xx, SEQNO , RBA , EXTRACT   It is not necessary to follow every step exactly as shown above; just choose the checkpoint record to "copy" and follow those steps. For example, in step 6, just add the RMTTRAIL as normally if the new Extract is to write to a different location and start from SEQNO 1 and RBA 0. Caveats It is very dangerous to change the production Extract checkpoint file. Any mistake could cause permanent data loss or break data integrity. Please consult the GoldenGate Support team before you try this procedure on any of your production Extracts. Altering recovery checkpoints currently does not work for DB2. ======================================================================== How to Configure GoldenGate Extract When Adding or Removing Redo Log Threads in an Oracle RAC ?, OGG-00446 (Doc ID 1267901.1) Adding new redo log threads Configuring GoldenGate extract when adding or removing redo If you have a RAC with a certain number of threads and an extract that does not explicitly exclude threads, this is the procedure to alter extract to capture the new threads when you add new threads to the RAC ALTER DATABASE ADD LOGFILE and ENABLE the new threads. Do not perform application updates to these new threads. Stop GoldenGate extract. GGSCI > INFO EXT , SHOWCH and print or save this information. DELETE EXT . ADD EXT , BEGIN NOW, TRANLOG, THREADS Update the checkpoint information saved in step 3 . Follow the note 964684.1 to use the SHOWCH output to reset the inputs and outputs of the new extract to match the old one. e.g. Alter each previously existing threads EXTSEQNO and EXTRBA (for the current checkpoint),Alter each previously existing threads the recovery checkpoint IOEXTSEQNO and IOEXTRBA, Alter the Output checkpoint (EXTTRAIL or RMTTRAIL) SEQNO and RBA START EXTRACT. Allow application updates to these new threads. Extract will now capture and checkpoint all ENABLE threads. Activating disabled or inactive redo log threads 1)    If a thread is inactive and you wish to enable it, you need to add the parameter PROCESSTHREADS SELECT. However if there is an existing thread which you want to disable, you need to add PROCESSTHREADS EXCEPT for such thread/s Your current extract will have this parameter PROCESSTHREADS SELECT or PROCESSTHREADS EXCEPT Add the following parameter if you are running V11.1.1 and later, BR BROFF @BR BROFF is needed due to bug 12859529 To stop extract attempting to capture from these inactive threads: Just before you enable the threads, 1) edit the extract parameter file to either removing these parameters or specify what is required depending on which threads you wish to enable. 2) stop the extract 3) enable the thread 4) start extract as soon as possible and before** doing transactions on these newly enabled threads. Extract will now capture and checkpoint all ENABLE threads. **Note: If you have transactions in these threads before you start extract you will lose data into these threads. You can recover by configuring another extract reading only these threads. Disabling redo log threads 1)     The  purpose is to remove an existing RAC thread from goldengate extract so that extract will not capture from that thread. 1) Edit the extract parameter file to either remove these parameters or specify what is required depending on which threads you wish to enable. See THREADOPTIONS PROCESSTHREADS description below. 2) Disable the redo log threads. 3) The extract will abend because these threads are not available. Simply restart the extract as you now have added the THREADOPTIONS PROCESSTHREADS in the extract parameter file. e.g. i) Edit the extract parameter file to add SELECT or add EXCEPT processing from the disabled nodes. For a three node RAC system where node # 2 fails you would add THREADOPTIONS PROCESSTHREADS EXCEPT 2 Alternately, you could also have THREADOPTIONS PROCESSTHREADS SELECT 1 THREADOPTIONS PROCESSTHREADS SELECT 3 Add the following parameter if you are running V11.1.1 and up BR BROFF @BR BROFF is needed due to bug 12859529 ii) Restart your extract NOTE : If a node is EXCLUDED or NOT SELECTED no data that is updated via that node will be extracted, now and in the future. Correlating DataBase Threads to OGG Threads;     When the new RAC thread is added , the OGG thread mapping to RAC thread may change. We need to verify the RAC thread order from v$log and identify the correct GG thread mapping. It is then necessary to alter the extract according to the corresponding archive sequece as per the new identified mapping. e.g: Here is the example showing the same. The RAC thread# sequence should be taken in the order it is shown in 'select distinct thread# from v$log;' The OGG thread# sequence is always be from 1 to n<no.of distinct rac threads>. As the new RAC node is added, the newly added extract will change thread mapping accordingly. Before node addition GG thread 2 is mapped to RAC thread 1. But after node addition , GG thread 2 is pointing to RAC thread 5. Before node addition: ==================== RAC THREAD# OGG thread ------------ -------------- 5 - 1 1 - 2 2 - 3 3 - 3 4 - 5 After node addition: =================== The corresponding thread mapping for Golden Gate is identified as given below: RAC THREAD# OGG thread ----------- ---------- 6 - 1 5 - 2 1 - 3 2 - 4 3 - 5 4 - 6 So when the new extract is added, Extract should be altered according to the new mapping. N.B. removing instance 2 frm rac will cause  error OGG-00446 as OGG-00446  Oracle GoldenGate Capture for Oracle, esmeoe.prm:   The number of Oracle redo threads (1) is not the same as the number of checkpoint threads (2). EXTRACT groups on RAC systems should be created with the THREADS parameter (e.g., ADD EXT , TRANLOG, THREADS 1, BEGIN...). ====================================================================== The Importance of Bounded Recovery Bounded Recovery is a component of Oracle GoldenGate’s Extract process checkpointing facility. It guarantees an efficient recovery after Extract stops for any reason, planned or unplanned, no matter how many open (uncommitted) transactions there were at the time that Extract stopped, nor how old they were. Bounded Recovery sets an upper boundary for the maximum amount of time that it would take for Extract to recover to the point where it stopped and then resume normal processing. Extract performs this recovery as follows: ·         If there were no open transactions when Extract stopped, the recovery begins at the current Extract read checkpoint. This is a normal recovery. ·         If there were open transactions whose start points in the log were very close in time to the time when Extract stopped, Extract begins recovery by re-reading the logs from the beginning of the oldest open transaction. This requires Extract to do redundant work for transactions that were already written to the trail or discarded before Extract stopped, but that work is an acceptable cost given the relatively small amount of data to process. This also is considered a normal recovery. ·         If there were one or more transactions that Extract qualified as long-running open transactions, Extract begins its recovery with a Bounded Recovery. Bounded Recovery is new feature in OGG 11.1, this is how it works: A transaction qualifies as long-running if it has been open longer than one Bounded Recovery interval, which is specified with the BRINTERVAL option of the BR parameter. For example, if the Bounded Recovery interval is four hours, a long-running open transaction is any transaction that started more than four hours ago. At each Bounded Recovery interval, Extract makes a Bounded Recovery checkpoint, which persists the current state and data of Extract to disk, including the state and data (if any) of long-running transactions. If Extract stops after a Bounded Recovery checkpoint, it will recover from a position within the previous Bounded Recovery interval or at the last Bounded Recovery checkpoint, instead of processing from the log position where the oldest open long-running transaction first appeared, which could be several trail files ago. Bounded Recovery is enabled by default for Extract processes and has a 4 hour BR interval. To adjust the BR interval to say 24 hours, use the following syntax in your Extract parameter file: BR BRINTERVAL 24, BRDIR BR The default location for BR checkpoint files is the GoldenGate home directory. This can be altered by including a full path: BR BRINTERVAL 24, BRDIR /ggsdata/brcheckpoint Case Study The Problem In a recent case, Bounded Recovery was disabled through the following Extract parameter: BR BROFF Consequently the following behavior prevented the Extract process from recovering and starting. 1.       Firstly, GoldenGate had fallen behind due to a batch job and subsequently the Extract process was reading the archived redologs and not the online redologs. Also at this time an archived redolog was deleted by RMAN during a scheduled backup, that caused the Extract process to abend with OGG-00446 (caused by ORA-15173) Error in ggserr.log 2012-07-04 11:03:03  ERROR OGG-00446  Oracle GoldenGate Capture for Oracle, euktds01.prm:  Getting attributes for ASM file +FRA/2_86717_716466928.dbf, SQL dbms_diskgroup.getfileattr('+FRA/2_86717_716466928.dbf', :filetype, :filesize, :lblksize); END;>: (15056) ORA-15056: additional error message ORA-15173: entry '2_86717_716466928.dbf' does not exist in directory '/' ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 304 ORA-06512: at line 1Not able to establish initial position for sequence 86717, rba 122140688.

2012-07-04 11:03:03  ERROR OGG-01668  Oracle GoldenGate Capture for Oracle, euktds01.prm:  PROCESS ABENDING.

2.       Some hours later, the deleted archived redolog file was restored and the Extract process restarted. However, despite the process running, the RBA# and Sequence# were not incrementing. The Extract process was stuck!

The INFO GGSCI command with DETAIL option revealed the source redo was not available.

GGSCI (dbserver09a) 2> info EUKMDS01, detail

Extract Source Begin             End

Not Available 2012-07-04 23:30 2012-07-04 23:30

Not Available 2012-07-04 23:28 2012-07-04 23:30

Not Available 2012-07-01 05:35 2012-07-04 23:28

+DATA/ukhub/onlinelog/group_4.282.716467031  2012-06-24 05:28  2012-07-01 05:35

+DATA/ukhub/onlinelog/group_3.280.716467027  2012-06-23 21:06  2012-06-24 05:28

3.       The ggserr.log also revealed a long running transaction detected.

2012-07-04 23:31:47  WARNING OGG-01027  Oracle GoldenGate Capture for Oracle, euko1els.prm:  Long Running Transaction: XID 197.8.3521317, Items 0, Extract EUKO1ELS, Redo Thread 2, SCN 51.3925309013 (222968641109), Redo Seq #86717, Redo RBA 122140688.

The Solution

The Extract process was stuck in recovery mode, but could not find the starting RBA. In order to get the process up and running, the following steps were executed on the source system.

1.       First of all, the Extract process was stopped with the force option.

GGSCI (dbserver09a) 4> send extract EUKMDS01, forcestop

2.       The start position of the Extract process was altered to the beginning of the long running transaction.

GGSCI (dbserver09a) 5> alter extract EUKMDS01, begin 2012-07-04 23:31:47

3.       The extract process was started.

GGSCI (dbserver09a) 4> start extract EUKMDS01

4.       Sure enough, the Extract process was reinitialized and continued to process the backlog.

GGSCI (uklpdptoy09a) 2> info EUKMDS01, detail

Extract Source Begin             End

+DATA/ukhub/onlinelog/group_4.282.716467031  2012-07-04 23:31  2012-07-05 02:58

Not Available * Initialized *   2012-07-04 23:31

Not Available 2012-07-04 23:30 2012-07-04 23:30

Conclusion

Never disable Bounded Recovery else Extract processes may fail to recover automatically. Furthermore, to prevent RMAN from deleting archived log files that are still required. If you register the extract with LOGRETENTION then the GoldenGate will retain the archive logs that Extract needs for recovery.

To register Extract do the following:

1.        Stop the Extract ( Ensure that all the archive log files starting from recovery checkpoint till current checkpoint is available on all nodes )

2.       Execute the following GGSCI commands

GGSCI> dblogin userid , password

GGSCI> register extract , LOGRETENTION

You can confirm whether Extract is registered or not using the query “select * from dba_capture”. (This sounds like Streams!) This should have an entry for Extract.

3.       Start the Extract

GGSCI>start extract