How to resync a table, and not the complete database, using Oracle's Export/Import utility, when compare/repair is not a viable option.
Though compare/repair can be used, it may have the following limitations:
1. No support for LOBs.
2. Compare has to be run during quiet periods, as heavy backlogging of queues can compromise it.
3. If the tables are large, compare may take a long time (unless it is feasible to use a WHERE clause),
and this may clash with the requirement that it be run during quiet periods.
Oracle's export/import utility can be used conveniently to resync a Target table while users can keep accessing the Source table.
The only limitation is that users cannot access the Target table for the duration of this procedure, but can continue to access the Source table.
The procedure is:
1. Briefly X-Lock the Source table.
sql> lock table <tablename> in exclusive mode;
2. Issue a Flush command on the SharePlex Source to stop Post on the Target.
On Source:
sp_ctrl> flush o.<sourcesid>
Replace the <sourcesid> with the SID of the Source database.
If there are multiple Post queues, then the queue name can be given with the flush command, so that it stops only one named Post queue.
sp_ctrl> flush o.<sourcesid> queue <queue_name>
3. Begin an Export of the Source table with CONSISTENT=Y
4. Quickly release the X-Lock (by issuing a rollback or commit).
5. After the Export is finished on the Source, and after Post stops due to flush, compare the Source and Target tables to ensure no DDL changes have occurred.
6. Truncate the Target table.
7. Import the data into the Target table.
8. Disable any triggers or constraints (except PK or UI type).
9. Start Post on Target.
If locking the Source table is not possible, steps 1 and 4 can be skipped, but there may be an out-of-sync introduced if a transaction happened on the Source table between the time steps 2 and 3 were executed. For this reason, it will be useful to have multiple windows open with commands already typed in and execute them in a co-ordinated way.
Here is the way to do it:
Specifically, have a window open for step 1, another one open for step 2, and yet a third one open for step 3.
Now, hit <enter> to execute the SQL in the window for step 1, and once the locking succeeds, quickly hit <enter> to execute the sp_ctrl command flush in the window for step 2, followed by hitting <enter> to execute the launch of Export in the window for step 3.
Then, as soon as possible, go to the SQL window for step 4 (the same window that was used to execute step 1) and enter ROLLBACK or COMMIT on the SQL prompt and hit <enter>.
The lock on the table will then be released. These orchestrated steps will ensure that the downtime is minimised on the Source table.
If the table is very busy, it may take a few attempts to execute step 1.
