Oracle GoldenGate reports processing errors in several ways by means of its monitoring and reporting tools. For more information about these tools, see Monitoring Oracle GoldenGate Processing.
13.1 Overview of Oracle GoldenGate Error Handling
Oracle GoldenGate provides error-handling options for:
13.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.
To handle extraction errors that relate to DDL operations, use the
For a complete parameter list, see Reference for Oracle GoldenGate.
13.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
DEFAULT2 options, and also handle other errors in a specific manner.
The following comprise the range of
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 Handling Errors as Exceptions for more information.
IGNORE: ignore the error and continue processing.
]: retry the operation, optionally up to a specific number of times.
TRANSABORT [, MAXRETRIES
] [, DELAY[C]SECS
]: abort the transaction and reposition to the beginning, optionally up to a specific number of times at specific intervals.
RESET: remove all previous
REPERRORrules and restore the default of
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
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
REPERROR syntax and usage.
13.3.1 Handling Errors as Exceptions
When the action of
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 About the Exceptions Table.
To map the exceptions to the exceptions table, use either of the following options of the
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
MAPstatement, 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
MAPstatement as an exceptions
MAPstatement, use the
EXCEPTIONSONLYoptions. The exceptions
MAPstatement must immediately follow the regular
MAPstatement that contains the same source table. Use a
COLMAPclause 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
For more information about these parameters, see Reference for Oracle GoldenGate.
MAPstatement that maps the source table
ggs.equip_accountto its target table
MAPstatement that maps the same source table to the exceptions table
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.
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.
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.
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.
This example shows how to use
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.
188.8.131.52 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
MAPEXCEPTION, include the
INSERTALLRECORDS parameter in the
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.
Example 13-2 MAPEXCEPTION
This is an example of how to use
MAPEXCEPTION for exceptions mapping. The
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') ) );
184.108.40.206 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
LOGALLSUPCOLS parameter or the following parameters in the Extract parameter file:
NOCOMPRESSDELETESparameter so that all columns of a row are written to the trail for
GETUPDATEBEFORESparameter so that Extract captures the before image of a row and writes them to the trail.
13.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.
13.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
Table 13-1 TCPERRS Columns
Specifies a TCP/IP error for which you are defining a response.
Controls whether or not Oracle GoldenGate tries to connect again after the defined error. Valid values are either
Controls how long Oracle GoldenGate waits before attempting to connect again.
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.
Example 13-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
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 13-1:
13.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.
13.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.