-
Title
Why does the use of "force" option in a copy table or append table show a warning -
Description
When issuing a copy with "force" option for replicating tables, you see the following warning message:
sp_ctrl (server_name:port_num)> copy owner.table_name to owner.table_name at server_name@o.target_SID force
Warning: Use of the force option on a table in replication, "OWNER"."table_name", may cause out-of-sync conditions
copy started; job id nnnn -
Cause
Use of copy with force option can result in out of sync under certain circumstances.
-
Resolution
If the command specifies all the required information, and the table is in replication, but the force option is not specified, then the table copy will be synchronized with replication. Without the "force" option, Shareplex takes into cognizance such type of transactions and does equivalent of the following steps behind the scenes so as to ensure that the target table is synchronized in conjunction with Shareplex:
a. Establishes a reference point and sends an appropriate message through Shareplex queue.
b. Acquires brief lock on source table and starts Oracle Export with CONSISTENT = y and then releases lock
c. After making sure that the Post is suspended, imports data to target table using Oracle Import.
d. Once the data is imported, the Post resumes its normal functioning.
If the table is in replication and the force option is specified, then the table copy will occur without regard to replication. This can result in out of sync. One simple scenario would be, say, the copy is able to sync the target table, and this is done using Oracle Export/Import without taking the existing replication into account, then after the target table is supposedly sync'd, you can still have messages in the pipeline (Shareplex queues) that may need to be posted that may otherwise have been sent via Oracle Export/Import. This can result in attempt by Shareplex to post the transactions twice and it may succeed if there are no unique constraints on the target table.