At times compare runs into Oracle errors on source and/or target. There is a DEBUG parameter named SP_DEQ_IGNORE_ORACLE_ERROR_NUM that is used for troubleshooting the issue. This solution simply delves on what the parameter can and cannot do to help in fixing the underlying Oracle error as well as the out of sync.
General information.
Compare can encounter many types of errors, including, but not limited to, network errors, SharePlex processes down, compare timeout, Oracle errors, etc. The parameter SP_DEQ_IGNORE_ORACLE_ERROR_NUM can be used to in conjunction with another compare parameter SP_DEQ_MALLOC to troubleshoot certain types of Oracle errors encountered and identify the rowid that caused it or was impacted by it. When running compare this way, the repair option should not be used as the parameter is used for diagnostics only. The following is the way to run compare and debug the Oracle error:
1. On source and target:
sp_ctrl> set param SP_DEQ_MALLOC 50
sp_ctrl> set param SP_DEQ_IGNORE_ORACLE_ERROR_NUM nnnn (where nnnn represents the 3, 4 or 5 digit Oracle error, for example, 1403 represents ORA-01403)
2. Issue the compare command without repair option.
3. Once it finishes, unset the two parameters with:
sp_ctrl> reset param SP_DEQ_MALLOC
sp_ctrl> reset param SP_DEQ_IGNORE_ORACLE_ERROR_NUM
4. Examine the *desvr* (compare log from source) and *declt* (compare log from target) for the rowid that is causing the Oracle error. The rowid shown can be from the source or target database depending on what row is causing issues with compare, and is logged in the desvr or declt log respectively. The rows on source and target can be examined and corrective action taken. The entry in the compare logs for problem rowid may look like this (the entry shows ORA-01821 but there can be other Oracle errors):
DEBUG: Oracle error 1821 detected & ignored, rowid= AACN2MAMSAAAhI8AAm, rows fetched=1
Then fix the Oracle error (in the above example 1821 is error ORA-01821) and re-run compare with repair option or sync the table using other means.
One thing to note is that SP_DEQ_MALLOC must be set to 50 to isolate the row that is causing the issue. This small batch size together with extra logging in compare logs can cause compare to run very slow. To overcome this, it is advisable to run compare with where clause to reduce the number of rows compared.
In brief, the parameter SP_DEQ_IGNORE_ORACLE_ERROR_NUM by itself will not let compare run on other rows and ignore this row for synchronizing the target table. Rather, it will help identify the rowid and error number that is causing the Oracle error during compare. The resolution would be to fix the data on source or target (if that was the cause of this error) and re-run compare. SOL23926 delves on running compare on a specific row and may be useful in our situation. Another thing to note is that not all Oracle errors can be fixed this way, an example would be tablespace running out of space. Finally, the logs can be referred to Support for further analysis if they are not helpful in fixing out of sync.
