Pros and cons of copy and compare for the purpose of synchronizing a target table. (4300585)

A new command named copy has been introduced starting with Shareplex 6.0. The command essentially uses Oracle Export/Import with CONSISTENT option. This opens up new avenues for resync of target tables. The command is documented in Shareplex Admin Guide and this solution simply delves on the pros and cons of copy vis-Ã -vis compare.

N.A.

Advantages of copy over compare:

1. It can handle very large tables, something that may not work with compare unless you are using a "where" clause. Note that it is not always feasible to formulate a "where" clause.
2. It can sync tables that do not form part of the replication, though a brief downtime on source table may still be required.
3. It can be used to instantiate a table on target with the help of source table.
4. It may work faster than compare if the target table is grossly out of sync.
5. It can be conveniently used for migration.
6. Data can be sent to target in compressed form.
7. Can be used for tables with certain datatype not supported by compare, e.g., NCHAR, NVARCHAR2.

Advantages of compare over copy:

1. Virtually no downtime is required on source table. The locking is very brief and there is retry logic in compare that will allow it to keep trying, in case the source table is very busy. The copy command may need a brief downtime before the Oracle Export is launched.
2. Compare will work for sync of tables from higher to lower version of Oracle (though this type of replication is not recommended).
3. Compare will work even if the target and source table structures are not identical, e.g., in case of Shareplex's vertical partitioning.
4. If feasible, the "where" clause in compare can drastically reduce the # of rows to be compared, consequently reducing the time needed to sync the target table.
5. Compare can use hints to improve the efficiency.
6. It is possible to first analyze the out of sync by using a compare without any "repair" option. This can greatly aid in investigation of out of sync before attempting to repair the target table.
7. One can run compare to sync up a specific row with the help of rowid information found in the *errlog.sql file. (see Solution # SOL23926 for further details). This is very useful if the table in question is very large and the rows to be sync'd up are few.
8. One can use the "key-repair" option to conveniently sync in certain situations where the comparison is done on key columns only and repairs performed.