-
Title
Post stopped due to error "Notice: ORA-08102: index key not found" -
Description
You see the following error in the event log indicating the problem with one index:
06/17/07 04:33 Process exited sp_opst (for o.ep01-o.rp02 queue ProbQ) [pid = 27733] - exit(1)
06/17/07 04:33 Error: Unexpected Oracle error: ORA-08102: index key not found, obj# 1099412, dba 92342200 (2). (object name: "BOM"."CST_ITEM_COSTS") [sp_opst (for o.ep01-o.rp02 queue ProbQ)/27733]
06/17/07 04:33 Notice: ORA-08102: index key not found, obj# 1099412, dba 92342200 (2). [sp_opst(osp)/27733]
06/17/07 04:31 Notice: Post will not open more than 1990 cursors (OPEN_CURSORS - 10). [sp_opst (for o.ep01-o.rp02 queue ProbQ)/27733]
06/17/07 04:31 Notice: Oracle version 81 [sp_opst(osp)/27733]
06/17/07 04:31 Notice: Oracle env - rp02:/rp02_apps/orarp02/product/8174 [sp_opst(pdb)/27733]
06/17/07 04:31 Process launched: sp_opst (for o.ep01-o.rp02 queue ProbQ) [pid = 27733] -
Cause
Corrupt index causing Post to stop due to error
-
Resolution
Here are three workarounds explained:
WORKAROUND 1:
Disable posting on a specific object (as taken from solution # SOL6679)
You can set the following parameter to cause post to discard messages routed to a specific objectid (where objectid is from source table):
sp_ctrl> stop post
sp_ctrl> set param SP_OPO_DISABLE_OBJECT_NUM objectid (replace objectid with the actual objectid of the table)
sp_ctrl> start post
When you want to restore the posting on the specified object, do the following:
sp_ctrl> stop post
sp_ctrl> set param SP_OPO_DISABLE_OBJECT_NUM 0
sp_ctrl> start post
The following is the information on the above parameter:
SP_OPO_DISABLE_OBJECT_NUM
This parameter prevents Post from posting replicated DML and DDL operations for a table, based on the source table's Oracle object ID. You can set this parameter if a source table's data is invalid or corrupted, or for any other reason that you do not want operations for the table to be reflected in the target database(s). Setting this parameter prevents further replication activity on the target table until you have time to resynchronize the data and reactivate the configuration file. The Post process discards all replicated messages for this table from the post queue, and the messages do not accumulate in the queue.
Use the parameter with caution. If it is enabled and DDL or DML is executed for the source table, the target data and the SharePlex object cache will be out of date because the changes are not posted. If there are dependencies on the table, such as a foreign key in other tables outside the replication configuration, disabling posting will prevent the dependencies from being satisfied.
WORKAROUND 2:
Instruct Post to ignore the Oracle error ORA-01802 by configuring the oramsglist file as follows (as taken from solution # SOL16270 and also detailed in Admin Guide):
1. Go to vardir/data/ directory
2. Edit the oramsglist file
3. Increment the error count (number) on the top of the file by one ( equals to total number of lines in the file minus 1 )and add a new line with the error 1802 at the bottom of the line. For example, if the current number stored in the first line is 21, make it 22.
4. Save the file and exit
5. In sp_ctrl > set param sp_opo_cont_on_err 1
6. start post in sp_ctrl> start post
From now on the Oracle error ORA-01802 will be ignored. Please be aware that this will be done for any table that encounters ORA-01802 so you may not want to take this route if this is undesirable. Once post is past the error the above can be unset:
1. sp_ctrl> stop post
2.sp_ctrl> status (Make sure post is stopped by user)
3. Edit the file oramsglist in the vardir/data directory, decrease the number in first line by 1, remove the message number 1802 from the file and then save the file.
4. from sp_ctrl> set param SP_OPO_CONT_ON_ERR 0
5. sp_ctrl> start post
The result of the above workaround will be that the table will go out of sync and will need to be synced in future.
WORKAROUND 3:
Stop post and rebuild the corrupted index.