Capture Parameters
These parameters are used by the SharePlex Capture process.
SP_OCT_ALLOW_DP_DDL
This parameter can be enabled if SharePlex fails to replicate DDL operations that occur when running an Oracle Data Pump export/import. Occasionally, SharePlex identifies DDL in a Data Pump load as recursive DDL that should be ignored. This parameter directs SharePlex to capture that DDL.
A setting of 1 enables this parameter. After the load is finished, set this parameter back to 0 and then restart Capture.
Default: 0
Range of valid values: 0 or 1 (flag)
Takes effect: upon restart of the Capture process
SP_OCT_ARCH_LOC
This parameter defines the path to the archive logs. When the redo logs wrap, SharePlex looks for the archive log in Oracle’s archive log list. If SharePlex cannot find the archive log there, it looks in the directory or directories specified by this parameter. It also searches the sub-directories. The default is the /home directory. If the path for the archive logs is different, specify the correct full path name with this parameter. You can specify more than one directory path if you separate them with semicolons (;) as shown in the following example. In this example, Capture will search under both /disk1/log and /disk2/log paths to find the logs.
sp_ctrl> set param SP_OCT_ARCH_LOC /disk1/log;/disk2/log
To specify the directory path for SharePlex for RAC, use the following:
sp_ctrl> set param SP_OCT_ARCH_LOC !;<node1_oracle_sid>;<node1_arch_absolutepath>;<node2_oracle_sid>;<node_arch_absolute_path>
Note that the non-RAC format for path names allows a list of directory path names separated by a semicolon. The list can be any length desired up to the limit of 1023 bytes. However, the RAC format, which is !; followed by a list of sid;pathname pairs, does not allow the pathname to be a list of path names as the non-RAC format does. Each SID must be followed by exactly one directory pathname. If you want to specify more than one pathname for a single instance SID, you must put in two pairs of entries in which each of the entries has the same SID. Thus, you cannot specify
!;sid1;path2;path2;path3;sid2;path4
Rather, the proper format for the preceding example is
!;sid1;path2;sid1;path2;sid1;path3;sid2;path4
Default: empty string
Range of valid values: full directory path to the restored archive logs
Takes effect: immediately.
SP_OCT_ASM_CACHE_AGE
This parameter controls how long the current ASM cache is kept, before before a re-query of the ASM blocks is performed to update it.
Default: 40
Range of valid values: Any positive integer
Takes effect: immediately
SP_OCT_ASM_ECACHE_SIZE
The number of elements in the buffer to cache extents. Extents are the units of file storage in Oracle ASM. Increasing this number increases memory usage.
Default: 20
Range of valid values: Any value greater than 9
Takes effect: upon restart of the Capture process
SP_OCT_ASM_FLIST_SIZE
Maximum number of ASM files that will be read simultaneously. Increasing this number increases memory usage.
Default: 10
Range of valid values: Any value greater than 4
Takes effect: upon restart of the Capture process
SP_OCT_ASM_MULTI_OCI_BLOCK_SIZE
This parameter controls the size of the buffer that is used by each Capture thread when SP_OCT_ASM_MULTI_OCI is set to enable multi-threaded capture on Exadata systems. This parameter should be left to its default. Capture automatically adjusts its buffer size to the value of the AU_SIZE parameter that is set for the disk group where the logs reside. This is the recommended buffer size for best performance.
Default: 0
Range of valid values: Any positive integer
Takes effect: upon restart of the Capture process
SP_OCT_ASM_MULTI_OCI
This parameter controls the number of threads that Capture uses to read the redo logs. To use this parameter, you must also set SP_OCT_OLOG_USE_OCI to 1.
The value for this parameter must be set to at least 2 but no more than the number of disks in the redo log disk group.
A large number of threads is not required, and performance actually diminishes with too many threads. The more threads, the more memory Capture requires. Start with a small number of threads and monitor performance, then add threads if needed until you obtain an ideal balance between performance gain and memory usage.
Default: 0 (single threaded capture)
Range of valid values: Minimum value is 2, maximum value is the number of disks in the ASM disk group that stores the redo logs.
Takes effect: upon restart of the Capture process
SP_OCT_ASM_SLIST_SIZE
Maximum number of sessions that will be accessing files simultaneously.
Default: 10
Range of valid values: Any value greater than 4
Takes effect: upon restart of the Capture process
SP_OCT_ASM_SUPPORT
This parameter enables and disables support for redo and archive logs on ASM supported platforms. It is enabled automatically if the SharePlex database connection is configured to connect to ASM.
Default: 0 (off)
Range of valid values: 0 or 1 (flag)
Takes effect: upon restart of the Capture process
SP_OCT_AUTOADD_ENABLE
This parameter is a global parameter that enables newly created objects to be added automatically to an active replication configuration. By default it applies to tables and indexes. To auto-add other object types, additional parameter settings are required.
The functionality works as follows:
Auto-add of the following objects is enabled by default when you enable SP_OCT_AUTOADD_ENABLE:
- A table named in a CREATE TABLE operation is automatically added to replication if the name matches a wildcard specification in the active configuration file.
- An index created with a CREATE INDEX operation is automatically added to replication if the table on which it was created is specified in the configuration file.
- A table named in a CREATE TABLE AS SELECT operation is automatically added to replication if the name matches a wildcard specification in the active configuration file. SharePlex creates the new table on the target, followed by the replication of the data in the source table.
- ALTER and DROP on the added objects.
The following objects must be explicitly enabled for auto-add support in addition to enabling SP_OCT_AUTOADD_ENABLE:
The supporting table for a materialized view named in a CREATE MATERIALIZED VIEW operation is automatically added to replication if the name of the materialized view matches a wildcard in the active configuration file and the SP_OCT_AUTOADD_MVIEW parameter is set to 1. The SP_SYS_TARGET_COMPATIBILITY parameter must be set to at least 8.6.2 to support auto-add of new materialized views.
A sequence named in a CREATE SEQUENCE operation is automatically added to replication if the name matches a wildcard in the active configuration file and the SP_OCT_AUTOADD_SEQ parameter is set to 1. The SP_SYS_TARGET_COMPATIBILITY parameter must be set to at least 8.6.3 to support auto-add of new sequences.
Note: To replicate changes to the sequence, the SP_OCT_REPLICATE_SEQUENCES parameter must be set to 1 (the default).
Make sure DDL replication is fully enabled by checking that the parameter SP_OCT_REPLICATE_DDL is set to 1 or 3.
Default: 1 (on)
Range of valid values: 0 or 1 (flag)
Takes effect: immediately
SP_OCT_AUTOADD_MVIEW
This parameter determines whether SharePlex automatically adds materialized views to replication when they are created after activation. When enabled, it causes SharePlex to add the underlying table of a new materialized view to replication if the name of the materialized view satisfies a wildcard in the active configuration file. SharePlex converts the CREATE MATERIALIZED VIEW into a CREATE TABLE statement, posts the CREATE TABLE to the target, and replicates the DML that populates the view. The table is maintained by replication through future supported DDL and DML changes.
To auto-add materialized views, both this parameter and the SP_OCT_AUTOADD_ENABLE parameter must be set to 1, and the version of SharePlex on the target must be at least 8.6.2.
Default: 0 (off)
Range of valid values: 0 or 1 (flag)
Takes effect: Process restart
SP_OCT_AUTOADD_SEQ
This parameter determines whether SharePlex automatically adds sequences to replication when they are created after activation. When enabled, it causes SharePlex to replicate the CREATE statement if the name of the sequence satisfies a wildcard in the active configuration file. SharePlex then maintains the object on the target throughout future DDL and DML changes. To auto-add sequences, both this parameter and the SP_OCT_AUTOADD_ENABLE parameter must be set to 1, and the version of SharePlex on the target must be at least 8.6.3.
Important! To replicate sequences, the supplemental logging of primary and unique keys must be enabled at the database level, or you must enable supplemental logging for primary keys on the sys.seq$ table.
Default: 0 (off)
Range of valid values: 0 or 1 (flag)
Takes effect: Process restart
SP_OCT_CHECKPOINT_LOG
Sometimes, the Capture process does not checkpoint on a regular basis. Checkpointing saves the state of the process in case it is needed for recovery. If the Capture process terminates for some reason and the redo logs wrap, SharePlex attempts to recover to a checkpoint that no longer exists. SP_OCT_CHECKPOINT_LOG ensures that the checkpointing occurs before the logs switch.
The checkpoint is triggered when Capture lags a specified number of logs behind Oracle. For example, with the default of 2, Capture does a checkpoint when it falls 2 or more logs behind Oracle.
The range of permissible values for this parameter is from 2 (the default) to a value equal to the number of logs you are using. A value of 0 disables this feature. This parameter is useful in environments where frequent log switches can cause a switch to occur before SharePlex’s internal checkpoint mechanism can be triggered.
Default: 2 logs
Range of valid values: 2 to the number of redo logs
Takes effect: immediately
SP_OCT_CHECKPOINT_TIME
This parameter works in conjunction with the SP_OCT_CHECKPOINT_FREQ parameter. It defines the time delay, in seconds, before the Capture process checkpoints. If the value set for this parameter is reached before the value set for SP_OCT_CHECKPOINT_FREQ, it triggers the checkpoint. (Checkpointing saves the state of the process in case it is needed for failure recovery.)
Default: 120 seconds
Range of valid values: any positive integer
Takes effect: immediately
SP_OCT_CK_LOC_FIRST
This parameter causes the Capture process to first search for an archived redo log in the location specified by SP_OCT_ARCH_LOC. If SP_OCT_ARCH_LOC is an empty string, Capture will first search in the Archive Log Destination as configured in Oracle.
Default: 0 (off)
Range of valid values: 0 or 1 (flag)
Takes effect: Process restart
SP_OCT_DATA_OBJ_CACHE_SIZE
This parameter specifies the number of entries/elements used in the Data Object ID to Table Object ID mapping cache. For certain operations, such as DLOADs or LOB data, the redo-record for such operations contains only the Data Object ID. The Capture process needs to map the Data Object ID to the Table Object ID to see if the operation is of interest. The Data Object ID to Table Object ID cache provides a look-up solution; otherwise, the Capture processmust query Oracle to map the Data Object ID to Table Object ID.
Default: 50
Range of valid values: any positive integer
Takes effect: immediately
SP_OCT_DATE_ MSG
This parameter can be set so that the Capture process prints a warning message to the Event Log and the Capture Log when it detects an invalid date column. A setting of 0 disables it, and a setting of 1 activates it. The error message generated by Capture is:
Invalid DATE format detected in record with rowid=rowid, on obj object_id. See capture log for detail.
Default: 0 (do not print messages)
Range of valid values: 0 or 1 (flag)
Takes effect: immediately
SP_OCT_DDL_LOGGING
This parameter controls the Capture logging levels for DDL activity. The logging of DDLs to the DDL log is not dependent upon the SP_OCT_REPLICATE_ALL_DDL setting. Even when SP_OCT_REPLICATE_ALL_DDL is 0, the DDL can still be logged. The following settings are available:
0 = no logging,
1 = replicated DDL only
2 = all DDL
Default: 2
Range of valid values: 0, 1, 2
Takes effect: immediately
SP_OCT_DDL_UPDATE_CONFIG
This parameter controls whether SharePlex updates the table name in the configuration file when processing ALTER TABLE RENAME. By default, SharePlex updates the table name when it processes this DDL operation. To prevent updates the configuration file, disable this parameter.
Default: 1 (enabled)
Range of valid values: 0 or 1
Takes effect: when Capture is restarted
SP_OCT_DEF parameters
The following parameters can be set so that SharePlex corrects the format of dates and times if they were incorrectly entered by a user or application and bypassed the database’s validity check. These parameters take effect as soon as they are activated.
|
SP_OCT_DEF_MONTH |
range of values is from 1 - 12 |
| SP_OCT_DEF_DAY |
range of values is from 1 - 31 |
| SP_OCT_DEF_YEAR |
range of values is from 1987 - 9999 |
| SP_OCT_DEF_HOUR |
range of values is from 0 - 23 |
| SP_OCT_DEF_MIN |
range of values is from 0 - 59 |
| SP_OCT_DEF_SEC |
range of values is from 0 – 59 |
SP_OCT_DENIED_USERID
This parameter can be used to specify an Oracle userid for which all DML and DDL transactions should be ignored or filtered by the Capture process.
Important: Ignoring transactions on the source machine may lead to an out-of-sync condition.
SharePlex does not verify that the specified userid exists.
Default: 0x00000000
Range of valid values: integers (any valid Oracle userid)
Takes effect: immediately
SP_OCT_DLOAD_MAX_COLS_PER_MSG
This parameter controls the maximum size of a DLOAD message. The number of rows in a message is determined by dividing this parameter by the number of columns in each row. For example: If a table has 99 columns and there is a DLOAD with 90 rows and this parameter is set to 5000 then it would split the message into 2 DLOAD messages with 50 rows of 99 columns for a total of 4950 columns in the first message the rest in the second message.
Default: 10000
Range of valid values: any value greater than 99
Takes effect: immediately
SP_OCT_ENABLE_LOBMAP
This parameter controls whether or not SharePlex uses a LOB map when replicating tables that contain out-of-row LOB columns. The LOB map is used by the Capture process to map LOBIDs and rows when PK/UK logging is not enabled. LOB mapping is enabled by default. The SHAREPLEX_LOBMAP table stores these mappings.Transactions with numerous LOB operations can slow down Capture because it needs to maintain and refer to the mappings. If PK/UK logging is enabled on the database, you can disable LOB mapping by setting this parameter to 0.
Default: 1 (on)
Range of valid values: 0 or 1
Takes effect: Process restart
SP_OCT_INCLUDE_UNCHANGED_COL
This parameter controls whether or not unchanged columns are included in the after image of an UPDATE operation. By default, SharePlex only includes the changed values in the after image.
Default: 0 (off)
Range of valid values: 0 or 1
Takes effect: immediately
SP_OCT_INSERT_INCLUDE_NULLS
This parameter controls whether or not columns with NULL values are replicated for an INSERT statement. By default, SharePlex does not replicate null values. For example, a statement like INSERT INTO mytable (col1, col2, col3) VALUEs ('red', 'green', 2), where mytable has six columns, causes SharePlex to replicate only the explicit 'red", 'green', and 2 values, but not the implicit NULL values for the other three columns. If SP_OCT_INSERT_INCLUDE_NULLS is set to 1, SharePlex replicates 'red', 'green', 2, ' ', ' ', ' '.
Default: 0 (off)
Range of valid values: 0 or 1
Takes effect: immediately
SP_OCT_LOB_BUFFER_SIZE
This parameter controls the size of Capture’s LOB buffers, which must be maintained until Capture can assemble the related transaction information. If SharePlex is unable to determine the transaction, it uses a special sub-queue outside of the ones containing the transactions. The headers for these sub-queues require more shared memory, which can require an increase in the SP_OCT_LOB_BUFFER_SIZE parameter.
The larger the LOB buffers, the more likely that system memory will run out, preventing buffering for subsequent LOB operations until memory becomes available again.
Therefore, you might need to increase the SP_QUE_Q_SHMSIZE parameter in conjunction with increasing the SP_OCT_LOB_BUFFER_SIZE parameter. Generally, only the LOB data for VARRAYs is buffered, so this parameter generally only impacts transactions involving VARRAYs.
Default: 5 MB of memory per LOB
Range of valid values: any positive integer
Takes effect: when Capture is restarted
SP_OCT_LOG_FILESIZE
This parameter sets the size of the Capture debug log file.
Default: 50000000 bytes
Range of valid values: any value greater than 9999 bytes
Takes effect: when Capture is restarted
SP_OCT_LOG_MEMBER
The SP_OCT_LOG_MEMBER parameter is used to augment queries of the data dictionary to specify redo log locations. You can use this parameter to specify exactly what log file you want to operate on.
Default: % (percent symbol)
Range of valid values: any correctly formed file name specification with wildcard characters
Takes effect: when Capture is restarted
SP_OCT_LOG_NUMFILES
This parameter controls the number of Capture debug log files that are allowed before the oldest one is deleted and a new one is created.
Default: 3
Range of valid values: any value greater than 1
Takes effect: when Capture is restarted
SP_OCT_LOG_READ_SIZE
The Capture process reads multiple redo-log file blocks in one pass. This parameter controls the number of blocks read at a time, enabling you to adjust the value according to the system’s configuration and whether or not Capture is falling behind the pace of Oracle’s processing. Keep in mind that when the value is higher than necessary, it incurs more system overhead.
Default: 64 blocks
Range of valid values: any positive integer
Takes effect: when Capture is restarted
SP_OCT_LOGWRAP_RESTART
This parameter controls how Capture behaves in response to the following situations:
- The archives are not available.
- Capture is denied permission to open a log.
When Capture cannot find an archive log, or when it cannot open a log, the process stops by default (a setting of 0). At this setting, when Capture cannot access a log, the show capture command returns a status of "Stopped due to missing archive log."
You can configure Capture to wait a certain amount of time (in seconds) and then start again automatically. This allows time to assign the correct read permissions or run any external log-management processing that moves the archives into the location expected by SharePlex. Capture waits, checks for the logs, stops if they are not yet available or cannot be opened, and continues checking and stopping until the logs are restored or opened. At this setting, the show capture command returns a status of "waiting for logfile."
Default: 0 seconds (do not restart automatically if logs are not available)
Range of valid values: 1 to any positive number of seconds
Takes effect: Immediately
SP_OCT_MIN_SESSIONS
This parameter controls the minimum number of subqueues that Capture creates and maintains to contain data from concurrent transactions. This parameter supports the Post Enhanced Performance (PEP) feature by allowing Post to increase its own concurrency. The PEP feature is controlled with the SP_OPO_DEPENDENCY_CHECK parameter.
For best results, set SP_OCT_MIN_SESSIONS to at least the number of CPU cores that you have on the target system.
Default: 8
Range of valid values: any positive integer
Takes effect: when Capture is restarted
SP_OCT_OLOG_DELAY
This parameter controls the amount of microseconds capture's redo log reader spends sleeping when it has detected that there is no more data to read. This parameter essentially comes into play only when capture is idle. When busy capture essentially won't be sleeping since there are records to read.
Default: 10000 (microseconds)
Range of valid values: any positive integer
Takes effect: Immediately
SP_OCT_OLOG_NO_DATA_DELAY
This parameter controls the length of time that the log reader will sleep before a retry when there is no data to process.
Default: 10000 (microseconds)
Range of valid values: any positive integer
Takes effect: when Read is restarted
SP_OCT_OLOG_QUEUE_SIZE
This parameter sets the size of the log reader queue.
Default: 16384
Range of valid values: 100 to any positive integer
Takes effect: Process restart
SP_OCT_OLOG_RDS_MINER
Note: Enabling the SP_OCT_OLOG_RDS_MINER parameter is deprecated and no longer supported starting with Oracle 19c.
This parameter controls whether or not Capture uses a second thread to help keep Capture from lagging behind Oracle when capturing from an Oracle RDS database.
Due to the processing load incurred by using this thread, it is disabled by default. To enable it, set this parameter to 1.
Default: 0 (off)
Range of valid values: 0 or 1 (enabled)
Takes effect: Process restart
SP_OCT_OLOG_REOPEN
This parameter controls whether or not the Capture process will close and re-open the online redo log when it reaches the end of the file. If this parameter is enabled, the user may control the length of time that the Capture process will wait before retry by employing the SP_OCT_OLOG_NO_DATA_DELAY parameter.
Default: 1 (off)
Range of valid values: 0 or 1 (flag)
Takes effect: Process restart
SP_OCT_OLOG_USE_OCI
This parameter controls whether Capture captures the source change records by reading the redo logs directly from the files or by using OCI calls. The default of 0 directs Capture to read directly from the redo logs.
- If the source database is running on ASM, enabling this parameter is optional. Capture can read the redo logs directly or use OCI calls.
- If the source database is remote from the machine where Capture is running, such as on a cloud service, this parameter must be set to 1 so that Capture uses OCI calls to get the source change records.
Default: 0 (off, Capture reads directly from the redo logs)
Range of valid values: 0 or 1 (flag)
Takes effect: Process restart
SP_OCT_ONELINE_DEBUG
This parameter controls the oneline debug feature for the Capture 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_oct_oneline_debug 230230, 351626
The following items are logged:
- 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)
- The time when the operation occurred (Oracle)
- The log sequence number and offset within the log of the affected operation
- The Oracle SCN 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_OCT_OPS_LOGREADER_DELAY
This parameter controls the log reader delay time before retry when it encounters no_data.
Default: 50000 (microseconds)
Range of valid values: any positive integer
Takes effect: when Capture is restarted
SP_OCT_OPS_LOGREADER_RETRY
This parameter controls the number of times the log reader will retry when it encounters no_data.
Default: 3
Range of valid values: any positive integer
Takes effect: when Capture is restarted
SP_OCT_OPS_NO_DATA_DELAY
This parameter controls the length of the sleep that is enforced per node when no data is encountered in the sequencer.
Default: 5 (deciseconds)
Range of valid values: any positive integer
Takes effect: when Read is restarted
SP_OCT_PREFER_ARCHIVE
This parameter controls whether Capture reads the online version of a redo log or the archived version if it is available. By default, Capture reads only from the online logs and only reads an archived version if the online log is no longer available.
When this parameter is set and Capture finds the required log online, Capture makes an additional check to determine if the log is also archived. If the log is archived, Capture queries the archive path and reads the archived log instead of the online one. If the log is not archived, Capture reads the online log.
Normally, SharePlex provides the least latency when reading from the online redo logs, but this parameter may help improve Capture performance on Exadata. SharePlex can process higher volumes of data when reading from a multiplexed archive location outside of the Exadata ASM file system. Should a backlog develop with high volumes of data on Exadata, you can set SP_OCT_PREFER_ARCHIVE parameter to a value of 1 and configure SharePlex as directed in the Tune Capture on Exadata section in the SharePlex Administration Guide.
To use this parameter, multiplexing of the redo logs must be enabled, and the path to the archive logs must be set in SharePlex. For more information, see SP_OCT_ARCH_LOC .
Default: 0 (disabled)
Range of valid values: 0 or 1 (flag)
Takes effect: when Capture is restarted
SP_OCT_READ_ARC
This parameter controls whether SharePlex will read only from Oracle archive logs. The default setting of 0 means SharePlex will utilize both online and archived logs to read Oracle redo logs. Any non-zero value means SharePlex capture process will read only from archived Oracle redo logs. SharePlex will wait until an online log is archived before attempting to read that sequence of the Oracle redo log.
0 = default behavior SharePlex, read both online and archived redo logs
1 = read only archived redo logs. The search sequence for the archived logs will start from Oracle defined archived location followed by searching under SP_OCT_ARCH_LOC directory specification.
2 = read only archived redo logs. The search will only occur under SP_OCT_ARCH_LOC directory specification. *(Will* *NOT search under ORACLE defined archive locations ).* SP_OCT_CK_LOC_FIRST value will be ignored with this setting. It will be assumed to be 1.
3 = read only archived redo logs. Read only from Oracle defined archive locations. SP_OCT_ARCH_LOC directory will be ignored. SP_OCT_CK_LOC_FIRST value will be ignored with this setting. It will be assumed as 0.
Best practice: When enabling SP_OCT_READ_ARC, a natural delay of at least the length of time to generate and copy an archive log from an online log will be apparent in data latency. While this latency could be acceptable, reader's performance in fetching keys is reliant on read consistent views. Reader key fetching is dependent on the consistent views being current (or close to it) but using the parameter delays the age of the view with at least the latency time. To circumvent the need for reader to fetch keys, it's highly recommended that customers using the mentioned capture parameter enable the source database's supplemental logging for primary keys and unique keys (PU/UK) for tables in replication.
Default: 0
Range of valid values: 0 - 3
Takes effect: when Capture is restarted
SP_OCT_REDOLOG_ENSURE
This parameter controls the amount of seconds that Capture waits before processing a change record from the online redo log. This wait is the difference between the Oracle timestamp of the record and the current system time. It prevents Capture from trying to read records that the Oracle logwriter is not finished writing, which would result in the capture of a partial record. It ensures that Capture has a complete block to process.
Capture disables this parameter automatically when capturing from a remote database.
Default: 2 seconds
Range of valid values: 0 to any positive integer
Takes effect: when Capture is restarted
SP_OCT_REDUCED_KEY
This parameter controls which parts of an operation in the redo log are sent by Capture to the target for use by Post. Different SharePlex features may require more or less data to be made available to the Post process.
- If set to 0, this parameter directs Capture to send all of the data that Oracle writes to the redo record other than LONGs and LOBs. This setting sends the most data across the network, but is required to support certain SharePlex features.
- If set to 1 (the default), this parameter directs Capture to send the key values and, for UPDATEs, the before and after values of columns that were changed in the operation. This setting is the default. 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. For more information about how SharePlex uses before and after values, see the SharePlex Administration Guide.
- If set to 2, this parameter directs Capture to send the key values and, for UPDATEs, the after values of the columns that changed to the target. This setting sends the least amount of data across the network and also enables Post to only use the key values when posting data, which improves performance.
See also SP_OPO_REDUCED_KEY.
Default: 1
Range of Valid Values: 0, 1, 2
Takes effect: Immediately
SP_OCT_REPLICATE_ALL_DDL
This parameter controls expanded DDL replication, which is the replication of DDL for objects that are not in the SharePlex replication configuration file. The objects must exist in the source database before configuration activation and also on the target (except in the case of CREATE). SharePlex replicates the DDL statements, but does not maintain the objects on the target throughout any future DML changes.
Setting the SP_OCT_REPLICATE_ALL_DDL requires identical source and target databases in order for the replicated DDL to execute successfully on the target system. Because this DDL is broadcast to all target machines, all target systems must be identical.
SharePlex replicates supported DDL for all objects in all schemas of the active datasource, so those components must exist in the target database for the operations to succeed.
To configure the Post process to stop for errors encountered when applying replicated DDL operations to the target database, set the SP_OPO_STOP_ON_DDL_ERR parameter.
If you are replicating the creation of packages or stored procedures, the name of the objects in the body of the package or stored procedure should be fully qualified, because other users or schemas may execute this package or stored procedure.
For a list of operations supported by SharePlex, see the Release Notes that correspond to your version of SharePlex.
|
Note: Expanded DDL replication supports not only tables and sequences but also a wide range of other objects such as procedures, functions, users, and views, which are not part of replication. Some of these objects may have underlying objects that are in replication. In those cases, Expanded DDL replication applies not only to the object that is outside the replication configuration, but also to the underlying objects that are in replication. |
|
SharePlex does not support the Oracle Flashback Table feature. If the SP_REPLICATE_ALL_DDL parameter is enabled (value of 1), SharePlex may try to replicate the flashback DDL, which will return an error. To perform Flashback Table on a table that is in replication, use the following procedures in the SharePlex Administrator Guide to work around this issue:
- Remove source objects from replication
- Perform the flashback
- Add or change objects in an active configuration
|
Default: 0 (off)
Range of valid values: 0 or 1 (flag)
Takes effect: immediately
SP_OCT_REPLICATE_COMMENT
This parameter controls whether or not SharePlex replicates the following DDL operations on tables that are listed in the configuration file:
- ALTER TABLE to ADD COMMENT
- COMMENT ON
By default, the preceding DDL operations are replicated. To disable replication of these operations, set this parameter to 0. This can be useful if replication of these operations is impacting performance.
Default: 1 (enabled)
Range of valid values: 0 or 1
Takes effect: when Capture is restarted
SP_OCT_REPLICATE_CTAS
This parameter determines how SharePlex replicates CREATE TABLE AS SELECT (CTAS) statements when the name of the new table matches a wildcard specification in the SharePlex configuration file. It has options to specify the way that CTAS statements are replicated by SharePlex, depending on whether or not the table exists on the target.
|
1 (Default) |
SELECT FROM table exists on the target |
This setting replicates only the original CTAS statement and requires a populated SELECT FROM table to exist on the target.
For example, the following CTAS statement is replicated as-is to the target:
create table my_table as select * from table_a;
In this example, table_a must exist on the target in order for the SELECT to succeed and populate the new table my_table. |
|
2 |
SELECT FROM table does not exist on the target |
This setting causes SharePlex to:
- Replicate a CREATE TABLE statement to create the new table on the target.
- Replicate all of the DML that is returned by the source SELECT statement as INSERTS to populate the new target table.
For example, if a source statement is:
create table my_table as select * from table_a;
The statements posted by SharePlex on the target are the following, assuming table_a has two columns and two rows:
Create table my_table (c1 number, c2 varchar2(20));
Insert into my_table values (1,'a');
Insert into my_table values (2,'b');
Commit;
This produces the same result as a CREATE TABLE AS SELECT, but does not require the SELECT FROM source table to exist on the target.
Important! The replicated DML data must fit into memory that is allocated by Capture. If the amount of data is very large, this could cause Capture to fail. |
Default: 1
Range of valid values: 1 or 2
Takes effect: Process restart
SP_OCT_REPLICATE_DDL
This parameter controls whether SharePlex replicates the following operations on objects in the active configuration that are being replicated by SharePlex:
- ALTER TABLE to ADD COLUMN, MODIFY COLUMN, DROP COLUMN
-
ALTER TABLE to ADD, MODIFY, DROP, SPLIT, COALESCE, MOVE, TRUNCATE, EXCHANGE PARTITION/SUBPARTITION
- TRUNCATE TABLE
You can control whether both operation types are replicated, or just one or the other, or none.
Note: SharePlex 11.1 with Oracle 21C as a source currently supports only DML operations. Support for DDL operations will be added in a future version.
See also SP_OCT_AUTOADD_ENABLE for additional default DDL replication functionality.
To replicate DDL on objects outside the configuration file, see the SP_OCT_REPLICATE_ALL_DDL parameter.
Default: 3 (replicate ALTER TABLE and TRUNCATE)
Range of valid values:
0 (disable replication of both ALTER TABLE and TRUNCATE)
1 (enable ALTER replication only)
2 (enable TRUNCATE replication only)
3 (enable replication of ALTER and TRUNCATE)
Takes effect: immediately
SP_OCT_REPLICATE_DLOAD
This parameter controls whether or not SQL*Loader direct-path loads are replicated. The default setting of 1 enables direct-path load replication. SharePlex supports replication for non-parallel loads only (PARALLEL=FALSE). The database must be in archive mode, and table logging must be enabled. To disable replication of direct-path loads, change this parameter to 0.
Default: 1 (replicate direct-path loads)
Range of valid values: 0 or 1 (flag)
Takes effect: immediately
SP_OCT_REPLICATE_GRANT
This parameter controls whether SharePlex replicates GRANT commands that are issued for tables that are listed in the configuration file.
Default: 0 (disabled)
Range of valid values: 0 or 1
Takes effect: when Capture is restarted
SP_OCT_REPLICATE_MVIEW
This parameter controls whether or not activation will put the existing materialized views found during activation into replication. By default, it is enabled. The materialized views must exist in the source and target before activation.
This parameter is used only during activation to decide whether or not to put the materialized view in replication. To configure SharePlex to add materialized views to replication when they are created after activation, see SP_OCT_AUTOADD_MVIEW.
Default: 1 (replicate materialized views)
Range of valid values: 0 or 1
Takes effect: Process restart
SP_OCT_REPLICATE_POSTER
This parameter controls whether or not the Capture process on a system replicates data posted by the Post process on that system. Leave this parameter at the default setting of 0, which tells Capture to ignore Post activities on the same system. When establishing certain replication configurations — primarily cascading replication — you might be instructed to set this parameter to 1, which causes Capture to replicate posted changes.
Default: 0 (do not replicate Post transactions)
Range of valid values: 0 or 1 (flag)
Takes effect: when Capture is restarted
SP_OCT_REPLICATE_SEQUENCES
This parameter controls whether SharePlex replicates changes made to sequences that are listed in the configuration file, either explicitly or by means of a wildcard. By default, it is enabled. The sequences must exist in the source and target before activation.
You can configure SharePlex to add sequences to replication when they are created after activation. For more information, see SP_OCT_AUTOADD_SEQ.
A setting of 0 excludes sequences from replication, even if they are listed in the configuration file or their names satisfy a wildcard specification.
Important! To replicate sequences, the supplemental logging of primary and unique keys must be enabled at the database level, or you must enable supplemental logging for primary keys on the sys.seq$ table.
Default: 1 (replicate sequences)
Range of valid values: 0 or 1
Takes effect: Process restart
SP_OCT_REPLICATE_SYNONYM
This parameter controls whether SharePlex replicates CREATE SYNONYM and DROP SYNONYM commands that are issued for tables that are listed in the configuration file.
Default: 0 (disabled)
Range of valid values: 0 or 1
Takes effect: when Capture is restarted
SP_OCT_REPLICATE_TRIGGER
This parameter controls whether SharePlex replicates CREATE TRIGGER and DROP TRIGGER commands that are issued for tables that are listed in the configuration file.
Note: Do not replicate triggers in an active-active replication scenario. For more information, see Configure replication to maintain multiple peer databases in the SharePlex Administration Guide.
Default: 0 (disabled)
Range of valid values: 0 or 1
Takes effect: when Capture is restarted
SP_OCT_REQUIRED_DATA_IS_LOGGED
This parameter enables the Capture process to gather additional information to improve rollback handling. With rollback handling, the Read process will require more resources to accomplish and improve performance in return. If system resources are scarce and the systems experiences lots of rollbacks, to lower the Read process resource consumption, you can disable this parameter. This parameter is enabled by default.
Default: 1 (on)
Range of valid values: 0 or 1 (flag)
Takes effect: when Capture is restarted
SP_OCT_TRUNC_PARTITION_BY_ID
Use this parameter to ensure that replication of an ALTER TABLE to truncate or drop a system-generated partition affects the correct partition on the target.
Because the database generates the names of system-generated partitions, the names of those partitions on the source will not match the names of their corresponding partitions on the target. However, the partition positions for the same time frame will match if the target is an exact copy of the source.
To ensure that replication affects the correct partition on the target:
- Make certain the source and target tables structures and partition definitions are identical. For partitions with the same high value, the partition position from dba_tab_partitions should be the same between source and target before replication starts.
- Set the SP_OCT_TRUNC_PARTITION_BY_ID parameter to 1. This setting directs SharePlex to identify the partition by using the partition position, rather than by using the partition name that is specified in the original ALTER TABLE command. Post maps the partition position to the correct partition name in the target table.
When this parameter is enabled, SharePlex checks the SP_SYS_TARGET_COMPATIBILITY parameter to verify that the target Post process is version 8.6.4 or higher. This is the minimum version that supports ALTER TABLE to truncate or delete a system-generated partition by ID. If the target Post process is an earlier version, the ALTER TABLE is replicated by using the partition name, and a warning message is logged to the SharePlex event_log on the target.
Default: 0 (disabled)
Range of valid values: 0 or 1
Takes effect: immediately
SP_OCT_USE_DST
Use this parameter to tell SharePlex to disable the correction for daylight savings time in redolog to ensure logic.
Default: 0 (Disabled)
Range of valid values: 0 or 1
Takes effect: Process restart
SP_OCT_USE_SUPP_KEYS
Use this parameter to tell SharePlex to use the columns set by Oracle's supplemental logging as the key columns when a row is updated or deleted.
In a typical replication scheme, SharePlex chooses a set of key columns for Post to use when it updates or deletes a row. When Oracle's PK/UK supplemental logging is enabled, Oracle logs key columns for each update. These key columns may not always match what SharePlex chose as its keys, which causes unnecessary work for the Read process. Setting this parameter overrides the default behavior of SharePlex, allowing SharePlex to be faster and more efficient.
If a table is configured with horizontal partitioning in the configuration file, you must include the horizontal partitioning column condition in a redo log group, unless that column condition is already part of the PK/UK for that table.
NOTE: The SP_OCT_USE_SUPP_KEYS parameter takes effect only if the supplemental logging is enabled for PK/UI.
This parameter is not compatible with the SP_ORD_HP_IN_SYNC parameter. If both parameters are enabled, SP_OCT_USE_SUPP_KEYS overrides SP_ORD_HP_IN_SYNC.
Default: 0 (Disabled)
Range of valid values: 0 or 1 (Enabled)
Takes effect: Immediately
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: Immediately
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 used 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.
This parameter is not compatible with SP_OCT_REDUCED_KEY and SP_OPO_REDUCED_KEY [any value: 1 or 2] as it overrides the behavior of both the parameters.
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)
- The time when the operation occurred (Oracle)
- The log sequence number and offset within the log of the affected operation
- The Oracle SCN 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: 10,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: Process restart
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 100,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: 100,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.
