When setting up SharePlex to work with an Oracle database, you provide the ORACLE_SID and then SharePlex gets the ORACLE_HOME from the oratab file on Unix/Linux. Both values are stored in the SharePlex environment. SharePlex uses the Oracle libraries that are in the location specified with ORACLE_HOME.
To determine the ORACLE_SID and ORACLE_HOME being used by SharePlex:
Issue the orainfo command in sp_ctrl.
sp_ctrl (mysysl11:2101)> orainfo
Oracle instance #1:
Oracle SID ora12
Oracle HOME /oracle/products/12
Oracle Version 12
Oracle instance #2:
Oracle SID ora12
Oracle HOME /oracle/products/12
Oracle Version 12
To determine the default ORACLE_SID and ORACLE_HOME on UNIX and Linux:
On most Unix and Linux systems the oratab file is under /etc/oratab. On Oracle Solaris systems, it is under /var/opt/oracle, but sometimes there is an oratab file in the /etc directory as well.
The entry in the file looks like the following example:
qa12:/qa/oracle/ora12/app/oracle/product/12.0
In the example, qa12 is the ORACLE_SID and /qa/oracle/ora12/app/oracle/product/12.0 is the ORACLE_HOME.
This chapter contains an overview of the SharePlex Compare and Repair feature. SharePlex provides this feature as built-in support for Oracle tables to help you maintain data that is synchronized between the source and target systems.
Contents
In addition to regularly monitoring the health and performance of replication, it is good practice to compare the source and target data on a regular schedule to ensure that all of the data is still synchronized. Post detects out-of-sync conditions for the rows that it is processing, but there can be hidden out-of-sync conditions. Examples of these are DML applied on the target or an incomplete backup restore. These conditions can go undetected until Post applies an operation that affects the out-of-sync row. The SharePlex Compare and Repair feature enables you to detect hidden out-of-sync conditions and then repair them.
Note: To understand how hidden out-of-sync conditions can occur, see Understand the Concept of Synchronization.
SharePlex provides the following commands for comparing and repairing out-of-sync data:
- compare: Compares an individual source table to its target table or compares a wildcarded set of tables in the same schema.
- compare using: Takes input from a file to compare some or all of the tables in the active replication configuration.
- repair: Repairs an individual target table or a wildcarded set of tables in the same schema.
- repair using: Takes input from a file to repair some or all of the tables in the active replication configuration.
Supported sources
Oracle
Supported targets
Oracle
Overview of the server and client processes
The compare and repair commands are always issued on the source system. The command spawns a server process on the source system and then sends a message through the SharePlex queues to spawn a client process on the target system.
The server and client processes then begin communication with each other. Depending on the syntax options included in the command, the processes may be multithreaded on the target. The two processes compare the source and target tables and then write the results to a log file.
How locks are managed
During a comparison, SharePlex obtains a brief exclusive lock on the source table to get read consistency for its row selection. On the target system, SharePlex obtains an exclusive lock on the target table and retains the lock for the duration of the comparison of that table. This prevents the table from being modified while SharePlex is processing it.
After locking the tables, the rows are read and sorted in identical fashion on both source and target. Next, a batch of rows is read and a check sum is performed. If the check sums match, another batch of rows is processed the same way. If any check sums do not match, the processes determine which rows are out-of-synchronization and then they create the SQL statements to repair them. If a repair command is issued, SharePlex repairs the rows.
Before running the compare or repair commands, review these guidelines.
- All of the SharePlex processes (Capture, Read, Export, Import, Post) must be running when you run a comparison or repair command.
- 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 compare 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.
- If a table is large, it will probably need to be sorted in the TEMP tablespace. Before running the compare or repair commands, the TEMP tablespace may need to be made larger. The size depends on the setting of the SP_DEQ_THREADS parameter or the threads option within the command syntax, both of which controls the number of processing threads used by SharePlex on the target. Each thread processes a table. At the default of two threads, the size of the tablespace should be larger than the sum of the sizes of the two largest tables. If you set the number of threads higher, then increase the size of the tablespace to accommodate a proportionate number of the largest tables. However,
- The UNDO tablespace may also need to be increased. Based on transaction volume and the length of time it takes to compare the largest table, increase the size of the UNDO tablespace and increase the undo_retention database parameter to avoid an ORA-1555 Snapshot too old error. Tables with LOBs take much longer to compare or repair than tables without them.
The following are commonly modified compare and repair parameters. Do not increase the values unless necessary. For details about these parameters, see their documentation in the SharePlex Reference Guide.
| SP_DEQ_MALLOC |
This parameter controls the fetch batch size. The batch size controls the number of rows that SharePlex selects at once for comparison. Larger batch sizes increase processing speed but require more memory. The value is divided equally by the number of compare threads to be used, and then the batch size is recalculated based on all column sizes added together. |
| SP_DEQ_PARRALLISM |
This parameter manages the select statement Degree of Parallelism hint. The parallelism option of the command overrides this setting.
|
| SP_DEQ_PART_TABLE_UPDATE |
This parameter controls how the repair commands work on Oracle partitioned tables, depending on whether row movement is possible. |
| SP_DEQ_READ_BUFFER_SIZE |
This parameter controls the size of the buffer that holds fetched LONG and LOB data and can be adjusted based on available system memory. |
| SP_DEQ_ROW_LOCK_THRESHOLD |
This parameter sets a threshold that controls whether SharePlex uses row-level or table-level locking when a where option is used. |
| SP_DEQ_SKIP_LOB |
This parameter determines whether or not LOBs are included in the compare/repair processing.
- When the parameter is set to the default of 0, the compare processes include LOBs in their processing.
- When the parameter is set to 1, only non-LOB columns are compared and repaired. If LOBs are not modified once inserted, you can speed up processing by setting this parameter to 1.
Set this parameter on the source system. |
| SP_DEQ_TIMEOUT |
This parameter sets a queue backlog threshold. High backlogs delay the establishment of a connection between the source and target compare/repair processes. If the backlog meets or exceeds this value, any compare or repair command that is issued on the source will exit and return an error. If this happens, consider running the compare or repair when the system is less busy. |
