SharePlex 9.0 - Reference Guide

Read Parameters

These parameters are used by the SharePlex Read process.

SP_ORD_BATCH_ENABLE

This parameter controls the enabling of the Batch Processing functionality. By default it is enabled. This allows the Read process to combine multiple identical records into a single record, or into a batch, for processing by the Post process.

 

Default: 1 (on)

Range of valid values: 0 or 1 (flag)

Takes effect: when Read is restarted

 

SP_ORD_BATCH_MAX

This parameter controls the maximum number of concurrent batch transactions.

 

Default: 50 (transactions)

Range of valid values: any integer between (and including) 5 and 250

Takes effect: when Read is restarted

 

SP_ORD_BATCH_MATCH_MIN

This parameter controls the minimum number of matched operations before processing a batch.

 

Default: 2

Range of valid values: any positive integer

Takes effect: when Read is restarted

 

SP_ORD_CDA_LIMIT

This parameter controls the number of cursors cached by each login of the Read process. You might need to increase its value if replication starts falling behind Oracle activity on the source system. An initial setting of 15 cursors is recommended if you have a large number of tables in replication.

 

Default: 5 cursors

Range of valid values: any positive integer

Takes effect: when Read is restarted

 

SP_ORD_DATE_ MSG

This parameter can be set so that the Read process prints a warning message to the Event Log and the Read log when it detects an invalid date column. A setting of 0 disables the parameter, and a setting of 1 activates it. The error message generated by Read is:

An oerr#1801 has occurred on record with rowid rowid, on object object_id. Rec skipped. It is usually caused by invalid column data of type DATE. Creating a unique index that doesn't include column of type DATE and reactivating same configuration may solve the problem.

 

Default: 0 (do not print warning message)

Range of valid values: 0 or 1 (flag)

Takes effect: immediately.

 

SP_ORD_DELAY_RECORDS

The Read process processes records in batches. The size of the batches depends on the number of records in the capture queue. If the number of records is large, Read ignores the value of this parameter, and the batches are kept as small as possible. If Read is keeping pace with Capture, the size of the batch is approximately the value set by this parameter.

Set this parameter low if SharePlex is generating rollback segment too old messages in the Event Log. It instructs SharePlex to pass the data along sooner. This parameter is of use where only minimal latency can be tolerated.

Important: Use this parameter with caution, because reducing the number of records increases the I/O that SharePlex must perform, which increases system overhead. It can also negatively impact smooth interaction between SharePlex processes.

 

Default: 200 records

Range of valid values: any positive integer

Takes effect: Immediately

 

SP_ORD_FIRST_FIND

This parameter controls how the Read process checks column conditions to determine whether or not a replicated row change satisfies them.

• At the default of 1, when a row change satisfies a column condition, SharePlex does not check any other column conditions to see if that row change also satisfies any of them.
• At a value of 0, SharePlex sends the data to all target systems where the column conditions are satisfied.

For more information about horizontal partitioning, see the SharePlex Administration Guide.

 

Default: 1 (on)

Range of valid values: 0 or 1 (flag)

Takes effect: when Read is restarted

 

SP_ORD_HP_HASH

This parameter controls the number of slots in the hash table sued for Horizontal Partitioning. By default this parameter is set to 16 slots to minimize memory usage (usage is the number of slots x 32 bytes for every transaction with an insert). If the user’s system does a lot of insert operations followed by updates (in the same transaction) on a horizontally partitioned table then SharePlex will use this hash table a lot and this value may need to be increased for performance. Additionally, if the user system has a lot of long transactions with inserts on tables with horizontal partitioning the value of this parameter might need to be increased.

 

Default: 16 slots

Range of valid values: any positive integer

Takes effect: when Read is restarted

 

SP_ORD_HP_IN_SYNC

This parameter is used for horizontally partitioned replication to ensure that data is replicated properly when a value for a column in a column condition changes so that the row no longer satisfies the condition. It enables SharePlex to automatically correct the following:

• UPDATEs that cause a row to meet a different column condition than the one created for that row, sending the changes to a different location. An example would be an UPDATE to a row for which the column condition is region=East that changes the value of the region column to WEST. Such operations will fail because the original INSERT statement for that row was replicated to the original location (the Eastern region), so the row does not exist in the new location (the Western region) when Post attempts the update there.

• UPDATEs that cause a row to meet a column condition (and be replicated) when the row was not supposed to be replicated. An example would be when the region column is updated from the value of HEADQUARTERS (for which a row is not replicated) to the value of WEST. Such operations will fail because the original INSERT statement for that row (into the headquarters system) was not replicated to the Western region’s system, so Post cannot perform the update there.

• UPDATEs that cause a row to no longer meet any column condition. An example would be when the region column is updated from the value of WEST to the value of HEADQUARTERS. The original INSERT statement was replicated to the Western region’s system, but the update to the new value is not replicated, because the new value does not meet a column condition (headquarters data is not shared). The rows are now out of synchronization, but there are no errors.

When this parameter is enabled, SharePlex automatically corrects rows for which UPDATEs cause the preceding conditions. SharePlex converts the UPDATE to a DELETE and, if needed, an INSERT.

To convert an UPDATE statement (which normally only uses the changed columns and the key) to an INSERT statement, SharePlex needs values for all of the columns. Enabling SP_ORD_HP_IN_SYNC directs SharePlex to send all of the columns in a row to the Post process when there is an UPDATE to a table using horizontally partitioned replication, so that an INSERT can be constructed.

Set this parameter on the source system before you activate the configuration. If replication is active, set the parameter and then reactivate the configuration so that SharePlex can rebuild its object cache.

If you know that the columns in column conditions for tables using horizontally partitioned replication will never change, leave this parameter set to 0, because using it incurs processing overhead.

 

Default: 0 (off)

Range of valid values: 0 or 1 (flag)

Takes effect: when Read is restarted

 

SP_ORD_LDA_ARRAY_SIZE

This parameter controls the number of logins made to the database for read consistency. If the Read process slows down, try increasing the value of this parameter. The maximum setting is determined by the MAX_PROCESSES parameter in the init_ora file.

 

Default: 5 logins

Range of valid values: any positive integer

Takes effect: when Read is restarted

 

SP_ORD_ONELINE_DEBUG

This parameter controls the oneline debug feature for the Read process.

The oneline debug feature enables you to perform debugging for out-of-sync errors without consuming a large amount of the system resources. The oneline debug logs just enough information in one line to detect where in the data stream an out-of-sync condition occurred. Once oneline debug identifies the process that is causing the problem, you can then enable regular debugging for that process.

To enable this parameter, specify the objects that you want to debug by their object ID, and separate each one by a comma. An example is:

sp_ctrl> set sp_ord_oneline_debug 230230, 351626

The following items are logged:

• The state of the operation, which can be Dispatch (read from queue), processMessage (process the SQL code), or ExecSQL (apply to target)
• The transaction identifier used by SharePlex
• The type of DML or DDL operation that was affected
• The owner and name of the target table
• The object ID of the affected source table
• The row identifier of the affected row. (Oracle rowid or a placeholder consisting of the letter A for SQL Server tables, because SQL Server does not use row IDs.)
• The time when the operation occurred (Oracle) or the time when the operation was captured by Capture (SQL Server)
• The log sequence number and offset within the log of the affected operation
• The Oracle SCN or SQL Server LSN for the transaction
• An internal SharePlex code that prevents redundant operations
• The routing information

Note: This information is repeated for each target if the row is being routed to multiple targets.

 

Default: Disabled

Range of valid values: a string that forms a list of objects listed by object ID, separated by commas.

Takes effect: Immediately

 

SP_ORD_MSGS_CK_FREQ and SP_ORD_RCM_SKIP_RATIO

These parameters work together, so if one of them is set its default value of 0, then the other parameter, if set to a number other than 0, does not work. Both parameters support the automated process in which the Read process detects that too much time is being taken to process its queries, by checking the ratio of disk gets per executed queries. Once Read makes this determination, it gets rid of its existing read consistent views and replaces it with a new view.

The ORD_MSGS_CK_FREQ parameter is the frequency that the Read process checks if the ratio has been surpassed. For example, if you set this parameter to 100, the Read process will check every 100 queries to see if the ratio is still good. The recommended setting is 1,000 queries.

The ORD_RCM_SKIP_RATIO parameter specifies the number (the ratio of disk gets to executed queries) that when reached, causes the read consistent view to be replaced. The most sensitive setting is 1, which means one disk get per one executed query. The recommended setting is 2.

To turn off these parameters, set one of them to 0, which disables the other parameter.

 

Default:

SP_ORD_MSGS_CK_FREQ: 1,000

SP_ORD_RCM_SKIP_RATIO: 2

Range of valid values:

SP_ORD_MSGS_CK_FREQ: 0 to 100,000

SP_ORD_RCM_SKIP_RATIO: 0 to 1000 (not recommended to set this greater than 5)

Takes effect: Immediately

 

SP_ORD_RMSG_LIMIT

This parameter controls the frequency of the checkpoints performed by the Read process when it reads messages from the capture queue and determines the key values. A checkpoint saves the capture queue to a cache file, commits the outgoing queue messages (being passed to the export queue), and does a read release (delete) on the heldover records in the capture queue that have already been received by the export queue.

The higher the value of this parameter, the more records will be held in memory before the checkpoint is triggered. A very high value causes less I/O on the system and faster processing — but at the expense of increased memory usage and a longer recovery time should something unforeseen cause the Read process stop. A low value increases I/O, which increases the recovery speed but reduces throughput speed.

The default value of 1,000 records should establish a reasonable balance between the need for speed and the conservation of memory and process recovery, but you can adjust this parameter to suit your processing requirements. Adjustment options range from checkpointing after every record to holding as many records as the system and its memory can accommodate.

 

Default: 1,000 records

Range of valid values: any positive integer within system limitations

Takes effect: immediately

 

SP_ORD_ROLLBACK_TXNS_MAX

This parameter limits the number of entries the reader will keep in its transaction cache that indicate a transaction was completely rolled back

 

Default: 5,000 records

Range of valid values: any positive integer within system limitations

Takes effect: process restart

 

SP_ORD_SEND_DDL_TO_FIRST

This parameter helps improve Post performance when multiple post queues are in use and you are replicating DDL for objects that are not in the replication configuration (SP_OCT_REPLICATE_ALL_DDL=1).

This parameter directs Import to send DDL for objects that are not in the replication configuration to a specific post queue. Normally, DDL that is not related to objects in the replication configuration can be sent to any available queue. Large amounts of this DDL can block the DML of the objects that are in replication, causing data latency to increase.

This DDL, because it has no association with the objects in replication, does not have to be applied in any sequence relative to those objects. By routing it independently through a dedicated queue, you can free the other queues to process replicated data and its related DDL.

If you activate this parameter, you must specify the post queue through which to process the DDL for the non-replicating objects. This specification must be placed on the first line after the "Datasource:o.SID" line in the configuration file, as follows (the ! is a placeholder that replaces an actual object specification):

Datasource: o.dbprod

#Route for non-replication DDL

! sysmm:queddl1@o.dbprep

#The rest of the configuration entries

SCOTT.EMP SCOTT.EMP sysmm:que1@o.dbrep

SCOTT.FOO SCOTT.FOO sysmm:que2@o.dbrep

If you do not make an explicit designation in that manner, the DDL will be routed to the first route listed. For example, in the following configuration file, all DDL that is not associated with an object in replication will be sent to sysmm:que1@o.dbrep.

Datasource: o.dbprod

SCOTT.EMP SCOTT.EMP sysmm:que1@o.dbrep

SCOTT.FOO SCOTT.FOO sysmm:que2@o.dbrep

 

Default: 1 (on)

Range of valid values: 0 or 1 (flag)

Takes effect: immediately.

 

Export Parameters

These parameters are used by the SharePlex Export process.

SP_XPT_AES_KEY_LENGTH

This parameter controls the size of the AES encryption key that is generated by the create encryption key command. To configure SharePlex for AES encryption, see the SharePlex Administration Guide.

 

Default: 128-bits

Range of valid values: 128, 192 or 256 bits

Takes effect: After restarting Export

 

SP_XPT_ENABLE_AES

This parameter controls whether Advanced Encryption Standard (AES) is enabled. The Export parameter SP_XPT_AES_KEY_LENGTH controls whether encryption is performed and the size of the key. Export communicates these factors to the Import process.

For more information, see SP_XPT_AES_KEY_LENGTH.

To configure SharePlex for AES encryption, see the SharePlex Administration Guide.

 

Default: 0 (disabled)

Range of valid values: 0 or 1 (enabled)

Takes effect: After restarting Export

 

SP_XPT_ENABLE_COMPRESSION

This parameter controls the compression of data across TCP/IP.

You can enable compression to reduce the amount of data that SharePlex sends across the network. SharePlex uses LZIP lossless compression. Enabling compression on the source SharePlex instance automatically enables compression to all targets of the source SharePlex instance.

By default compression is disabled. You can enable compression by itself or in conjunction with encryption. For more information about encryption, see the SharePlex Administration Guide.

 

Default: 0 (disabled)

Range of valid values: 0 or 1 (enabled)

Takes effect: After restarting Export

SP_XPT_PORT_OVERRIDE

This parameter works in conjunction with the SP_XPT_USE_LOCALHOST parameter to enable SharePlex to send data through a secure tunnel port created with SSH® Secure ShellTM software. This parameter sets the local connection, which overrides the default SharePlex port. From that port number, the SSH daemon directs the connection to a different system (the SharePlex target machine) using another port number and the SSH data encryption.

 

Default: 0 (disabled)

Range of valid values: 0 or any positive integer

Takes effect: when Export is restarted

 

SP_XPT_SO_SNDBUF

This parameter tunes the TCP/IP window size on the source machine. It works in conjunction with the SP_COP_SO_RCVBUF parameter on the target machine to establish the size of a packet sent across the network. SharePlex references both parameters when TCP/IP sockets are created at the startup of sp_cop and the Export process.

If SharePlex is replicating across a WAN and the export queue is continually backlogged, try adjusting both parameters. SP_XPT_SO_SNDBUF must be set equal to or greater than the value of SP_COP_SO_RCVBUF, in multiples of 1024 bytes. To size the parameters, determine the ping time between the source and target machines, then use the following formula for both parameters:

For example, if ping time is 200 milliseconds, and the value for the two parameters is 64K, SharePlex will send five 64K-packets every second, totaling 320K per second.

Unless you observe a bandwidth problem, Quest recommends leaving both parameters set to their defaults, which use the system's setting. To change SP_XPT_SO_SNDBUF, set it on the source system, then stop and start Export on that system.

If transfer still is slow, try increasing the SP_IMP_WCMT_MSGCNT and SP_IMP_WCMT_TIMEOUT parameters on the target system. Set SP_IMP_WCMT_MSGCNT to at least 10,000.

 

Default: 0 (default is set by the operating system)

Range of valid values: positive integers, in bytes, using multiples of 1024. Maximum is set by the operating system.

Takes effect: when Export is restarted

 

SP_XPT_USE_LOCALHOST

This parameter enables SharePlex to send data through a secure tunnel port created with SSH® Secure ShellTM software. The Export process reads this parameter before making a TCP connection. If the parameter is enabled, the Export process connects to the local host through a local port number, where the SSH daemon directs the connection to a different system (the SharePlex target machine) using another port number and the SSH data encryption.

 

Default: 0 (disabled)

Range of valid values: 0 or 1 (flag)

Takes effect: when Export is restarted

 

Import Parameters

These parameters are used by the SharePlex Import process.

SP_IMP_QUEUE_PAUSE

This parameter pauses the writing of data to the post queue when that queue contains the specified number of messages. Post stores queue messages in shared memory until it issues a checkpoint, after which it releases the data from memory.

If the post queue runs out of shared memory, the read and write functions will start incurring file IO to free up the memory buffers. By pausing the queue writing, this parameter helps Post maintain its performance by avoiding the need for disk storage and the resultant slowdown in IO.

When Import is requested to write to a post queue, and the backlog for that queue is equal to or greater than SP_IMP_QUEUE_PAUSE, Import checkpoints with it associated Export and stops writing to any post queue. Import does, however, remain running. A warning is written to the event log and the status of the process in the show statusdb command output is shown as "paused." The status command shows "Paused" instead of "Running."

Import resumes writing to the post queue(s) if Import is stopped and restarted or Import detects that the backlog is less than or equal to the SP_IMP_QUEUE_RESUME parameter.

Use the SP_IMP_QUEUE_RESUME parameter to set the number of messages at which Import resumes writing to the post queue.

To use this feature, both SP_IMP_QUEUE_PAUSE and SP_IMP_QUEUE_RESUME must be greater than zero, and SP_IMP_QUEUE_PAUSE must be greater than SP_IMP_QUEUE_RESUME.

 

Default: 0 messages (disabled)

Range of valid values: n thousand messages, where n is any positive integer

Takes effect: immediately

 

SP_IMP_QUEUE_RESUME

This parameter works in conjunction with SP_IMP_QUEUE_PAUSE. If the number of messages in the post queue is lower or equal to the value set with this parameter, Import resumes writing to the post queue.

To use this feature, both SP_IMP_QUEUE_PAUSE and SP_IMP_QUEUE_RESUME must be greater than zero, and SP_IMP_QUEUE_PAUSE must be greater than SP_IMP_QUEUE_RESUME.

 

Default: 0 messages (disabled)

Range of valid values: n thousand messages, where n is any positive integer

Takes effect: immediately

 

SP_IMP_WCMT_MSGCNT

This parameter works in conjunction with the SP_IMP_WCMT_TIMEOUT parameter. It defines the number of messages that are processed before the Import process checkpoints. Checkpointing saves the state of the process in case it is needed for failure recovery.

When Import checkpoints, it triggers the Export process to perform its own checkpoint. If the value for this parameter is reached before the value set for SP_IMP_WCMT_TIMEOUT, it triggers the checkpoint.

In a WAN environment, you can increase this parameter to as many as 10,000 messages, with the understanding that increasing the message interval between checkpoints can negatively affect SharePlex's fault tolerance. If you are using SharePlex in an environment where the network continually fails, you can decrease this parameter.

 

Default: 10,000 messages

Range of valid values: any positive integer

Takes effect: immediately

 

SP_IMP_WCMT_TIMEOUT

This parameter works in conjunction with the SP_IMP_WCMT_MSGCNT parameter. It defines the number of seconds that pass before the Import process checkpoints. Checkpointing saves the state of the process in case it is needed for failure recovery.

When Import checkpoints, it triggers the Export process to perform its own checkpoint. If the value for this parameter is reached before the value set for SP_IMP_WCMT_MSGCNT, it triggers the checkpoint.

 

Default: 30 seconds

Range of valid values: any positive integer

Takes effect: immediately

Oracle Post Parameters

These parameters are used by the SharePlex Post process when applying data to an Oracle target.

SP_OPO_CHANGE_ID_START_VALUE

This parameter sets a start value for the changeid metadata column that can be included in a change history target and is set with the target command. The changeid is generated by SharePlex to uniquely identify records and prevent duplicates.

 

Default: 0

Range of valid values: 0 to 9223372036854775807

Takes effect: when Post is restarted

 

SP_OPO_COMMIT_REDUCE_MSGS

Sets the threshold for the Commit Reduction feature of Post. When the specified number of messages is reached, Post issues a commit. The commits of transactions whose transactional borders are within this span of messages are skipped, and those transactions are all committed as one transaction. Commit reduction is on by default. To disable it, set this parameter to a value of 1.

 

Default: 100 messages

Range of valid values: 1 or any positive integer.

Takes effect: when Post is restarted

 

SP_OPO_CONNECTION_POOL

This parameter controls how Post uses the connection pool.

When connection pooling is enabled (the default) and a subqueue needs a connection to Oracle, it will try to find a subqueue that is committed. If it finds one, it will take that connection rather than open a new connection to Oracle. This allows Post to operate with fewer connections to Oracle.

Without connection pooling, each subqueue has a separate connection to Oracle and will keep that connection for a number of seconds (determined by the parameter SP_OPO_IDLE_LOGOUT) until the subqueue is committed.

To use this parameter:

• Enabling connection pooling can be helpful if SharePlex is running with a large number of subqueues.
• If SharePlex is running with a small number of subqueues (less than 20) that get reused quickly, it may be more efficient to disable the pooling and not move the connections around.

 

Default: 1 (enabled)

Range of valid values: 0 or 1 (flag)

Takes effect: when Post is restarted.

 

SP_OPO_CONT_ON_ERR

This parameter controls whether or not Post stops when it encounters errors that can be corrected. When this flag is set to the default of 0, Post stops for all Oracle and SharePlex errors. To have Post continue posting despite certain SharePlex or Oracle errors, list them in the oramsglist file in the data subdirectory of the variable-data directory and set this parameter to 1. Post will always continue to post despite the following errors, regardless of the parameter setting:

Default errors for which Post will not stop

• unique key violation
• operation interrupted
• no data found
• no such table
• invalid number
• non-numeric in date
• invalid rowid
• invalid hex number
• cannot update not-null to null
• Year must be between -4713 and +9999
• check constraint violated
• packet writer failure*
• sequence not found
• Oracle internal error

*Packet writer failure and resource busy with nowait will retry based on the SP_OPO_RETRIES_MAX parameter which defaults to 10 after which poster will exit

For more information about how to configure Post to continue on errors, see the SharePlex Administration Guide.

 

Default: 0 (stop on all errors)

Range of valid values: 0 or 1 (flag)

Takes effect: immediately

 

SP_OPO_DEPENDENCY_CHECK

This parameter enables or disables the following features:

• The Commit Reduction and Transaction Concurrency features of Post (known together as Post Enhanced Performance, or PEP). For more information about how to tune the performance of Post, see the SharePlex Administration Guide.
• Post handling of enabled ON DELETE CASCADE constraints on target tables (apply the replicated parent delete and ignore the replicated cascaded deletes).

To support these features, do the following:

1. Set SP_OPO_DEPENDENCY_CHECK to the appropriate value:

   • To enable Commit Reduction and Transaction Concurrency, set this parameter to 1.
   • To enable ON DELETE CASCADE, set this parameter to 2. Note: The ON DELETE CASCADE feature is dependent on the Post Enhanced Performance feature, which is enabled with a setting of 2. In addition to setting SP_OPO_DEPENDENCY_CHECK, you must also enable the logging of primary keys, unique index columns, and foreign key columns on the source.
2. Set the SP_OCT_REDUCED_KEY parameter to 0, which sends all of the required data to the target for use by Post. See SP_OCT_REDUCED_KEY .
3. Set the SP_OPO_REDUCED_KEY parameter to 0, 1 (the default) or 2. See SP_OPO_REDUCED_KEY.

Default: 0 (off)

Range of valid values: 0, 1, 2 (flag)

Takes effect: when Post is restarted

 

SP_OPO_DEPENDENCY_SIZE

This parameter sets the size of the memory that is used by dependency checking in the Post Enhanced Performance feature. The default value should be sufficient, but you can increase it if needed.

 

Default: 7019

Range of valid values: Any valid memory value

Takes effect: when Post is restarted

SP_OPO_DISABLE_OBJECT_NUM

This parameter prevents Post from posting replicated DML and DDL operations to the target, based on the object ID of the source table. You can set this parameter if the data in a source table is invalid or corrupted, if the source table contains datatypes that are not supported on the target, or for any other reason that you do not want operations for a table to be reflected in the target database.

You can set this parameter for one or more tables. It prevents further replication activity on the target table(s) of a source table until you have time to resynchronize the data and reactivate the configuration file. The Post process discards all replicated messages for these tables from the post queue, and the messages do not accumulate in the queue.

Use the parameter with caution. If it is enabled and DDL or DML is executed for the source table(s), the target data will be out of date because the changes are not posted. If there are dependencies on the table(s), such as a foreign key in other tables outside the replication configuration, disabling posting will prevent the dependencies from being satisfied.

This parameter is disabled by default. To enable it, issue the following command on the target system, where the numbers shown are the object IDs of the source tables to exclude from posting.

Separate each object ID with a comma, and allow no spaces between them. To use spaces between entries, enclose the entire list within quotes, as in the following example:

sp_ctrl(sysB)> set param SP_OPO_DISABLE_OBJECT_NUM "498438, 1000, 497109")

Invalid object IDs will be ignored.

When you are ready to begin posting to the target table again, set SP_OPO_DISABLE_OBJECT_NUM to 0.

 

Default: 0 (off)

Range of valid values: list of valid Oracle Object IDs separated by commas

Takes effect: when Post is restarted

 

SP_OPO_HINTS_LIMIT

This parameter controls the maximum number of hints (table/index combinations) that can be listed in the hints file. Use hints only if you see that Post is doing full table scans on tables where there are defined indexes. Using a large number of hints can reduce the performance of the Post process. See the SharePlex Administration Guide for more information about the hints feature.

 

Default: 100 hints

Range of valid values: any positive integer

Takes effect: when Post is restarted

 

SP_OPO_LOG_CONFLICT

This parameter enables the logging of information about successful conflict resolution procedures to the SHAREPLEX_CONF_LOG table. This feature applies only to the prepared routines that are provided by SharePlex.

This parameter offers two active settings:

• A setting of 1 enables the logging of conflict resolution to the SHAREPLEX_CONF_LOG table.
• A setting of 2 enables the logging and also directs Post to query the target database to get the current timestamp of the target row. Note: A setting of 2 may affect the performance of Post as a result of making the query.

 

Default: 0 (Disabled)

Range of valid values: 0, 1, or 2

Takes effect: when Post is restarted

 

SP_OPO_MAX_CDA

This parameter is for the SQL cache module and controls the max number of cursor caches that a session can concurrently open. This number must be smaller than the Oracle setting for OPEN_CURSORS in v$parameter view. It is only used when SP_OPO_SQL_CACHE_DISABLE is set to 0 (enabled).

 

Default: 50

Range of valid values: any positive integer

Takes effect: when Post is restarted

 

SP_OPO_MAX_OEXN_TIME

This parameter controls the maximum duration, in seconds, that a SQL thread may spend in a call to OCIStmtExecute ( ) before the timekeeper thread defaults to a deadlocked position and forces the Multi-threaded Post process to exit.

 

Default: 900 [seconds]

Range of valid values: any positive integer

Takes effect: when Post is restarted

 

SP_OPO_MAX_ROLLBACK_MESSAGES

This parameter is the maximum number of messages to which the backward count is incremented for a partial rollback. The main thread scans forward and counts the backward messages. Once the backward count reaches the maximum number set for this parameter, the main thread resolves the rollback. When the rollback is resolved, the main thread resumes dispatching messages. If there are more backward messages, the main thread again scans forward in the subqueue. The next group of 10,000 backward messages (if the value for the parameter is set to 10,000) is then resolved, using the same procedure.

 

Default: 10,000 messages

Range of valid values: 11 - 1,000,000

Takes effect: when Post is restarted.

 

SP_OPO_NLS_CONVERSION

This parameter controls character set conversion between an Oracle source and an Oracle target.

For SharePlex to replicate all characters within the Oracle character sets that you are using, one of the following must be true:

• The character sets are identical on the source and target
• The character set of the source database is a subset of the character set of the target database (all characters contained on the source exist in the character set of the target)

The following character sets are tested and supported for SharePlex:

US7ASCII

UTF8

WE8ISO8859P1

AL16UTF16

AL32UTF8

KO16KSC5601

By default, SharePlex allows an Oracle target database to perform character conversion. Post notifies Oracle of the character encoding of the source data and Oracle performs any required conversion.

Depending on the character sets involved, the Oracle conversion might lead to data loss. For example:

Example 1: The Japanese character for 'rice' in the JA16SJIS character set has no corresponding symbol in the US7ASCII character set. If you attempt to replicate this symbol into a US7ASCII database, Oracle converts it to a '?' character.

Example 2: According to Oracle, the WE8ISO8859P1 character set is a superset of the US7ASCII character set, so it is logical to assume that any character in US7ASCII is posted unconverted into a WE8ISO8859P1 target database. This is true for characters in the range 0x00 to 0x7F. However, Oracle strips off the top bit of characters in the range 0x80 to 0xFF. This "conversion" may result in data loss while replicating to a character set that is a superset of the source.

Note: Oracle does not convert characters if the character sets are identical. Thus, posting WE8ISO8859P1 data to a database with a character set of WE8ISO8859P1 bypasses the Oracle conversion process.

To apply data without conversion

Set the SP_OPO_NLS_CONVERSION parameter to 1 to apply the data without conversion.

Note: SharePlex will always convert NVARCHAR and NCLOB data if the NLS_NCHAR_CHARACTERSET of the source database is not the same as that of the target database.

 

Default: 1

Range of valid values: 0 or 1 (flag)

Takes effect: when Post is restarted.

 

SP_OPO_NLS_DEFAULT_COMPAT

This parameter controls whether Post converts character data (CHAR, VARCHAR). By default no conversion is performed. When set to 7, Post will not convert character data, but will convert CLOB data. The compare/repair commands will compare and repair character data without conversion and CLOB data with conversion. The replication of NCHAR data is not affected.

SP_OPO_NLS_DEFAULT_COMPAT takes precedence over SP_OPO_NLS_CONVERSION.

 

Default: 0

Range of valid values: 0 or 7

Takes effect: when Post is restarted.

 

SP_OPO_OBJID_DEBUG

This parameter allows debugging for one specific object, such as a table, for Post. The SP_OPO_OBJID_DEBUG parameter is typically used for debugging out-of-syncs. To use the SP_OPO_OBJID_DEBUG parameter, set its value on the target system to the value of the object ID for the object from the source database.

 

Default: 0

Range of valid values: Any number greater than or equal to 0

Takes effect: immediately

 

SP_OPO_ONELINE_DEBUG

This parameter controls the oneline debug feature for the Post process.

The oneline debug feature enables you to perform debugging for out-of-sync errors without consuming a large amount of the system resources. The oneline debug logs just enough information in one line to detect where in the data stream an out-of-sync condition occurred. Once oneline debug identifies the process that is causing the problem, you can then enable regular debugging for that process.

To enable this parameter, specify the objects that you want to debug by their object ID, and separate each one by a comma. An example is:

sp_ctrl> set sp_opo_oneline_debug 230230, 351626

The following items are logged:

• The state of the operation, which can be Dispatch (read from queue), ProcessMessage (process the SQL code), Skipped (commit if the Commit Reduction feature is enabled) or ExecSQL (apply to target)
• The transaction identifier used by SharePlex
• The type of DML or DDL operation that was affected
• The object ID of the affected source table
• The row identifier of the affected row. (Oracle rowid or a placeholder consisting of the letter A for SQL Server tables, because SQL Server does not use row IDs.)
• The time when the operation occurred (Oracle) or the time when the operation was captured by Capture (SQL Server)
• The log sequence number and offset within the log of the affected operation
• The Oracle SCN or SQL Server LSN for the transaction
• An internal SharePlex code that prevents redundant operations

 

Default: Disabled

Range of valid values: a string that forms a list of objects listed by object ID, separated by commas.

Takes effect: Immediately

 

SP_OPO_OUT_OF_SYNC_SUSPEND

This parameter controls what SharePlex does when it encounters an out-of-sync condition, based on the following:

• INSERT: The row already exists, resulting in a Unique constraint violation error.
• UPDATE and DELETE: The row is not in the database.

Value of 0 (default)

The default Post behavior when a transaction contains an out-of-sync operation is to continue processing other valid operations in the transaction to minimize latency and keep targets as current as possible. Latency is the amount of time between when a source transaction occurs and when it is applied to the target. Different factors affect the amount of latency in replication, such as unusually high transaction volumes or interruptions to network traffic.

Post logs the SQL statement and data for the out-of-sync operation to the ID_errlog.sql log file, where ID is the database identifier. This file is in the log sub-directory of the variable-data directory on the target system.

Value of 1

A value of 1 directs Post to stop posting when it encounters an out-of-sync condition. Post logs the SQL statement and data for the out-of-sync operation to the ID_errlog.sql log file, and then stops.

When you set this parameter to a value of 1, check the status of the replication processes frequently. Stopping Post causes latency between source and target databases, and it causes data to accumulate in the replication queues, which could cause them to exceed available disk space. SharePlex provides several sp_ctrl commands for checking replication status, and it provides tools for unattended SharePlex monitoring. See the SharePlex Administration Guide for more information about how to monitor SharePlex.

See also SP_OPO_SAVE_OOS_TRANSACTION to configure Post to roll back the entire transaction if it contains any out-of-sync operations.

 

Default: 0 (do not stop for out-of-sync conditions)

Range of valid values: 0 or 1 (flag)

Takes effect: immediately

 

SP_OPO_POSTER_DELAY

This parameter controls the amount of time that the Post process delays before it posts records to the target instance. Normally, Post applies the changes immediately to minimize latency between the source and target systems, but you can delay posting for up to 5 days (7200 minutes).

Delaying posting helps protect the data against accidental loss caused by unwanted deletes or object drops on the source system. The delay gives you enough time to detect the mistake and retrieve the data from the target instance before the mistake is replicated. Running “what-if” analyses is another reason you could change the default of this parameter. Having the target database behind in time enables you to validate predictive modeling compared to the real thing.

The delay caused by SP_OPO_POSTER_DELAY is measured from the time the message first appears in the redo logs. Things to consider when using this parameter include the following:

1. Make certain that there is enough disk space for the data to collect in the post queue for the designated time interval until it is applied to the database.

2. To determine when to start posting based on the parameter’s setting, SharePlex compares the target system’s current timestamp with the time that a record enters the redo log on the source system. Those machines could be in different locations, perhaps thousands of miles from each other. Consider any difference between time zones, and add that to the delay time.

   For example, there is an 18-hour time difference between Los Angeles, California, U.S.A., and Sydney, Australia. To delay posting for five hours, you would need to set SP_OPO_POSTER_DELAY to 23 hours (1380 minutes) to account for the desired five-hour delay plus the 18-hour time difference. Also consider whether or not a location observes Daylight Savings Time, which could change the time difference between two locations. The way that Daylight Savings Time is observed varies among, and even within, nations that use it.

3. Do not use this parameter going back in time, for example when a source system is in Sydney and a target system is in Los Angeles. To SharePlex, this as a negative time difference when the time stamps on each system are compared. For example, 2:00 in the morning on April 2 in Sydney is 8:00 in the morning on April 1 in Los Angeles. The parameter does not accommodate settings of less than 0.

 

Default: 0 minutes

Range of valid values: 0 to 7200 minutes

Takes effect: immediately

 

SP_OPO_PRB_MISMATCH_SUSPEND

This parameter, when set to 1, will cause Post to stop when there is a mismatch on a partial rollback. This allows the problem to be investigated and resolved before Post resumes processing.

 

Default: 0 (off)

Range of valid values: 0 or 1 (flag)

Takes effect: immediately

 

SP_OPO_READRELEASE_INTERVAL

This parameter controls how often Post read/releases. Normally, Post performs a read/ release after it receives each COMMIT, which means it purges that transaction's data from the queue as part of the checkpoint recovery process. For smaller transactions, this causes excessive I/O on the target system and slows Post. If most transactions are small, you can set this parameter so that Post read/releases after a certain number of messages instead of after every COMMIT.

When you set a read/release interval, be aware that if new data does not follow a committed transaction (for example, if it was the last transaction of the day or there is no more user activity), Post processes the COMMIT and waits a certain amount of time, which is controlled by the internal SP_OPO_IDLE_LOGOUT parameter. If no data arrives, Post performs the read/release even though the SP_OPO_READRELEASE_INTERVAL interval is not satisfied. The number of messages in the post queue reduces to 0, indicating that Post is finished processing all messages from the queue.

Note: Because the Post process has multiple threads, the number of messages is associated with each thread instead of the queue. Thus, the read/release interval can be longer than expected, and you might need to lower the default value.

 

Default: 100

Range of valid values: any positive integer

Takes effect: immediately

 

SP_OPO_REDUCED_KEY

This parameter controls the content of the Post WHERE clause. Post uses a WHERE clause to find the row in the target that needs to be changed by a replicated UPDATE from the source. Different SharePlex features may require more or less data to be used in the Post WHERE clause.

• If set to 0, this parameter directs Post to construct a WHERE clause of all of the data that is sent by Capture. The data that Capture sends depends on the setting of the SP_OCT_REDUCED_KEY parameter. If you want the WHERE clause to include the values of the keys and all of the columns other than LONGs or LOBs, set both parameters to 0.

  IMPORTANT! In a peer-to-peer configuration, a setting of 0 is required.

• If set to 1, this parameter directs Post to build a WHERE clause with the key values and the before values of the columns that changed. This setting provides a good balance between replication performance and target data integrity, because it enables Post to perform a before-and-after comparison of the changed columns when constructing the WHERE clause. To be able to use this setting, the SP_OCT_REDUCED_KEY parameter must be set to 0 or 1. For more information about how SharePlex uses before and after values, see the SharePlex Administration Guide.
• If set to 2, this parameter directs Post to build a WHERE clause of only the key columns. This setting can be used to maximize posting performance. Because this setting omits the before-and-after comparison of the changed columns, you should ensure that no process or user can make changes to the target data except SharePlex. Additionally, it is recommended that you perform regular integrity verification by using the compare command.

See also SP_OCT_REDUCED_KEY .

 

Default: 1

Range of Valid Values: 0, 1, 2

Takes effect: When Post is restarted

 

SP_OPO_RETRIES_MAX

This parameter controls the number of times that the Post process attempts to post a SQL statement that failed the first time.

Post will retry certain failed operations when there is the possibility that they will succeed with another attempt. The main operations that Post will retry are TNS write failures, connection failures, or locks on tables when Post needs to apply a TRUNCATE.

To increase the likelihood that the failed operations are successful, you can increase the SP_OPO_RETRIES_MAX parameter so that Post tries the operation more times. At the same time, increase the SP_OPO_RETRY_DELAY_TIME parameter to increase the time interval between the attempts. That gives the lock or other blocking operation enough time to be resolved between attempts.

If the Post process is set to continue on error (SP_SYS_SUSPEND_ON_ERROR=0) or if the error message is listed in the oramsglist file, Post moves on to the next transaction in the queue. In all other cases, Post stops after it reaches the maximum allowed attempts.

Note: for more information about the oramsglist file, see the SharePlex Administration Guide.

Important: Reducing this parameter can cause the data to accumulate in the queues, possibly causing them to exceed the available disk space.

 

Default: 10 times

Range of valid values: 0 or any positive integer

Takes effect: immediately

 

SP_OPO_SAVE_OOS_TRANSACTION

This parameter controls whether or not Post rolls back and discards a transaction if it contains any out-of-sync operations. This functionality is controlled by the SP_OPO_SAVE_OOS_TRANSACTION parameter.

How to use this parameter

When this parameter is set to 1 and a transaction contains any operations that generate out-of-sync errors, Post discards the entire transaction and saves all the operations of that transaction to a SQL file. You can edit this file to repair the problem with the failed SQL and then run the file to apply the transaction to the target database.

When this parameter is set to 1, Post will continue to process valid transactions that follow a rolled back transaction by default. This is to prevent target latency. To configure Post to stop after rolling back a transaction, set the SP_OPO_OUT_OF_SYNC_SUSPEND parameter to 1.

Important!

This parameter should only be used if you know that your applications make all interdependent changes within one transaction.

All of the tables involved in the transaction that is being rolled back must contain only the following data types:

• CHAR – US7ASCII
• VARCHAR – US7ASCII
• NUMBER
• DATE
• TIMESTAMP
• TIMESTAMP WITH TIME ZONE
• TIMESTAMP WITH LOCAL TIME ZONE
• INTERVAL
• ROWID
• RAW
• BINARY FLOAT
• BINARY DOUBLE

Each rolled back transaction has its own SQL file. The file name is SCN_queue.sql, where:

• SCN is the commit System Change Number (SCN) of the transaction.
• queue is the name of the Post queue that contains the transaction.

Example file name:

4346118046_postq1.sql

Note:

Each SQL file is stored in the oos subdirectory of the SharePlex variable-data directory. You may need to change the location of the files if you use the ora_cleansp utility to re-initiate the SharePlex environment. This utility deletes the transaction files when you run it. You can modify the location, size, and number of SQL files by using any of the options of the file category of the target command.

Notes:

• When this feature is enabled, Post writes the whole transaction to the SCN_queue.sql file, instead of writing only the out-of-sync portion of the transaction to the errlog.sql file.
• This feature supports Oracle targets only.
• This feature is not supported on the HP-UX platform.
• Post will still generate out-of-sync messages in the statusdb which will show when the show statusdb command is issued in sp_ctrl. You can use the clear status command to clear these status messages after you apply the transaction using the SQL file.
• A rolled back transaction cannot be "un-rolled back."

 

Default: 0 (off)

Range of valid values: 0 or 1 (flag)

Takes effect: when Post is restarted

 

SP_OPO_SQL_CACHE_DISABLE

This parameter enables or disables the SQL Cache feature. By default, SQL Cache is on, and this parameter is set to 0. A setting of 1 disables SQL Cache. A setting of 3 disables SQL Cache for batch operations to reduce the amount of memory that Post uses. (A setting of 2 is not available.)

If you disable SQL caching, SharePlex prints the following message to the Event Log: SQL Cache disabled.

For more information about SQL Cache, see the SharePlex Administration Guide.

 

Default: 0 (on)

Range of valid values: 0, 1, or 3

Takes effect: when Post is restarted

 

SP_OPO_STOP_ON_DDL_ERR

This parameter controls whether or not the Post process stops when there is an error applying DDL. The default of 1 directs Post to stop for errors. An error usually indicates that the source component for which the DDL was executed does not exist in the target database, indicating the likelihood that subsequent DML changes will also fail. Stopping Post prevents the DML failures and enables you to correct the problem to keep the databases synchronized.

 

Default: 1 (on)

Range of valid values: 0 or 1 (flag)

Takes effect: immediately

 

SP_OPO_SUPPRESS_DELETE

This parameter controls whether or not Post applies DELETE operations to the target. Suppressing DELETE operations may be appropriate in situations such as a data warehouse, where a row must exist centrally even though it is deleted from its source table. You can set and unset this parameter without modifying or activating a configuration file. Set it to 1 to suppress DELETES.

 

Default: 0 (off)

Range of valid values: 0 or 1 (flag)

Takes effect: immediately

 

SP_OPO_SUPPRESSED_OOS

This parameter controls whether Post returns an out-of-sync message when it detects that a target row already contains the changes that are being replicated from the source. If this condition is met, Post discards the replicated SQL and does not write an out-of-sync message to the Event Log or the errlog.sql log file.

This parameter supports INSERT and UPDATE operations in the following cases:

• All values (including the key value) of a replicated INSERT match the existing values of a row in the target.
• The key value of an UPDATE matches a row in the target and the existing values in that row match the after (change) values that were replicated from the source. Note: SharePlex will return an out-of-sync message if the target values do not match the replicated after values.

By default this parameter is enabled. However, you should still use tools that verify all of the target data, such as the compare command, because out-of-sync values can exist that are not detected by Post.

 

Default: 1

Range of valid values: 0 (disabled) or 1

Takes effect: Immediately

 

SP_OPO_SYNC_LOG_FREQUENCY

This parameter controls how often Post logs out-of-sync messages to the event log (event_log file in the variable-data directory). When Post detects an out-of-sync condition, it generates an out-of-sync event. If target target table is so out-of-sync that the event log is filling up with these messages, you can use this parameter to control how frequently these messages are logged.

When this parameter is greater than 0, Post logs the first out-of-sync error and then logs out-of-sync messages only at the specified interval. Post continues posting valid data until the out-of-sync table can be re-synchronized.

 

Default: 1 (log every out-of-sync message)

Range of valid values: Integer greater than 0

Takes effect: Immediately

 

SP_OPO_THREADS_MAX

This parameter is used primarily in testing to view the behavior of a SQL thread when it is handling several subqueues. The minimum number of threads is four, which enables the Post process to create the main thread, the timekeeper thread, the signal waiter thread, and one SQL thread.

 

Default: 65 threads

Range of valid values: 4 threads (minimum) to 128 threads (maximum)

Takes effect: When the Post process is restarted

 

SP_OPO_TRACK_COMMITS

This parameter controls whether or not the Post process tracks commits. If it is 1, Post will also insert a row for every commit.

 

Default: 0 (off)

Range of valid values: 0 or 1 (flag)

Takes effect: When the Post process is restarted

 

SP_OPO_TRACK_OPERATIONS

This parameter controls which DML is tracked by Post when maintaining a change history target database. By default, Post tracks all DML types. For example, to configure Post so that only inserts and updates are tracked, set the parameter to I/U.

 

Default: I/U/D (inserts, updates and deletes)

Range of valid values: Any combination of I, U, or D separated by a slash (/)

Takes effect: When the Post process is restarted

 

SP_OPO_TRACK_PREIMAGE

This parameter controls whether or not the Post process tracks the before image of inserts and updates or the after image of deletes. The before image for an insert and the after image for a delete includes the key values, with all other columns set to null.

You can set this parameter to I, U, or D, or any combination of those values separated by slashes, for example: I/U. When this parameter is used, Post applies two rows for each operation of the specified type. One has the column values of the before image, and the other has the values of the after image.

 

Default: off

Range of valid values: I, U, D

Takes effect: When the Post process is restarted

 

SP_OPO_TRUSTED_SOURCE

This parameter specifies the trusted source system, which contains the data that is considered to be the primary set of data in a peer-to-peer replication environment. The trusted host can be used in custom conflict resolution routines and is also the basis of one of the prepared routines provided with SharePlex. For more information about conflict resolution and peer-to-peer replication, see the SharePlex Administration Guide.

Default: None

Range of valid values: Any valid host name in the SharePlex replication environment

Takes effect: When the Post process is restarted

 

SP_OPO_UPDATE_SCN

This parameter controls the tracking of Oracle SCNs and allows SharePlex to recover quickly when operating in a high-availability cluster. When this parameter is enabled, SharePlex tracks SCNs in its internal transaction table. This enables SharePlex to activate to the lowest applied transaction and then reconcile to the correct transaction when there is a failover.

Note: Enabling this parameter disables the commit reduction feature of the Post Enhanced Performance feature. That feature is not supported when tracking SCNs.

• Use a value of 1 if both the source and target are mirrored. In this case, pre-failover replication is from the primary source to the primary target, but not from the primary source to the secondary target. For this scenario, Post disables the commit reduction feature of the Post Enhanced Performance (PEP) feature.
• Use a value of 2 if only the source is mirrored with DataGuard. In this case, pre-failover replication is from the primary source to both the primary target and the secondary target. In that case, commit reduction does not need to be disabled, and a setting of 2 allows it to continue.

 

Default: 0

Range of valid values: 0 (disabled) or 1 (enabled)

Takes effect: when Post is restarted

 

SP_OPO_USE_VARNUM

This parameter causes Post to use a varnum to enter numbers into the target database. It allows non-standard high precision values to be entered into a number column in the database.

 

Default: 0 (Off)

Range of valid values: 0 or 1 (flag)

Takes effect: when Post is restarted

 

SP_OPO_WAIT_MSG_DELAY

This parameter controls how long Post waits before generating a message indicating that it is killing old or stalled Oracle sessions. Those messages occur at the startup of Post.

 

Default: 300 seconds

Range of valid values: any positive integer up to 86400

Takes effect: when Post is restarted