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!
19 Comments. Leave new
Very very useful, thanks Luke, you rock as always.
An useful and innovative way to read discard files
Thank you Luke. This is good stuff and looks very useful. We have a requirement to evaluate a large volume of discards from a goldegate discard file. Can I take the liberty of borrowing your code and tweaking it to our needs on our system to analyze the discards? Please let me know. Thank you!! Ajay.
Hi Ajay
Glad you could find it useful. By all means take the code and tweak to your requirements.
Thanks for asking.
Thank you Luke!! I will proceed accordingly.
Wow thank you for sharing this !!!!
Thanks for sharing this is awesome !!!
ERROR at line 8:
ORA-29284: file read error
ORA-06512: at “SYS.UTL_FILE”, line 106
ORA-06512: at “SYS.UTL_FILE”, line 746
ORA-06512: at “TESTGG.READ_DISCARD”, line 68
ORA-06512: at “TESTGG.READ_DISCARD”, line 120
function created successfully ..when i tried to query im getting the above error .
infact i just wrote a small routine to read the discard file and it is successfuly.
[email protected]> DECLARE
2 vInHandle utl_file.file_type;
vNewLine VARCHAR2(250);
3 4 BEGIN
5 vInHandle := utl_file.fopen(‘GGDISCARD’, ‘gsi_sb010.dsc’, ‘R’);
6 LOOP
7 BEGIN
8 utl_file.get_line(vInHandle, vNewLine);
9 dbms_output.put_line(vNewLine);
10 EXCEPTION
11 WHEN OTHERS THEN
12 EXIT;
13 END;
END LOOP;
14 15 utl_file.fclose(vInHandle);
16 END fopen;
17 /
PL/SQL procedure successfully completed.
what could be wrong here.
Hi Sunny
What file name did you pass into the function?
Note that on line 62 I append a ‘.dsc’ to the filename, so your case you should pass in ‘gsi_sb010’ rather than ‘gsi_sb010.dsc’ to the read_discard function.
If this is not your problem – please can you post your statement you are using to access the function.
Thanks
Luke
Hi Sir Thanks for reply.
the bellw is the code that im using
select LINE_NUMBER
, ORACLE_ERROR
, SOURCE_OBJECT_NAME
, ERROR_OPERATION
, ERROR_COLUMN
, ERROR_VALUE
, PK_TABLE_NAME
from table(read_discard(‘gsi_sb010’));
this sql is throwing the error.
Hi Sunny
I suspect that your discard file has line(s) in it that are over 1024 characters long.
Try amending the utl_file.fopen call to add a fourth parameter and set to 32767
like so -> FileHandle := UTL_FILE.FOPEN(‘GGDISCARD’,DiscardName||’.dsc’,’R’,32767);
And see if it then runs.
HTH
Luke
Hi,
I am getting below error.
SQL> select LINE_NUMBER
, ORACLE_ERROR
, SOURCE_OBJECT_NAME
, ERROR_OPERATION
, ERROR_COLUMN
, ERROR_VALUE
, PK_TABLE_NAME
from table(read_discard(‘ODSREP’)); 2 3 4 5 6 7 8
from table(read_discard(‘ODSREP’))
*
ERROR at line 8:
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at “SYS.READ_DISCARD”, line 153
Thanks
Arul Raman
Hi Arul
I suspect that your ‘Current Time:’ lines are slightly different to mine and therefore you make sure that the data format mask and substring values are correct for your implementation.
Thanks
Luke
Hi Luke,
Thanks for this , I tried the above setup in my database but its throwing the below error
SQL> select LINE_NUMBER
2 , ORACLE_ERROR
3 , SOURCE_OBJECT_NAME
4 , ERROR_OPERATION
5 , ERROR_COLUMN
6 , ERROR_VALUE
7 , PK_TABLE_NAME
8 from table(read_discard(‘rprincbedis.dsc’));
from table(read_discard(‘rprincbedis.dsc’))
*
ERROR at line 8:
ORA-29283: invalid file operation
ORA-06512: at “SYS.UTL_FILE”, line 536
ORA-29283: invalid file operation
ORA-06512: at “SYS.READ_DISCARD”, line 52
ORA-06512: at “SYS.READ_DISCARD”, line 97
Function is created sccessfully , i tried with
like so -> FileHandle := UTL_FILE.FOPEN(‘GGDISCARD’,DiscardName||’.dsc’,’R’,32767);
and tried with sys user and granted read , write , execute on that directory permisiion also and OS level also i granted full permission to dirrpt folder
and also tried discard file name ,like, ‘discard.dsc’ ,and ‘discard’ and ‘.dsc’ but no luck
and actual file name is discard.dsc
Please help to resolve this.
Hi ,
thanks for your information , could you please help me for fixing following issue ?
SQL> alter session set nls_date_format = ‘YYYY-MM-DD HH24:MI:SS’;
Session altered.
SQL> select LINE_NUMBER
, ORACLE_ERROR
, SOURCE_OBJECT_NAME
, ERROR_OPERATION
, ERROR_COLUMN
, ERROR_VALUE
, PK_TABLE_NAME
from table(read_discard(‘rep_my2_discard’)); 2 3 4 5 6 7 8
from table(read_discard(‘rep_my2_discard’))
*
ERROR at line 8:
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at “SYS.READ_DISCARD”, line 153
Hey Luke This is a very usefull information to me, I’m building Active-Active Replication and where I have to get the discard data stored in table to show them to application and DB development pepole. It’s realy very helpful to me.. Thanks once again …
ERROR at line 8:
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at “SYS.READ_DISCARD”,
CREATE OR REPLACE FUNCTION date_tester (p_str VARCHAR2)
RETURN DATE
IS
TYPE format_masks IS TABLE OF VARCHAR2 (40);
l_mask format_masks := format_masks (‘YYYY-MM-DD HH24:MI:SS’);
l_date DATE;
BEGIN
FOR i IN 1 .. l_mask.COUNT
LOOP
BEGIN
l_date := TO_DATE (p_str, l_mask (i));
RETURN l_date;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END LOOP;
RETURN NULL;
END;
/
look for the portion with the lines below
DiscardRec.message_date := TO_DATE(SUBSTR(FileBuffer,INSTR(FileBuffer,’:’)+2),’YYYY-MM-DD HH24:MI:SS’);
and replace with
DiscardRec.message_date :=
date_tester (
SUBSTR (FileBuffer, INSTR (FileBuffer, ‘:’) + 1));
DiscardRec.line_number := LineNumber;
Hi,
When execute the select into function I see the columns in null value, example:
LINE_NUMBER ORACLE_ERROR SOURCE_OBJECT_NAME ERROR_OPERATION ERROR_COLUMN ERROR_VALUE PK_TABLE_NAME
1
8
2 ORA-01403
114
115
117
223
225
Regards bro.
I suspect that since I wrote the original that the discard files have had some gradual format changes resulting in this lack of successful data.
You will need to debug the code to find out how it is breaking up each line.
The idea of the code was to provide a framework with which to adapt to your environment.