The delete queue command results in error “Cop: delete queue failed: que_INUSE: Que is already open (4302266)

The delete queue command results in error “Cop: delete queue failed: que_INUSE: Que is already open

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)

The process/processes associated with the queue in question are still running.

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. 

Starting with SharePlex 9 and up, the command “delete queue” has been grossly simplified. In fact it worked in earlier versions with a cumbersome syntax but was more of an “internal” command in recent versions and was, therefore, not documented. This has changed with SharePlex 9. Since the command is documented in Release Notes and in Reference Guide, it is imperative that users know how to use the command correctly since they now have access to the syntax. Moreover, using the command results in loss of data (which is an expected outcome when using “delete queue”) but if the steps are not followed correctly which include deleting queues downstream and truncating SHAREPLEX_TRANS table, this would result in additional data loss. This will increase workload as far as resync of the affected objects is concerned. For syntax of "delete queue" commands for Capture, Export and Post queues, please refer to SharePlex Reference Guide.