SharePlex 11.3 - Administration Guide

Continue to Post When there is a DML Error

SharePlex provides a way for Post to continue processing after it encounters a DML error, rather than stop.

Continue posting on Oracle and SharePlex errors

Valid for Oracle targets

When SharePlex posts to an Oracle target, you can configure Post to ignore specific Oracle DML errors and specific SharePlex error messages and continue processing. Post determines which messages to ignore based on the list in the oramsglist file. The file is installed with a small list of errors by default, but you can remove any of them as desired.

When Posts ignores an error, it writes the error to the SharePlex Event Log. Post also logs the error and the SQL statements that caused the error to the Error log. This log is named SID_errlog.sql log file and is stored in the data directory in the SharePlex variable-data directory. For more information, see View Events and Errors.

Note: There are certain errors that Post will not ignore, and it will stop for those messages even if they are listed in the oramsglist file.

Important: Use caution when using this feature. It could result in hidden out-of-sync conditions. Enable this parameter only if your target users cannot tolerate replication lag and it is acceptable to have some out-of-sync data. Check the SID_errlog.sql log frequently to see if there were errors that could cause replication problems.

To configure Post to continue on errors:

On the target system, change directories to the data sub-directory of the SharePlex variable-data directory.
Find the oramsglist file.
If replication is not active, open the file in a text editor. If replication is active, make a copy of the file and then open the copy in the editor.
Increase the number on the first line by the number of errors that you are adding. This number must be equal to the total number of errors that are in the file. For example, in the following file there are 10 errors listed.
```
ora@sys1dad > vi oramsglist
10
604
900
902
908
909
910
911
932
960
1026
```
Starting at the end of the file, add the number of each Oracle or SharePlex error, one per line as shown in the preceding example. The messages need not be in numerical order.
Save and close the file.
Stop Post (if running).

sp_ctrl> stop post
If you edited a copy of the oramsglist file, save the copy to the original name of oramsglist.
Change the value of the SP_OPO_CONT_ON_ERR parameter to 1. Or change the value to 2 to also continue posting on table errors listed in the oramsglist file. See the SharePlex Reference Guide for a description of the SP_OPO_CONT_ON_ERR parameter.

sp_ctrl> set param SP_OPO_CONT_ON_ERR 1
Start Post.

sp_ctrl> start post

Continue posting on ODBC errors

Valid for Open Target

When SharePlex posts to an Open Target target, you can configure Post to ignore ODBC errors and continue processing. Post writes the error to the SharePlex Event Log. Post also logs the error and the SQL statements that caused the error to the Error log. This log is named ID_errlog.sql log file, where ID is the database identifier. The file is stored in the data directory in the SharePlex variable-data directory. For more information, see View Events and Errors.

To configure Post to continue on errors:

On the target system, change directories to the data sub-directory of the SharePlex variable-data directory.
Look for one of the following files, depending on the database. These files are installed empty.

File name Supported database

postgresmsglist
Postgres

sqlservermsglist Microsoft SQL Server

mysqlmsglist Oracle MySQL

Note: There are certain errors for which Post will stop, even if you list those errors in the message file.
If replication is not active, open the file in a text editor. If replication is active, make a copy of the file and then open the copy in the editor.
Starting at the end of the file, add the number of each error, one per line as shown in the example. The messages need not be in numerical order.

Example:

sqlservermsglist:

8102

8180

544

2627

3621
Save and close the file.
Stop Post (if running).

sp_ctrl> stop post
If you copied the original file, save it back to its original name.
Change the value of the SP_OPX_CONT_ON_ERR parameter to 1.

sp_ctrl> set param SP_OPX_CONT_ON_ERR 1
Start Post.

sp_ctrl> start post

Continue to Post on DDL errors

Continue to Post When there is a DDL Error

File name	Supported database
postgresmsglist	Postgres
sqlservermsglist	Microsoft SQL Server
mysqlmsglist	Oracle MySQL

By default, Post stops on DDL errors. An error usually indicates that the database component for which the DDL was executed on the source system does not exist in the target database. The default setting of the SP_OPO_STOP_ON_DDL_ERR parameter stops Post on DDL errors, so that subsequent DML on that object does not fail. This enables you to correct the problem and keep the databases synchronized. For more information about this parameter, see theSharePlex Reference Guide.

Increase the number of retries on error

Post will retry certain failed operations when there is the possibility that they will succeed with another attempt. The main operations that Post will retry are TNS write failures, connection failures, or locks on tables when Post needs to apply a TRUNCATE.

To increase the likelihood that the failed operations are successful, you can increase the SP_OPO_RETRIES_MAX parameter so that Post tries the operation more times. At the same time, increase the SP_OPO_RETRY_DELAY_TIME parameter to increase the time interval between the attempts. That gives the lock or other blocking operation enough time to be resolved between attempts.

If the Post process is set to continue on error (SP_SYS_SUSPEND_ON_ERROR=0) or if the error message is listed in the oramsglist file, Post moves on to the next transaction in the queue. In all other cases, Post stops after it reaches the maximum allowed attempts.

Important: Reducing this parameter can cause the data to accumulate in the queues, possibly causing them to exceed the available disk space.

For more information, see SP_OPO_RETRIES_MAX in the SharePlex Reference Guide.

Handle transactions that contain out-of-sync operations

You can configure Post to handle out-of-sync conditions. The following explains the default and alternate behaviors.

Default Post handling of out-of-sync errors

The default Post behavior when a transaction contains an out-of-sync operation is to continue processing other valid operations in the transaction to minimize latency and keep targets as current as possible. Latency is the amount of time between when a source transaction occurs and when it is applied to the target. Different factors affect the amount of latency in replication, such as unusually high transaction volumes or interruptions to network traffic.

Post logs the SQL statement and data for the out-of-sync operation to the ID_errlog.sql log file, where ID is the database identifier. This file is in the log sub-directory of the variable-data directory on the target system.

Stop on out-of-sync errors

You can configure Post to stop when it encounters an out-of-sync condition by setting the following parameter to 1:

Oracle targets: SP_OPO_OUT_OF_SYNC_SUSPEND
Open Target targets: SP_OPX_OUT_OF_SYNC_SUSPEND

If you use this feature, make certain to monitor replication frequently. If Post stops, latency increases and data accumulates in the queues. For more information, see the parameter documentation in theSharePlex Reference Guide.

Roll back the transaction if it generates out-of-sync errors

You can configure Post to roll back and discard a transaction if any operation in that transaction generates an out-of-sync error. The entire transaction is logged to a SQL file, but not applied to the target.You can edit the SQL file to fix the invalid DML and then run the SQL file to apply the transaction. This feature is enabled by setting the SP_OPO_SAVE_OOS_TRANSACTION to 1.

For more information, see the parameter documentation in theSharePlex Reference Guide.

제품을 선택하십시오.

더 나은 서비스 제공을 위해 채팅 목적을 작성해 주십시오.

문제에 대한 권장 솔루션