SharePlex 12.0 - Reference Guide

The SharePlex commands configure, start, stop, control, and monitor the replication process. SharePlex commands are issued through the sp_ctrl interface.

Compare / compare using

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.

• The compare command compares individual source and target tables or any number of tables in a given schema through the use of wildcards. When comparing individual tables, you can make use of column-based filtering to control the rows that are selected for comparison.
• The compare using command compares all of the tables listed in the active configuration or in another file that contains a subset of the tables in the active configuration.

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.

Supported operations

• SharePlex can detect and repair out-of-sync rows in a target table that are caused by DML operations: INSERT, UPDATE, DELETE.

• Compare/Repair can be executed on single or multiple instances that have the same Oracle client version, i.e., the same ORACLE_HOME.

Non-supported operations for Oracle as a source

SharePlex does not support (and will skip) the comparison and repair of the following:

• If a table has a hash partition, comparing or repairing using the partition name is not supported. The user can use the table name instead. However, if the sub-partition is hash-based and the root partition is not, the user can use either the table name or the root partition name. (This limitation is applicable only for Oracle to PostgreSQL replication)

• 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.

• XML type 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.

  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.

• If multiple ORACLE_HOME directories with different Oracle versions are installed on the same machine, users can perform compare/repair operations only for the Oracle version that was used during the SharePlex configuration.

See the SharePlex Release Notes for additional information about data types that are supported by compare and compare using.

Non-supported operations for PostgreSQL as a source

• Network configurations in which a pass-through server is used to pass data between the source and target servers.

• Comparisons and repairs are not supported in a cascading replication environment.

• 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.

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.

• (Applicable only for PostgreSQL source) To enhance the performance of the Compare process, SharePlex supports parallel hints via the SP_DEQ_PARALLELISM parameter, allowing you to specify the degree of parallelism, i.e., the number of worker processes to be launched.

  By default, the PostgreSQL database optimizer selects the best-suited query execution plan for SQL statements, which may not be directly related to the degree of parallelism specified in the parallel hints. pg_hint_plan makes it possible to adjust execution plans using parallel hints each time a query is executed. SharePlex internally utilizes the pg_hint_plan extension if it is installed or configured in the database.

  The example syntax to use parallel queries is as follows:

  The pg_hint_plan extension supports the Parallel hint: Parallel(table <# of workers> [soft|hard])

  SharePlex uses Parallel hints with hard parsing support: Parallel(table <# of workers> [hard])

  When performing table-level partitioning on DATE or TIMESTAMP data types in Oracle to PostgreSQL comparisons, the NLS_CALENDAR parameter must be set to GREGORIAN on the Oracle database.

  Note: The number of workers should be less than the CPU VCore of the database system.

• If the Oracle table is set for replication to multiple targets that include Oracle and PostgreSQL (or other non-Oracle) targets, the compare command will stop with the message: "Oracle to multiple targets detected! Use 'at target' or 'to table' Compare/Repair options to select only one of its targets." (Applicable only for Oracle as a source)

• It is expected that characters in the Oracle database are stored with the correct byte encoding, as per the character set specified in the NLS_CHARACTERSET (or NLS_NCHAR_CHARACTERSET for nchar, etc.). If the byte values are corrupted, Shareplex Compare/Repair will not be supported. For more information on setting NLS_LANG at the client or session level while inserting data into the DB, refer to https://docs.oracle.com/en/database/oracle/oracle-database/21/nlspg/setting-up-globalization-support-environment.html#GUID-372A5104-DC0A-41CA-B449-6AEC93F09C2E (Applicable only for Oracle as a source)

Supported Charactersets

The following character sets are tested and supported for Oracle database as a source:

• US7ASCII

• UTF8

• WE8ISO8859P1

• AL16UTF16

• AL32UTF8

Special use cases

The following scenarios require special handling when running a comparison.

How the comparison works

A comparison detects out-of-sync conditions in a target table that are caused by DML operations:

• extra or missing rows
• rows whose values do not match

When you run the compare or compare using command on the source system, SharePlex initiates the following events:

1. The sp_cop process spawns a sp_xdesvr (server) process on the source system.
2. The sp_desvr process returns control and use of the sp_ctrl interface to the user, and replication continues while the comparison proceeds.
3. The sp_xdesvr process sends a message to the Post process to initiate a sp_xdeclt (client) process on the target system.
4. The server and client processes establish direct communication with each other.
5. The processes perform a sanity check by comparing the number of rows in the source and target tables. If the difference in the number of rows between a source and target table is larger than 20 percent, the command terminates. The sanity check prevents wasting time to compare or repair tables that could be fixed more quickly by reloading the target data. You can use the copy or append command to reload the target data or use another method of your choosing. To override the sanity check, reissue the compare command with the override option.
6. To override the sanity check, reissue the Compare command with Override option.

7. If the row count passes the sanity check, the tables are compared as follows:

   • If the compare command is being used, sp_xdesvr selects the source rows, and sp_xdeclt selects the target rows, then the rows are sorted and compared.
   • If the compare using command is being used, the sp_xdesvr 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_xdeclt (client) process. The server and client processes establish direct communication with each other. The processing load is divided among the client processes. The rows from each source and target table are selected, sorted and compared.
8. When finished, the processes write their log files, and you can view the results with the show compare command.

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. (applicable only for Oracle as a source)
• 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.

Compare a subset of a configuration

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:

  Oracle: sp_ctrl> compare using config.active at prodsys@o.ora112

  PostgreSQL: sp_ctrl> compare using config.active at prodsys@r.database_name

• To compare a custom subset of the tables in a configuration, specify them in a compare file. This is a plain text file that lists only the source tables that you want to compare. The target tables are taken from the configuration file at the time of comparison. You can create a compare 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 compared

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:

  • Use a sourcewhere or targetwhere option only for the extra columns.

  • Use the standard where option for the other columns that have the same name on both source and target.

  • 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 comparison, use the compare status command in sp_ctrl.

• The basic command displays the processing status of the most recently started comparison job, as well as other comparison jobs that are still running.
• Additional options can be used to display a status summary for comparison jobs for which there is history, or to display detailed information about a comparison job.

For more information, see Compare status.

View warnings and errors for Oracle to Oracle replication

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 xdesvr_<jobid>_r.<dbid>_p<process id>.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 xdeclt_<jobid>-<tableid>_r.<dbid>_p<processid>.log 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:

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.

View warnings and errors for Oracle to PostgreSQL and PostgreSQL to PostgreSQL replication

The sp_xdesvr and sp_xdeclt 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_xdesvr process is xdesvr_<jobid>_r.<dbid>_p<process id>.log, where:

• JobID is the SharePlex-assigned job ID.
• DBID is the database ID of the PostgreSQL instance.
• ProcessID is the process ID of the sp_xdesvr process.

The names of the files written by the sp_xdeclt process are xdeclt_<jobid>-<tableid>_r.<dbid>_p<processid>.log 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.
• DBID is the database ID of the PostgreSQL instance.
• 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:

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.

Cancel a compare 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.

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.

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.

Note: To repair out-of-sync rows found by the comparison, use the repair or repair using command. See Repair / repair using.

Usage

Syntax

Syntax description

Required command components

Optional command components

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.

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. Target table (or Out-Of-Sync rows if less than threshold value) is locked during the repair process to correct any Out-of-Sync rows.

Supported operations

• SharePlex can detect and repair out-of-sync rows in a target table that are caused by DML operations: INSERT, UPDATE, DELETE.

• Compare/Repair can be executed on single or multiple instances that have the same Oracle client version, i.e., the same ORACLE_HOME.

Non-supported operations for Oracle as a source

SharePlex does not support (and will skip) the comparison and repair of the following:

• If a table has a hash partition, comparing or repairing using the partition name is not supported. The user can use the table name instead. However, if the sub-partition is hash-based and the root partition is not, the user can use either the table name or the root partition name. (This limitation is applicable only in Oracle to PostgreSQL replication)

• UDT inside VARRAY. 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.
• XML type 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.

  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.
• If multiple ORACLE_HOME directories with different Oracle versions are installed on the same machine, users can perform compare/repair operations only for the Oracle version that was used during the SharePlex configuration.

Non-supported operations for PostgreSQL as a source

• Network configurations in which a pass-through server is used to pass data between the source and target servers.

• Comparisons and repairs are not supported in a cascading replication environment.

• 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.

• If the Oracle table is set for replication to multiple targets that include Oracle and PostgreSQL (or other non-Oracle) targets, the compare command will stop with the message: "Oracle to multiple targets detected! Use 'at target' or 'to table' compare/repair options to select only one of its targets."

• When performing table-level partitioning on DATE or TIMESTAMP data types in Oracle to PostgreSQL comparisons, the NLS_CALENDAR parameter must be set to GREGORIAN on the Oracle database.

• It is expected that characters in the Oracle DB are stored with the correct byte encoding, as per the character set specified in the NLS_CHARACTERSET (or NLS_NCHAR_CHARACTERSET for nchar, etc.). If the byte values are corrupted, Shareplex compare/repair will not be supported. For more information on setting NLS_LANG at the client or session level while inserting data into the DB, refer to https://docs.oracle.com/en/database/oracle/oracle-database/21/nlspg/setting-up-globalization-support-environment.html#GUID-372A5104-DC0A-41CA-B449-6AEC93F09C2E

Supported Charactersets

The following character sets are tested and supported for Oracle database as a source:

• US7ASCII

• UTF8

• WE8ISO8859P1

• AL16UTF16

• AL32UTF8

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.

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:

1. The sp_cop process spawns a sp_xdesvr (PostgreSQL) or sp_desvr (Oracle) server process on the source system.
2. The sp_xdesvr (PostgreSQL) or the sp_desvr (Oracle) process returns control and use of the sp_ctrl interface to the user, and replication continues while the comparison proceeds.
3. The sp_xdesvr (PostgreSQL) or the sp_desvr (Oracle) process sends a message to the Post process to initiate a sp_xdeclt (PostgreSQL) or sp_declt (Oracle) client process on the target system.
4. The server and client processes establish direct communication with each other.

5. The row selection and repair proceeds as follows:

   • If the repair command is being used, the sp_xdesvr (PostgreSQL) or the sp_desvr(Oracle) 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_xdesvr (PostgreSQL) or the sp_desvr (Oracle) 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_xdeclt (PostgreSQL) or sp_declt (Oracle) 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.

6. 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 PostgreSQL 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 (Oracle source)

  sp_ctrl> repair using config.active at prodsys@r.dbid (PostgreSQL source)

• 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:

  • Use a sourcewhere or targetwhere option only for the extra columns.

  • Use the standard where option for the other columns that have the same name on both source and target.

  • 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 for Oracle to Oracle replication

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:

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.

View warnings and errors for Oracle to PostgreSQL and PostgreSQL to PostgreSQL replication

The sp_xdesvr and sp_xdeclt 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_xdesvr process is xdesvr_<jobid>_r.<dbid>_p<process id>.log, where:

• JobID is the SharePlex-assigned job ID.
• DBID is the database ID of the PostgreSQL instance.
• ProcessID is the process ID of the sp_xdesvr process.

The names of the files written by the sp_xdeclt process are xdeclt_<jobid>-<tableid>_r.<dbid>_p<processid>.log 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.
• DBID is the database ID of the PostgreSQL instance.
• SourceHost is the name or IP address of the source host.
• ProcessID is the process ID of the sp_xdeclt process.

Example log file names:

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

Syntax

Syntax description

Required command components

Optional command components

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.

The SharePlex utilities help you configure, test, and manage the SharePlex environment.

Contents