These parameters are used by the SharePlex Post process when applying data to an Oracle target.
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
Sets the threshold for the Commit Reduction component of the Post Enhanced Performance feature. The Commit Reduction feature enables you to configure Post to combine smaller transactions into larger ones. This reduces the number of commits and acknowledgments that must be processed. The smaller the transaction, the bigger the performance gain.
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.
The parameter setting is not an absolute threshold. SharePlex will not break up a transaction across different combined transactions. Therefore, Post may need to exceed that threshold in order to include all of the operations and the commit of the last transaction in the group.
Default: 100 messages
Range of valid values: 1 or any positive integer.
Takes effect: when Post is restarted
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:
Default: 1 (enabled)
Range of valid values: 0 or 1 (flag)
Takes effect: when Post is restarted.
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. If this parameter is set to 2, Post will treat table errors the same as other errors and stop unless the error is listed in the oramsglist file.
Post will always continue to post despite the following errors, regardless of the parameter setting:
Default errors for which Post will not stop
*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, 1, 2 (flag)
Takes effect: immediately
This parameter controls the following features:
To support these features, do the following:
Set SP_OPO_DEPENDENCY_CHECK to the appropriate value:
Default: 0 (off)
Range of valid values: 0, 1, 2 (flag)
Takes effect: when Post is restarted
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
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 data types 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.
sp_ctrl(sysB)> set param SP_OPO_DISABLE_OBJECT_NUM object 12345,67890
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
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
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 2 enables the logging to the SHAREPLEX_CONF_LOG table and also directs Post to log the following to the Post process log:
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
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
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
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.
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 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.
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.
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
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:
Default: Disabled
Range of valid values: a string that forms a list of objects listed by object ID, separated by commas.
Takes effect: Immediately
This parameter controls what SharePlex does when it encounters an out-of-sync condition, based on the following:
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
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:
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.
Default: 0 minutes
Range of valid values: 0 to 7200 minutes
Takes effect: immediately
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
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
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.
See also SP_OCT_REDUCED_KEY .
Default: 1
Range of Valid Values: 0, 1, 2
Takes effect: When Post is restarted
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
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.
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:
Each rolled back transaction has its own SQL file. The file name is SCN_queue.sql, where:
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:
Default: 0 (off)
Range of valid values: 0 or 1 (flag)
Takes effect: when Post is restarted
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
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
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
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, UPDATE, and DELETE operations in the following cases:
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
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
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
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
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
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
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
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.
Default: 0
Range of valid values: 0 (disabled) or 1 (enabled)
Takes effect: when Post is restarted
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
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
These parameters are used by the SharePlex Post process when applying data to an Open Target (non-Oracle) target.
To maximize performance when applying multiple transactions of the same operation, the Post process can group the transactions and apply as a batch. The SP_OPX_BATCH_ENABLE parameter controls this feature and is enabled by default. SP_OPX_MAX_BATCH_MBYTES controls the maximum size of the batch.
Default: Enabled
Range of valid values: 0 or 1
Takes effect: When Post is restarted
Sets the threshold for the Commit Reduction component of the Post Enhanced Performance feature. The Commit Reduction feature enables you to configure Post to combine smaller transactions into larger ones. This reduces the number of commits and acknowledgments that must be processed. The smaller the transaction, the bigger the performance gain.
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.
The parameter setting is not an absolute threshold. SharePlex will not break up a transaction across different combined transactions. Therefore, Post may need to exceed that threshold in order to include all of the operations and the commit of the last transaction in the group.
Default: 100 messages
Range of valid values: Any positive integer.
Takes effect: when Post is restarted
This parameter controls whether or not Post stops when it encounters specific ODBC errors. When this flag is set to the default of 0, Post stops for all ODBC errors. To configure Post to ignore certain errors and continue processing, list them in one of the following files (depending on the database) and then set SP_OPX_CONT_ON_ERR to 1.
hanamsglist
mysqlmsglist
postgresmsglist
sqlservermsglist
sybasemsglist
tdmsglist
These files are located in the data subdirectory of the variable-data directory. For more information about how to configure these files, see the SharePlex Administration Guide.
For each error that it ignores, Post will log an error in the SharePlex errlog.sql file and then resume posting. If Post receives an error that is not specified in the file, the process stops.
Default: 0 (stop on all errors)
Range of valid values: 0 or 1 (flag)
Takes effect: immediately
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 data types 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.
sp_ctrl(sysB)> set param SP_OPX_DISABLE_OBJECT_NUM 12345,67890
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_OPX_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_OPX_DISABLE_OBJECT_NUM to 0.
Default: 0 (off)
Range of valid values: list of valid object IDs separated by commas
Takes effect: immediately
This parameter controls the maximum size of a batched DML operation. To maximize its performance when applying SQL operations to the target, the Post process groups smaller replicated operations into one larger batched operation. Post groups replicated operations when the operations are identical and the SQL is similar. Post continues to add operations (with matching criteria) to the batch until the batch size specified by SP_OPX_MAX_BATCH_MBYTES is reached.
The SP_OPX_BATCH_ENABLE parameter enables this feature (enabled by default).
Default: 1 megabyte
Range of valid values: 1 to any positive integer
Takes effect: When Post is restarted
This parameter controls the amount of memory that the Post process uses. The Open Target Post process uses an internal queue to store transaction data temporarily. This parameter controls the maximum amount of memory that this internal queue uses.
Default: default 5000 messages
Range of valid values: integer, no maximum
Takes effect: when Post is restarted
This parameter controls character set conversion between an Oracle source and a non-Oracle target.
When replicating to an Open Target target (non-Oracle target), SharePlex supports replication from any Oracle Unicode character set and the US7ASCII character set. SharePlex posts data to Open Target in the Unicode character set, and therefore if the source data is Unicode or US7ASCII, no conversion on the target is required.
However, if the following are true, conversion is required on the target:
To perform conversion with an Oracle client on Linux
To perform conversion with an Oracle client on Windows
To apply Unicode and US7ASCII data without conversion
If the source data is Unicode or US7ASCII and you are not replicating LOB data, no conversion or Oracle client is needed. Set the SP_OPX_NLS_CONVERSION parameter to 0 to disable conversion, and then restart Post if it is running.
Default: 1
Range of valid values: 0 or 1 (flag)
Takes effect: when Post is restarted.
This parameter controls the oneline debug feature for the Open Target 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:
Default: Disabled
Range of valid values: a string that forms a list of objects listed by object ID, separated by commas.
Takes effect: Immediately
This parameter controls what SharePlex does when it encounters an out-of-sync condition, based on the following:
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.
Default: 0 (do not stop for out-of-sync conditions)
Range of valid values: 0 or 1 (flag)
Takes effect: immediately
This parameter is used when Post is configured to post to a JMS server in transactional style (issue a JMS commit at intervals, rather than after every message as directed by the session_transacted property of the target command).
This parameter controls the read/release interval, which is how often Post purges the data of a transaction from the queue.
Default: 1,000
Range of valid values: any positive integer
Takes effect: when Post is restarted
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.
See also SP_OCT_REDUCED_KEY .
Default: 1
Range of Valid Values: 0, 1, 2
Takes effect: When Post is restarted
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
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
This parameter controls the Transaction Concurrency component of the Post Enhanced Performance feature. When set to a value of 2 or greater, Post runs with the specified number of threads (concurrent operations).
This parameter supports SQL Server and PostgreSQL databases. Typically, it should be set to the number of cores in the processors on the system.
For more information about how to tune the performance of Post, see the SharePlex Administration Guide.
Default: 1 thread (disabled)
Range of valid values: 1 to 256
Takes effect: when Post is restarted
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. This parameter is used in a prepared conflict resolution routine provided by SharePlex. The SP_OPX_TRUSTED_SOURCE parameter should be set to same value on both servers. 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
These parameters control properties of the SharePlex queues.
This parameter determines the maximum number of queues allowed per instance of sp_cop. By default, each capture and export queue uses 8 MB of shared memory and each post queue uses 32MB. If the system has enough memory, you can increase the value of this parameter. On startup, SharePlex displays the amount of shared memory that is required for the number of queues in the active configuration files.
Default: 25
Range of valid values: 9 - 254
Takes effect: When sp_cop is restarted
This parameter determines the specific shared-memory size for the post queue.
Important: Increasing this parameter might require an increase in the maximum shared memory segment size in your system settings.
Default: 32 megabytes
Range of valid values: integer greater than 0
Takes effect: When sp_cop is restarted
This parameter determines the specific shared-memory size for the capture and export queues.
Default: 8 megabytes
Range of valid values: integer greater than 0, no maximum
Takes effect: When sp_cop is restarted
This parameter controls the shared-memory buffer size, which determines how much information the SharePlex processes read from, or write to, the queues at one time. The buffer is part of the SharePlex checkpoint recovery process that facilitates fast, safe, asynchronous transport of data. The default setting is an optimal value and should not be changed without the guidance of a Technical Support representative or documented SharePlex procedure. However, it is acceptable to change this parameter if the majority of transactions are large. Try an initial setting of 65536. All values set for this parameter must be a power of 2.
Important: Increasing this parameter to a value greater than 64k might require an increase in the maximum shared memory segment size in your system settings.
Default: 32,768 bytes
Range of valid values: any positive integer in powers of 2
Takes effect: when SharePlex is restarted
SP_QUE_SYNC instructs the queue module to verify that the writes of queue data to disk have been written to the disk media before returning. This is not the standard disk write model in Unix and Linux.
In the default OS disk I/O procedure, disk writes are written to an internal OS buffer cache and then are written to disk later. This action distributes the overhead of writing to disk so processes do not have to wait for the data to be written to disk. When the data blocks are written to the buffer cache, the applications accessing the data cannot distinguish the data written to the buffer cache from data written to disk. Unlike the Oracle COMMIT, all processes that have access to the file also have access to the data in the buffer cache. If a system crash should occur between the time the data blocks are written to the buffer cache and to the physical media, any data not written to the media is at risk for being lost upon the system recovery.
The SP_QUE_SYNC parameter is implemented as follows:
SP_QUE_SYNC=0
The OS default case described in the second paragraph is the disk write algorithm used for the SharePlex queue data.
SP_QUE_SYNC=1
Setting SP_QUE_SYNC to 1 causes the O_SYNC flag to be set upon opening each queue data and header file. This flag tells the OS not to return a write call until the data has been successfully written to disk. Without the sync flag ("normal" I/O), space would be allocated for the file data but the file data might not be written due to a system crash, cluster failover, or other critical problem that causes the OS to stop executing.
SP_QUE_SYNC=2 (SharePlex default setting)
Setting the SP_QUE_SYNC parameter to 2 does not turn on the O_SYNC flag. Instead, normal writes are done until a queue write COMMIT is called. As part of the write COMMIT, the queue module executes the system call fsync on each queue data file and then on the queue header files. This eliminates redundant sync operations on data that may be rewritten later by a later write COMMIT.
Impact of setting SP_QUE_SYNC
Setting SP_QUE_SYNC to a value that causes disk writes to complete before returning might have an impact on SharePlex performance and may slightly increase I/O processing for non-SharePlex processes. The amount of overhead is dependent upon the amount of data in the queue, the filesystem types, and the types of disk drive and disk controller in use.
What SP_QUE_SYNC does not do
There are many functions that the SP_QUE_SYNC parameter does not do. Among the most common issues:
It does not cause or prevent “out of shared memory” errors or “out of virtual memory” errors. Any message indicating loss of shared memory is most likely a failure in the queue module’s demand paging system. If an “out of virtual memory” error occurs, verify the OS parameters for data segment limits. If the usual or expected limits are not enough, contact Technical Support.
Using SP_QUE_SYNC in a cluster
In a cluster, setSP_QUE_SYNC on the primary node of the cluster (the node to which the shared variable-data directory is mounted) and on each source and target machine outside the cluster that is part of the same replication environment.
Default: 2
Range of valid values: 0 - 2
Takes effect: When SharePlex is restarted
This parameter improves queue performance. If the source has a high amount of concrrency, the post queue may contain numerous subqueues, each of which represents a transaction session. This may reduce the performance of the Post process. This parameter directs SharePlex to use improved indexing to access the subqueue structures. A message "Subqueue index enabled queuename" is written to the Event Log for every Post queue for which this parameter is enabled.
This parameter does not support VARRAYs. If you are replicating VARRAYs and this parameter is enabled, the parameter is ignored.
Default: 0
Range of valid values: 0 (disabled) or 1 (enabled)
Takes effect: When SharePlex is restarted
These parameters are used by the SharePlex sp_cop program.
This parameter determines the time interval during which sp_cop is inactive. The purpose of idle time is to conserve a machine's resources. In general, sp_cop should not be idle for long, because it plays a central role in replication. This parameter enables you to increase the idle time if sp_cop is using too much CPU time for overhead activities. A recommended value is 600 seconds to reduce the overhead.
Default: 300 seconds
Range of valid values: any positive integer
Takes effect: immediately
This parameter sets the initial time interval sp_cop uses to scan its services table to determine if a process needs to be stopped or restarted.
As SharePlex continues to run, the interval might change, depending upon the history of each process.
Default: 300 seconds
Range of valid values: any positive integer
Takes effect: immediately
This parameter tunes the TCP/IP window size on the target machine. It works in conjunction with the SP_XPT_SO_SNDBUF parameter on the source 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_COP_SO_RCVBUF must be set equal to or greater than the value of SP_XPT_SO_SNDBUF, 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:
param_value / ping_time= bytes per second
For example, if ping time is 200 milliseconds, and the value for the two parameters is 64K, SharePlex sends 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_COP_SO_RCVBUF, set it on the target system, then stop and start sp_cop 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 SharePlex is restarted
This parameter sets the TCP/IP port number for TCP/IP communications among Share- Plex sp_cop processes. All sp_cop processes replicating among each other must use the same TCP/IP port number. The default setting for SP_COP_TPORT is 2100; however, you may set a different port number during installation. After installation you may set a different port number by using the set port command. Thereafter, sp_cop uses the new port number.
Default: 2100
Range of valid values: any positive integer
Takes effect: when SharePlex is restarted
This parameter sets the UDP port number for communication among SharePlex sp_cop processes. All sp_cop processes replicating among each other must use the same UDP port number. The default setting for SP_COP_UPORT is 2100; however, you may set a different port number during installation. After installation you may set a different port number by using the set port command. Thereafter, sp_cop uses the new port number. SharePlex only uses the UDP port for hello and are-you-alive messages, and it actually uses RMP through UDP. All other communication is through TCP.
Default: 2100
Range of valid values: any positive integer
Takes effect: when SharePlex is restarted
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. 이용 약관 개인정보 보호정책 Cookie Preference Center
