In an environment where numerous named Post queues are present, it can be a nightmare to single out Post queues that are not valid anymore. Such queues typically come into existence when the tables using dedicated Post queues are removed from replication or are routed thru another named Post queue. These redundant queues also take up valuable shared memory and disk resources.
To detect such queues would require examining the active config file on the source and looking for Post queue names in it and comparing those Post queue names to the actual Post queues existing on the target to see if one is present. For any queue on the target that does not have a corresponding queue name in the active config file, that queue on the target can be removed. In case of numerous Post queues, this exercise can be stressful and prone to human error and corresponding loss of data if the user decides to remove unwanted Post queues on the basis of such investigation.
Luckily SharePlex offers a very convenient way to determine this info. You can issue a “flush” command from source to achieve this. Here are the steps
1. Issue “flush” from source sp_ctrl:
sp_ctrl>flush o.SID (where SID refers to the SID of the source Oracle instance)
The command will put a marker message in the source database and Capture process will then Capture that message and sent it to target via all the active queues. When the Post queues on target process this message, the queues are stopped. You can issue “status” on target sp_ctrl and you will see “stopped due to flush” for all the valid Post queues that are currently getting replicated data from source. Any queues that are still running and are not “stopped due to flush” are not valid at this time and can be removed
2. Once you have identified such unwanted queues, you may want to start Post on target sp_ctrl so that replication can resume:
sp_ctrl>start post
In fact the “flush” command is one of the easiest ways to test a replication. If a replication is running fine, any flush which is issued from source will result in all the Post queues stopped due to flush. We have used this functionality of flush command to determine unwanted queues in our situation. There are many other uses of flush command.
Please be aware that if the Post queues were heavily backlogged when the flush was issued on source, it can take a while for the flush message to reach Post process. This may necessitate monitoring of Post process until it stops due to flush so as to start it without much delay. For this reason it is best to carry out this activity when the queues are not unduly backlogged.
