How to cleanup the target without using ora_cleansp. (48463)

Normally, if you run ora_cleansp on source, you need to do the same on the target before you can start afresh and activate a config on source. If you ran ora_cleansp on a source involved in a centralized replication environment, then cleaning up the remnants of the Post queue structure on the target would require special consideration, depending on whether you want to salvage the Post queues on the target which are not spawned because of this source. You will not be able to run clean_vardir.sh and "truncate table shareplex_trans" in lieu of ora_cleansp on the target.

Ran ora_cleansp on source, now need to clean the corresponding Post queues and Shareplex internal table on the target in a centralized replication environment.

In general, if you need to delete specific queues and not all the queues (for example, when you ran ora_cleansp on the source and the corresponding target has numerous named queues due to the way it is configured, say it is a centralized replication environment, then the best course of action may be:

a. Surgically remove the undesired queue(s) by deleteq command in qview as on target as follows:

Target:

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  (you 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 target. To be able to do this, we first stopped 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.

Target database:

./sqlplus splex/password
SQL> truncate table SPLEX. SHAREPLEX_TRANS;
SQL> exit

c. Start Shareplex on target and start Post.

Another option may be to run ora_cleansp on all the remaining sources and run ora_cleansp on the target. But this would require downtime on the sources that need to be set up to replicate to this target.