How to resync the secondary using Oracle's export/import in a high-availability environment (4330167)

This Solution is in sequel to Solution # 5624 which deals with resync using Oracle Export/Import. The only problem in HA environment is that when the data is imported into secondary using Oracle's Import, Shareplex's Capture process will start gathering changes to secondary database as a reverse config is in existence. This is because Capture will only ignore transactions done by Shareplex user in the database and will otherwise gather all other changes to replicated tables in database by any other user. Since Shareplex Export process is normally kept in a stopped state on secondary, messages sent by Capture on secondary will accumulate in Export queue on secondary. This will interfere with failover process as and when it occurs in future.

Running Oracle Import on secondary database in a high availability environment can result in messages gathered by Capture.

There are two ways of dealing with the above problem:

A. Since your Shareplex Export process on secondary is in stopped state, let the messages accumulate in the Export queue when you perform Oracle Import during the resync process. Once the process is complete, remove the messages so generated using qview as follows:

1) ON secondary
shutdown shareplex
./qview
qview> qsetup <port#>
qview> deleteq C
qview> deleteq X (delete all named Export queues by repeating the command, if they exist. If single Export queue exists, it will simply delete that)
qview> exit

2) ON primary (if Post queue(s) exist, then carry on the steps listed below):

shutdown shareplex
./qview
qview> qsetup <port#>
qview> deleteq P (delete all named Post queues by repeating the command, if they exist. If single Post queue exists, it will simply delete that)

qview> exit


3) ON primary
./sqlplus splex_user/splex_pwd
truncate table shareplex_trans;

You can then start Shareplex on secondary and primary and keep the Shareplex Export process stopped on secondary. The queues will spawn once the failover occurs and changes to replicated tables are gathered by Capture. There is a chance that after failover the Post on new secondary (old primary) may stop due to missing object cache file. You may have to refer to Solution # 2459 to resolve it or contact Support for any help. This is a very simple procedure.

B. You can force the Capture process on secondary to ignore the transactions that occur due to Oracle Import run on it for resync purpose. This will let Capture only gather row chaining info if any, since it needs it during UPDATE DMLs as otherwise the target rows may have out of sync issues later. Other than this, the Capture will not gather any transactions. You can later undo the change so that Capture will be ready to gather transactions, once the failover happens and the secondary becomes the primary database. The following is the procedure:

1. Set the parameter SP_OCT_GEN_STOP as follows:

sp_ctrl> set param SP_OCT_GEN_STOP 0x03

2. Carry out the Oracle Import on target to resync the table. Then unset the parameter as follows:

sp_ctrl> reset param SP_OCT_GEN_STOP

NOTE: As a word of caution, if the parameter is not unset, then Capture will not gather any changes to secondary database and this can cause out of sync problems when this secondary becomes primary during a failover.