Use the cancel command to cancel a running compare, repair, copy or append command job.
To cancel a job, you must supply its job ID. The job ID is reported back from the compare, repair, copy or append job when you issue the command that starts it:
sp_ctrl> repair using 1elliot
repairing 7 of 7 objects
repair started; job id 408
Alternatively, you can get the job ID of the most recently streamed job by issuing the job status command:
sp_ctrl> job status
Job ID: 408
PID: 11527
Host: prodsys
Started: 22-FEB-15 18:08:09
Job Type: Repair
Status: Processing - 0 objects completed
| Supported sources: | Oracle |
| Supported targets: | Oracle |
| Authorization level: | Operator (2) |
| Issues on: | source system |
| Related commands: | compare, repair, copy, append |
| Basic command | Remote options |
|---|---|
| cancel job_id |
[ on host | on host:portnumber | on login/password@host | on login/password@host:portnumber ] |
| Component | Description |
|---|---|
| jobID |
The ID of the job to be canceled. Example: sp_ctrl>cancel 407 |
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 |
Use the clean history command to remove the information and logs from old compare, repair, copy, and append command jobs.
These jobs generate log files on the source and target systems. By default, the job information and log files are cleaned when the job is older than the value set with the SP_SYS_JOB_HISTORY_RETENTION parameter. The clear history command can be used to clear the job information and logs on demand for a specific job or table, or for all jobs that are of a specific age.
| Supported sources: | Oracle |
| Supported targets: | Oracle |
| Authorization level: | Operator (2) |
| Issues on: | source system |
| Related commands: | compare, repair, copy, append |
| Basic command | Command options | Remote options |
|---|---|---|
|
clear history { all | source_owner.source_table | age days | jobID } |
[for o.source_sid] |
[ on host | on host:portnumber | on login/password@host | on login/password@host:portnumber ] |
| Component | Description |
|---|---|
| all |
Causes all job to be removed. Example: sp_ctrl(sysA)> clear history all |
| source_owner.source_table |
Causes history for a particular table to be removed. Example: sp_ctrl(sysA)> clear history clear history user2.employee |
| age days |
Causes the job history older than the specified number of days to be removed. Example: sp_ctrl(sysA)> clear history age 10 |
| jobID | Causes the history for the specified job id (obtained using the job status command) to be removed. |
| for o.source_sid |
Optional. Can be used to employ the clear history command when there is no active configuration, or if there are more than one active configurations. In either case, the source SID must be specified using the for option. Example: sp_ctrl(sysA)>clear history all for o.source_sid1 |
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 |
Use the clear status command to remove old warning messages from the Status Database. To use this command:
SharePlex puts a message in the Event Log identifying the messages that were cleared.
This command clears messages from the Status Database for the default system. To clear messages from a Status Database on a different system, use the [on host] option.
Note: Some messages cannot be cleared.
| Supported sources: | Oracle |
| Supported targets: | All |
| Authorization level: | Operator (2) |
| Issued for: | source or target system |
| Related commands: | show statusdb |
| Basic command | Remote options |
|---|---|
| clear status {statusID| all} |
[ on host | on host:portnumber | on login/password@host | on login/password@host:portnumber ] |
| Component | Description |
|---|---|
| statusID |
The SharePlex-assigned ID of an individual message (obtained using the show statusdb command) that you want to remove. Example: sp_ctrl(sysA)>clear status 20 |
| all |
This argument removes all clearable messages. Example: sp_ctrl(sysA)>clear status all |
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 |
Use the compare and compare using commands (collectively known as the comparison commands) to verify that a target table is in synchronization with its source table.
After running a comparison, you can run the repair or repair using command to repair any out-of-sync rows.
Note: A running comparison or repair does not affect the source tables in any way. SharePlex logs into the database only to query for read consistency, and the locks on the source tables are brief. SharePlex briefly locks the target tables during the processing, but users can continue accessing them with little or no awareness of the lock.
SharePlex can detect and repair out-of-sync rows in a target table that are caused by DML operations: INSERT, UPDATE, DELETE.
SharePlex does not support (and will skip) the comparison and repair of the following:
Do not perform DDL on a table that is being compared or repaired. A comparison does not detect out-of-sync conditions caused by DDL operations, including those that SharePlex supports. If the DDL changes the table definition, it invalidates the SELECT statement that is built by the comparison process to get the rows that need to be compared. The following error indicates that DDL occurred:
Oracle Error: ORA-01466: unable to read data - table definition has changed
Once you correct an out-of-sync condition caused by DDL, you can use the repair command to resynchronize the data in the rows.
See the SharePlex Release Notes for additional information about data types that are supported by compare and compare using
Replication latency reduces the performance of compare and repair processing. The message from the source that spawns the comparison and repair processes on the target is sent through the queues with the replicated data. Delays caused by a data backlog also delay the spawn message and can cause the source process to lose its read consistency. If possible, perform comparisons and repairs during off-peak hours.
To repair a view, the following must be true:
The following scenarios require special handling when running a comparison.
| Use case | Compare support |
|---|---|
| Consolidated replication |
Consolidated replication is supported if the target database and Post processes are configured to add the ID of the source host to each row. To compare or repair the correct rows in the central target table, use the targetwhere option and base the where clause on the source ID value. For example, to compare a table in the database at the Eastern headquarters of a company to the correct rows in the central corporate database, you could use a source ID of "East" for the Eastern database and then base the targetwhere clause on that value. Use the same targetwhere clause in the repair command. The comparison and repair processes can use the source ID value to select only the rows that are valid for the Eastern database. The use the comparison or repair commands for any implementation of consolidated replication, other than one that identifies a source ID, may result in the unwanted deletion of target rows. For more information about this configuration, see the SharePlex Administration Guide. You may need to combine the targetwhere option with the standard where option to ensure that the target rows are selected accurately. |
| Peer-to-peer replication |
In a peer-to-peer configuration, you must decide which system is the trusted source system and which is the secondary, or target, system. The secondary system is the one where any repairs will be performed. Before you run a comparison or repair in a peer-to-peer environment, follow these steps:
For more information about this configuration, see the SharePlex Administration Guide. |
| Tables without keys |
The comparison and repair commands issue a SELECT statement with an ORDER BY clause on the source and target systems. The ordering is faster if large tables have a primary key or a unique, non-null key and an index (preferably a unique index). Otherwise, all of the columns are used as a key. If a table has no unique row identifier, but does have one or more columns that can identify a row as unique, you can use the compare command with the orderby option. When this option is used, SharePlex prints a notice to the sp_desvr log on the source system that the command used those columns as a key. |
| Tables with extra source or target columns |
Use the compare command with the sourcewhere or targetwhere clause if the source or target table contains extra columns and those columns contain unique values on which to base the sorting. See Control which rows are compared. |
A comparison detects out-of-sync conditions in a target table that are caused by DML operations:
When you run the compare or compare using command on the source system, SharePlex initiates the following events:
If the row count passes the sanity check, the tables are compared as follows:
The compare and repair commands write the SQL that is needed to repair any out-of-sync rows to a SQL file in the same location as the log files. If only a compare command is issued, SharePlex does not execute these SQL statements. If a repair command is issued, the command works identically to the compare commands except that it executes the SQL statements to repair the out-of-sync rows.
You can suppress the output of the SQL log file. Some reasons to suppress this file are:
To suppress the SQL log file, use the nosqllog option with the compare or repair command.
To suppress the output of the SQL log file for all compare and repair runs while the current instance of SharePlex is running, set the SP_SYS_SECURE_MODE environment variable to 1. This variable must be set before starting SharePlex, so if the sp_cop process is running it must be restarted after setting this variable. When sp_cop is run with this environment variable, the compare and repair commands will not put data into SQL files and the Post process will not put data into the SharePlex error log.
All of the compare and repair commands enable you to run multiple processes concurrently.
A maximum of 20 SharePlex processes can use the post queue at the same time, including the replication processes and the comparison and repair processes. It is recommended that you allow a maximum of five comparison and repair processes to run at any given time. By using the compare using and repair using commands, you can work around the 20-process limit by comparing more tables per process.
If a comparison or repair fails because the limit is reached, SharePlex logs a message to the Event Log.
Note: You can run multiple commands more easily by using the edit command to edit a previous command to create a new one.
You can compare subsets of an active configuration file in the following ways.
To compare all of the tables in replication that belong to one schema, use the compare command with a wildcard:
sp_ctrl> compare scott.%
To compare all of the tables in a configuration file, use the compare using command:
sp_ctrl> compare using myconfig
To compare all of the tables in replication to one target route, use the compare using command with the at option:
sp_ctrl> compare using config.active at prodsys@o.ora112
The compare and repair commands have where options that enable you to filter the rows that are selected for processing. By default, these commands affect all rows of a table and ignore columns in the target table that are not contained in the source table.
Use the where option to filter rows based on identically named columns in the source and target tables.
Use the sourcewhere and targetwhere options if one or more extra columns exists in either the source or target table and those rows contain values that determine row uniqueness.
To use this option correctly:
Use the standard where option for the other columns that have the same name on both source and target.
Important! If you plan to run both a comparison and repair for a target table that has extra rows, only use targetwhere to compare for UPDATEs and DELETEs. The repair command cannot determine the correct values for INSERTs. To work around this issue, set a default value for the extra columns or manually update the inserted rows.
Every time that a comparison or repair command is issued, the job ID is shown in the sp_ctrl display. If the sp_ctrl display is not available, you can view the job ID by running the compare status command.
To view the status or results of a comparison, use the compare status command in sp_ctrl.
For more information, see compare status.
The sp_desvr and sp_declt processes write a log file on the system where they run. The logs are stored in the log sub-directory of the SharePlex variable-data directory.
The name of the log written by the sp_desvr process is desvr_JobID_SID_pProcessID.log, where:
The names of the files written by the sp_declt process are declt_JobIDTableID_SID_SourceHost_pProcessID appended with either .log or .sql, where:
Example log file names:
desvr_606_ora112_p14610.log
declt_606-1_ora112_prodsys_p6528.log
declt_606-1_ora112_prodsys_p6528.sql
To control disk usage, the logs are aged in a circular fashion. SharePlex generates a new log file when the current log reaches the size limit. New logs are created up to a maximum number of logs, and then SharePlex starts overwriting the oldest log.
Note: For the compare using command, there rarely is more than one log file.
Use the cancel command to stop a running comparison or repair job.
sp_ctrl(sysA)>cancel JOBID
For more information, see cancel.
SharePlex retains a history of each finished job in the database on the source system. The SP_SYS_JOB_HISTORY_RETENTION parameter controls how long history is retained.
To clear this history on demand, use the clear history command. When SharePlex removes the history of a job, it also removes the log file that was the source of the history.
To remove the log files from the source system without clearing the job history from the database, use the remove log command. You can also use this command to remove old log files from the target system.
To control the size of the log files, set the SP_DEQ_LOG_FILESIZE parameter.
To produce separate SQL files for INSERT, UPDATE, and DELETE operations, rather than use just one file for all operation types, use the log split option.
You can control the size of the block of rows that is fetched when the process makes its SELECT query. The block size is calculated based on the value set with the SP_DEQ_MALLOC parameter. The value is divided equally by the number of comparison threads to be used, and then it is recalculated based on the size of all of the columns added together.
To repair out-of-sync rows found by the comparison, use the repair or repair using command. See repair / repair using.
| Supported sources: | Oracle |
| Supported targets: | Oracle |
| Authorization level: | Operator (2) |
| Issues on: | source system |
| Related commands: | repair / repair using |
| Command | Command options | Remote options |
|---|---|---|
| compare owner.source_table[.partition] |
[ at target_host@o.target_sid ] | [ for o.source_sid ] | [ hint “hint” ] | [ {include | exclude} "column_list" ] | [ key ] | [ log rowdata ] | [ log split ] | [ {nolocksource | nolocktarget | nolockboth} ] | [ nosqllog ] | [ not "exception_list" ] | [ orderby "column_list” ] | [ override ] | [ parallelism degree ] | [ port port_number ] | [ quickcheck ] | [ sourcewhere “clause” ] | [ threads thread_count ] | [ targetwhere “clause” ] | [ to target_owner.target_table[.partition] ] | [ where “clause” ] |
[ on host | on host:portnumber | on login/password@host | on login/password@host:portnumber ] |
| compare using filename |
[key] | [log rowdata] | [log split] | [parallelism degree] | [port port_number] | [quickcheck] | [threads threads_count] |
[ on host | on host:portnumber | on login/password@host | on login/password@host:portnumber ] |
| Component | Description |
|---|---|
| compare owner.source_table[.partition] |
The basic command compares all of the source rows with all of the target rows. owner.source_table is the owner and name of the source table. Use double quotes to enforce case-sensitivity or spaces within a name, for example “HR”.emp. Wildcarded table names (but not owner names) are supported. To be compared, tables that satisfy a wildcard in this command must be listed (explicitly or by wildcard) in the active replication configuration. For more information about how SharePlex handles wildcards, see the SharePlex Administration Guide. Example sp_ctrl(sysA)>compare scott.emp sp_ctrl(sysA)>compare scott.emp.west |
| compare using filename |
The basic command compares all of the source rows with all of the target rows in the tables listed in filename. filename is the name of the file that contains the names of the source tables that you want to compare. Example sp_ctrl(sysA)>compare using sales |
| Component | Description |
|---|---|
| at target_host@o.target_sid |
Valid for compare Compares the source table to only one of its targets. Use when the source table replicates to multiple target systems. target_host is the name of the target system. target_sid is the ORACLE_SID of the target Oracle instance. Example sp_ctrl(SysA)>compare scott.emp at prod@o.prodsid |
| for o.SID |
Valid for compare Specifies the Oracle instance that contains the source table. Use when the same source table is in multiple Oracle instances on a system. SID is the ORACLE_SID of the source instance. It is case-sensitive and must be typed as it appears in the oratab file, V$PARAMETER table, or Windows Registry. When used, this option must appear after the required command arguments, but it can appear in any order with other options. Example sp_ctrl (SysA)>compare scott.emp for o.oraA |
| hint "hint" |
Valid for compare Includes an Oracle hint in the SELECT statement. The hint is used on the source and target systems. “hint” is a standard Oracle hint no longer than 2000 characters. Enclose the entire hint within double quotes. Omit the leading /*+ and trailing */ in the hint string. They are added by SharePlex. When used, this option must appear after the required command arguments, but it can appear in any order with other options. Example sp_ctrl (SysA)>compare scott.emp where “file >001005” hint “emp(salary)” When running a comparison from the command line of the operating system, quoted strings must have an extra set of escaped double quotes as in this example: /productdir/bin/sp_ctrl compare scott.emp hint “\“emp(salary)\”” |
| {include | exclude} "(column_list" |
Valid for compare Filters the columns to be compared.
(column_list) is the list of columns to include or exclude.
Note: There could still be rows that are out-of-sync in the columns that were not compared. Example sp_ctrl (SysA)>compare scott.emp exclude "color, weight" |
| key |
Valid for compare and compare using Performs a fast comparison of large tables. This command does not compare all of the data values. It compares one of the following:
Important: Even if the keys or the orderby values match, the tables remain out of synchronization if values in other columns do not match. When used, this option must appear after the required command arguments. It can appear in any order with other options. Do not use this option to base a comparison on a SharePlex key definition. For more information about SharePlex key definitions, see the SharePlex Administration Guide. Example sp_ctrl (SysA)>compare scott.emp key sp_ctrl(sysA)>compare using sales key |
| log rowdata |
Valid for compare and compare using Directs the client process to produce a SQL file that logs the actual row data rather than hash values. The file is produced on the target system. Do not use log rowdata if the tables being compared have LONGs, LOBs, and VARRAYs. If the value of any of these data types is very large, SharePlex may not be able to log the actual data. IMPORTANT! The purpose of this SQL file is to provide a view of the data that was compared. Do not use it to repair target tables. The data values that were captured when the command was run may be different from those currently in the database. Use the repair command to repair any out-of-sync rows. Example sp_ctrl(sysA)>compare scott.emp log rowdata sp_ctrl(sysA)>compare using sales log rowdata |
| log split |
Valid for compare and compare using Directs the client process to split its SQL file into three different files based on the operation type: one for INSERTs, one for UPDATEs, and one for DELETEs. Example sp_ctrl(sysA)>compare scott.emp log split sp_ctrl(sysA)>compare using sales log split |
|
{nolocktarget | nolocksource | nolockboth} |
Valid for compare Prevents the comparison process from locking tables during the comparison phase of a run that includes a repair. Normally, SharePlex locks the tables momentarily during a comparison to get a read-consistent view, and then unlocks them immediately, but SharePlex always locks target tables during a repair. SharePlex locks source tables momentarily during a repair to get a read-consistent view. |
| nosqllog |
Suppresses output of the SQL log file. This file contains the SQL that is needed to repair out-of-sync rows. Some reasons not to output this file include:
|
| not “exception_list” |
Valid for compare Specifies an exception list of tables not to compare when the table specification includes wildcards. “exception_list” is a list of names of the tables not to compare.
Example sp_ctrl(SysA)>compare scott.% not (%temp%) |
| orderby “column_list” |
Valid for compare Specifies columns for the comparison process to use in its ORDERBY clause when it sorts rows to be compared. This option enables comparisons to be performed on tables that have no primary or unique key. “column_list” is the names of the columns to use in the ORDERBY clause.
Example sp_ctrl(SysA)>compare scott.emp where “file >001005” orderby “Last Name,Division” When running a comparison from the command line of the operating system, quoted strings must have an extra set of escaped double quotes: /productdir/bin/sp_ctrl compare scott.emp orderby “\“Last Name,Division\”” |
| override |
Valid for compare and compare using Overrides the sanity check and allows the comparison of tables that are very out of synchronization. The alternative to comparing tables that are very out of synchronization is to reload the target table by using the copy command (see copy / append). For more information about sanity check, see How the comparison works. Example sp_ctrl(sysA)>compare scott.emp override sp_ctrl(sysA)>compare using sales override |
| port port_number |
Valid for compare and compare using Available for backward compatibility if the version of SharePlex is earlier than 8.0 on the source or target system. Specifies a port on the source system for the client process to use for communication with the server process. In earlier versions of SharePlex, the communication is two-way, and a random port number is selected by default for client-to-server communication. This option overrides the random port selection with a specific port number, such as that required by a firewall. Example sp_ctrl(sysA)>compare scott.emp port 1234 |
|
quickcheck |
Valid for compare and compare using Performs an initial check to determine if any rows are out of synchronization. As soon as one out-of-sync row is detected, the process stops. No further information is returned. No out-of-sync row information is logged to the compare SQL file. Do not use this option with the key option or any of the where options. This option does not support LONG columns. Example sp_ctrl(sysA)>compare scott.emp quickcheck sp_ctrl(sysA)>compare using sales quickcheck |
| parallelism degree |
Valid for compare and compare using Adds a parallel hint to the SELECT statement. For degree, set the degree of parallelism. Example sp_ctrl(sysA)>compare scott.emp parallelism 4 sp_ctrl(sysA)>compare using sales parallelism 4 |
| sourcewhere “clause” |
Valid for compare Bases the comparison on one or more columns in the source table when those columns do not exist in the target table.
Example #1: sp_ctrl(sysA)>compare scott.emp sourcewhere “file >001005” Example #2: The following example shows how the sourcewhere and where options are combined to get the desired result. Only the source comparison process will use the sourcewhere clause, but both the source and target comparison processes will use the where clause. sp_ctrl(SysA)>compare scott.emp sourcewhere “deptno = 200” where “mgr = ‘SMITH’” |
| targetwhere "clause" |
Valid for compare Bases the comparison on one or more columns in the target table when those columns do not exist in the source table.
Example #1: sp_ctrl(SysA)> compare scott.emp targetwhere “file >001005” Example #2: The following example shows how the targetwhere and where options are combined to get the desired result. Only the target comparison process will use the targetwhere clause, but both the source and target comparison processes will use the where clause. sp_ctrl(SysA)>compare scott.emp where “deptno = 200” targetwhere “mgr = ‘SMITH’” repair |
| threads thread_count |
Valid for compare and compare using Sets the number of processing threads that are used by the comparison process. Example sp_ctrl(sysA)>compare scott.emp threads 4 sp_ctrl(sysA)>compare using sales threads 4 |
| to target_owner.target_table[.partition] |
Valid for compare Compares the source table to only one of its targets. Use when the source table replicates to multiple target systems and the target tables have different names. This option can also be used to specify a target partition. Example (Compares a partition) sp_ctrl(SysA)>compare scott.emp to scott.allemp.east |
| where “clause” |
Valid for compare Include a WHERE clause in the SELECT statement on both the source and target systems. The WHERE clause acts as a filter to compare specific rows. For “clause” specify a standard WHERE clause that does not include subqueries.
Example sp_ctrl (SysA)>compare scott.emp where “region=4” |
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 |
© ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
