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.
(missing or bad snippet)Supported operations
(missing or bad snippet)Not supported
(missing or bad snippet)Other conditions
(missing or bad snippet)How to use the repair commands
(missing or bad snippet)When to run the repair
(missing or bad snippet)Special use cases
The following scenarios require special handling when running a comparison.
| Use case | Compare support |
|---|---|
| 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 (missing or bad snippet) |
| Peer-to-peer replication | (missing or bad snippet) |
| Tables without keys | (missing or bad snippet) |
| 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:
|
| 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:
|
| 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 on page 1. |
| Repair operation on the XML data |
SharePlex displays the “ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT” error while performing the Repair operation on the XML data. Workaround:
|
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 on page 1.
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:
- If the repair command is being used, the sp_desvr selects the rows from the source tables, and sp_declt selects the rows from the target tables. The rows are sorted, compared and repaired.
- If the repair using command is being used, the sp_desvr process creates a number of processing threads on the target system. The value set by the SP_DEQ_THREADS parameter controls the number of threads created. Each thread spawns an sp_declt (client) process. The server and client processes establish direct communication with each other. The processing load is divided between the client processes. The rows from each source and target table are selected, sorted, compared, and repaired. The target tables are locked when it is their turn to be repaired, and then the lock is released.
-
Target tables are locked when it is their turn to be repaired, and then the lock is released.
- 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.
(missing or bad snippet)Run multiple processes
(missing or bad snippet)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
(missing or bad snippet)Identify processes
(missing or bad snippet)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
(missing or bad snippet)Manage compare history and logs
(missing or bad snippet)Control the batch size
(missing or bad snippet)Usage
| Supported sources: | Oracle |
| Supported targets: | Oracle |
| Authorization level: | Operator (2) |
| Issues on: | source system |
| Related commands: | compare / compare using |
Syntax
| Basic commands | Command options | Remote options |
|---|---|---|
| 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 ] | [ portport_number ] | [ sourcewhere “clause” ] | [ threads thread_count ] | [ targetwhere “clause” ] | [ to target_owner.target_table[.partition] ] | [ where “clause” ] |
(missing or bad snippet) |
| repair using filename |
[ key ] | [ onepass ] | [ port port_number ] | [ threads threads_count ] | [ parallelism degree ] |
(missing or bad snippet) |
Syntax description
Required command components
| Component | Description |
|---|---|
| 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 (missing or bad snippet). 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
| Component | Description |
|---|---|
| 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 or V$PARAMETER table. 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.
(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 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 (missing or bad snippet). 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: (missing or bad snippet) |
| 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.
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.
Example sp_ctrl(SysA)>repair scott.emp where “file >001005” orderby “Last Name,Division” |
| parallelismdegree |
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.
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.
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. compare source_owner.source_table.[source_partition] to target_owner.target_table.[target_partition] Example (Repairs a partition) sp_ctrl(SysA)>repair scott.emp.east to scott.allemp.alleast |
| 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.
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.
| 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 |