Read GoldenGate Discard Files

GoldenGate discard files can quickly become cumbersome and overwhelming. Depending on the volume of discards, the complexity of errors, and the number of columns in your tables, a single failure can generate an massive amount of data. For instance, an update failure on a table with over 100 fields causes the entire row to be output column-by-column, leading to files that are hundreds or thousands of lines long.

This makes assessing the actual impact of replication failures—such as determining how many unique records were affected—extremely difficult. To solve this, I built a PL/SQL pipelined function that parses these files, reducing the output to a clean, one-line-per-error format that can be queried and filtered using standard SQL.

1. Setting Up the Data Structures

The first step is to create an object type that captures all the relevant fields we want to extract from the discard file, along with a table type to allow for pipelined output.

Create the Custom Types

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  / 

Establish the File Directory

Next, create an Oracle directory object pointing to the location of your GoldenGate report files (typically located in /ggs/dirrpt).

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

2. The Pipelined Function Logic

The core of the solution is the read_discard function. It uses UTL_FILE to scan the text file, identifies GoldenGate's specific output patterns (like "OCI Error" or "Mapping problem"), and maps them to the structured DiscardRecord type.

The read_discard Function

Note: This function assumes a .dsc file extension and handles common Oracle errors like ORA-00001 (Unique Constraint), ORA-01403 (No Data Found), and ORA-02291 (Integrity Constraint).

CREATE OR REPLACE FUNCTION read_discard ( DiscardName VARCHAR2 ) RETURN DiscardTable PIPELINED IS      -- [Refer to the original text for the full function body including cursors and loop logic]     -- Note: Ensure you adjust the file suffix on line 62 of the original code if yours differs. END read_discard; / 

3. Querying and Analyzing Discard Data

Once the function is compiled, you can treat your GoldenGate discard file like a standard database table. This allows you to perform advanced filtering and aggregation that is impossible with a text editor.

Example Usage

If you have a discard file named resa01sb.dsc, you can run a query to see the exact line number, error code, and the specific column value that caused the failure:

SELECT      line_number,      oracle_error,      source_object_name,      error_operation,      error_column,      error_value,      pk_table_name  FROM TABLE(read_discard('resa01sb')); 

Expected Output

Instead of hundreds of lines of raw text, you get a clean summary:

Important Caveats

• Formatting Dependency: This function is heavily dependent on the specific output format of Oracle GoldenGate. If Oracle changes the way discard files are written in future versions, the string parsing logic will need updating.
   
• Error Coverage: While the function covers the most common replication errors, it may need to be expanded for highly specific or rare ORA errors.

Incorporating this tool into your GoldenGate workflow can significantly reduce the time spent troubleshooting data sync issues.

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?