Chat now with support
Chat with Support

SharePlex 8.6.6 - Reference Guide

About this guide Conventions used in this guide SharePlex commands SharePlex parameters SharePlex utilities Appendix B: SharePlex environment variables

SharePlex parameters

SharePlex parameters

SharePlex parameters control and tune various aspects of replication.

For instructions on setting parameters, see the SharePlex Administration Guide.

Contents

Descriptions of user-configurable parameters

SharePlex parameters > Descriptions of user-configurable parameters

This section describes the user-configurable SharePlex parameters. Parameters not documented in this chapter are internal parameters that should only be modified under guidance of a Quest developer or Technical Support representative.

Parameters are grouped as follows:

Configuration Parameters

These parameters are used by the SharePlex activation process. They take effect at the time that you activate a configuration file.

SP_OCF_HASH_BY_BLOCK

This parameter controls whether the hash algorithm used in horizontally partitioned replication is based on the rowid or on the block where the row resides. The default is 0, which uses the hash based on rowid. Using a hash based on the block may improve the performance of the Post process when processing tables that are using horizontally partitioned replication. To enable a hash based on the block, set this parameter to 1 and then reactivate the configuration file.

 

Default: 0 (disabled)

Range of valid values: 0 or 1 (enabled block-based hash)

Takes effect: for the next activation

 

SP_OCF_LOCK_WAIT_TIME

This parameter tells SharePlex how long to wait before failing activation on a particular table. Since the table must be locked for activation, the logic is to retry the lock for a designated period of time, and this designated period of time is controlled by the SP_OCF_LOCK_WAIT_TIME parameter.

 

Default: 5 minutes

Range of valid values: Any positive integer

Takes effect: for the next activation

 

SP_OCF_THREAD_COUNT

This parameter controls the default number of threads that SharePlex generates during configuration activation. It might be necessary to experiment with the number of threads to determine the optimal performance level. As an example, for a 32-CPU machine with a large disk array, 10 or more threads could show improved performance. The value for the thread count is independent of the number of tables to be analyzed.

 

Default: 3 threads

Range of valid values: 1 to 32

Takes effect: for the next activation

 

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. 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

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_MULTI_OCI_BLOCK_SIZE

This parameter controls the size of the buffer that is used by each Capture thread when SP_OCT_ASM_MULTI_OCT 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 on an Exadata system.

The value set for this parameter must satisfy all of the following:

  • greater than 1.
  • an even divisor of the number of disks in the ASM disk group that stores the redo logs. For example, if there are 16 disks, the values of 2, 4, 8, and 16 are valid.
  • not larger than the number of disks in the ASM disk group that stores the redo logs.

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. For example, if there are 16 disks, you could start with a value of 2.

 

Default: 0 (single threaded capture)

Range of valid values: 2 to the number of disks in the ASM disk group that stores the redo logs (as an even divisor of that number)

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_ASM_USE_OCI

This parameter controls Capture from redo logs stored in ASM on an Exadata system. It must be enabled for SharePlex to capture on an Exadata system.

 

Default: 0 (off)

Range of valid values: 0 or 1 (flag)

Takes effect: immediately

 

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.

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. TheSP_OCT_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_OCT_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

 

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: immediately

 

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.

 

Default: 0 (off)

Range of valid values: 0 or 1 (flag)

Takes effect: immediately

 

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: immediately

 

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_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 10g+ 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_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: immediately

 

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: 1 (on)

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: when Capture is restarted

 

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: when Capture is restarted

 

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: 5000

Range of valid values: 100 to any positive integer

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: when Read is restarted

 

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.
  • The time when the operation occurred
  • 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 Tune Capture on Exadata 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: 1 (off)

Range of valid values: 0 or 1 (flag)

Takes effect: when Capture is restarted

 

SP_OCT_REDO_LOG_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.

 

Default: 2 seconds

Range of valid values: 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: When Capture is restarted

 

SP_OCT_REPLICATE_ALL_DDL

This parameter controls 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.

 

Default: 0 (off)

Range of valid values: 0 or 1 (flag)

Takes effect: immediately

 

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.

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:

  1. Replicate a CREATE TABLE statement to create the new table on the target.
  2. 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: for the next activation

 

SP_OCT_REPLICATE_DDL

This parameter controls whether SharePlex replicates the following operations on objects that are being replicated by SharePlex in the active configuration:

  • ALTER TABLE to ADD COLUMN
  • ALTER TABLE to DROP COLUMN
  • TRUNCATE TABLE

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 and TRUNCATE)

Range of valid values:

0 (disable replication of both ALTER 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_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_MVIEW

This parameter controls whether SharePlex replicates changes made to materialized views that are listed in the parameter file, either explicitly or by means of a wildcard. By default, it is enabled. The materialized views must exist in the source and target before activation.

You can configure SharePlex to add materialized views to replication when they are created after activation. For more information, see SP_OCT_AUTOADD_MVIEW.

A setting of 0 excludes materialized views from replication, even if they are listed in the configuration file or their names satisfy a wildcard specification.

 

Default: 1 (replicate materialized views)

Range of valid values: 0 or 1

Takes effect: for the next activation

 

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.

 

Default: 1 (replicate sequences)

Range of valid values: 0 or 1

Takes effect: for the next activation

 

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.

 

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_TARGET_COMPATIBILITY

This is renamed to SP_SYS_TARGET_COMPATIBILITY in v9.0 and later.

This parameter enables you to preserve compatibility between different versions of SharePlex to allow for smoother migrations and upgrades. As SharePlex continues to evolve to satisfy new user requirements, features or functionality added in a new version are not always compatible with previous versions. This parameter enables replication from a higher version of SharePlex on the source system to a lower version on a target system.

By default, this parameter is set to the version of the installed SharePlex software.

  • When replicating from a higher version on a source system to a lower version of SharePlex on a target, set this parameter to a value that most closely matches the version of SharePlex on the target. For example, if SharePlex is version 8.6.3 on the source and 8.6.2 on the target, set this parameter to 8.6.2.
  • When replicating between identical versions, leave this parameter set to the default on both systems.

 

Default: The version of the installed SharePlex software

Range of valid values: SharePlex release versions from 6.0.0 to the current version, up to three numbers long (for example 8.6.3).

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:

  1. 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.
  2. 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_OCT_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: 1 (Enabled)

Range of valid values: 0 or 1

Takes effect: immediately

 

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 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.

 

Default: 0 (Disabled)

Range of valid values: 0 or 1 (Enabled)

Takes effect: when Capture is restarted

Related Documents