A convenient way to get rid of data in Shareplex queues without losing existing activation by using the “purge config” option. (4300610)

Shareplex provides a wide range of options to clean up the queues or get rid of existing replication. These actions are necessary for a variety of reasons ranging from testing replication in a QA environment to dealing with replication issues in a production environment. Each of the tools has its own advantages/disadvantages. The solution delves primarily on the command to purge the config but also explores other options such as to abort or deactivate the config and explains the subtle difference between these options. It briefly delves on other options like deleting queues as an alternative. The solution tries to explain all this by means of case studies. For details on these commands, one can refer to the Reference Guide.

N.A.

If one needs to get rid of Shareplex queues, then there are a wide range of options available and their suitability depends on certain factors as discussed below. It may also be noted that for certain tasks more than one of these options may work and the selection of a particular option may be more of an individual preference. Here are some scenarios:

A. My Shareplex is running and the queues are backlogged due to some error conditions or because of heavy activity on source. I do not wish to wait for the messages post to the target. I am not concerned about the resulting out of sync as I can later sync up the target tables. I do not wish to incur a downtime on the source database. What options may I have?

Answer: If all the Shareplex processes are running, the command purge config may be the most suitable for this case. The moment this command is issued, Shareplex will get rid of the contents of the queues starting from Capture and down to Post. If any of the processes is stopped for any reason (by user or otherwise), Shareplex will wait for the stopped process(es) to be started and will resume its actions once the process(es) is/are started. After the command finishes, there will still be an active config and there will be queue structures but no data in them. The target needs to be resyncd after this. If, for any reason, the error conditions cannot be addressed, then another option will be to shutdown Shareplex, reset or delete the queues, truncate the Shareplex internal table shareplex_trans and restart Shareplex and resync the target. See Solution # SOL540 for details. However, this may also result in another Post error about missing object cache so one may need to address that as well. See Solution # SOL2459 for reference. The options abort config or deactivate config may not be suitable in this case since they render the current config in an inactive state and this requires activation and sync of the target. As one may observe, the purge config does away with having to use the qview utility to get rid of the queues and for this reason, it is very convenient, so long as there are no errors with any of the Shareplex processes.

B. My Shareplex is running but I wish to discontinue with the current replication once the existing data has been posted to the target. I do not care about the need to have database downtime when activating another config.


Answer: In this scenario the best option may be deactivate config. This will allow the current data in the queue to be posted but no other changes from source will be captured once the command is issued. This way the configuration is terminated gracefully. The success of this command will depend on there being no error conditions existing for any Shareplex processes. If there are any error conditions, then they will need to be resolved first. Otherwise one may have to shutdown Shareplex, reset or delete the queues, truncate the Shareplex internal table shareplex_trans and restart Shareplex. This will result in target not receiving all the changes from source and consequent out of sync.

C. My Shareplex is running but I wish to immediately stop it and get rid of the queue and other variable directory structures. I do not care about the resulting out of sync, or the need to have database downtime when activating another config.

Answer: In this scenario the best option may be abort config. Again, its success will depend on whether there are any stopped Shareplex processes due to error or otherwise, and if so, they need to be addressed first. Otherwise one may have to shutdown Shareplex, run ora_cleansp on both source and target to clean the queue structures and the content of Shareplex internal tables in source and target database. See Shareplex Admin Guide for instructions on running ora_cleansp.

One thing to note is that none of the options, purge, deactivate or abort, will work if there are issues with any of the Shareplex processes like Capture, Read, Export, Import, or Post. In that case the only viable option may be to reset the queues or run ora_cleansp depending on whether one wishes to do another activation after the clean up.

The Reference Guide does delve on these commands at different sections since the commands are located in alphabetical order. This solution may help the user understand the commands as they relate to each other as well as some alternatives to these commands.