Use the remove config command to permanently delete a configuration file from the system. This command does not prompt for confirmation, and removing a configuration file cannot be undone. You cannot remove an active configuration. To remove an active configuration, deactivate it first.
TIP: You might be able to recover an accidentally deleted configuration if that configuration was previously active and you did not run ora_cleansp since it was activated. To recover the configuration, view the Event Log to determine the activation ID for that configuration file, then look in the save sub-directory of the SharePlex variable-data directory for a .conf.actid file, where actid is the activation ID you got from the Event Log.
Usage
| Supported sources: |
Oracle |
| Supported targets: |
All |
| Authorization level: |
Operator (2) |
| Issued for: |
source system |
| Related commands: |
deactivate config, list config, show config, view config |
Syntax
| remove config filename |
[ on host |
on host:portnumber |
on login/password@host |
on login/password@host:portnumber ] |
Syntax description
| filename |
The name of the configuration that you want to remove. Configuration names are case-sensitive.
Example:
sp_ctrl(sysA)> remove config sales |
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 |
The compare, compare using, repair, copy, and append commands generate log files both on the source, and on the target. The job information and source log files are cleaned up when the job is older than SP_SYS_JOB_HISTORY_RETENTION, or if the clear history command is used. To remove the log files on the source without clearing job history from the database, or to remove log files from the target, use the remove log command.
For example:
sp_ctrl> remove log age 5
Logs removed
Usage
| Supported sources: |
Oracle |
| Supported targets: |
Oracle |
| Authorization level: |
Operator (2) |
| Issued for: |
source system |
| Related commands: |
compare, repair, copy, append |
Syntax
| remove log {all | age days | copy | compare} |
[ on host |
on host:portnumber |
on login/password@host |
on login/password@host:portnumber ] |
Syntax description
| all |
This argument causes all logs to be removed.
Example:
sp_ctrl(sysA)> remove log all |
| age days |
This argument causes logs older than the specified number of days to be removed.
Example:
sp_ctrl(sysA)> remove log age 10 |
| copy |
This argument causes logs for the copy or append commands to be removed.
Example:
sp_ctrl(sysA)> remove log copy |
| compare |
This argument causes logs for the compare and/or repair command to be removed.
Example:
sp_ctrl(sysA)> remove log |
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 repair and repair using commands (collectively known as the repair commands) to repair out-of-sync rows in a target table or tables.
- The repair command repairs individual target tables or any number of target tables in a given schema through the use of wildcards. When repairing individual tables, you can make use of column-based filtering to control the rows that are selected for repair.
- The repair using command repairs all of the target tables listed in the active configuration or in another file that contains a subset of the tables in the active configuration.
The repair commands first perform a comparison to identify the rows that need to be repaired, and then they perform the repair. For more information about how tables are compared, see compare / compare using.
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.
Supported operations
SharePlex can detect and repair out-of-sync rows in a target table that are caused by DML operations: INSERT, UPDATE, DELETE.
Not supported
-
SharePlex does not support (and will skip) the comparison and repair of the following:
- UDT inside VARRARY. Other UDTs are supported.
- Sequences.
- Tables for which transformation was used.
- Network configurations in which a pass-through server is used to pass data between the source and target servers.
- XMLtype with different storage on the source and target (CLOB on source, BINARY on target, or the other way around). Like to like compare/repair is supported.
- VARRAY types not inside SDO_GEOMETRY or ST_GEOMETRY.
- Comparisons and repairs are not supported in a cascading replication environment.
-
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.
- Comparison and repair command strings longer than 255 characters are not supported. This is an operating system limitation. To work around this limitation, use the edit command on the source system. You can type the command string within a text file, and then the command automatically executes the file.
See the SharePlex Release Notes for additional information about data types that are supported by compare and compare using
Other conditions
- The tables that you want to compare or repair must be part of an active configuration file.
- Uncommitted transactions on a source table prevent the comparison and repair processes from obtaining the brief locks they need to obtain read consistency. Make certain that all transactions are committed before you run a comparison or repair.
-
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.
- Do not truncate a table that is being compared or repaired. The comparison commands take a snapshot of each source table when they start. If a table is truncated, the table view in the snapshot is truncated, and this can cause the command to return invalid out-of-sync conditions.
-
To repair a view, the following must be true:
- The view table must contain all of the constraint columns of the underlying table.
- The names of the constraint columns in the view must be identical to the names of the constraint columns in the underlying table.
How to use the repair commands
The recommended procedure for maintaining synchronized data through the comparison and repair commands is to run the compare or compare using command first, then view the results with the repair status command. This command shows any rows that are out-of-sync and the possible cause. Unless the cause of the out-of-sync condition is corrected, replication will go out of synchronization again, even if you repair the rows this time. After the problem is fixed, issue the repair or repair using command.
You can run the repair or repair using command without doing a preliminary comparison. The command performs a comparison first, to identify the out-of-sync rows, and then it repairs those rows. However, the underlying cause of the out-of-sync condition must be corrected to prevent future out-of-sync conditions.
See the SharePlex Administration Guide for causes and solutions for out-of-sync conditions.
When to run the repair
The best time to repair a target table depends on its size, the cause of the problem, the extent of out-of-sync rows, and how long you are willing to tolerate users being locked out. Before you initiate a repair, consider the following:
- Although the users of the tables are not usually affected by the brief locks that are applied when tables are compared, they are locked out of the target table for the duration of the repair process. For a small table, this might not be disruptive, but for a large table needing extensive repairs, the wait can be significant.
- Locks on a target table can reduce posting performance if Post must wait for the repair to finish before it can apply changes to that table and move on to other tables. This increases the latency of the target data and causes operations to accumulate in the post queue. If the objects that Post needs to change are different from those being repaired, the two processes run simultaneously.
-
If you must repair a table immediately, but cannot tolerate locks or replication latency, you can use the where option to limit the repair to certain rows. An alternative is to use the key option, but this option may cause the repair to miss some out-of-sync rows.
- If the repair can wait, correct the cause of the problem immediately and then do the repair during non-peak hours.
Special use cases
The following scenarios require special handling when running a comparison.
| Consolidated replication |
The target table in a central database has more rows than any of its contributing source databases, and often it has more columns than the source databases. Special consideration is required when using the repair commands in this environment.
repair using command
Consolidated replication is not supported by the repair using command. The repair using command will cause unwanted deletion of target rows that do not exist in those source tables.
As a workaround, create a subset of the configuration that excludes the tables that are involved in consolidated replication, and repair the subset configuration instead. You can use the repair command to repair the tables that are involved in consolidated replication.
repair command
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:
- Stop user access on the secondary system and wait for replicated operations from that system to post to the trusted source database. Users can continue to access the source database.
- Issue the qstatus command on the source and secondary systems.
- When there are 10 or fewer messages in the queues, run the comparison from the source system.
- During a comparison, you can permit user access to the source and secondary databases after the sp_desvr and sp_declt start.
- Use the repair command with the where option to repair selected rows in a target table without locking users out of the table.
|
| 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. |
| Target tables with more columns than the source table |
The repair and repair using commands ignore target columns that are not contained in the source table. A repair does the following:
- An INSERT inserts values into target columns that have corresponding columns in the source table, but not into the extra columns. Columns with NOT NULL constraints but no default values cause Oracle errors. Default values are recommended for extra columns in target tables.
- An UPDATE resynchronizes values in target columns that have corresponding columns in the source table, but not the values in the extra columns.
- A DELETE is not affected by extra columns in the target table, because the repair command selects rows based on column data in the matching columns.
|
| Tables with a UNIQUE constraint |
Columns defined with a UNIQUE constraint can cause the repair or repair using command to return unique-constraint violation errors. The following example shows source and target tables with two columns each. The first column is the primary key, and the second column has the UNIQUE constraint.
When SharePlex attempts to repair row 1 of the target table to match the source, the UNIQUE constraint on column 2 returns an error because the value 'ABC' already exists in row 2. The same thing happens for row 2 of the target table, because 'XYZ' already exists in row 1. Workarounds are:
- Disable UNIQUE constraints on the target table before you run a repair command, then enable them again after the repair is finished.
- Delete the target rows for which the unique constraint violations occurred, then run the repair again so that SharePlex inserts those rows with the correct data.
|
| Tables with LOB columns |
Repairs take longer if any target tables have LOB columns. For a faster repair, you can set the SP_DEQ_SKIP_LOB parameter to 0 so that the LOB columns are skipped in the comparison and repair. For more information, see SP_DEQ_SKIP_LOB . |
How the repair works
A repair repairs out-of-sync conditions in a target table that are caused by DML operations:
- extra or missing rows
- rows whose values do not match
The conditions for a repair
The repair and repair using commands issue the following corrective SQL statements:
- If a row exists on the source side but not the target side, SharePlex issues an INSERT statement.
- If a row exists on the target side but not the source side, SharePlex issues a DELETE statement.
- If a target row differs from the source row, and the key columns match, SharePlex issues an UPDATE statement based on the source values.
- If you are using the repair or repair using command for an Oracle partitioned table, the default behavior of the repair process is to repair rows by using INSERTs and DELETEs only. Repairs that require UPDATEs are converted to a DELETE followed by an INSERT to prevent errors should an UPDATE cause a row to change partitions and row movement is not enabled for the table. You can change the repair behavior so that SharePlex repairs partitioned tables by using INSERTs, UPDATEs, and DELETEs as appropriate. For more information, see SP_DEQ_PART_TABLE_UPDATE .
The process
A repair always includes a comparison to locate the out-of-sync conditions in a target table. When you run the repair or repair using command, SharePlex initiates the following sequence of events:
- The sp_cop process spawns a sp_desvr (server) process on the source system.
- The sp_desvr process returns control and use of the sp_ctrl interface to the user, and replication continues while the comparison proceeds.
- The sp_desvr process sends a message to the Post process to initiate a sp_declt (client) process on the target system.
- The server and client processes establish direct communication with each other.
-
The row selection and repair proceeds as follows:
- When finished, the processes write their log files, and you can view the results with the show repair command.
Error handling
If SharePlex encounters a database error when it applies a repair SQL statement, it stops the repair from that statement forward and commits only the previously applied valid statements. Thus, the table is partially repaired, but it still could be out of synchronization. The repair status command alerts you to this situation.
Manage the SQL log file
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:
- The data contains sensitive information. The SQL log file is written in clear text. By not producing a SQL log file, the sensitive data is not persisted to disk, which may satisfy security requirements for data at rest, such as those required to meet PCI compliance standards.
- The compared or repaired tables have a very large number of out-of-sync rows. A log file of this size can consume a large amount of disk space.
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.
Run multiple processes
All of the compare and repair commands enable you to run multiple processes concurrently.
- Multiple compare and repair commands can operate concurrently, each processing a pair of source and target tables, or you can use one command with Oracle wildcards to specify multiple sets of tables. See the SharePlex Administration Guide for more information about how SharePlex supports wildcards.
- The compare using and repair using commands operate on an entire file. For example, you can compare or repair the tables in an entire configuration file, or you can create one or more compare files or repair files to affect a subset of the target tables, and then run one or more of them concurrently. See the command syntax for instructions.
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.
Repair a subset of a configuration
You can repair subsets of an active configuration in the following ways.
-
To repair all of the target tables in replication that belong to one schema, use the repair command with a wildcard:
sp_ctrl> repair scott.%
-
To repair all of the target tables in a configuration file, use the repair using command:
sp_ctrl> repair using myconfig
-
To repair all of the target tables in one target route, use the repair using command with the at option:
sp_ctrl> repair using config.active at prodsys@o.ora112
- To repair a custom subset of the tables in a configuration, specify them in a repair file. This is a plain text file that lists only the source tables whose targets you want to repair. The target tables are taken from the configuration file at the time that the command is issued. You can create a repair file by using the create config or copy config command. Make certain to give this file a name that makes it clear it is not a configuration file. See the command syntax for more information.
Control which rows are repaired
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 where option for a table that uses vertically partitioned replication. The source and target columns can have different names. Base the where selection on the source columns. SharePlex reads the column mappings from the configuration file to build the correct WHERE clause for the target table.
-
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.
- Use sourcewhere if the source table contains the extra columns.
- Use targetwhere if the target table contains the extra columns.
To use this option correctly:
- SharePlex combines the where option with the sourcewhere or targetwhere option to create the complete WHERE clause.
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.
Identify processes
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.
View status and results in sp_ctrl
To view the status or results of a repair, use the repair status command in sp_ctrl.
- The basic command displays the processing status of the most recently started repair job, as well as any other jobs that have not yet finished.
- Additional options can be used to display a summary status for all repair jobs for which there is history, or to display detailed information about one job.
For more information, see repair status.
View warnings and errors
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:
- JobID is the SharePlex-assigned job ID.
- SID is the ORACLE_SID of the Oracle instance where the source table resides.
- ProcessID is the process ID of the sp_desvr process.
The names of the files written by the sp_declt process are declt_JobIDTableID_SID_SourceHost_pProcessID appended with either .log or .sql, where:
- JobID is the SharePlex-assigned job ID for the job.
- TableID is the SharePlex-assigned table ID for the table in the job.
- SID is the ORACLE_SID of the Oracle instance where the source table resides.
- SourceHost is the name or IP address of the source host.
- ProcessID is the process ID of the sp_declt process.
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.
Cancel a repair job
Use the cancel command to stop a running comparison or repair job.
sp_ctrl(sysA)>cancel JOBID
For more information, see cancel.
Manage compare history and logs
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.
Control the batch size
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.
Usage
| Supported sources: |
Oracle |
| Supported targets: |
Oracle |
| Authorization level: |
Operator (2) |
| Issues on: |
source system |
| Related commands: |
compare / compare using |
Syntax
| repair owner.source_table[.partition] |
[ at target_host@o.target_sid ] |
[ for o.source_sid ] |
[hint “hint”] |
[ {include | exclude} "column_list" ] |
[ insertonly ] |
[ key ] |
[ nosqllog ] |
[ not "exception_list" ] |
[ onepass ] |
[ orderby "column_list” ] |
[ parallelism degree ] |
[ port port_number ] |
[ 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 ] |
| repair using filename |
[ key ] |
[ onepass ] |
[ port port_number ] |
[ threads threads_count ] |
[ parallelism degree ] |
[ on host |
on host:portnumber |
on login/password@host |
on login/password@host:portnumber ] |
Syntax description
Required command components
| repair owner.source_table[.partition] |
The basic command repairs 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 repaired, 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)>repair scott.emp |
| repair using filename |
The basic command repairs all of the out-of-sync rows in the target tables listed in filename.
filename is the name of the file that contains the names of the source tables whose targets you want to repair.
Example
sp_ctrl(sysA)>repair using sales |
Optional command components
| at target_host@o.target_sid |
Valid for repair
Repairs only one of the target tables in a configuration where 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)>repair scott.emp at prod@o.prodsid |
| for o.SID |
Valid for repair
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)>repair scott.emp for o.oraA |
| hint "hint" |
Valid for repair
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)>repair scott.emp where “file >001005” hint “emp(salary)”
When running a repair from the command line of the operating system, quoted strings must have an extra set of escaped double quotes:
/productdir/bin/sp_ctrl repair scott.emp hint “\“emp(salary)\”” |
| {include | exclude} "(column_list)" |
Valid for repair
Filters the columns to be repaired.
- Use include to specify columns that you want to repair. No other columns are repaired. You must include all of the key columns in an include clause.
- Use exclude to repair all columns except those specified with exclude. Do not exclude any key columns.
(column_list) is the list of columns to include or exclude.
- Separate each name with a comma. No spaces are permitted in the list unless the name of a column contains spaces.
- Enclose the column list within double quote marks.
- List columns in any order. The sort is performed in ascending order.
- Column names are not case-sensitive.
- When used, this option must appear after the required arguments of the command, but it can appear in any order with other options.
Note: There could still be rows that are out-of-sync in the columns that were not repaired.
Example
sp_ctrl (SysA)>repair scott.emp exclude "color, weight" |
| insertonly |
Valid for repair
Repairs the target table for INSERT statements only.
Example
sp_ctrl(SysA)>repair scott.emp insertonly |
| key |
Valid for repair and repair using
Performs a fast compare and repair of large tables. This command does not compare all of the data values, but only compares one of the following:
If the key or orderby columns do not match, SharePlex repairs the entire row by deleting it and then inserting it again based on the source values.
Important! Use this option with caution. Even if key values match, it is possible for values in non-key columns to be out of synchronization.
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 repair on a SharePlex key definition. For more information about SharePlex key definitions, see the SharePlex Administration Guide.
Example
sp_ctrl (SysA)>repair scott.emp key
sp_ctrl(sysA)>repair using sales key |
| 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:
- The data contains sensitive information. The SQL log file is written in clear text. By not producing a SQL log file, the sensitive data is not persisted to disk, which may satisfy security requirements for data at rest, such as those required to meet PCI compliance standards.
- The compared or repaired tables have a very large number of out-of-sync rows. A log file of this size can consume a large amount of disk space.
|
| not “exception_list” |
Valid for repair
Specifies an exception list of tables not to repair when the table specification includes wildcards.
“exception_list” is a list of names of the tables not to repair.
- Use the owner.tablename format.
- Separate each name with a comma. No spaces are permitted in the list.
- Enclose the list within double quote marks.
- List the tables in any order.
- When used, this option must appear after the required arguments of the command, but it can appear in any order with other options.
Example
sp_ctrl(SysA)>repair scott.% not (%temp%) |
| onepass |
Valid for repair and repair using
Use this option to run a compare and repair concurrently. Use it for large out-of-sync tables.
Normally, a repair runs in two passes: first a compare, then a repair, which locks the target table. Both passes require a consistent view. With onepass, the target table is locked and repaired as soon as the compare client receives the consistent view marker.
Example
sp_ctrl(SysA)>repair scott.emp onepass |
| orderby “column_list” |
Valid for repair
Specifies columns for the repair process to use in its ORDERBY clause when it sorts rows to be compared. This option enables repairs 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.
- Separate each name with a comma. No spaces are permitted in the list unless the name of a column contains spaces.
- Enclose the column list within double quote marks.
- List columns in any order. The sort is performed in ascending order.
- Column names are not case-sensitive.
- When used, this option must appear after the required arguments of the command, but it can appear in any order with other options.
-
When running a repair from the command line of the operating system, quoted strings must have an extra set of escaped double quotes:
/productdir/bin/sp_ctrl repair scott.emp orderby “\“Last Name,Division\””
Example
sp_ctrl(SysA)>repair scott.emp where “file >001005” orderby “Last Name,Division” |
| parallelism degree |
Valid for repair and repair using
Adds a parallel hint to the SELECT statement. For degree, set the degree of parallelism.
Example
sp_ctrl(sysA)>repair scott.emp parallelism 4
sp_ctrl(sysA)>repair using sales parallelism 4 |
| port port_number |
Valid for repair and repair 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 |
| sourcewhere “clause” |
Valid for repair
Bases the repair on one or more columns in the source table when those columns do not exist in the target table.
- Enclose clause within double quote marks and refer to tables by their fully qualified names, for example scott.emp.
- Use double quote marks to enforce case sensitivity or spaces within a table name.
- Dates must be in the format of 'YYYYSMMDDHH24MISS'. To convert a date to that format, use the Oracle TO_DATE function. For example if c1 is a DATE column, the WHERE clause "c1 > '10-SEP-2001'" will not work, but "c1 > to_date('10- SEP-2001', 'DD-MON-YYYY')" will work.
-
When running a repair from the command line of the operating system, quoted strings must have an extra set of escaped double quotes, like the following example:
sp_ctrl> repair scott.emp sourcewhere “\“file >001005\””
- When used, this option must appear after the required command arguments, but it can appear in any order with other options.
Example #1:
sp_ctrl(sysA)>repair 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 repair process uses the sourcewhere clause, but both the source and target repair processes use the where clause.
sp_ctrl(SysA)>repair scott.emp sourcewhere “deptno = 200” where “mgr = ‘SMITH’” |
| targetwhere "clause" |
Valid for repair
Bases the repair on one or more columns in the target table when those columns do not exist in the source table.
- Enclose the clause within double quote marks and refer to tables by their fully qualified names, for example scott.emp.
- Use double quote marks to enforce case sensitivity or spaces within a table name.
- Dates must be in the format of 'YYYYSMMDDHH24MISS'. To convert a date to that format, use the Oracle TO_DATE function. For example if c1 is a DATE column, the WHERE clause "c1 > '10-SEP-2001'" will not work, but "c1 > to_date('10- SEP-2001', 'DD-MON-YYYY')" will work.
-
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 repair scott.emp targetwhere “\“file >001005\””
- When used, this option must appear after the required command arguments, but it can appear in any order with other options.
Example #1:
sp_ctrl(SysA)> repair 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 repair process will use the targetwhere clause, but both the source and target repair processes will use the where clause.
sp_ctrl(SysA)>repair scott.emp where “deptno = 200” targetwhere “mgr = ‘SMITH’” repair |
| threads thread_count |
Valid for repair and repair using
Sets the number of processing threads that are used by the repair process.
Example
sp_ctrl(sysA)>repair scott.emp threads 4
sp_ctrl(sysA)>repair using sales threads 4 |
| to target_owner.target_table [.partition] |
Valid for repair
Repairs only one of the targets of a source table. 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
(Repairs a partition)
sp_ctrl(SysA)>repair scott.emp to scott.allemp.east |
| where “clause” |
Valid for repair
Include a WHERE clause in the SELECT statement on both the source and target systems. The WHERE clause acts as a filter to repair specific rows.
For “clause” specify a standard WHERE clause that does not include subqueries.
- Enclose the clause within double quote marks and refer to tables by their fully qualified names, for example scott.emp.
- Use double quote marks to enforce case sensitivity or spaces within a table name.
- Dates must be in the format of 'YYYYSMMDDHH24MISS'. To convert a date to that format, use the Oracle TO_DATE function. For example if c1 is a DATE column, the WHERE clause "c1 > '10-SEP-2001'" will not work, but "c1 > to_date('10- SEP-2001', 'DD-MON-YYYY')" will work.
- 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)>repair scott.emp where “region=4” |
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 |
