It is very easy to resync a table if it is not very large. You can use compare or Oracle Export/Import. The former does not need a downtime whereas the latter may need a downtime lasting a few moments (see solution #SOL5624 for more details on latter). Both these methods will have limitations as listed in the resolution.
Need to sync a very large table
If the table is huge, it may pose a challenge for both compare and Export/Import. The Oracle Export/Import may take too much time and since the target will be unavailable for the duration of Import, it may hinder reporting. It may also stretch the bandwidth. The compare does not need a downtime but the queue size need to small and even with a small queue the compare will fail for a very large table due to resource limitations.
If the table has a dedicated tablespace (used exclusively for that table and no other table), you can use the transportable tablespace feature of Oracle in conjunction with the standard procedure devised by Quest to sync the table with relative ease. The following are the limitations/assumptions:
a. The familiarity with the transportable tablespace feature is a must
b. There is a brief downtime required on source table
c. The target table is unavailable till the whole procedure is implemented but the procedure does not take long
d. The tablespace should only contain the table to be resynced. If it has more than one, then you will have to amend the procedure to resync all such tables.
e. The procedure is for reporting environment only.
The following is the procedure:
1. Make tablespace read only on the source
2. Issue flush from sp_ctrl on the source
sp_ctrl> flush o.<source_sid>
3. Take metadata export. After export is done make tablespace read/write
4. Make sure that the post stopped due to flush
5. Move the datafile to the target
6. Import metadata after post stopped due to flush
7. Make tablespace read/write on target
8. Disable triggers, constraints, jobs, etc
9. Start post.
The following information has been taken from OTN:
Overview of Transportable Tablespace Procedure
The transportable tablespace procedure was introduced in Oracle8i to quickly move data from one database to another. Transportable tablespace is faster than other Oracle data copying methods (e.g. import/export) since the underlying datafiles are just copied to the destination database location and "plugged in" to the destination database using an export dump file of the tablespace metadata.
The steps in transporting a tablespace are as follows:
1. Place tablespace in read-only mode to get a consistent copy of data in the source database.
2. Generate metadata information about the contents of the tablespaces using export (or Data Pump in Oracle Database 10g).
3. Use any desired method to copy the datafiles to the destination database location.
4. If the destination database already contains a tablespace with the same name, then rename or drop it. Typically, for data publishing operations that occur at regular intervals, the old version of the tablespace is dropped from the destination database.
5. Plug in the copied files at the destination database by importing (or by using Data Pump to import) the metadata export dump created at the source database.