The “copy table” is a handy tool for sync of target table subject to certain limitations. It uses Oracle Export/Import to sync target table with CONSISTENT option. In certain situations, you run the risk of losing the target table altogether if the command fails after a certain stage.
The failure during the import phase of “copy table” can cause the target table to be lost.
The “copy table” performs a series of steps on the target side before truncating it. After the handshake between sync server and sync client happens and the Post process receives the sync marker, and the parameter files is created, among others, the target table is then truncated (the exact sequence of events is an internal proprietary information and cannot be documented). After this, the Oracle Import would populate the data in the target table. There could be a number of reasons which can cause the Import to fail. Some of the common ones being ORA-01555, ORA-01653: Unable to extend table %s.%s by %s in tablespace %s, database shutdown during import, etc. This can cause the target table to be lost instead of being sync’d. Of course the problem can always be addressed by running another “copy table” but if the source table is very large, the sync may require time, causing inconvenience to the users on target.
Another thing that can be done to avoid this occurrence is to make a copy of the target table using “create table table2 nologging as select * from table1” where table1 is the target table and table2 is its copy that we are trying to make. The NOLOGGING keyword will ensure that the creation of its copy is faster since the creation is not logged in redologs. Though NOLOGGING has the disadvantage of not being able to recover from the failed “create table as…” if it happened, we are not concerned about that since we can re-run the command if it failed. Once the “create table as ….” succeeds, we can then run our “copy table” to sync the target table. If the “copy table” failed, we can re-create the lost table table1 by renaming its copy table2 back to table1. At least we will have the target table to fall back upon for reporting purposes, even if it is out of sync with the source table.
