Return
-
Title
Using "copy table" or "append table" command in a broadcasting replication environment. -
Description
In a broadcasting replication environment, copy or append command errors with the following message printed on the screen:
Table owner.table is being replicated to more than one target
Use "to" or "at" option to specify correct target to use -
Cause
One cannot sync all the target tables corresponding to the source table in one go using the "copy table". It needs to be done on a target by target basis.
-
Resolution
A broadcasting replication environment is one where a source table is replicated to several targets. As a result the copy command must be issues on a per target basis. For example:
sp_ctrl (source_servername:port_num)> copy owner.table1 at target_server1
Additional Information:
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 special considerations when synchronizing object(s) in a broadcasting replication or data distribution environment. The solution also applies to the "append table" command.
The following example illustrates how a broadcasting replication or data distribution environment is configured:
sp_ctrl (source_servername:port_num)> view config config2
datasource:o.source_SID
#source tables target tables routing map
owner.table1 owner.table1 target_server1@o.target_SID1
owner.table1 owner.table1 target_server2@o.target_SID2
When running a "copy table" command without providing any options about the target server, SID, etc, you run into the following errors:
sp_ctrl (source_servername:port_num)> copy owner.table1
Table owner.table1 is being replicated to more than one target
Use "to" or "at" option to specify correct target to use
One cannot sync all the target tables corresponding to the source table in one go using the "copy table". It needs to be done on a target by target basis. In the above example, if one were to sync the target table located in the target server named target_server1, then the "copy table" needs to be issued as follows (using the [at {TARGET_HOST.EN}] option):
sp_ctrl (source_servername:port_num)> copy owner.table1 at target_server1
copy started; job id 20
sp_ctrl (source_servername:port_num)> copy status
Job ID : 20
Host : source_servername
Started : 04-NOV-08 20:47:42
Job Type : Copy
Status : Done
ID Tablename Total Rows %Comp Status Status e
------ ------------------------------------ ---------- ----- ---------- --------
1 "owner"."TABLE1" 6 100 Done 0
The command can be issued once for every target table named TABLE1 which needs to be sync'd.