Using "copy table" or "append table" command in a peer-to-peer environment (4330538)

Starting with Shareplex 6.0 and up, the "copy table" has been introduced to sync tables. It uses Oracle's Export/Import utility and has a number of options available which includes synchronizing tables not involved in replication. This solution delves on the limitations of the copy command when synchronizing object(s) in a bi-directional replication environment. The solution also applies to the "append table" command.

The "copy table" command may not always be suitable in a peer-to-peer environment. There are two issues that may occur when running this command in such environment. First, the series of steps occurring during sync may result in the Capture in secondary side gathering the DMLs that were executed to sync the table on secondary node by the Oracle Import process. Secondly, the command may fail if the object is currently locked in EXCLUSIVE MODE on the secondary node.

Running "copy table" in a peer-to-peer environment may run into issues because of the way the command operates and may need special considerations.

The following is the resolution to the problems mentioned above:

A. The problem of locking:

The problem shows up in "copy status" output on primary sp_ctrl and in the copy logs, after the "copy table" has been issued from the primary:

sp_ctrl (source_servername:port_num)> copy status

Job ID   : 14
Host     : source_servername
Started  : 31-OCT-08 18:50:01
Job Type : Copy
Status   : Failed - Could not truncate target table

ID     Tablename                            Total Rows %Comp Status     Status T
ime Total Time
------ ------------------------------------ ---------- ----- ---------- --------
--- ----------
1      "owner"."tablename"                              6       Failed            
N/A       0:18

Copy log on target:

Fri Oct 31/18:50:23.716:: Truncating table "owner"."tablename"
Fri Oct 31/18:50:23.943:: ERROR: ORA-54: ORA-00054: resource busy and acquire with NOWAIT specified at sync/oci/sync_ociquery.cpp:174 at sync/oci/sync_ociquery.cpp:199
Fri Oct 31/18:50:23.943:: ERROR: Failed to truncate table owner.tablename at sync/clt/sync_table_handler.cpp:197

The command can be run again with the hope that it may succeed the next time. If it does not, there will be no harm done.

B. The problem of Capture gathering the DMLs generated by the Oracle Import process during sync:

As opposed to compare, the copy command would result in Oracle Import process executing DMLs on target that would be gathered by the Capture process in the reverse config active on the secondary node of the master-master replication. This is because Capture will only ignore transactions done by Shareplex user in the database and will otherwise gather all other changes to replicated tables in database by any other user, and the transactions executed by Oracle Import process on the secondary node falls in the latter category. If the conflict resolution routines are in place as they should be when running Shareplex in such active-active environment, then there is no harm done as this loopback of transactions back to the source will fail. If the conflict resolution is not properly configured, then there will be problems of sync. But even if the conflict resolution will ward off any sync issues, it will still result in unnecessary load on Shareplex when it sends data to the primary node. The quantum of such data would depend on the size of the table being resync'd.