The following error are observed when attempting to delete a queue using the command “delete queue <queue_name>:
sp_ctrl (alvsupu16:5438)> delete post queue queue1 for o.ORA11GR2
cnc_delete_queue() Queue error - user/queue was not deleted; Check event_log for details.
The event log shows the following error:
sp_ctrl (alvsupu16:5438)> show log reverse
Error 2017-05-11 16:54:41.482319 22633 1 Cop: delete queue failed: que_INUSE: Que is already open [module cop]
Notice 2017-05-11 16:54:41.481355 28267 1 User command: oracle delete queue queue1:P for o.ORA11GR2-o.ORA11GR2 (from alvsupu16)
It is imperative that the associated processes be stopped prior to running the “delete queue” command. Otherwise SharePlex will fail to delete the queue specified for deletion. The reason is, the queue is still being accessed by associated processes.
Here are the guidelines that specify the associated processes that need to be stopped prior to running a “delete queue” command:
The delete queue command removes a queue, including any associated subqueues. The process that writes to that queue rebuilds it when replication resumes, replacing the data that was in the queue but not yet processed.
Before using the delete queue command, stop the processes that writes to the queue and those that read it.
• To delete a capture queue, stop Capture and Read processes.
• To delete an export queue, stop Read and Export processes.
• To delete a post queue, stop Import and Post processes.
After deleting a queue, delete all subsequent queues (associated with the deleted queue) downstream which get their data feed from the deleted queue:
• After deleting a capture queue, delete the export queue(s), then the post queue(s).
• After deleting an export queue, delete the corresponding post queue(s).
• After deleting a post queue, no other action is required.
When deleting a Post queue, it is preferable to use the cleartrans option as this would result in the entries in the SharePlex internal table SHAREPLEX_TRANS for the Post queue in question to be deleted. Otherwise, after deleting the queues, stop all the Post queues cleanly, TRUNCATE the SHAREPLEX_TRANS table in the SharePlex schema in the target database and restart all the Post queues. To ascertain if the Post stopped cleanly when issuing “stop post”, issue “status” and wait till the Post is “stopped by user” and if it is “stopping”, then wait till it is stopped by user.
Not truncating the SHAREPLEX_TRANS table after deleting a Post queue or not running the “delete queue” command for Post with cleartrans option would cause SharePlex to undertake a recovery process and generate recovery notices in the Event Log. SharePlex is designed to recover from a system failure and will interpret the queue deletions as such. When you delete a queue but do not delete the associated queues downstream, SharePlex will discard any newly generated DML/transaction and the processes downstream will go into recovery. This will result in data loss. So the omission of deletion of queues downstream or not truncating the SHAREPLEX_TRANS table will result in data loss. Though it is possible to henceforth prevent any further data loss by taking corrective measures of deleting the downstream queues and truncating the SHAREPLEX_TRANS table, the data loss that already occurred due to such omission will need to be fixed later.