These parameters are used by the SharePlex Capture process.
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
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.
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
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
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_ASM_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
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
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:
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_MV 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).
Default: 1 (on)
Range of valid values: 0 or 1 (flag)
Takes effect: immediately
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: immediately
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: immediately
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
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
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: immediately
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
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
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
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
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
|
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
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: immediately
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
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
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
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
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
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
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
This parameter controls how Capture behaves in response to the following situations:
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: when Capture is restarted
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
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: when Capture is restarted
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
This parameter sets the size of the log reader queue.
Default: 5000
Range of valid values: 100 to any positive integer
Takes effect: Process restart
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
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
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.
Default: 0 (off, Capture reads directly from the redo logs)
Range of valid values: 0 or 1 (flag)
Takes effect: Process restart
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:
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 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
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
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
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
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
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.
See also SP_OPO_REDUCED_KEY.
Default: 1
Range of Valid Values: 0, 1, 2
Takes effect: When Capture is restarted
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.
NoteS:
|
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
|
Default: 0 (off)
Range of valid values: 0 or 1 (flag)
Takes effect: immediately
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.
|
Parameter Value |
Use Case |
Description |
|---|---|---|
|
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:
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: for the next activation
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, MODIFY, DROP, SPLIT, COALESCE, MOVE, TRUNCATE, EXCHANGE PARTITION/SUBPARTITION
You can control whether both operation types are replicated, or just one or the other, or none.
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
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
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
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: for the next activation
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
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: for the next activation
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
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
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
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:
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
Use this parameter to tell SharePlex to disable the correction for daylight savings time in redolog to ensure logic.
Default: 1 (Enabled)
Range of valid values: 0 or 1
Takes effect: immediately
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 you set SP_OCT_USE_SUPP_KEYS to 1 and any 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.
A setting of 1 is required for this parameter when replicating to an Open Target database target.
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: when Capture is restarted
These parameters are used by the SharePlex Read process.
This parameter controls the enabling of the Batch Processing functionality. By default it is enabled. This allows the Read process to combine multiple identical records into a single record, or into a batch, for processing by the Post process.
Default: 1 (on)
Range of valid values: 0 or 1 (flag)
Takes effect: when Read is restarted
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
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
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
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.
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
This parameter controls how the Read process checks column conditions to determine whether or not a replicated row change satisfies them.
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
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
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 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.
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 the SP_OCT_USE_SUPP_KEYS parameter. If both parameters are enabled, SP_OCT_USE_SUPP_KEYS overrides SP_ORD_HP_IN_SYNC.
Default: 0 (off)
Range of valid values: 0 or 1 (flag)
Takes effect: when Read is restarted
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
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:
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
These parameters work together, so if one of them is set its default value of 0, then the other parameter, if set to a number other than 0, does not work. Both parameters support the automated process in which the Read process detects that too much time is being taken to process its queries, by checking the ratio of disk gets per executed queries. Once Read makes this determination, it gets rid of its existing read consistent views and replaces it with a new view.
The ORD_MSGS_CK_FREQ parameter is the frequency that the Read process checks if the ratio has been surpassed. For example, if you set this parameter to 100, the Read process will check every 100 queries to see if the ratio is still good. The recommended setting is 1,000 queries.
The ORD_RCM_SKIP_RATIO parameter specifies the number (the ratio of disk gets to executed queries) that when reached, causes the read consistent view to be replaced. The most sensitive setting is 1, which means one disk get per one executed query. The recommended setting is 2.
To turn off these parameters, set one of them to 0, which disables the other parameter.
Default:
SP_ORD_MSGS_CK_FREQ: 1,000
SP_ORD_RCM_SKIP_RATIO: 2
Range of valid values:
SP_ORD_MSGS_CK_FREQ: 0 to 100,000
SP_ORD_RCM_SKIP_RATIO: 0 to 1000 (not recommended to set this greater than 5)
Takes effect: Immediately
This parameter controls the frequency of the checkpoints performed by the Read process when it reads messages from the capture queue and determines the key values. A checkpoint saves the capture queue to a cache file, commits the outgoing queue messages (being passed to the export queue), and does a read release (delete) on the heldover records in the capture queue that have already been received by the export queue.
The higher the value of this parameter, the more records will be held in memory before the checkpoint is triggered. A very high value causes less I/O on the system and faster processing — but at the expense of increased memory usage and a longer recovery time should something unforeseen cause the Read process stop. A low value increases I/O, which increases the recovery speed but reduces throughput speed.
The default value of 1,000 records should establish a reasonable balance between the need for speed and the conservation of memory and process recovery, but you can adjust this parameter to suit your processing requirements. Adjustment options range from checkpointing after every record to holding as many records as the system and its memory can accommodate.
Default: 1,000 records
Range of valid values: any positive integer within system limitations
Takes effect: immediately
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
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.
These parameters are used by the SharePlex Export process.
This parameter controls the size of the AES encryption key that is used by Export to encrypt data that is sent across the network from source to target. The key itself is generated by issuing the create encryption key command, which generates a full 256-bit key. The SP_XPT_AES_KEY_LENGTH parameter controls how much of that length is used as the key when Export encrypts the data. The default is 128 bits, the minimum length. A longer key is harder to hack but requires more CPU power. To configure SharePlex for AES encryption, see the SharePlex Administration Guide.
Default: 128-bits
Range of valid values: 128, 192 or 256 bits
Takes effect: when Export is restarted
This parameter controls whether Advanced Encryption Standard (AES) is enabled to encrypt replicated data that is sent by Export across the network. To configure SharePlex for AES encryption, see the SharePlex Administration Guide.
Default: 0 (disabled)
Range of valid values: 0 or 1 (enabled)
Takes effect: when Export is restarted
This parameter controls the compression of data across TCP/IP.
You can enable compression to reduce the amount of data that SharePlex sends across the network. SharePlex uses LZIP lossless compression. Enabling compression on the source SharePlex instance automatically enables compression to all targets of the source SharePlex instance.
By default compression is disabled. You can enable compression by itself or in conjunction with encryption. For more information about encryption, see the SharePlex Administration Guide.
Default: 0 (disabled)
Range of valid values: 0 or 1 (enabled)
Takes effect: when Export is restarted
This parameter controls whether the Export process sends a "hello" message to Import at regular intervals to prevent TCP timeouts when replication activity is low. If the network times out because no replication packets are being sent, SharePlex alerts you with a message such as "Export cannot connect to import on server2: timeout waiting for ack." By setting SP_XPT_KEEPALIVE to 1, you can eliminate this message and keep the SharePlex network connection alive.
Default: 0 (disabled)
Range of valid values: 0 or 1 (enabled)
Takes effect: when Export is restarted
This parameter works in conjunction with the SP_XPT_USE_LOCALHOST parameter to enable SharePlex to send data through a secure tunnel port created with SSH® Secure ShellTM software. This parameter sets the local connection, which overrides the default SharePlex port. From that port number, the SSH daemon directs the connection to a different system (the SharePlex target machine) using another port number and the SSH data encryption.
Default: 0 (disabled)
Range of valid values: 0 or any positive integer
Takes effect: when Export is restarted
This parameter tunes the TCP/IP window size on the source machine. It works in conjunction with the SP_COP_SO_RCVBUF parameter on the target machine to establish the size of a packet sent across the network. SharePlex references both parameters when TCP/IP sockets are created at the startup of sp_cop and the Export process.
If SharePlex is replicating across a WAN and the export queue is continually backlogged, try adjusting both parameters. SP_XPT_SO_SNDBUF must be set equal to or greater than the value of SP_COP_SO_RCVBUF, in multiples of 1024 bytes. To size the parameters, determine the ping time between the source and target machines, then use the following formula for both parameters:
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 will send five 64K-packets every second, totaling 320K per second.
Unless you observe a bandwidth problem, Quest recommends leaving both parameters set to their defaults, which use the system's setting. To change SP_XPT_SO_SNDBUF, set it on the source system, then stop and start Export on that system.
If transfer still is slow, try increasing the SP_IMP_WCMT_MSGCNT and SP_IMP_WCMT_TIMEOUT parameters on the target system. Set SP_IMP_WCMT_MSGCNT to at least 10,000.
Default: 0 (default is set by the operating system)
Range of valid values: positive integers, in bytes, using multiples of 1024. Maximum is set by the operating system.
Takes effect: when Export is restarted
This parameter enables SharePlex to send data through a secure tunnel port created with SSH® Secure ShellTM software. The Export process reads this parameter before making a TCP connection. If the parameter is enabled, the Export process connects to the local host through a local port number, where the SSH daemon directs the connection to a different system (the SharePlex target machine) using another port number and the SSH data encryption.
Default: 0 (disabled)
Range of valid values: 0 or 1 (flag)
Takes effect: when Export is restarted
These parameters are used by the SharePlex Import process.
This parameter enforces the use of data encryption between the source system and the target on which it is set. It prevents Import from accepting incoming data unless data encryption is enabled in the Export process. It must be enabled if SP_XPT_ENABLE_AES is enabled.
To configure SharePlex for AES encryption, see the SharePlex Administration Guide.
Default: 0 (disabled)
Range of valid values: 0 or 1 (enabled)
Takes effect: when Import is restarted
When Import is requested to write to a post queue, and the backlog for that queue is equal to or greater than SP_IMP_QUEUE_PAUSE, Import checkpoints with it associated Export and stops writing to any post queue. Import does, however, remain running. A warning is written to the event log and the status of the process in the show statusdb command output is shown as "paused." The status command shows "Paused" instead of "Running."
Import resumes writing to the post queue(s) if Import is stopped and restarted or Import detects that the backlog is less than or equal to the SP_IMP_QUEUE_RESUME parameter.
Use the SP_IMP_QUEUE_RESUME parameter to set the number of messages at which Import resumes writing to the post queue.
To use this feature, both SP_IMP_QUEUE_PAUSE and SP_IMP_QUEUE_RESUME must be greater than zero, and SP_IMP_QUEUE_PAUSE must be greater than SP_IMP_QUEUE_RESUME.
Default: 0 messages (disabled)
Range of valid values: n thousand messages, where n is any positive integer
Takes effect: immediately
This parameter works in conjunction with SP_IMP_QUEUE_PAUSE. If the number of messages in the post queue is lower or equal to the value set with this parameter, Import resumes writing to the post queue.
To use this feature, both SP_IMP_QUEUE_PAUSE and SP_IMP_QUEUE_RESUME must be greater than zero, and SP_IMP_QUEUE_PAUSE must be greater than SP_IMP_QUEUE_RESUME.
Default: 0 messages (disabled)
Range of valid values: n thousand messages, where n is any positive integer
Takes effect: immediately
This parameter works in conjunction with the SP_IMP_WCMT_TIMEOUT parameter. It defines the number of messages that are processed before the Import process checkpoints. Checkpointing saves the state of the process in case it is needed for failure recovery.
When Import checkpoints, it triggers the Export process to perform its own checkpoint. If the value for this parameter is reached before the value set for SP_IMP_WCMT_TIMEOUT, it triggers the checkpoint.
In a WAN environment, you can increase this parameter to as many as 10,000 messages, with the understanding that increasing the message interval between checkpoints can negatively affect SharePlex's fault tolerance. If you are using SharePlex in an environment where the network continually fails, you can decrease this parameter.
Default: 10,000 messages
Range of valid values: any positive integer
Takes effect: immediately
This parameter works in conjunction with the SP_IMP_WCMT_MSGCNT parameter. It defines the number of seconds that pass before the Import process checkpoints. Checkpointing saves the state of the process in case it is needed for failure recovery.
When Import checkpoints, it triggers the Export process to perform its own checkpoint. If the value for this parameter is reached before the value set for SP_IMP_WCMT_MSGCNT, it triggers the checkpoint.
Default: 30 seconds
Range of valid values: any positive integer
Takes effect: immediately
© ALL RIGHTS RESERVED. 使用条款 隐私 Cookie Preference Center
