The copy/append command uses the Oracle Export/Import utility to allow users to set up replication and/or (re)synchronize individual tables already in replication.
Users may copy/append individual tables to a single target, specify multiple targets, or all targets in the replication configuration.
Important:
Use the copy command to synchronize or instantiate a table (or tables) from one host to any other host when you want the target table truncated prior to synchronizing data.
Use the append command to synchronize or instantiate a table (or tables) from one host to any other host when you do not want the target table truncated prior to synchronizing data.
Use the copy/append command in the following ways:
The copy/append command supports the following:
The copy/append command does not support:
The append command does not support Oracle partitions. (Only the copy command supports Oracle partitions.)
Important:
The launcher process must be running on the target system(s) prior to executing the copy/append command. To start the launcher process the user must execute the following command on each of the target systems:
sp_ctrl(sysB)> start launcher
While the launcher process is running the show launcher command may be used to view process details.
When the user has completed synchronization the launcher may be stopped on the target system(s). To stop the launcher process the user must execute the following command on each of the target systems:
sp_ctrl(sysB)> stop launcher
The copy/append command initiates a process to synchronize individual tables between individual hosts.
The following is the sequence of events that illustrates simple synchronization process activity:
You can issue multiple copy/append commands at a time. Users should note that while the copy process is in progress the Post process stops all replication.
The synchronization processes are not listed in the sp_ctrl console as they are not started by sp_cop process. To identify the process use your operating system to locate the executables.
sp_sync_clnt
sp_sync_lstnr
sp_sync_launcher
The following is a list of parameters associated with synchronization using the copy/ append command. These parameters are all live and take effect the next time the command is issued.
Note: For more information on each of the parameters associated with synchronization using copy/append please refer to each individual parameter in the “SharePlex Parameters” section of this guide.
The best way to view the status of one or more copy/append commands is to use the copy status command or the append status command. The basic command displays the results of all copy/append processes on a system.
The basic command displays the status of all copy/append jobs currently running on the system, and the most recently executed copy/append if it isn’t included in the currently running list. This list can be filtered to show only a specified status.
The best way to view the results of one or more copy/append commands is to use the report command with the copy option in sp_ctrl.
In the example text that follows, the copy/append command was issued against a table not in replication. The copy status that follows displays basic information about the table and the status of the procedure.
sp_ctrl (alvlabl17:8708)> copy sp_iot.SYNC_iot_BASIC to sp_iot.SYNC_iot_BASIC for o.w920a32f at irvqasu15@o.a102u64f force
copy started; job id 111
sp_ctrl (alvlabl17:8708)> copy status 111 for o.w920a32f
Job ID : 111
Host : alvlabl17.quest.com
Started : 17-MAR-08 13:59:28
Job Type : Copy
Status : Done
ID | Tablename | Total rows | %Comp | Status | Status time | Total time |
---|---|---|---|---|---|---|
--- | ------------------------- | ----------- | -------- | -------- | ----------- | ---------- |
1 | "SP_IOT"."SYNC_IOT_BASIC1" | 3720 | 100 | Done | N/A | 0:08 |
2 | "SP_IOT"."SYNC_IOT_BASIC2" | 3720 | 100 | Error | N/A | 0:08 |
3 | "SP_IOT"."SYNC_IOT_BASIC3" | 3720 | 100 | Done | N/A | 0:08 |
To filter this list to only show the tables with a status of error, execute the following command:
sp_ctrl (alvlabl17:8708)> copy status 111 for o.w920a32f “Error”
The synchronization routing file is very similar in structure to a configuration file used for activation, in that the file contents have the same formatting requirements. The difference between the two files is that some tables or objects in a configuration file may not be supported by the synchronization process and therefore should not be included in a synchronization routing file. For example, horizontally or vertically partitioned tables are not supported by the synchronization process and should not be included in a synchronization routing file.
Use of Oracle wildacrds in synchronization routing files is supported. The application of Oracle wildcards in a synchronization routing file is the same as in a config file. For information on using Oracle wildcards in this context, please refer to the “Specifying source tables using wildcards” section of the SharePlex Administrator’s Guide.
Datasource:o.s_sid1 | ||
s_user0.sync_multi_trgt | d_user0.sync_multi_trgt | d_host1@o.d_sid1 |
s_user0.sync_multi_trgt | d_user0.sync_multi_trgt | d_host2@o.d_sid1 |
s_user0.sync_multi_trgt | d_user0.sync_multi_trgt_bu | d_host1@o.d_sid2 |
Note:
The structure of the source and target table(s) should be identical, with the exception that the source tables may contain less columns than the target tables.
The copy/append command should not be used for source tables that contain more columns than their corresponding target tables.
When tables in a configuration use partitioned replication:
To take advantage of machines with multiple processors, you can set the number of copy/append processing threads on the source system using the SP_OSY_THREADS parameter. This thread parameter only affects a partitioned table. The default value of this parameter is set to five (5) threads, and the range of valid values is from one (1) to ten (32) threads.
Please note that synchronization process is single threaded when the tables are in replication. Multi-threaded processing only comes into play with tables that are not in replication.
For more information on the SP_OSY_THREADS parameter please refer this parameter in the “SharePlex Parameters” section of this guide.
Oracle wildcards may be employed when specifying the source_table portion of the copy/append command. Additionally, you may use not notation to exclude specific objects.
You may specify all the tables in a specific schema using the following:
sp_ctrl (irvspxu14:8567)> copy scott.%
In the above example, all objects under the scott schema will be copied, provided the objects are currently in replication.
You can also set exclusions using the following:
sp_ctrl (irvspxu14:8567)> copy scott.% not (%"foo"%)
In the above example, all objects under the scott schema, except objects whose name contain “foo”, will be copied, provided the objects are currently in replication.
To gain a better understanding of how to employ Oracle wildcards please refer to the “Specifying source tables using wildcards” section of the SharePlex Administrator’s Guide.
When a config file is specified, which contains multiple objects, the source and target schema name can only be different if all of the target objects have the same schema name.
Note: The syntax for the copy and append commands are exactly the same. The examples that appear below utilize the copy command, but the append command can be invoked by simply substituting append for copy.
Supported targets: | Oracle |
Authorization level: | Operator (2) |
Issues on: | source system |
Related commands: | None |
Basic command | Command options | Remote options |
---|---|---|
{copy | append} source_owner.source_table
|
[not (list of exceptions)] [to target_owner.target_table[.partition]] [at target_host[@target_SID] [for o.source_SID] [singlethread] [force] [status] |
[ on host | on host:portnumber | on login/password@host | on login/password@host:portnumber ] |
{copy | append} using filename |
[for o.source_SID] [force] |
[ on host | on host:portnumber | on login/password@host | on login/password@host:portnumber ] |
Component | Description |
---|---|
source_owner.source_table |
Specifies an individual source owner and source table name or a group of objects using wildcards. Not valid if using {copy | append} with the using filename syntax. When used without the to target_owner.target_table option, this syntax assumes that the synchronization target is the same as the replication target. Examples: sp_ctrl(SysA)>copy s_user1.sync_single_tbl sp_ctrl(sysA)>append s_user1.sync_single_tbl |
filename |
The name of a synchronization routing file containing the tables to be synchronized. File names are case-sensitive. Use this option when using the {copy | append} using command. Example: sp_ctrl(SysA)> copy using sync_file_2.txt sp_ctrl(sysA)> append using sync_file_2.txt |
totarget_owner.target_table [.partition] |
Specifies the target table for synchronization. This option only valid with the source_owner.source_table option. If the table is not in replication, use this option in conjunction with the at target_host@ [target_sid] option. Examples: sp_ctrl(SysA)> copy s_user1.sync_single_tbl to d_user1.sync_single_tbl sp_ctrl(SysA)> append s_user1.sync_single_tbl to d_user1.sync_single_tbl |
at target_host@ [target_SID] |
Specifies the location of the target table for synchronization. Use in conjunction with the totarget_owner.target_table [.partition] option. Examples: sp_ctrl(SysA)> copy s_user1.sync_single_tbl to d_user1.sync_single_tbl@d_host1@o.d_sid1 sp_ctrl(SysA)> append s_user1.sync_single_tbl to d_user1.sync_single_tbl@d_host1@o.d_sid1 |
for o.source_SID |
Use to specify the datasource that contains the objects to be synchronized, if there is no active configuration, or if there are more than one active configurations. Examples: sp_ctrl(SysA)> copy s_user1.sync_single_tbl for o.source_sid1 sp_ctrl(SysA)> append s_user1.sync_single_tbl for o.source_sid1 |
singlethread | Use this option to copy a partitioned table as one object. The default behavior is to copy each partition of a partitioned table separately so that the copy can be done in parallel. |
force |
Use this option when tables intended for synchronization are not in replication or when the table route does not match an existing or known route. Examples: sp_ctrl(SysA)> copy s_user1.sync_single_tbl for o.source_sid1 force sp_ctrl(SysA)> append s_user1.sync_single_tbl for o.source_sid1 force |
status |
Displays status history for previous copy/append commands. sp_ctrl(SysA)> copy status sp_ctrl(SysA)> append status |
These options enable you to issue the command on a remote machine and to script commands that include a login name, password, port number, or combination of those items.
Option | Description |
---|---|
on host |
Execute the command on a remote system (one other than the one where the current sp_ctrl session is running). You are prompted for login credentials for the remote system. If used, must be the last component of the command syntax. Example: sp_ctrl(sysB)>status on SysA |
on host:portnumber |
Execute the command on a remote system when a remote login and port number must be provided. If used, must be the last component of the command syntax. Example: sp_ctrl(sysB)>status on SysA:8304 |
on login/password@host |
Execute the command on a remote system when a remote login, password, and host name must be provided. If used, must be the last component of the command syntax. Example: sp_ctrl(sysB)>status on john/spot5489@SysA |
on login/password@host:portnumber |
Execute the command on a remote system when a remote login, password, host name, and port number must be provided. If used, must be the last component of the command syntax. Example: sp_ctrl(sysB)>status on john/spot5489@SysA:8304 |
© 2022 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy