Pythian Blog: Technical Track

Read GoldenGate Discard Files

by Luke Davies on Nov 26, 2012 12:00:00 AM

GoldenGate discard files can get big and cumbersome. This, of course, depends on many factors, such as the number of discards, the types of errors, and the number columns in your tables. For example, if you get update failures, the entire row is output column by column, and if those tables contain > 100 fields, the file will rapidly expand into many hundreds or possibly thousands of lines. Getting the information you require out of them can then become more difficult. For example, if you have many failures, it is hard to assess how many records are actually affected (in other words, how many rows have not been updated, deleted, or inserted due to the volume of information). To address this, I have built a PL/SQL function to read the discard files, thereby reducing the output to one line per error. It also allows me to apply SQL logic to filter out the unwanted entries. Here's how to do it. First, we need to create a type to have all the fields we want to capture. [sourcecode language="sql"] CREATE OR REPLACE TYPE DiscardRecord AS OBJECT ( message_type VARCHAR2(7) , message VARCHAR2(120) , message_date DATE , description VARCHAR2(500) , line_number NUMBER , oracle_error VARCHAR2(10) , error_number NUMBER(5) , source_object_owner VARCHAR2(30) , source_object_name VARCHAR2(30) , target_object_owner VARCHAR2(30) , target_object_name VARCHAR2(30) , error_operation VARCHAR2(20) , error_object_owner VARCHAR2(30) , error_object_name VARCHAR2(30) , error_action VARCHAR2(30) , error_column VARCHAR2(120) , error_value VARCHAR2(200) , pk_table_name VARCHAR2(30) , operation_seqno NUMBER , operation_rba NUMBER ) / CREATE OR REPLACE TYPE DiscardTable AS TABLE OF DiscardRecord / [/sourcecode] Now, create a directory to point to your directory under the GoldenGate home where you are placing your discard files. (Mine are in /ggs/dirrpt.) [sourcecode language="sql"] CREATE OR REPLACE DIRECTORY GGDiscard AS '/ggs/dirrpt'; [/sourcecode] Then, you are set to create the pipelined function to read the discard file. Note that my discard files all have the suffix .dsc. You, of course, would need to change this if your file names are not set up this way (see line 62). [sourcecode language="sql"] CREATE OR REPLACE FUNCTION read_discard ( DiscardName VARCHAR2 ) RETURN DiscardTable PIPELINED IS FileHandle UTL_FILE.FILE_TYPE; DiscardRec DISCARDRECORD; DiscardExtra DISCARDRECORD; FileBuffer VARCHAR2(32767); PartLine VARCHAR2(32767); OutputRow BOOLEAN; ErrorRow BOOLEAN; FindingColumn BOOLEAN; LineNumber NUMBER := 0; /* Cursors */ CURSOR get_cons_cols_curs ( ConstraintOwner VARCHAR2 , ConstraintName VARCHAR2 ) IS SELECT column_name FROM dba_cons_columns WHERE owner = ConstraintOwner AND constraint_name = ConstraintName AND position = 1; CURSOR get_pk_table_curs ( ConstraintOwner VARCHAR2 , ConstraintName VARCHAR2 ) IS SELECT pk.table_name FROM dba_constraints fk , dba_constraints pk WHERE pk.owner = fk.r_owner AND pk.constraint_name = fk.r_constraint_name AND fk.owner = ConstraintOwner AND fk.constraint_name = ConstraintName; CURSOR get_pk_col_curs ( TableOwner VARCHAR2 , TableName VARCHAR2 ) IS SELECT col.column_name FROM dba_cons_columns col , dba_constraints pk WHERE pk.owner = TableOwner AND pk.table_name = TableName AND pk.owner = col.owner AND pk.constraint_name = col.constraint_name AND pk.constraint_type = 'P' AND col.position = 1; /* Procedures */ PROCEDURE OpenDiscard IS BEGIN FileHandle := UTL_FILE.FOPEN('GGDISCARD',DiscardName||'.dsc','R'); END OpenDiscard; PROCEDURE ReadDiscard IS BEGIN UTL_FILE.GET_LINE(FileHandle,FileBuffer); LineNumber := LineNumber + 1; END ReadDiscard; PROCEDURE CloseDiscard IS BEGIN UTL_FILE.FCLOSE(FileHandle); END CloseDiscard; PROCEDURE InitialiseRecord ( Discard_INOUT IN OUT DISCARDRECORD ) IS BEGIN Discard_INOUT := DISCARDRECORD( NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ); END InitialiseRecord; BEGIN OpenDiscard; ErrorRow := FALSE; FindingColumn := FALSE; InitialiseRecord(DiscardRec); LOOP ReadDiscard; WHILE FileBuffer IS NULL LOOP ReadDiscard; END LOOP; /* Read first 2 words */ IF FileBuffer = '*' THEN PartLine := '*'; ELSE PartLine := SUBSTR(FileBuffer,1,INSTR(FileBuffer,' ',2,2)-1); END IF; OutputRow := FALSE; CASE PartLine WHEN 'Oracle GoldenGate' THEN IF ErrorRow THEN PIPE ROW (DiscardRec); InitialiseRecord(DiscardRec); ErrorRow := FALSE; END IF; DiscardRec.message_type := 'INFO'; DiscardRec.message := SUBSTR(FileBuffer,19,INSTR(FileBuffer,' ',19,1)-19) ||' Process ' ||SUBSTR( FileBuffer ,INSTR(FileBuffer,'for Oracle process')+19 ,INSTR(FileBuffer,',')-(INSTR(FileBuffer,'for Oracle process')+19)); -- DiscardRec.message := 'Capture Process '||SUBSTR(FileBuffer,47,INSTR(FileBuffer,',')-47); DiscardRec.description := SUBSTR( FileBuffer,INSTR(FileBuffer,',')+2 ,INSTR(FileBuffer,':') - (INSTR(FileBuffer,',')+2)); DiscardRec.message_date := TO_DATE( SUBSTR(FileBuffer,INSTR(FileBuffer,':')+2) ,'YYYY-MM-DD HH24:MI:SS'); DiscardRec.line_number := LineNumber; OutputRow := TRUE; WHEN 'Process Abending' THEN IF ErrorRow THEN PIPE ROW (DiscardRec); InitialiseRecord(DiscardRec); ErrorRow := FALSE; END IF; DiscardRec.message_type := 'WARNING'; DiscardRec.message := PartLine; DiscardRec.message_date := TO_DATE(SUBSTR(FileBuffer,INSTR(FileBuffer,':')+2),'YYYY-MM-DD HH24:MI:SS'); DiscardRec.line_number := LineNumber; OutputRow := TRUE; WHEN 'Current time:' THEN IF ErrorRow THEN PIPE ROW (DiscardRec); InitialiseRecord(DiscardRec); END IF; DiscardRec.message_type := 'ERROR'; DiscardRec.message_date := TO_DATE(SUBSTR(FileBuffer,INSTR(FileBuffer,':')+2),'YYYY-MM-DD HH24:MI:SS'); DiscardRec.line_number := LineNumber; ErrorRow := TRUE; OutputRow := FALSE; WHEN ' Error text' THEN DiscardRec.oracle_error := SUBSTR(FileBuffer,INSTR(FileBuffer,', ')+2,INSTR(FileBuffer,':')-(INSTR(FileBuffer,', ')+2)); DiscardRec.error_number := TO_NUMBER(SUBSTR(DiscardRec.oracle_error,INSTR(DiscardRec.oracle_error,'-')+1)); DiscardRec.message := SUBSTR(FileBuffer,INSTR(FileBuffer,':')+2,INSTR(FileBuffer,',',1,2)-(INSTR(FileBuffer,':')+2)); DiscardRec.description := SUBSTR(FileBuffer,INSTR(FileBuffer,'SQL ')); OutputRow := FALSE; WHEN 'Operation failed' THEN DiscardRec.operation_seqno := SUBSTR( FileBuffer,INSTR(FileBuffer,'seqno ')+6 ,INSTR(FileBuffer,' rba ')-(INSTR(FileBuffer,'seqno ')+6)); DiscardRec.operation_rba := SUBSTR(FileBuffer,INSTR(FileBuffer,' rba ')+5); OutputRow := FALSE; WHEN 'OCI Error' THEN DiscardRec.oracle_error := SUBSTR(FileBuffer,INSTR(FileBuffer,' ',1,2)+1,INSTR(FileBuffer,':')-(INSTR(FileBuffer,' ',1,2)+1)); DiscardRec.error_number := TO_NUMBER(SUBSTR(DiscardRec.oracle_error,INSTR(DiscardRec.oracle_error,'-')+1)); DiscardRec.message := SUBSTR(FileBuffer,INSTR(FileBuffer,':')+2,INSTR(FileBuffer,',')-(INSTR(FileBuffer,':')+2)); DiscardRec.description := SUBSTR(FileBuffer,INSTR(FileBuffer,'SQL ')); CASE DiscardRec.error_number WHEN 1 THEN PartLine := SUBSTR( DiscardRec.message,INSTR(DiscardRec.message,'(')+1 ,INSTR(DiscardRec.message,')')-(INSTR(DiscardRec.message,'(')+1)); DiscardRec.error_object_owner := SUBSTR(PartLine,1,INSTR(PartLine,'.')-1); DiscardRec.error_object_name := SUBSTR(PartLine,INSTR(PartLine,'.')+1); WHEN 2291 THEN PartLine := SUBSTR( DiscardRec.message,INSTR(DiscardRec.message,'(')+1 ,INSTR(DiscardRec.message,')')-(INSTR(DiscardRec.message,'(')+1)); DiscardRec.error_object_owner := SUBSTR(PartLine,1,INSTR(PartLine,'.')-1); DiscardRec.error_object_name := SUBSTR(PartLine,INSTR(PartLine,'.')+1); ELSE NULL; END CASE; OutputRow := FALSE; WHEN 'Discarding record' THEN DiscardRec.error_action := SUBSTR( FileBuffer,INSTR(FileBuffer,' ',1,4)+1 ,INSTR(FileBuffer,' ',1,5)-(INSTR(FileBuffer,' ',1,4)+1)); DiscardRec.error_number := TO_NUMBER(SUBSTR(FileBuffer,INSTR(FileBuffer,' ',-1))); DiscardRec.oracle_error := 'ORA-'||TO_CHAR(DiscardRec.error_number,'FM09999'); OutputRow := FALSE; WHEN 'Problem replicating' THEN PartLine := SUBSTR( FileBuffer,INSTR(FileBuffer,' ',1,2)+1 ,INSTR(FileBuffer,' to ')-(INSTR(FileBuffer,' ',1,2)+1)); DiscardRec.source_object_owner := SUBSTR(PartLine,1,INSTR(PartLine,'.')-1); DiscardRec.source_object_name := SUBSTR(PartLine,INSTR(PartLine,'.')+1); PartLine := SUBSTR(FileBuffer,INSTR(FileBuffer,' to ')+4); DiscardRec.target_object_owner := SUBSTR(PartLine,1,INSTR(PartLine,'.')-1); DiscardRec.target_object_name := SUBSTR(PartLine,INSTR(PartLine,'.')+1); OutputRow := FALSE; WHEN 'Mapping problem' THEN DiscardRec.error_operation := UPPER(SUBSTR( FileBuffer,INSTR(FileBuffer,' ',-1,4)+1 ,INSTR(FileBuffer,' ',-1,3)-(INSTR(FileBuffer,' ',-1,4)+1))); OutputRow := FALSE; WHEN 'Record not' THEN DiscardRec.message := FileBuffer; OutputRow := FALSE; WHEN '*' THEN IF FindingColumn THEN FindingColumn := FALSE; OutputRow := TRUE; ELSE /* Gather some information for specific messages */ CASE DiscardRec.error_number WHEN 1 /* Unique constraint violation */ THEN /* Find the first column for the key and then be able to find it's value in the text */ OPEN get_cons_cols_curs(DiscardRec.error_object_owner,DiscardRec.error_object_name); FETCH get_cons_cols_curs INTO DiscardRec.error_column; CLOSE get_cons_cols_curs; WHEN 1403 /* Row not found */ THEN /* Find the PK of the table and then find it's value - if possible */ DiscardRec.error_object_owner := DiscardRec.source_object_owner; DiscardRec.error_object_name := DiscardRec.source_object_name; OPEN get_pk_col_curs(DiscardRec.error_object_owner,DiscardRec.error_object_name); FETCH get_pk_col_curs INTO DiscardRec.error_column; CLOSE get_pk_col_curs; WHEN 2291 /* Integrity constraint voilation */ THEN /* Find the fk table name , column name and then be able to find the value of the offending row */ /* Get the column name */ OPEN get_cons_cols_curs(DiscardRec.error_object_owner,DiscardRec.error_object_name); FETCH get_cons_cols_curs INTO DiscardRec.error_column; CLOSE get_cons_cols_curs; /* Get primary key table name */ OPEN get_pk_table_curs(DiscardRec.error_object_owner,DiscardRec.error_object_name); FETCH get_pk_table_curs INTO DiscardRec.pk_table_name; CLOSE get_pk_table_curs; ELSE NULL; END CASE; FindingColumn := TRUE; END IF; ELSE IF FindingColumn THEN IF DiscardRec.error_column IS NOT NULL THEN IF DiscardRec.error_column = SUBSTR(FileBuffer,1,INSTR(FileBuffer,' = ')-1) THEN DiscardRec.error_value := SUBSTR(FileBuffer,INSTR(FileBuffer,' = ')+3); END IF; END IF; ELSE /* Found something unusual so let's output an extra record */ InitialiseRecord(DiscardExtra); DiscardExtra.message_type := 'UNKNOWN'; DiscardExtra.message := SUBSTR(FileBuffer,1,120); DiscardExtra.description := SUBSTR(FileBuffer,120); DiscardExtra.line_number := LineNumber; PIPE ROW (DiscardExtra); END IF; END CASE; IF OutputRow THEN PIPE ROW (DiscardRec); InitialiseRecord(DiscardRec); ErrorRow := FALSE; END IF; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN /* Output last row if not output */ IF DiscardRec.message_type IS NOT NULL THEN PIPE ROW(DiscardRec); END IF; CloseDiscard; END read_discard; / [/sourcecode] There are a certain number of caveats. It is heavily dependent on the output format of GoldenGate, and if this changed significantly, the function would need attention. Also, I have not allowed for every eventuality, but for the most common errors we see, it is useful. Here is an example of how to use it and what output you might expect. (Table names have been changed to protect the innocent.) The file name we want to examine is called /ggs/dirrpt/resa01sb.dsc and is 567 lines long. Issue the following command: [sourcecode language="sql"] select LINE_NUMBER , ORACLE_ERROR , SOURCE_OBJECT_NAME , ERROR_OPERATION , ERROR_COLUMN , ERROR_VALUE , PK_TABLE_NAME from table(read_discard('resa01sb')); [/sourcecode] And we got back some output that looked like this: 
LINE_NUMBER ORA_ERROR SOURCE_OBJECT_NAME ERROR_OPERATION ERROR_COLUMN ERROR_VALUE PK_TABLE_NAME
 ----------- --------- ------------------ --------------- ----------------- ---------------- -------------
  1 ORA-02291 CHILD_TABLE1 INSERT PARENT_ID 4100000000360956 PARENT
  44 ORA-02291 CHILD_TABLE2 INSERT CHILD1_ID 6100000000041100 CHILD_TABLE1
  63 ORA-02291 CHILD_TABLE3 INSERT PARENT_ID 4100000000360956 PARENT
  263 ORA-01403 TABLE1 UPDATE TABLE1_ID 4100000000562453
  311 ORA-01403 PARENT UPDATE PARENT_ID 4100000000360956
  392 ORA-01403 CHILD_TABLE3 UPDATE CHILD3_ID 6100000000264611
 
 6 rows selected.
You can use a WHERE clause and filter on any of the columns in the DISCARDRECORD type. I hope you find this as useful as we have in examining your discard files!

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email

Follow Us     Pythian-LinkedIn   Pythian-Twitter   Pythian-FB

 

Pythian-logo-White

© 2025 All rights reserved.