Use the compare status command to view the status of the last compare or repair job run. The compare status command can be used to view detailed status on a compare or repair job or a portion of a compare or repair job, or to view status on all compare and repair jobs for which SharePlex has history.
Note: For details and examples about using the compare status command, see the job status command.
Usage
Syntax
| job status |
[job_id]
[Job_id.table_id]
[all]
[full]
[detail]
[status] |
[ on host |
on host:portnumber |
on login/password@host |
on login/password@host:portnumber ] |
Syntax description
| job status |
Shows status of all compare and repair jobs for which SharePlex has history. |
| job_id |
Displays status history for the job with the specified SharePlex-assigned job ID.
Example:
sp_ctrl(sysA)>job status 2828.2 |
|
job_id.table_id |
Displays status history for the job with the specified SharePlex-assigned job ID and table.
Example:
sp_ctrl(sysA)>job status 2828.HR.SRC_TEST3 |
| all |
Displays a summary line for every job with history in the database.
Example:
sp_ctrl(sysA)>job status all |
| full |
Displays the status of every object in the job. By default, the job status command displays the status of those objects not completed, or completed with an exceptional status.
Example:
sp_ctrl(sysA)>job status 2828 full |
| detail |
Displays detail information for every object reported upon. By default, the job status command displays a summary line for every object reported upon. Note that the detail information is the same as is displayed for the job_id.table_id option.
Example:
sp_ctrl(sysA)>job status detail |
| status |
Displays status history for previous jobs with the specified status.
Example:
sp_ctrl(sysA)>job status "Error" |
Remote options
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.
| 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 |
Use the connection command to configure SharePlex to connect to a database.
How to use the connection command
Use the connection command to establish connection properties for SharePlex to use when connecting to a database. Use this command only if there is no database setup utility available for the database. To determine whether a setup utility exists for the database, see Database Setup Utilities.
Use the connection command in the following ways:
- With the set option to set connection attributes. Issue one connection command per option used. The command does not support the stringing of multiple keyword/value pairs with one command.
- With the reset option to clear connection settings.
- With the show option to view the current connection settings.
Notes:
Important: Make certain to stop and then restart the Post process after using this command.
Usage
| Supported sources: |
Oracle |
| Supported targets: |
All |
| Authorization level: |
Operator (2) |
| Issues on: |
source or target system |
| Related commands: |
target |
Syntax
|
connection {o. | r.}database
{
set keyword=value |
show [all] |
reset [keyword]
} |
[user=username]
[password=password]
[tns_alias=alias]
[asm_sid=SID]
[asm_user=username]
[asm_password=password]
[asm_tns_alias=alias]
[wallet_location = path]
[wallet_automode = Y/N]
[dsn=DSN]
[server=servername]
[driver=path]
[port=portnumber]
[connect_string=string]
[plugin = pluginname]
[plugin_version = versionnumber]
[plugin_direction = source/target/both]
[sp_host_port = Hostname:port]
[admin_user = username]
[database = SID]
[dstype = Datastorename]
[version = versionnumber]
[hostaddr = IPaddress]
[host = servername]
[dbname = databasename]
[db_host = IP]
[db_port = portnumber]
[oracle_home = path]
[service_name = servicename] |
Not available |
Syntax description
| {o. | r.}database |
The database for which you are configuring the connection. Use the following format:
Oracle:
o.SID
where: SID is the ORACLE_SID of the database.
Open Target (ODBC):
r.database
where: database is the name (not the DSN) of the database.
Note: The database specification must match the database specification on the datasource line or in the routing map of the configuration file. For example, if the routing map is myhost@r.mydb then a connection command would include the same database specification, like this:
connection r.mydb set user=myuser |
| set keyword=value |
Sets a connection property. See Keywords. |
| show [all] |
show displays the current connection settings for the specified database.
show all displays connection settings for all local databases. Do not use the database specification with show all, as in this example:
connection show all |
| reset [keyword] |
reset clears all of the connection settings for the specified database.
reset keyword clears the connection setting only for a specific connection property. See Keywords |
Keywords
| user=username |
All |
The name of the database user that SharePlex will use to connect to the database specified with database. |
| password=password |
All |
The password for the user specified with username. |
| tns_alias=alias |
Oracle |
The TNS alias to which SharePlex will connect. Required if connections to the database are managed with a tnsnames.ora file. |
| asm_sid=SID |
Oracle |
The Oracle SID of the ASM instance. Required if Oracle Automatic Storage Management is being used by the database. |
| asm_user=username |
Oracle |
The name of the ASM user that SharePlex will use to connect to the ASM instance. |
| asm_password=password |
Oracle |
The password of the ASM user. |
| asm_tns_alias=alias |
Oracle |
The TNS alias of the ASM instance. Required if connections to the ASM instance are managed with a tnsnames.ora file. |
| wallet_location = path |
Oracle |
If set, path to Oracle wallet file |
| wallet_automode = Y/N |
Oracle |
Set to Y if setup for SharePlex auto open |
| dsn=DSN |
Open Target (ODBC) |
The data source name (DSN) that is associated with the database. If a DSN exists for the database, this keyword plus the user and password keywords are the only required connection settings. |
| server=servername |
Open Target (ODBC) |
The name or IP address of the server that hosts the database. |
| driver=path |
Open Target (ODBC) |
The full path to the ODBC driver that supports ODBC connectivity to the database. |
| port=portnumber |
Open Target (ODBC) |
The port number of the database. |
| connect_string=string |
Open Target (ODBC) |
A user-defined connection string. When using your own connection string, make certain it includes all of the required elements to make a successful ODBC connection, but omit the user name and password. Use the connection command with the user and password options to supply user information. |
| plugin = pluginname |
|
Name of plugin |
| plugin_version = versionnumber |
|
Plugin version |
| plugin_direction = source/target/both |
|
The plugin_direction value can be set as source, target or both. |
| sp_host_port = Hostname:port |
Database (Non-Oracle) |
SharePlex host: port that ran setup |
| admin_user = username |
|
Administrator user SQL Server |
| database = SID |
|
Database name |
| dstype = Datastorename |
|
Datastore name |
| version = versionnumber |
|
|
| hostaddr = IPaddress |
Database (Non-Oracle) |
The server IP address to which ODBC will connect |
| host = servername |
Database (Non-Oracle) |
The server name to which ODBC will connect |
| dbname = databasename |
Database (Non-Oracle) |
The database name to which ODBC will connect |
| db_host = IP |
Database (Non-Oracle) |
The host of the database through DSN |
| db_port = portnumber |
Database (Non-Oracle) |
The port used by the database through DSN |
| oracle_home = path |
Oracle |
The path to Oracle Home |
| service_name = servicename |
Oracle |
Oracle service name |
Examples
Oracle examples
connection o.myora12 set user=myuser
connection o.myora12 set password=mypassword
connection o.myora12 set tns_alias=myora12
Open Target examples
DSN exists
connection r.mydb set user=myuser
connection r.mydb set password=mypassword
connection r.mydb set dsn=mydsn
Remove a connection value
connection r.mydb reset port
connection r.mydb reset
View connection settings
connection r.mydb show
connection show all
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:
- This command invokes Export with CONSISTENT=Y. This requires an adequately sized RBS to avoid an ORA-01555 error.
- This command cannot be used in a bi-directional or high availability environment without additional steps. Refer to the Knowledge Base solution 48020.
- Before you use this command, review all of this documentation. Improper use could result in lost data and out-of-sync conditions.
The copy command
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.
The append command
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.
When to use the copy/append command
Use the copy/append command in the following ways:
- To migrate a database.
- To sync a table that the Compare/Repair process is unable to repair.
- Place a table into replication while instantiating it.
What the copy/append command supports
The copy/append command supports the following:
- Objects supported by the copy/append command are those objects supported by Oracle’s Import/Export utility.
- Copying from a lower version of Oracle to a higher version of Oracle, or between the same versions of Oracle.
- The copy/append of objects in a synchronization routing file that are included as a result of Oracle wildcard specification.
- The use of Oracle wildcard specification in command line syntax.
What the copy/append command does not support
The copy/append command does not support:
Important:
- The copy/append command supports Oracle wildcards; however, if the set of objects indicated by the wildcard specification includes objects that are not presently in replication, the copy/append command will only act on those objects currently in replication.
- If the force option is employed and all the target information is specified, the tables in replication will be copied statically, as is the current behavior.
The launcher process
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
Overview of copy/append process
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:
- Log onto the target system and issue the start launcher command from sp_ctrl.
- When the copy/append command is issued from a sp_ctrl session the sp_cnc spawns sp_sync_svr on the source system.
- The sp_sync_svr connects to the target system and starts the sp_sync_clnt on the target system.
- The sp_sync_svr sends the sp_syn_clnt a table list to verify information on the target table. a) If the object is being copied (versus an append) the target table is truncated.
- For each object under copy the sp_sync_svr starts up an Oracle export process.
- If the object under copy is in replication sp_sync_svr must wait for sp_sync_clnt to indicate that the Post process is ready.
- The sp_sync_clnt process starts an Oracle import process for each of the objects that the sp_sync_svr process opened an Oracle export process for.
- The sp_sync_svr process transfers data to the sp_sync_clnt process until the copy is complete.
- If the object under copy is in replication the Post process re-initiates and resumes it’s posting activities.
- Log onto the target system and issue the stop launcher command from sp_ctrl.
Running concurrent copy processes
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.
Identifying synchronization processes
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_svr
-
sp_sync_clnt
-
sp_sync_lstnr
-
sp_sync_launcher
Tuning parameters for the synchronization
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.
- SP_OSY_COMPRESSION: This parameter adjusts the data compression from off (0) to full (9). The default integer value is set to six (6).
- SP_OSY_EXP_BUFFER_SIZE: This parameter adjusts the amount of data that is buffered before being sent to the target system. The default integer value is 1024 Kilobytes.
- SP_OSY_IMP_BUFFER_SIZE: This parameter adjusts the amount of data that is buffered before being applied on the target system. The default integer value is 1024 Kilobytes.
- SP_OSY_LOCK_TIMEOUT: This parameter set the number of seconds that the synchronization process will wait to obtain a table lock. The default integer value is 2 seconds.
- SP_OSY_PORT: This parameter sets the port number for the synchronization process. The default integer value is set to port number 2501.
- SP_OSY_POST_TIMEOUT: This parameter sets the number of seconds that the synchronization process will wait for the Post process to be ready and the synchronization to begin. The default integer value is set to 1800 seconds (30 minutes).
- SP_OSY_TCP_TIMEOUT: This parameter sets the number of seconds for the IPC time-out. The default integer value is set to 60 seconds.
- SP_OSY_THREADS: This parameter sets the number of synchronization processing threads. This thread parameter only affects a partitioned table. The default integer value is set to 5 threads. It has a maximum value of 32 threads.
Viewing copy status
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
sp_ctrl (alvlabl17:8708)> copy status 111 for o.w920a32f
Host : alvlabl17.quest.com
Started : 17-MAR-08 13:59:28
Job Type : Copy
Status : Done
| 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”
Using a synchronization routing file
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 about how SharePlex supports wildcards, see the SharePlex Administration Guide.
Example routing file
| 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:
- When using the copy/append command for a group of tables all the tables must be in replication or all the tables must not be in replication. If you have a combination of tables in replication and tables not in replication the user must create a separate synchronization routing file for each group and a separate synchronization process be started with each file.
- The copy/append command copies tables in random order and does not take into account referential integrity. Therefore, we recommend that users copy/append only one table at a time, or limit the copy/append to a very small number of tables.
Copying dissimilar source and target tables
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.
Using copy with partitioned replication
When tables in a configuration use partitioned replication:
- The copy/append command will support the copying of individual tables and individual partitioned tables, but not individual partitions.
- Data is never copied to an individual partition, even when the target object specified is a partition.
- Specifying a partition as a target is not supported.
- The synchronization of data, for both, copy and append, is always done on the base table and never the partition.
- The copy/append command does not support vertically or horizontally partitioned tables currently in replication.
- Using the option, [singlethread], a partitioned table will be copied 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.
Controlling the number of processing threads
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 1 to 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.
Using Oracle wildcards in command line syntax
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.
For more information about how SharePlex supports wildcards, see the SharePlex Administration Guide.
Other considerations for using copy/append
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.
Usage
| Supported sources: |
Oracle |
| Supported targets: |
Oracle |
| Authorization level: |
Operator (2) |
| Issues on: |
source system |
| Related commands: |
None |
Syntax
|
{copy | append} source_owner.source_table
|
[not (list of exceptions)]
[totarget_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 ] |
Syntax 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 usingfilename syntax.
When used without the totarget_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 attarget_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 |
| attarget_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 |
| foro.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 |
Remote options
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.
| 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 |
Use the copy cache command to copy the object cache from the source to the target.
If you find the following message in the SharePlex Event Log, include in the copy cache command the target host and target sid for the Post process, as well as the actid (activation ID) from the error message. This is the activation ID of the activation for which the Post process is currently posting.
Poster [SP-OPO01009] cannot read object cache for actid <nnnn>
If the copy cache command is issued with no options, SharePlex will identify the current activation ID on the source and copy the object cache for that activation ID from the source to all targets.
Usage
| Supported sources: |
Oracle |
| Supported targets: |
All |
| Authorization level: |
Operator (2) |
| Issues on: |
source system |
| Related commands: |
show log |
Syntax
|
copy cache |
[actid actid]
[at target_host[@target_sid]] |
[ on host |
on host:portnumber |
on login/password@host |
on login/password@host:portnumber ] |
Syntax Description
| copy cache |
If you issue the copy cache command with no options, SharePlex determines the current activation ID (actid) on the source and copies the object cache for that actid to all targets. |
| actid actid |
Specifies the activation ID for the object cache you want to copy to the specified target or to all targets (if no target is specified). |
| attarget_host[@target_sid] |
Specifies the target to which you want to copy the object cache for the specified actid or for the current actid (if no actid is specified). |
Remote options
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.
| 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 |
