Read GoldenGate Discard Files

Nov 26, 2012 / By Luke Davies

Tags: , , ,

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.

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
/

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.)

CREATE OR REPLACE DIRECTORY GGDiscard AS '/ggs/dirrpt';

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).

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;
/

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:

select LINE_NUMBER
     , ORACLE_ERROR
     , SOURCE_OBJECT_NAME
     , ERROR_OPERATION
     , ERROR_COLUMN
     , ERROR_VALUE
     , PK_TABLE_NAME
from table(read_discard('resa01sb'));

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!

7 Responses to “Read GoldenGate Discard Files”

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>