How to resync a table using Oracle's Export/Import (4297174)

How to resync a table and not the complete database using Oracles Export/Import where 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 lean periods as heavy backlogging of queues can fail it.
3. If the tables are large, compare may take a long time (unless it is feasible to use WHERE clause) and this may clash with the requirement that it be run during lean periods.

Oracles export/import can be used conveniently to resync target table while users can keep accessing 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 Flush from Source to stop post on 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 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 on the Target( sp_ctrl>show should show that the queue is stopped due to flush), compare the Source table to the Target table to make sure both are the same structure (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 U type)
9. Start post on target.

If locking the source table is not at all possible even for a brief instant, 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 command already typed in and execute them in a co-ordinated way.

Here is the way to do it:

If locking the source table is not at all possible even for a brief instant, one can skip steps 1 and 4 but there may be an out of sync introduced if a transaction happened on the source table between the time one executed steps 2 and 3. For this reason, one will find it useful to have multiple windows open with command already typed in and waiting for executing them in a co-ordinated way. Specifically, have a window open for step 1, another one open for step 2, and yet a third one open for the step 3. Now, hit <enter> to execute the SQL on window for step 1, and once the locking succeeds, quickly hit <enter> to execute the sp_ctrl command flush on window for step 2, followed by hitting <enter> to execute the launch of Export on window for step 3. Then as soon as possible,go to the SQL window for step 4 (the same window was used to execute step 1) and enter ROLLBACK or COMMIT on sql prompt and hit <enter>. The lock on the table will then be released. The orchestrated steps will ensure that the downtime is minimum on the source table. If the table is very busy, it may take a few tries to execute the step 1.

One thing that needs to watch for is the rollback/undo segment issues since the CONSISTENT flag in Export is used. One may run into ORA-01555 and its occurrence depends on the size of the table being exported and the quantum of changes taking place while the Export is in progress.