These parameters are used by the SharePlex Capture process.

SP_OCT_ALLOW_DP_DDL

This parameter can be enabled if SharePlex fails to replicate DDL operations that occur when running an Oracle Data Pump export/import. Occasionally, SharePlex identifies DDL in a Data Pump load as recursive DDL that should be ignored. This parameter directs SharePlex to capture that DDL.

A setting of 1 enables this parameter. After the load is finished, set this parameter back to 0 and then restart Capture.

 

Default: 0

Range of valid values: 0 or 1 (flag)

Takes effect: upon restart of the Capture process

 

SP_OCT_ARCH_LOC

This parameter defines the path to the archive logs. When the redo logs wrap, SharePlex looks for the archive log in Oracle’s archive log list. If SharePlex cannot find the archive log there, it looks in the directory or directories specified by this parameter. It also searches the sub-directories. The default is the /home directory. If the path for the archive logs is different, specify the correct full path name with this parameter. You can specify more than one directory path if you separate them with semicolons (;) as shown in the following example. In this example, Capture will search under both /disk1/log and /disk2/log paths to find the logs.

sp_ctrl> set param SP_OCT_ARCH_LOC /disk1/log;/disk2/log

To specify the directory path for SharePlex for RAC, use the following:

sp_ctrl> set param SP_OCT_ARCH_LOC !;<node1_oracle_sid>;<node1_arch_absolutepath>;<node2_oracle_sid>;<node_arch_absolute_path>

Note that the non-RAC format for path names allows a list of directory path names separated by a semicolon. The list can be any length desired up to the limit of 1023 bytes. However, the RAC format, which is !; followed by a list of sid;pathname pairs, does not allow the pathname to be a list of path names as the non-RAC format does. Each SID must be followed by exactly one directory pathname. If you want to specify more than one pathname for a single instance SID, you must put in two pairs of entries in which each of the entries has the same SID. Thus, you cannot specify

!;sid1;path2;path2;path3;sid2;path4

Rather, the proper format for the preceding example is

!;sid1;path2;sid1;path2;sid1;path3;sid2;path4

 

Default: empty string

Range of valid values: full directory path to the restored archive logs

Takes effect: immediately.

 

SP_OCT_ASM_CACHE_AGE

This parameter controls how long the current ASM cache is kept, before before a re-query of the ASM blocks is performed to update it.

 

Default: 40

Range of valid values: Any positive integer

Takes effect: immediately

 

SP_OCT_ASM_ECACHE_SIZE

The number of elements in the buffer to cache extents. Extents are the units of file storage in Oracle ASM. Increasing this number increases memory usage.

 

Default: 20

Range of valid values: Any value greater than 9

Takes effect: upon restart of the Capture process

 

SP_OCT_ASM_FLIST_SIZE

Maximum number of ASM files that will be read simultaneously. Increasing this number increases memory usage.

 

Default: 10

Range of valid values: Any value greater than 4

Takes effect: upon restart of the Capture process

 

SP_OCT_ASM_MULTI_OCI_BLOCK_SIZE

This parameter controls the size of the buffer that is used by each Capture thread when SP_OCT_ASM_MULTI_OCI is set to enable multi-threaded capture on Exadata systems. This parameter should be left to its default. Capture automatically adjusts its buffer size to the value of the AU_SIZE parameter that is set for the disk group where the logs reside. This is the recommended buffer size for best performance.

 

Default: 0

Range of valid values: Any positive integer

Takes effect: upon restart of the Capture process

 

SP_OCT_ASM_MULTI_OCI

This parameter controls the number of threads that Capture uses to read the redo logs. To use this parameter, you must also set SP_OCT_OLOG_USE_OCI to 1.

The value for this parameter must be set to at least 2 but no more than the number of disks in the redo log disk group.

A large number of threads is not required, and performance actually diminishes with too many threads. The more threads, the more memory Capture requires. Start with a small number of threads and monitor performance, then add threads if needed until you obtain an ideal balance between performance gain and memory usage.

 

Default: 0 (single threaded capture)

Range of valid values: Minimum value is 2, maximum value is the number of disks in the ASM disk group that stores the redo logs.

Takes effect: upon restart of the Capture process

SP_OCT_ASM_SLIST_SIZE

Maximum number of sessions that will be accessing files simultaneously.

 

Default: 10

Range of valid values: Any value greater than 4

Takes effect: upon restart of the Capture process

 

SP_OCT_ASM_SUPPORT

This parameter enables and disables support for redo and archive logs on ASM supported platforms. It is enabled automatically if the SharePlex database connection is configured to connect to ASM.

 

Default: 0 (off)

Range of valid values: 0 or 1 (flag)

Takes effect: upon restart of the Capture process

 

SP_OCT_AUTOADD_ENABLE

This parameter is a global parameter that enables newly created objects to be added automatically to an active replication configuration. By default it applies to tables and indexes. To auto-add other object types, additional parameter settings are required. The functionality works as follows:

Auto-add of the following objects is enabled by default when you enable SP_OCT_AUTOADD_ENABLE:

  • A table named in a CREATE TABLE operation is automatically added to replication if the name matches a wildcard specification in the active configuration file.
  • An index created with a CREATE INDEX operation is automatically added to replication if the table on which it was created is specified in the configuration file.
  • A table named in a CREATE TABLE AS SELECT operation is automatically added to replication if the name matches a wildcard specification in the active configuration file. SharePlex creates the new table on the target, followed by the replication of the data in the source table.
  • ALTER and DROP on the added objects.

The following objects must be explicitly enabled for auto-add support in addition to enabling SP_OCT_AUTOADD_ENABLE:

The supporting table for a materialized view named in a CREATE MATERIALIZED VIEW operation is automatically added to replication if the name of the materialized view matches a wildcard in the active configuration file and the SP_OCT_AUTOADD_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).

Make sure DDL replication is fully enabled by checking that the parameter SP_OCT_REPLICATE_DDL is set to 1 or 3.

 

Default: 1 (on)

Range of valid values: 0 or 1 (flag)

Takes effect: immediately

 

SP_OCT_AUTOADD_MVIEW

This parameter determines whether SharePlex automatically adds materialized views to replication when they are created after activation. When enabled, it causes SharePlex to add the underlying table of a new materialized view to replication if the name of the materialized view satisfies a wildcard in the active configuration file. SharePlex converts the CREATE MATERIALIZED VIEW into a CREATE TABLE statement, posts the CREATE TABLE to the target, and replicates the DML that populates the view. The table is maintained by replication through future supported DDL and DML changes.

To auto-add materialized views, both this parameter and the SP_OCT_AUTOADD_ENABLE parameter must be set to 1, and the version of SharePlex on the target must be at least 8.6.2.

 

Default: 0 (off)

Range of valid values: 0 or 1 (flag)

Takes effect: 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.

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

 

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_DDL_UPDATE_CONFIG

This parameter controls whether SharePlex updates the table name in the configuration file when processing ALTER TABLE RENAME. By default, SharePlex updates the table name when it processes this DDL operation. To prevent updates the configuration file, disable this parameter.

 

Default: 1 (enabled)

Range of valid values: 0 or 1

Takes effect: when Capture is restarted

SP_OCT_DEF parameters

The following parameters can be set so that SharePlex corrects the format of dates and times if they were incorrectly entered by a user or application and bypassed the database’s validity check. These parameters take effect as soon as they are activated.

SP_OCT_DEF_MONTH

range of values is from 1 - 12
SP_OCT_DEF_DAY range of values is from 1 - 31
SP_OCT_DEF_YEAR range of values is from 1987 - 9999
SP_OCT_DEF_HOUR range of values is from 0 - 23
SP_OCT_DEF_MIN range of values is from 0 - 59
SP_OCT_DEF_SEC

range of values is from 0 – 59

 

SP_OCT_DENIED_USERID

This parameter can be used to specify an Oracle userid for which all DML and DDL transactions should be ignored or filtered by the Capture process.

Important: Ignoring transactions on the source machine may lead to an out-of-sync condition.

SharePlex does not verify that the specified userid exists.

 

Default: 0x00000000

Range of valid values: integers (any valid Oracle userid)

Takes effect: immediately

SP_OCT_DLOAD_MAX_COLS_PER_MSG

This parameter controls the maximum size of a DLOAD message. The number of rows in a message is determined by dividing this parameter by the number of columns in each row. For example: If a table has 99 columns and there is a DLOAD with 90 rows and this parameter is set to 5000 then it would split the message into 2 DLOAD messages with 50 rows of 99 columns for a total of 4950 columns in the first message the rest in the second message.

 

Default: 10000

Range of valid values: any value greater than 99

Takes effect: immediately

 

SP_OCT_ENABLE_LOBMAP

This parameter controls whether or not SharePlex uses a LOB map when replicating tables that contain out-of-row LOB columns. The LOB map is used by the Capture process to map LOBIDs and rows when PK/UK logging is not enabled. LOB mapping is enabled by default. The SHAREPLEX_LOBMAP table stores these mappings.Transactions with numerous LOB operations can slow down Capture because it needs to maintain and refer to the mappings. If PK/UK logging is enabled on the database, you can disable LOB mapping by setting this parameter to 0.

 

Default: 1 (on)

Range of valid values: 0 or 1

Takes effect: 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: 0 (off)

Range of valid values: 0 or 1

Takes effect: immediately

 

SP_OCT_LOB_BUFFER_SIZE

This parameter controls the size of Capture’s LOB buffers, which must be maintained until Capture can assemble the related transaction information. If SharePlex is unable to determine the transaction, it uses a special sub-queue outside of the ones containing the transactions. The headers for these sub-queues require more shared memory, which can require an increase in the SP_OCT_LOB_BUFFER_SIZE parameter.

The larger the LOB buffers, the more likely that system memory will run out, preventing buffering for subsequent LOB operations until memory becomes available again.

Therefore, you might need to increase the SP_QUE_Q_SHMSIZE parameter in conjunction with increasing the SP_OCT_LOB_BUFFER_SIZE parameter. Generally, only the LOB data for VARRAYs is buffered, so this parameter generally only impacts transactions involving VARRAYs.

 

Default: 5 MB of memory per LOB

Range of valid values: any positive integer

Takes effect: when Capture is restarted

 

SP_OCT_LOG_FILESIZE

This parameter sets the size of the Capture debug log file.

 

Default: 50000000 bytes

Range of valid values: any value greater than 9999 bytes

Takes effect: when Capture is restarted

 

SP_OCT_LOG_MEMBER

The SP_OCT_LOG_MEMBER parameter is used to augment queries of the data dictionary to specify redo log locations. You can use this parameter to specify exactly what log file you want to operate on.

 

Default: % (percent symbol)

Range of valid values: any correctly formed file name specification with wildcard characters

Takes effect: when Capture is restarted

 

SP_OCT_LOG_NUMFILES

This parameter controls the number of Capture debug log files that are allowed before the oldest one is deleted and a new one is created.

 

Default: 3

Range of valid values: any value greater than 1

Takes effect: when Capture is restarted

 

SP_OCT_LOG_READ_SIZE

The Capture process reads multiple redo-log file blocks in one pass. This parameter controls the number of blocks read at a time, enabling you to adjust the value according to the system’s configuration and whether or not Capture is falling behind the pace of Oracle’s processing. Keep in mind that when the value is higher than necessary, it incurs more system overhead.

 

Default: 64 blocks

Range of valid values: any positive integer

Takes effect: when Capture is restarted

 

SP_OCT_LOGWRAP_RESTART

This parameter controls how Capture behaves in response to the following situations:

  • The archives are not available.
  • Capture is denied permission to open a log.

When Capture cannot find an archive log, or when it cannot open a log, the process stops by default (a setting of 0). At this setting, when Capture cannot access a log, the show capture command returns a status of "Stopped due to missing archive log."

You can configure Capture to wait a certain amount of time (in seconds) and then start again automatically. This allows time to assign the correct read permissions or run any external log-management processing that moves the archives into the location expected by SharePlex. Capture waits, checks for the logs, stops if they are not yet available or cannot be opened, and continues checking and stopping until the logs are restored or opened. At this setting, the show capture command returns a status of "waiting for logfile."

 

Default: 0 seconds (do not restart automatically if logs are not available)

Range of valid values: 1 to any positive number of seconds

Takes effect: 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_RDS_MINER

Note: Enabling the SP_OCT_OLOG_RDS_MINER parameter is deprecated and no longer supported starting with Oracle 19c.

This parameter controls whether or not Capture uses a second thread to help keep Capture from lagging behind Oracle when capturing from an Oracle RDS database.

Due to the processing load incurred by using this thread, it is disabled by default. To enable it, set this parameter to 1.

 

Default: 0 (off)

Range of valid values: 0 or 1 (enabled)

Takes effect: Process restart

 

SP_OCT_OLOG_REOPEN

This parameter controls whether or not the Capture process will close and re-open the online redo log when it reaches the end of the file. If this parameter is enabled, the user may control the length of time that the Capture process will wait before retry by employing the SP_OCT_OLOG_NO_DATA_DELAY parameter.

 

Default: 1 (off)

Range of valid values: 0 or 1 (flag)

Takes effect: Process restart

 

SP_OCT_OLOG_USE_OCI

This parameter controls whether Capture captures the source change records by reading the redo logs directly from the files or by using OCI calls. The default of 0 directs Capture to read directly from the redo logs.

  • If the source database is running on ASM, enabling this parameter is optional. Capture can read the redo logs directly or use OCI calls.
  • If the source database is remote from the machine where Capture is running, such as on a cloud service, this parameter must be set to 1 so that Capture uses OCI calls to get the source change records.

 

Default: 0 (off, Capture reads directly from the redo logs)

Range of valid values: 0 or 1 (flag)

Takes effect: Process restart

 

SP_OCT_ONELINE_DEBUG

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

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

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

sp_ctrl> set sp_oct_oneline_debug 230230, 351626

The following items are logged:

  • The transaction identifier used by SharePlex
  • The type of DML or DDL operation that was affected
  • The object ID of the affected source table
  • The row identifier of the affected row. (Oracle rowid)
  • The time when the operation occurred (Oracle)
  • The log sequence number and offset within the log of the affected operation
  • The Oracle SCN for the transaction
  • An internal SharePlex code that prevents redundant operations

 

Default: Disabled

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

Takes effect: Immediately

 

SP_OCT_OPS_LOGREADER_DELAY

This parameter controls the log reader delay time before retry when it encounters no_data.

 

Default: 50000 (microseconds)

Range of valid values: any positive integer

Takes effect: when Capture is restarted

 

SP_OCT_OPS_LOGREADER_RETRY

This parameter controls the number of times the log reader will retry when it encounters no_data.

 

Default: 3

Range of valid values: any positive integer

Takes effect: when Capture is restarted

 

SP_OCT_OPS_NO_DATA_DELAY

This parameter controls the length of the sleep that is enforced per node when no data is encountered in the sequencer.

 

Default: 5 (deciseconds)

Range of valid values: any positive integer

Takes effect: when Read is restarted

 

SP_OCT_PREFER_ARCHIVE

This parameter controls whether Capture reads the online version of a redo log or the archived version if it is available. By default, Capture reads only from the online logs and only reads an archived version if the online log is no longer available.

When this parameter is set and Capture finds the required log online, Capture makes an additional check to determine if the log is also archived. If the log is archived, Capture queries the archive path and reads the archived log instead of the online one. If the log is not archived, Capture reads the online log.

Normally, SharePlex provides the least latency when reading from the online redo logs, but this parameter may help improve Capture performance on Exadata. SharePlex can process higher volumes of data when reading from a multiplexed archive location outside of the Exadata ASM file system. Should a backlog develop with high volumes of data on Exadata, you can set SP_OCT_PREFER_ARCHIVE parameter to a value of 1 and configure SharePlex as directed in the Tune Capture on Exadata section in the SharePlex Administration Guide.

To use this parameter, multiplexing of the redo logs must be enabled, and the path to the archive logs must be set in SharePlex. For more information, see SP_OCT_ARCH_LOC .

 

Default: 0 (disabled)

Range of valid values: 0 or 1 (flag)

Takes effect: when Capture is restarted

 

SP_OCT_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. It ensures that Capture has a complete block to process.

Capture disables this parameter automatically when capturing from a remote database.

 

Default: 2 seconds

Range of valid values: 0 to any positive integer

Takes effect: when Capture is restarted

 

SP_OCT_REDUCED_KEY

This parameter controls which parts of an operation in the redo log are sent by Capture to the target for use by Post. Different SharePlex features may require more or less data to be made available to the Post process.

  • If set to 0, this parameter directs Capture to send all of the data that Oracle writes to the redo record other than LONGs and LOBs. This setting sends the most data across the network, but is required to support certain SharePlex features.
  • If set to 1 (the default), this parameter directs Capture to send the key values and, for UPDATEs, the before and after values of columns that were changed in the operation. This setting is the default. This setting provides a good balance between replication performance and target data integrity, because it enables Post to perform a before-and-after comparison of the changed columns when constructing the WHERE clause. For more information about how SharePlex uses before and after values, see the SharePlex Administration Guide.
  • If set to 2, this parameter directs Capture to send the key values and, for UPDATEs, the after values of the columns that changed to the target. This setting sends the least amount of data across the network and also enables Post to only use the key values when posting data, which improves performance.

See also SP_OPO_REDUCED_KEY.

 

Default: 1

Range of Valid Values: 0, 1, 2

Takes effect: When Capture is restarted

 

SP_OCT_REPLICATE_ALL_DDL

This parameter controls expanded DDL replication, which is the replication of DDL for objects that are not in the SharePlex replication configuration file. The objects must exist in the source database before configuration activation and also on the target (except in the case of CREATE). SharePlex replicates the DDL statements, but does not maintain the objects on the target throughout any future DML changes.

Setting the SP_OCT_REPLICATE_ALL_DDL requires identical source and target databases in order for the replicated DDL to execute successfully on the target system. Because this DDL is broadcast to all target machines, all target systems must be identical.

SharePlex replicates supported DDL for all objects in all schemas of the active datasource, so those components must exist in the target database for the operations to succeed.

To configure the Post process to stop for errors encountered when applying replicated DDL operations to the target database, set the SP_OPO_STOP_ON_DDL_ERR parameter.

If you are replicating the creation of packages or stored procedures, the name of the objects in the body of the package or stored procedure should be fully qualified, because other users or schemas may execute this package or stored procedure.

For a list of operations supported by SharePlex, see the Release Notes that correspond to your version of SharePlex.

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 to work around this issue:

  1. Remove source objects from replication
  2. Perform the flashback
  3. Add or change objects in an active configuration

 

Default: 0 (off)

Range of valid values: 0 or 1 (flag)

Takes effect: immediately

 

SP_OCT_REPLICATE_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 in the active configuration that are being replicated by SharePlex:

  • ALTER TABLE to ADD COLUMN, MODIFY COLUMN, DROP COLUMN

  • ALTER TABLE to ADD, MODIFY, DROP, SPLIT, COALESCE, MOVE, TRUNCATE, EXCHANGE PARTITION/SUBPARTITION

  • TRUNCATE TABLE

You can control whether both operation types are replicated, or just one or the other, or none.

See also SP_OCT_AUTOADD_ENABLE for additional default DDL replication functionality.

To replicate DDL on objects outside the configuration file, see the SP_OCT_REPLICATE_ALL_DDL parameter.

 

Default: 3 (replicate ALTER TABLE and TRUNCATE)

Range of valid values:

0 (disable replication of both ALTER TABLE and TRUNCATE)

1 (enable ALTER replication only)

2 (enable TRUNCATE replication only)

3 (enable replication of ALTER and TRUNCATE)

Takes effect: immediately

 

SP_OCT_REPLICATE_DLOAD

This parameter controls whether or not SQL*Loader direct-path loads are replicated. The default setting of 1 enables direct-path load replication. SharePlex supports replication for non-parallel loads only (PARALLEL=FALSE). The database must be in archive mode, and table logging must be enabled. To disable replication of direct-path loads, change this parameter to 0.

 

Default: 1 (replicate direct-path loads)

Range of valid values: 0 or 1 (flag)

Takes effect: immediately

 

SP_OCT_REPLICATE_GRANT

This parameter controls whether SharePlex replicates GRANT commands that are issued for tables that are listed in the configuration file.

 

Default: 0 (disabled)

Range of valid values: 0 or 1

Takes effect: when Capture is restarted

 

SP_OCT_REPLICATE_MVIEW

This parameter controls whether or not activation will put the existing materialized views found during activation into replication. By default, it is enabled. The materialized views must exist in the source and target before activation.

This parameter is used only during activation to decide whether or not to put the materialized view in replication. To configure SharePlex to add materialized views to replication when they are created after activation, see SP_OCT_AUTOADD_MVIEW.

 

Default: 1 (replicate materialized views)

Range of valid values: 0 or 1

Takes effect: for the next activation

 

SP_OCT_REPLICATE_POSTER

This parameter controls whether or not the Capture process on a system replicates data posted by the Post process on that system. Leave this parameter at the default setting of 0, which tells Capture to ignore Post activities on the same system. When establishing certain replication configurations — primarily cascading replication — you might be instructed to set this parameter to 1, which causes Capture to replicate posted changes.

 

Default: 0 (do not replicate Post transactions)

Range of valid values: 0 or 1 (flag)

Takes effect: when Capture is restarted

 

SP_OCT_REPLICATE_SEQUENCES

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

You can configure SharePlex to add sequences to replication when they are created after activation. For more information, see SP_OCT_AUTOADD_SEQ.

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

Important! To replicate sequences, the supplemental logging of primary and unique keys must be enabled at the database level, or you must enable supplemental logging for primary keys on the sys.seq$ table.

 

Default: 1 (replicate sequences)

Range of valid values: 0 or 1

Takes effect: 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.

Note: Do not replicate triggers in an active-active replication scenario. For more information, see Configure replication to maintain multiple peer databases in the SharePlex Administration Guide.

 

Default: 0 (disabled)

Range of valid values: 0 or 1

Takes effect: when Capture is restarted

 

SP_OCT_REQUIRED_DATA_IS_LOGGED

This parameter enables the Capture process to gather additional information to improve rollback handling. With rollback handling, the Read process will require more resources to accomplish and improve performance in return. If system resources are scarce and the systems experiences lots of rollbacks, to lower the Read process resource consumption, you can disable this parameter. This parameter is enabled by default.

 

Default: 1 (on)

Range of valid values: 0 or 1 (flag)

Takes effect: when Capture is restarted

 

SP_OCT_TRUNC_PARTITION_BY_ID

Use this parameter to ensure that replication of an ALTER TABLE to truncate or drop a system-generated partition affects the correct partition on the target.

Because the database generates the names of system-generated partitions, the names of those partitions on the source will not match the names of their corresponding partitions on the target. However, the partition positions for the same time frame will match if the target is an exact copy of the source.

To ensure that replication affects the correct partition on the target:

  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_SYS_TARGET_COMPATIBILITY parameter to verify that the target Post process is version 8.6.4 or higher. This is the minimum version that supports ALTER TABLE to truncate or delete a system-generated partition by ID. If the target Post process is an earlier version, the ALTER TABLE is replicated by using the partition name, and a warning message is logged to the SharePlex event_log on the target.

 

Default: 0 (disabled)

Range of valid values: 0 or 1

Takes effect: immediately

SP_OCT_USE_DST

Use this parameter to tell SharePlex to disable the correction for daylight savings time in redolog to ensure logic.

 

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

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