OGG : Handling Processing Errors

  source

• Overview of Oracle GoldenGate Error Handling
• Handling Extract Errors
• Handling Replicat Errors during DML Operations
• Handling Replicat errors during DDL Operations
• Handling TCP/IP Errors
• Maintaining Updated Error Messages
• Resolving Oracle GoldenGate Errors

15.1 Overview of Oracle GoldenGate Error Handling

Oracle GoldenGate provides error-handling options for:

• Extract
• Replicat
• TCP/IP

15.2 Handling Extract Errors

There is no specific parameter to handle Extract errors when DML operations are being extracted, but Extract does provide a number of parameters that can be used to prevent anticipated problems. These parameters handle anomalies that can occur during the processing of DML operations, such as what to do when a row to be fetched cannot be located, or what to do when the transaction log is not available. The following is a partial list of these parameters.

• FETCHOPTIONS
• WARNLONGTRANS
• DBOPTIONS
• TRANLOGOPTIONS

To handle extraction errors that relate to DDL operations, use the DDLERROR parameter.

For a complete parameter list, see Reference for Oracle GoldenGate for Windows and UNIX.

15.3 Handling Replicat Errors during DML Operations

To control the way that Replicat responds to an error during one of its DML statements, use the REPERROR parameter in the Replicat parameter file. You can use REPERROR as a global parameter or as part of a MAP statement. You can handle most errors in a default fashion (for example, to cease processing) with DEFAULT and DEFAULT2 options, and also handle other errors in a specific manner.

The following comprise the range of REPERROR responses:

• ABEND: roll back the transaction and stop processing.
• DISCARD: log the error to the discard file and continue processing.
• EXCEPTION: send the error for exceptions processing. See Section 15.3.1, "Handling Errors as Exceptions" for more information.
• IGNORE: ignore the error and continue processing.
• RETRYOP [MAXRETRIES n]: retry the operation, optionally up to a specific number of times.
• TRANSABORT [, MAXRETRIES n] [, DELAY[C]SECS n]: abort the transaction and reposition to the beginning, optionally up to a specific number of times at specific intervals.
• RESET: remove all previous REPERROR rules and restore the default of ABEND.
• TRANSDISCARD: discard the entire replicated source transaction if any operation within that transaction, including the commit, causes a Replicat error that is listed in the error specification. This option is useful when integrity constraint checking is disabled on the target.
• TRANSEXCEPTION: perform exceptions mapping for every record in the replicated source transaction, according to its exceptions-mapping statement, if any operation within that transaction (including the commit) causes a Replicat error that is listed in the error specification.

Most options operate on the individual record that generated an error, and Replicat processes the other, successful operations in the transaction. The exceptions are TRANSDISCARD and TRANSEXCEPTION: These options affect all records in a transaction if any record in that transaction generates an error. (The ABEND option also applies to the entire transaction, but does not apply error handling.)

See Reference for Oracle GoldenGate for Windows and UNIX for REPERROR syntax and usage.

15.3.1 Handling Errors as Exceptions

When the action of REPERROR is EXCEPTION or TRANSEXCEPTION, you can map the values of operations that generate errors to an exceptions table and, optionally, map other information about the error that can be used to resolve the error. See Section 15.3.1.3, "About the Exceptions Table".

To map the exceptions to the exceptions table, use either of the following options of the MAP parameter:

• MAP with EXCEPTIONSONLY
• MAP with MAPEXCEPTION

15.3.1.1 Using EXCEPTIONSONLY

EXCEPTIONSONLY is valid for one pair of source and target tables that are explicitly named and mapped one-to-one in a MAP statement; that is, there cannot be wildcards. To use EXCEPTIONSONLY, create two MAP statements for each source table that you want to use EXCEPTIONSONLY for on the target:

• The first, a standard MAP statement, maps the source table to the actual target table.
• The second, an exceptions MAP statement, maps the source table to the exceptions table (instead of to the target table). An exceptions MAPstatement executes immediately after an error on the source table to send the row values to the exceptions table.
  To identify a MAP statement as an exceptions MAP statement, use the INSERTALLRECORDS and EXCEPTIONSONLY options. The exceptions MAPstatement must immediately follow the regular MAP statement that contains the same source table. Use a COLMAP clause in the exceptions MAPstatement if the source and exceptions-table columns are not identical, or if you want to map additional information to extra columns in the exceptions table, such as information that is captured by means of column-conversion functions or SQLEXEC.

For more information about these parameters, see Reference for Oracle GoldenGate for Windows and UNIX.

Example 15-1 EXCEPTIONSONLY

This example shows how to use REPERROR with EXCEPTIONSONLY and an exceptions MAP statement. This example only shows the parameters that relate to REPERROR; other parameters not related to error handling are also required for Replicat.

REPERROR (DEFAULT, EXCEPTION)
MAP ggs.equip_account, TARGET ggs.equip_account2,
COLMAP (USEDEFAULTS);
MAP ggs.equip_account, TARGET ggs.equip_account_exception,
EXCEPTIONSONLY,
INSERTALLRECORDS
COLMAP (USEDEFAULTS,
DML_DATE = @DATENOW (),
OPTYPE = @GETENV ('LASTERR', 'OPTYPE'),
DBERRNUM = @GETENV ('LASTERR', 'DBERRNUM'),
DBERRMSG = @GETENV ('LASTERR', 'DBERRMSG'));

In this example, the REPERROR parameter is set for DEFAULT error handling, and the EXCEPTION option causes the Replicat process to treat failed operations as exceptions and continue processing.

There are two MAP statements:

• A regular MAP statement that maps the source table ggs.equip_account to its target table equip_account2.
• An exceptions MAP statement that maps the same source table to the exceptions table ggs.equip_account_exception.

In this case, four extra columns were created, in addition to the same columns that the table itself contains:

DML_DATE
OPTYPE
DBERRNUM
DBERRMSG

To populate the DML_DATE column, the @DATENOW column-conversion function is used to get the date and time of the failed operation, and the result is mapped to the column. To populate the other extra columns, the @GETENV function is used to return the operation type, database error number, and database error message.

The EXCEPTIONSONLY option of the exceptions MAP statement causes the statement to execute only after a failed operation on the source table. It prevents every operation from being logged to the exceptions table.

The INSERTALLRECORDS parameter causes all failed operations for the specified source table, no matter what the operation type, to be logged to the exceptions table as inserts.

Note:

There can be no primary key or unique index restrictions on the exception table. Uniqueness violations are possible in this scenario and would generate errors.

15.3.1.2 Using MAPEXCEPTION

MAPEXCEPTION is valid when the names of the source and target tables in the MAP statement are wildcarded. Place the MAPEXCEPTION clause in the regular MAP statement, the same one where you map the source tables to the target tables. Replicat maps all operations that generate errors from all of the wildcarded tables to the same exceptions table; therefore, the exceptions table should contain a superset of all of the columns in all of the wildcarded tables.

Because you cannot individually map columns in a wildcard configuration, use the COLMAP clause with the USEDEFAULTS option to handle the column mapping for the wildcarded tables (or use the COLMATCH parameter if appropriate), and use explicit column mappings to map any additional information, such as that captured with column-conversion functions or SQLEXEC.

When using MAPEXCEPTION, include the INSERTALLRECORDS parameter in the MAPEXCEPTION clause. INSERTALLRECORDS causes all operation types to be applied to the exceptions table as INSERT operations. This is required to keep an accurate record of the exceptions and to prevent integrity errors on the exceptions table.

For more information about these parameters, see Reference for Oracle GoldenGate for Windows and UNIX.

Example 15-2 MAPEXCEPTION

This is an example of how to use MAPEXCEPTION for exceptions mapping. The MAP and TARGET clauses contain wildcarded source and target table names. Exceptions that occur when processing any table with a name beginning with TRX are captured to the fin.trxexceptions table using the designated mapping.

MAP src.trx*, TARGET trg.*,
MAPEXCEPTION (TARGET fin.trxexceptions,
INSERTALLRECORDS,
COLMAP (USEDEFAULTS,
ACCT_NO = ACCT_NO,
OPTYPE = @GETENV ('LASTERR', 'OPTYPE'),
DBERR = @GETENV ('LASTERR', 'DBERRNUM'),
DBERRMSG = @GETENV ('LASTERR', 'DBERRMSG')
)
);

15.3.1.3 About the Exceptions Table

Use an exceptions table to capture information about an error that can be used for such purposes as troubleshooting your applications or configuring them to handle the error. At minimum, an exceptions table should contain enough columns to receive the entire row image from the failed operation. You can define extra columns to contain other information that is captured by means of column-conversion functions, SQLEXEC, or other external means.

To ensure that the trail record contains values for all of the columns that you map to the exceptions table, you can use either the LOGALLSUPCOLSparameter or the following parameters in the Extract parameter file:

• Use the NOCOMPRESSDELETES parameter so that all columns of a row are written to the trail for DELETE operations.
• Use the GETUPDATEBEFORES parameter so that Extract captures the before image of a row and writes them to the trail.

For more information about these parameters, see Reference for Oracle GoldenGate for Windows and UNIX.

15.4 Handling Replicat errors during DDL Operations

To control the way that Replicat responds to an error that occurs for a DDL operation on the target, use the DDLERROR parameter in the Replicat parameter file. For more information, see Reference for Oracle GoldenGate for Windows and UNIX.

15.5 Handling TCP/IP Errors

To provide instructions for responding to TCP/IP errors, use the TCPERRS file. This file is in the Oracle GoldenGate directory

Example 15-3 TCPERRS File

# TCP/IP error handling parameters
# Default error response is abend
#
# Error          Response   Delay(csecs)  Max Retries

ECONNABORTED     RETRY      1000          10
ECONNREFUSED     RETRY      1000          12
ECONNRESET       RETRY      500           10
ENETDOWN         RETRY      3000          50
ENETRESET        RETRY      1000          10
ENOBUFS          RETRY      100           60
ENOTCONN         RETRY      100           10
EPIPE            RETRY      500           10
ESHUTDOWN        RETRY      1000          10
ETIMEDOUT        RETRY      1000          10
NODYNPORTS       RETRY      100           10

The TCPERRS file contains default responses to basic errors. To alter the instructions or add instructions for new errors, open the file in a text editor and change any of the values in the columns shown in Table 15-1:

Table 15-1 TCPERRS Columns

Column	Description
Error	Specifies a TCP/IP error for which you are defining a response.
Response	Controls whether or not Oracle GoldenGate tries to connect again after the defined error. Valid values are either RETRY or ABEND.
Delay	Controls how long Oracle GoldenGate waits before attempting to connect again.
Max Retries	Controls the number of times that Oracle GoldenGate attempts to connect again before aborting.

If a response is not explicitly defined in the TCPERRS file, Oracle GoldenGate responds to TCP/IP errors by abending.

15.6 Maintaining Updated Error Messages

The error, information, and warning messages that Oracle GoldenGate processes generate are stored in a data file named ggmessage.dat in the Oracle GoldenGate installation directory. The version of this file is checked upon process startup and must be identical to that of the process in order for the process to operate.

15.7 Resolving Oracle GoldenGate Errors

To get help with specific troubleshooting issues, go to My Oracle Support at http://support.oracle.com and search the Knowledge Base.