Procedure to drop and recreate a SEQUENCE in Replication (4258827)

If a sequence is in replication, to drop and recreate it involves certain steps. Basically any replicating object that needs to be dropped and recreated needs to be taken out of replication prior to making changes to it and then brought back in after making the changes.

Replicating sequences may be dropped and recreated for a number of reasons.

Steps involved in dropping and recreating a SEQUENCE that is  replicating :

1. Before you drop the sequence you need to copy the currently active config to a new filename and remove the line(s) for the sequence(s) that you want to drop.

2. Activate the new config with the sequence(s) removed. These sequences will be removed from the currently active config.

3. Issue a flush on source sp_ctrl as follows:

sp_ctrl>flush o.SID (Where SID is the Oracle SID on source)

The flush will send a token message via Shareplex queues to the target and Post will stop once it reads this message. This will serve as a reference point after which there are no messages pertaining to the sequence(s) in the queue.

4. Drop / recreate or alter your sequence(s). If you change the name or owner of any of the sequence(s) you should modify the config file to reflect this. You should verify that the source and target sequence(s) are in sync. You can modify the target sequence(s) anytime after the Post is stopped due to flush.

5. After you have made all your changes to the sequence(s) you can activate the original config and you will again replicate the sequence(s).

6. Check if sequence between the source and target is in sync and refer to solution 16800 to sync up sequence.

7 Start Post that is stopped due to flush.