SharePlex parameters control and tune various aspects of replication.
For instructions on setting parameters, see the SharePlex Administration Guide.
SharePlex parameters control and tune various aspects of replication.
For instructions on setting parameters, see the SharePlex Administration Guide.
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:
These parameters are used by the SharePlex activation process. They take effect at the time that you activate a configuration file.
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
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: Immediately
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: Immediately
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
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
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
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_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
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
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_MVIEW parameter is set to 1. The SP_SYS_TARGET_COMPATIBILITY parameter must be set to at least 8.6.2 to support auto-add of new materialized views.
A sequence named in a CREATE SEQUENCE operation is automatically added to replication if the name matches a wildcard in the active configuration file and the SP_OCT_AUTOADD_SEQ parameter is set to 1. The SP_SYS_TARGET_COMPATIBILITY parameter must be set to at least 8.6.3 to support auto-add of new sequences.
Note: To replicate changes to the sequence, the SP_OCT_REPLICATE_SEQUENCES parameter must be set to 1 (the default).
Make sure DDL replication is fully enabled by checking that the parameter SP_OCT_REPLICATE_DDL is set to 1 or 3.
Default: 1 (on)
Range of valid values: 0 or 1 (flag)
Takes effect: immediately
This parameter determines whether SharePlex automatically adds materialized views to replication when they are created after activation. When enabled, it causes SharePlex to add the underlying table of a new materialized view to replication if the name of the materialized view satisfies a wildcard in the active configuration file. SharePlex converts the CREATE MATERIALIZED VIEW into a CREATE TABLE statement, posts the CREATE TABLE to the target, and replicates the DML that populates the view. The table is maintained by replication through future supported DDL and DML changes.
To auto-add materialized views, both this parameter and the SP_OCT_AUTOADD_ENABLE parameter must be set to 1, and the version of SharePlex on the target must be at least 8.6.2.
Default: 0 (off)
Range of valid values: 0 or 1 (flag)
Takes effect: Process restart
This parameter determines whether SharePlex automatically adds sequences to replication when they are created after activation. When enabled, it causes SharePlex to replicate the CREATE statement if the name of the sequence satisfies a wildcard in the active configuration file. SharePlex then maintains the object on the target throughout future DDL and DML changes. To auto-add sequences, both this parameter and the SP_OCT_AUTOADD_ENABLE parameter must be set to 1, and the version of SharePlex on the target must be at least 8.6.3.
Important! To replicate sequences, the supplemental logging of primary and unique keys must be enabled at the database level, or you must enable supplemental logging for primary keys on the sys.seq$ table.
Default: 0 (off)
Range of valid values: 0 or 1 (flag)
Takes effect: Process restart
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: Process restart
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 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
This parameter controls whether or not SharePlex uses a LOB map when replicating tables that contain out-of-row LOB columns. The LOB map is used by the Capture process to map LOBIDs and rows when PK/UK logging is not enabled. LOB mapping is enabled by default. The SHAREPLEX_LOBMAP table stores these mappings.Transactions with numerous LOB operations can slow down Capture because it needs to maintain and refer to the mappings. If PK/UK logging is enabled on the database, you can disable LOB mapping by setting this parameter to 0.
Default: 1 (on)
Range of valid values: 0 or 1
Takes effect: Process restart
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: Immediately
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: Immediately
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: 16384
Range of valid values: 100 to any positive integer
Takes effect: Process restart
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
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 whether SharePlex will read only from Oracle archive logs. The default setting of 0 means SharePlex will utilize both online and archived logs to read Oracle redo logs. Any non-zero value means SharePlex capture process will read only from archived Oracle redo logs. SharePlex will wait until an online log is archived before attempting to read that sequence of the Oracle redo log.
0 = default behavior SharePlex, read both online and archived redo logs
1 = read only archived redo logs. The search sequence for the archived logs will start from Oracle defined archived location followed by searching under SP_OCT_ARCH_LOC directory specification.
2 = read only archived redo logs. The search will only occur under SP_OCT_ARCH_LOC directory specification. *(Will* *NOT search under ORACLE defined archive locations ).* SP_OCT_CK_LOC_FIRST value will be ignored with this setting. It will be assumed to be 1.
3 = read only archived redo logs. Read only from Oracle defined archive locations. SP_OCT_ARCH_LOC directory will be ignored. SP_OCT_CK_LOC_FIRST value will be ignored with this setting. It will be assumed as 0.
Best practice: When enabling SP_OCT_READ_ARC, a natural delay of at least the length of time to generate and copy an archive log from an online log will be apparent in data latency. While this latency could be acceptable, reader's performance in fetching keys is reliant on read consistent views. Reader key fetching is dependent on the consistent views being current (or close to it) but using the parameter delays the age of the view with at least the latency time. To circumvent the need for reader to fetch keys, it's highly recommended that customers using the mentioned capture parameter enable the source database's supplemental logging for primary keys and unique keys (PU/UK) for tables in replication.
Default: 0
Range of valid values: 0 - 3
Takes effect: when Capture is restarted
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: Immediately
This parameter controls expanded DDL replication, which is the replication of DDL for objects that are not in the SharePlex replication configuration file. The objects must exist in the source database before configuration activation and also on the target (except in the case of CREATE). SharePlex replicates the DDL statements, but does not maintain the objects on the target throughout any future DML changes.
Setting the SP_OCT_REPLICATE_ALL_DDL requires identical source and target databases in order for the replicated DDL to execute successfully on the target system. Because this DDL is broadcast to all target machines, all target systems must be identical.
SharePlex replicates supported DDL for all objects in all schemas of the active datasource, so those components must exist in the target database for the operations to succeed.
To configure the Post process to stop for errors encountered when applying replicated DDL operations to the target database, set the SP_OPO_STOP_ON_DDL_ERR parameter.
If you are replicating the creation of packages or stored procedures, the name of the objects in the body of the package or stored procedure should be fully qualified, because other users or schemas may execute this package or stored procedure.
For a list of operations supported by SharePlex, see the Release Notes that correspond to your version of SharePlex.
|
Note: Expanded DDL replication supports not only tables and sequences but also a wide range of other objects such as procedures, functions, users, and views, which are not part of replication. Some of these objects may have underlying objects that are in replication. In those cases, Expanded DDL replication applies not only to the object that is outside the replication configuration, but also to the underlying objects that are in replication. |
|
SharePlex does not support the Oracle Flashback Table feature. If the SP_REPLICATE_ALL_DDL parameter is enabled (value of 1), SharePlex may try to replicate the flashback DDL, which will return an error. To perform Flashback Table on a table that is in replication, use the following procedures
|
Default: 0 (off)
Range of valid values: 0 or 1 (flag)
Takes effect: immediately
This parameter controls whether or not SharePlex replicates the following DDL operations on tables that are listed in the configuration file:
By default, the preceding DDL operations are replicated. To disable replication of these operations, set this parameter to 0. This can be useful if replication of these operations is impacting performance.
Default: 1 (enabled)
Range of valid values: 0 or 1
Takes effect: when Capture is restarted
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: Process restart
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: Process restart
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: Process restart
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: 0 (Disabled)
Range of valid values: 0 or 1
Takes effect: Process restart
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.
NOTE: The SP_OCT_USE_SUPP_KEYS parameter takes effect only if the supplemental logging is enabled for PK/UI.
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: Immediately
© ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center