Ran ora_cleansp on one of the nodes involved in a peer to peer replication environment. How to clean the Post queues and Shareplex internal tables in the corresponding node(s) without impacting replication emanating from the other node(s)?
General information.
In general, if one needs to delete specific queues and not all the queues (for example, when one ran ora_cleansp on the source and the corresponding target has other queues due to the way it is configured, say it is a peer to peer replication environment having Capture, Export and Post queues then the best course of action may be:
a.Surgically remove the undesired queue(s) by deleteq command in qview as on the node as follows:
1. Stop Post and once all the Post processes stop gracefully, shutdown Shareplex.
2. export SP_SYS_VARDIR= your-vardir
3. cd $PRODDIR/bin
./qview
qview> qsetup
qview> deleteq p (one will be prompted to specify the named Post queue that was spawned from the source on which the ora_cleansp was run)
qview> exit
b.Truncate the Shareplex internal table shareplex_trans on the this node. To be able to do this, first stop all the Post. If the Post cannot be stopped cleanly, then there is a chance of encountering ORA-00054 when attempting to truncate the shareplex_trans table:
./sqlplus splex/password
SQL> truncate table SPLEX. SHAREPLEX_TRANS;
SQL> exit
c.Start Shareplex on this node and start Post.
d.Stop Post on the node where ora_cleansp was run and then activate a config. Once the config finishes, start Post.
