The following errors are observed in event_log and the Import process remains in idle state when the Post queue is reset to resolve issues like queue corruption:
sp_ctrl (orapro:2100)> show log reverse
Info 2016-02-02 01:26:23.573203 30103 2086283104 Import launched, pid = 30103 (importing from server_name queue queue_name)
Info 2016-02-02 01:26:23.245315 28874 2086283104 Import exited with code=1, pid = 30099 (importing from server_name queue queue_name)
Error 2016-02-02 01:26:23.240701 30099 2830968672 Import: sp_mport: failure writing to queues - exiting (importing from server_name queue queue_name) [module imp]
Notice 2016-02-02 01:26:23.240214 30099 2830968672 Import: Error que_BAD_SQUEID: Invalid subqueue id detected que_write(queue_name+P+o.SID1-o.SID2) (importing from server_name queue queue_name) [module rim]
Notice 2016-02-02 01:26:23.239603 30099 2830968672 Import: Cannot write to que queue_name+P+o.SID1-o.SID2 (51484450) with subqueue greater than 0 - 5 (importing from server_name queue queue_name) [module que]
Info 2016-02-02 01:26:23.191212 30099 2830968672 Import connected to export on server_name
Info 2016-02-02 01:26:23.080410 30099 2086283104 Import launched, pid = 30099 (importing from server_name queue queue_name)
Since the Post queues have already been cleared by the reset command, there is no data in them. The structure of the queue is also corrupted. This is resulting in the Import process showing errors and preventing Export from sending data to target. To resolve the problem:
1. Shutdown Shareplex on target
2. Run deleteq command in qview as shown below (shown with actual server and SID names for sake of clarity):
$ qview -i (where $ denotes OS prompt and the command is invoked from /proddir/bin directory of Shareplex)
qview> qsetup
qview> list
The following queues exist:
alvsupu07+P+o.syam-o.ORA11GR2
subqueues range from 0 to 9
WRITER +PI+alvsupu07+sp_mport+0x0a010221 (10.1.2.33)
READER +PP+alvsupu07+sp_opst_mt+o.syam-o.ORA11GR2
qview> deleteq p
qview> exit
3. Login to target database as a DBA user and truncate the shareplex_trans table owned by SharePlex schema
4. Restart SharePlex.
This should get rid of queue structures as well as queue data and allow replication to resume.
