While replicating 9i Supported DDL, the following errors are observed on target Shareplex due to Oracle error ORA-01418:
sp_ctrl> show log reverse
Info 2009-04-22 16:05:32.173902 2685 1 Poster exited with code=1, pid = 6420
(posting from SID1, queue Queue_name, to SID2)
Error 2009-04-22 16:05:32.142507 6420 4 Poster: Poster exit on 9i ddl error due to ORA-01418: specified index does not exist. on drop index OWNER.INDEX_NAME (posting from SID1, queue Queue_name, to SID2) [module opo]
Notice 2009-04-22 16:05:32.142154 6420 4 Poster: ORA-01418: specified index does not exist. (posting from SID1, queue Queue_name, to SID2) [module osp]
The index specified in the SQL which Post is trying to apply does not exist on the target.
The error is typical when replicating 9i Supported DDL (by configuring the parameter SP_OCT_REPLICATE_ALL_DDL to 1 on source). Most likely the index has been dropped on source and is expected of Shareplex to drop it on target. But the specified index does not exist on target. So Shareplex's Post process cannot drop it. This is interfering with actions of Post. It is possible that the index may have been earlier created on target as a part of 9i Supported DDL enabled on source but may have been dropped subsequently on target though the source index still existed. Now, when the source index is dropped, Shareplex tries to drop the index on target but fails. See Solution SOL52606 for details on factors that can cause the failure of DDL on target when 9i Supported DDL is enabled.
The workarounds are:
A. On target ignore the DDL by configuring the parameter SP_OPO_STOP_ON_DDL_ERR to 0 for the time being and restart Post. From this point on all DDL errors will be ignored by Post. Once the Post is past the problematic DDL, the paremeter can be reset to the default of 1 by the sp_ctrl command "reset param <parameter name>". Keeping it enabled indefinitely is not a good idea. See SOL21396 for details. In brief:
sp_ctrl>set param SP_OPO_STOP_ON_DDL_ERR 0
To enable it back
sp_ctrl>reset param SP_OPO_STOP_ON_DDL_ERR
B. You can also configure the parameter SP_OPO_CONT_ON_ERR to ignore the Oracle error ORA-01418. See SOL277 for details.
C. If it was not an intent to replicate 9i Supported DDL (typically the DDL on non-replicating tables), then it may be worth disabling the feature by setting the parameter SP_OCT_REPLICATE_ALL_DDL to 0 on source. To do this:
sp_ctrl>reset param SP_OCT_REPLICATE_ALL_DDL (the parameter is live and will take place immediately).
See SOL36703 for details on 9i Supported DDL, and SOL53602 for details on pros and cons of using this feature.
The following are details on the Oracle error encountered:
Error: ORA-01418: specified index does not exist
Cause: You have referenced an index that does not exist in an ALTER INDEX, DROP INDEX, or VALIDATE INDEX command.
Action: The options to resolve this Oracle error are:
1. Make sure that your index exists before referencing it in an ALTER INDEX, DROP INDEX, or VALIDATE INDEX command.
You can list all of the indexes that exist by querying the ALL_INDEXES Oracle system table:
select * from ALL_INDEXES;
