REPERROR

Valid For

Replicat

Description

Use the REPERROR parameter to control how Replicat responds to errors. The default response of Replicat to any error is to abend.

You can use one REPERROR statement to handle most errors in a default manner, while using one or more other REPERROR statements to handle specific errors differently. For example, you can ignore duplicate-record errors but abend processing in all other cases.

You can use REPERROR globally (at the root of the parameter file) to affect all MAP statements that follow it, or you can use it within a MAP statement to affect the tables specified in that statement. Using REPERROR within a MAP statement gives you the ability to handle errors in a particular way for each thread of a coordinated Replicat.

Using Record-level Error Handling

All REPERROR options except TRANSDISCARD and TRANSEXCEPTION apply an error-handling action in response to an individual SQL operation on an individual record. Other, error-free records in the same transaction are processed as configured in the MAP statements and other parameters in the parameter file, as applicable.

Using Transaction-level Error Handling

The TRANSDISCARD, TRANSEXCEPTION, and ABEND options apply an error-handling action to an entire transaction. The triggering error can occur on an individual record in the transaction or on the commit operation. (Commit errors do not have a particular record associated with them.) These options can be used to:

prevent an entire source transaction from being replicated to the target when any error is associated with it.
respond to a commit error when deferred constraint checking is enabled on the target.

TRANSDISCARD and TRANSEXCEPTION are mutually exclusive.

Effect of Other Parameters on Transaction-level Options

TRANSDISCARD and TRANSEXCEPTION honor the boundaries of the source transaction; however, the presence of BATCHSQL, GROUPTRANSOPS, or MAXTRANSOPS in the parameter file may affect the error-handling logic or outcome, because they alter transaction boundaries.

Effect of BATCHSQL and GROUPTRANSOPS

BATCHSQL or GROUPTRANSOPS (the default) both group SQL operations from different transactions into larger transactions to improve performance, while maintaining transactional order. When these parameters are in effect and any error occurs, Replicat first tries to resolve it by entering an alternate processing mode (see the documentation for those parameters). If the error persists, TRANSDISCARD or TRANSEXCEPTION comes into effect, and Replicat reverts to source-processing mode as follows:

It rolls back the grouped or arrayed transaction.
It replays the offending transaction one SQL operation at a time, using the same transaction boundaries as the source transaction.
It performs the discard logic (TRANSDISCARD) or exceptions-mapping (TRANSEXCEPTION). (See those option descriptions for more detail.)
It resumes BATCHSQL or GROUPTRANSOPS mode after the TRANSDISCARD error handling is completed.

Effect of MAXTRANSOPS

The integrity of TRANSDISCARD and TRANSEXCEPTION transaction-level error handling can be adversely affected by the setting of the MAXTRANSOPS parameter. MAXTRANSOPS causes Replicat to split very large replicated source transactions into smaller transactions when it applies them on the target.

The TRANSDISCARD and TRANSEXCEPTION logic cause Replicat to roll back to the first record after the last successful commit. This may or may not be the actual beginning of the offending transaction. It depends on whether that transaction was split up and parts of it are in the previously committed transactions. If that is the case, Replicat cannot apply the TRANSDISCARD or TRANSEXCEPTION action to the whole transaction as it was issued on the source, but only to the part that was rolled back from the target.

If you use MAXTRANSOPS, make certain that it is set to a value that is larger than the largest transaction that you expect to be handled by TRANSDISCARD and TRANSEXCEPTION. This will ensure that transactions are not be split apart into smaller ones on the target.

Effect of Transaction-level Options on Statistics

The output of informational commands in GGSCI, such as STATS REPLICAT, will show the total number of records in the transaction that was processed by TRANSDISCARD or TRANSEXCEPTION logic. This number may reflect the following:

Replicat writes all records of the transaction to the discard file, including any records that were excluded from Oracle GoldenGate processing by means of a FILTER or WHERE clause in a MAP statement.
If a source table in the transaction has multiple targets, the discarded transaction will contain multiple copies of each record, one for each target.
Replicat ignores any exceptions mapping statements (as specified with EXCEPTIONSONLY or MAPEXCEPTION in a MAP statement) when discarding the transaction.

Replicat abends on errors that are caused by the discard processing (TRANSDISCARD) or exceptions mapping (TRANSEXCEPTION).

Getting More Information about Error Handling

See Administering Oracle GoldenGate for more information about configuring error handling.

See "TABLE | MAP" for more information about the MAP parameter.

Default

TRANSABORT for deadlocks; ABEND for all others

Syntax

REPERROR { 
(
{DEFAULT | DEFAULT2 | SQL_error | user_defined_error},
{ABEND | 
DISCARD | 
EXCEPTION | 
IGNORE |
RETRYOP [MAXRETRIES n] |
TRANSABORT [, MAXRETRIES] [, DELAYSECS n | DELAYCSECS n] |
TRANSDISCARD |
TRANSEXCEPTION
}
{PROCEDURE,[ABEND|IGNORE|DISCARD]}) |
RESET }

Error Specification Options

DEFAULT: Sets a global response to all errors except those for which explicit REPERROR statements are specified.
DEFAULT2: Provides a backup default action when the response for DEFAULT is set to EXCEPTION. Use DEFAULT2 when an exceptions MAP statement is not specified for a MAP statement for which errors are anticipated.
SQL_error: A SQL error number. This can be a record-level error or a commit-level error if using TRANSDISCARD and TRANSEXCEPTION.
user_defined_error: A user-defined error that is specified with the RAISEERROR option of a FILTER clause within a MAP statement.

Error Response Options

ABEND

Rolls back the transaction and terminates processing abnormally. ABEND is the default.

DISCARD

Logs the offending operation to the discard file but continue processing the transaction and subsequent transactions.

EXCEPTION

Handles the operation that causes an error as an exception, but processes error-free operations in the transaction normally. Use this option in conjunction with an exceptions MAP statement or to work with the MAPEXCEPTION option of MAP. For example, you can map columns from failed update statements into a "missing updates" table. In the parameter file, specify the exceptions MAP statement after the MAP statement for which the error is anticipated.

EXCEPTION applies exception handling only to an individual SQL operation on an individual record. To apply exception handling to the entire transaction, use the TRANSEXCEPTION option.

Note:

When the Conflict Detection and Resolution (CDR) feature is active, CDR automatically treats all operations that cause errors as exceptions if an exceptions MAP statement exists for the affected table. In this case, REPERROR with EXCEPTION is not necessary, but you should use REPERROR with other options to handle conflicts that CDR cannot resolve, or for conflicts that you do not want CDR to handle.

IGNORE

Ignores the error.

RETRYOP [MAXRETRIES n]

Retries the offending operation. Use the MAXRETRIES option to control the number of retries. For example, if a table is out of extents, RETRYOP with MAXRETRIES gives you time to add extents so the transaction does not fail. Replicat abends after the specified number of MAXRETRIES.

TRANSABORT [, MAXRETRIES n] [, DELAYSECS n | DELAYCSECS n]

Aborts the transaction and repositions to the beginning of the transaction. This sequence continues either until the record(s) are processed successfully or MAXRETRIES expires. If MAXRETRIES is not set, the TRANSABORT action will loop continuously.

Use one of the DELAY options to delay the retry. DELAYSECS n sets the delay in seconds and the default is 60 seconds. DELAYCSECS n sets the delay in centiseconds.

The TRANSABORT option is useful for handling timeouts and deadlocks on databases that support those conditions.

TRANSDISCARD

Discards the entire source transaction if any operation within that transaction, including the commit operation, causes a Replicat error that is listed in the REPERROR error specification. Replicat aborts the transaction and, if the error occurred on a record, writes that record to the discard file. Replicat then replays the transaction and writes all of the records to the discard file, including the commit record. Replicat abends on errors that are caused by the discard processing.

If the discarded record has already been data-mapped to a target record, Replicat writes it to the discard file in the target format; otherwise, it will be written in source format. The replayed transaction itself is always written in source format.

TRANSDISCARD supports record-level errors as well as commit errors.

Additional information is at the beginning of this topic.

TRANSEXCEPTION

If an error specified with REPERROR occurs on any record in a transaction, performs exceptions mapping for every record in the transaction according to its corresponding exceptions-mapping specification, as defined by a MAPEXCEPTION or EXCEPTIONSONLY clause in an exceptions MAP statement. If any record does not have a corresponding exceptions mapping specification, or if there is an error writing to the exceptions table, Replicat abends with an error message.

When an error is encountered and TRANSEXCEPTION is being used, Replicat aborts the transaction and, if the error occurred on a record, writes that record to the discard file. Replicat replays the transaction and examines the source records to find the exceptions-mapping specifications, and then executes them.

TRANSEXCEPTION supports record-level errors as well as commit errors. To handle errors at the record level (for individual SQL operations), without affecting error-free operations in the same transaction, use the EXCEPTION option in a MAP statement.

PROCEDURE,[ABEND|IGNORE|DISCARD]

Use PROCEDURE to configure behavior of Replicat when a procedural replication error occurs. By default, Replicat will ABEND when a procedural replication error occurs.

The IGNORE option ignores the call that failed. The DISCARD option stages the discarded errors in the apply error queue in the target database. These errors can be re-executed or deleted at a later time.

RESET

Use a REPERROR RESET statement to remove error-handling rules specified in previous REPERROR parameters and apply default error handling to all MAP statements that follow.

Examples of Using REPERROR Globally

These examples show REPERROR as used at the root of the parameter file to set global error-handling rules. You can override any or all of these rules for any given table or tables by using REPERROR in a MAP statement. See "Examples of Using REPERROR Globally and in a MAP Statement".

Example 1

The following example demonstrates how to stop processing for most errors, but ignore duplicate-record errors.

REPERROR (DEFAULT, ABEND)
REPERROR (-1, IGNORE)

Example 2

The following example invokes an exceptions MAP statement created to handle errors on the account table. Errors on the product table cause Replicat to end abnormally because an exceptions MAP statement was not defined.

REPERROR (DEFAULT, EXCEPTION)
REPERROR (DEFAULT2, ABEND)
MAP sales.product, TARGET sales.product;
MAP sales.account, TARGET sales.account;
INSERTALLRECORDS
MAP sales.account, TARGET sales.account_exception,
EXCEPTIONSONLY,
COLMAP (account_no = account_no,
optype = @GETENV ('lasterr', 'optype'),
dberr = @GETENV ('lasterr', 'dberrnum'),
dberrmsg = @GETENV ('lasterr', 'dberrmsg'));

Example 3

The following applies error rules for the first MAP statement and then restores the default of ABEND to the second one.

REPERROR (-1, IGNORE)
MAP sales.product, TARGET sales.product;
REPERROR RESET
MAP sales.account, TARGET sales.account;

Example 4

The following discards the offending record and then replays the entire transaction if any operation on a record within it generates an error 1403. Other error types cause Replicat to abend.

REPERROR DEFAULT ABEND
REPERROR 1403 TRANSDISCARD

Example 5

The following discards the offending record and then replays the entire transaction to search for an exceptions-mapping specification that writes to the exceptions table that is named tgtexception. Other errors cause Replicat to discard the offending record (if applicable) and then abend.

REPERROR DEFAULT ABEND
REPERROR 1403 TRANSEXCEPTION
MAP src, TARGET tgt, &
MAPEXCEPTION (TARGET tgtexception, INSERTALLRECORDS, COLMAP (…) );

Examples of Using REPERROR Globally and in a MAP Statement

The following examples show different ways that REPERROR can be used in a MAP statement in conjunction with a global REPERROR statement.

Example 1

REPLICAT group_name
REPERROR (error1 , response1)
MAP src1, TARGET tgt1, REPERROR (error1, response2);
MAP src2, TARGET tgt2, REPERROR (error2, response3);

In the preceding example, when error1 occurs for the first MAP statement, the action should be response2, not response1, because an override was specified. However, if an error1 occurs for the second MAP statement, the response should be response1, the global response. The response for error2 would be response3, which is MAP-specific.

Example 2

REPLICAT group_name
REPERROR (error1 , response1)
MAP src1, TARGET tgt1, REPERROR (error2, response2),
REPERROR (error3, response3);

In the preceding example, when replicating from src1 to src2, all errors and actions (1-3) should apply, because all REPERROR statements address different errors (there are no MAP-specific overrides).

Example 3

REPLICAT group_name
REPERROR (error1 , response1)
MAP src1, TARGET tgt1, REPERROR (error1, response2);
MAP src2, TARGET tgt2, REPERROR (error2, response3);
REPERROR (error1 , response4)
MAP src2, TARGET tgt2, REPERROR (error3, response3);

In the preceding example, if error1 occurs for the first MAP statement, the action should be response2. For the second one it would be response1 (the global response), and for the third one it would be response4 (because of the second REPERROR statement). A global REPERROR statement applies to all MAP statements that follow it in the parameter file until another REPERROR statement starts new rules.

Example 4

REPERROR DEFAULT ABEND
REPERROR 1403 TRANSDISCARD.
MAP src, TARGET tgt, REPERROR(600 TRANSDISCARD);

In the preceding example, if error 600 is encountered while applying source table src to target table tgt, the whole transaction is written to discard file. Encountering error 1403 also results in the same action based on the global REPERROR specification. On the other errors, the process simply discards only the offending record and then abends.