SharePlex supports DDL replication for Oracle databases only. SharePlex replicates certain Oracle DDL changes that are written to the redo logs. Changes that bypass the redo logs are not replicated.
For details on the DDL that SharePlex supports, see the SharePlexSharePlex Release Notes.
In a cascading configuration, DDL replication is supported from source to target through an intermediary system. However, DDL initiated on the intermediary system could cause inconsistencies leading to Post errors and must be avoided. For more information, see Configure replication through an intermediary system.
DDL against objects owned by the SharePlex user or against system-owned objects is not replicated.
Note: Because the source and target databases can be of different versions in SharePlex replication, the source and target objects can be different. When DDL is applied to the target, it may fail if the operation is forbidden on the target but allowed on the source.
For a list of objects for which DDL is supported, see the SharePlex Release Notes.
By default, SharePlex replicates some Oracle DDL for objects that are listed in the active configuration (explicitly or by wildcard), but you can expand this support with parameter settings.
Important!
SharePlex provides default DDL support for objects in the configuration file. You can expand this support through parameter settings.
See the SharePlex Release Notes for detailed information about the DDL that is supported by SharePlex.
SharePlex provides some basic DDL support by default.
By default SharePlex replicates:
ALTER TABLE to:
ADD, MODIFY, DROP, SPLIT, COALESCE, MOVE, TRUNCATE, EXCHANGE PARTITION/SUBPARTITION
ADD, MODIFY, or DROP columns
when:
This functionality is controlled by the SP_OCT_REPLICATE_DDL parameter. The valid values are as follows:
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)
By default, the SharePlex Auto-Add feature is also enabled to provide DDL support for tables and indexes that are created after activation. When SharePlex detects a CREATE statement for one of these objects and its name satisfies a wildcard in the configuration file, SharePlex does the following:
The Auto-Add feature is controlled by the SP_OCT_AUTOADD_ENABLE parameter, which is set to 1 (enabled) by default.
See the SharePlex Reference Guide for details about this parameter.
You can enable the replication of the following DDL when it is issued on objects that are in the configuration file. To enable the replication of a DDL command, set the associated parameter to 1.
| DDL command | Set this parameter to 1 |
|---|---|
| CREATE / DROP TRIGGER | SP_OCT_REPLICATE_TRIGGER |
| CREATE / DROP SYNONYM | SP_OCT_REPLICATE_SYNONYM |
| GRANT | SP_OCT_REPLICATE_GRANT |
See the SharePlex Reference Guide for details about these parameters.
You can expand Auto-Add support to include any of the object types listed in the following table. The object is added to replication if its name satisfies a wildcard specification in the active configuration file.
To enable auto-add of individual object types
Set the appropriate parameter to 1, using the following table as your guide.
| DDL to auto-add | Parameter to set to 1 | Additional requirements |
|---|---|---|
| CREATE / DROP SEQUENCE | SP_OCT_AUTOADD_SEQ |
Set the SP_SYS_TARGET_COMPATIBILITY parameter to at least 8.6.3. |
| CREATE /DROP MATERIALIZED VIEW* | SP_OCT_AUTOADD_MV |
Set the SP_SYS_TARGET_COMPATIBILITY parameter to at least 8.6.2. |
* SharePlex does not replicate materialized views to materialized views. SharePlex converts a CREATE MATERIALIZED VIEW to a CREATE TABLE, applies the CREATE TABLE to the target, and then replicates the DML that populates the view. SharePlex replicates DROP MATERIALIZED VIEW, but not ALTER MATERIALIZED VIEW. See the SharePlex Reference Guide for details about these parameters.
You can configure SharePlex to replicate DDL for certain objects that are not listed in the configuration file. SharePlex replicates the DDL statements, but does not replicate any data change operations made to the objects because they are not part of active replication. Therefore, SharePlex does not maintain synchronization of these objects on the target. The objects must exist in the source and target prior to configuration activation.
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:
|
To replicate DDL for objects outside the replication configuration
Set the SP_OCT_REPLICATE_ALL_DDL parameter to 1.
See the SharePlex Reference Guide for details about this parameter.
See the SharePlex Release Notes for more information about supported DDL for objects that are not in the configuration file.
You can filter the objects for which DDL is replicated when the SP_OCT_REPLICATE_ALL_DDL parameter is enabled. This is the DDL that is not in the replication configuration.
Note: DDL filtering is only allowed for objects outside the replication configuration. All DDL performed on objects inside the replication configuration must be replicated to keep the source and target metadata consistent so that DML succeeds.
DDL filtering is controlled in the SHAREPLEX_DDL_CONTROL table that is installed in the SharePlex schema.
|
Name |
Type |
|
------------- |
------------ |
|
DDL_PARAMETER |
NUMBER |
|
DDL_CODE |
NUMBER |
|
SCHEMA_FILTER |
VARCHAR2(32) |
|
OBJECT_FILTER |
VARCHAR2(32) |
Each row in the SHAREPLEX_DDL_CONTROL table defines a filter based on what you specify in each of the following columns:
SCHEMA_FILTER filters DDL by a schema name.
A null value in the DDL_CODE column means that the filter applies to all of the DDL types A null in the SCHEMA_FILTER or OBJECT_FILTER column means that the filter applies to any schema or object name.
Note: The DDL_PARAMETER column is not an active column as of this release of SharePlex.
To filter DDL
Insert a row into the table with the desired values in the active columns.
The following filters out of replication the DDL for ALTER TABLE:
INSERT INTO SPLEX.SHAREPLEX_DDL_CONTROL (DDL_CODE, SCHEMA_FILTER, OBJECT_FILTER) values (15,null,null);
The following filters out of replication all DDL for all objects with names that begin with TEST_ in any schema:
INSERT INTO SPLEX.SHAREPLEX_DDL_CONTROL (DDL_CODE, SCHEMA_FILTER, OBJECT_FILTER) values (null,null,'TEST_%');
The following filters out of replication the DDL for CREATE TABLE for the "Sales" schema and objects with names that begin with "TEST_":
INSERT INTO SPLEX.SHAREPLEX_DDL_CONTROL (DDL_CODE, SCHEMA_FILTER, OBJECT_FILTER) values (1,'Sales','TEST_%');
| DDL Type | DDL_CODE |
|---|---|
| CREATE TABLE | 1 |
| ALTER TABLE | 15 |
| DROP TABLE | 12 |
| ASSOCIATE STATISTICS | 168 |
| DISASSOCIATE STATISTICS | 169 |
| COMMENT TABLE, COMMENT ON COLUMNS | 29 |
| TRUNCATE | 85 |
| CREATE INDEX | 9 |
| ALTER INDEX | 11 |
| DROP INDEX | 10 |
| CREATE SEQUENCE | 13 |
| ALTER SEQUENCE | 14 |
| DROP SEQUENCE | 16 |
| CREATE CLUSTER | 4 |
| DROP CLUSTER | 8 |
| CREATE USER | 51 |
| ALTER USER | 43 |
| DROP USER | 53 |
| CREATE_ROLE | 52 |
| ALTER_ROLE | 79 |
| DROP_ROLE | 54 |
| GRANT | 17 |
| REVOKE | 18 |
| CREATE SYNONYM | 19 |
| DROP SYNONYM | 20 |
| CREATE VIEW | 21 |
| ALTER VIEW | 88 |
| DROP VIEW | 22 |
| CREATE TYPE | 77 |
| ALTER TYPE | 80 |
| DROP TYPE | 78 |
| CREATE TYPE BODY | 81 |
| DROP TYPE BODY | 83 |
| CREATE FUNCTION | 91 |
| ALTER FUNCTION | 92 |
| DROP FUNCTION | 93 |
| CREATE PROCEDURE | 24 |
| ALTER PROCEDURE | 25 |
| DROP PROCEDURE | 68 |
| CREATE PACKAGE | 94 |
| ALTER PACKAGE | 95 |
| DROP PACKAGE | 96 |
| CREATE PACKAGE BODY | 97 |
| ALTER PACKAGE BODY | 98 |
| DROP PACKAGE BODY | 99 |
| CREATE DIRECTORY | 157 |
| DROP DIRECTORY | 158 |
The following are best practices for the replication of Oracle ALTER TABLE operations.
Do not add a VARRAY column or abstract data type column if you plan to issue an ALTER TABLE to drop a column or set a column unused soon thereafter. SharePlex must query the database to obtain information about this data type. If the second DDL was performed before SharePlex was able to process the first DDL, the query will fail because the metadata is already changed.
If some metadata is system-specific, such as the storage parameters of database objects, there may be unexpected results when DDL on that metadata is replicated. For example, SharePlex replicates all of the storage parameters for a source Oracle object, even though only some of them were changed with the ALTER TABLE command. If the source and target objects were not created with the same storage parameters, one of two things can happen: either the target table will assume the storage of the source table or, if the DDL is not supported by the target, an error will be generated.
Example: consider a source table with MAXEXTENTS 525 and MINEXTENTS 20, and a target table with MAXEXTENTS 505 and MINEXTENTS 4. If the MAXEXTENTS of the source object is changed to unlimited, SharePlex will replicate both the MAXEXTENTS change and the non-changed MINEXTENTS of 20. This causes Oracle error 01570, because MINEXTENTS cannot be larger than the extents currently allocated. Alternatively, if the MINEXTENTS is changed to 1 on the source, but MAXEXTENTS is not changed, SharePlex replicates both, which results in target parameters of MAXEXTENTS 525 and MINEXTENTS 1.
When ALTER TABLE RENAME is issued on a source table that is currently in replication, SharePlex changes the name of the table in the active configuration file by commenting out the old configuration line and adding a new line at the end of the configuration file. If the source and target table names are the same, both are changed to the new name. Otherwise, just the source name is changed. The following is an example:When ALTER TABLE RENAME is issued on a source table that is currently in replication, SharePlex changes the name of the table in the active configuration file by commenting out the old configuration line and adding a new line at the end of the configuration file. If the source and target table names are the same, both are changed to the new name. Otherwise, just the source name is changed. The following is an example:
# Table scott.table1 renamed to scott.table2 August 5, 2003 10:14
scott.table2 scott.table2 sysA@o.ora555
Whether the Post process stops on RENAME operations or not depends on the setting of the SP_OPO_STOP_ON_DDL_ERR parameter.
Because the database generates the names of system-generated interval partitions/subpartitions, the names of those partitions on the source will not match the names of their corresponding partitions on the target. Set the SP_OCT_TRUNC_PARTITION_BY_ID parameter to 1 to ensure that SharePlex truncates the correct partition when it replicates an ALTER TABLE to TRUNCATE a system-generated interval partition. This setting directs SharePlex to identify the partition by using the partition ID, rather than by using the partition name that is specified in the original ALTER TABLE command. Post maps the partition ID to the correct partition name on the target table. For more information, see SP_OCT_TRUNC_PARTITION_BY_ID .
To support the replication of system-named interval partitions/subpartitions, both the source and target must be SharePlex version 8.6.4 or later.
SharePlex does not support TRUNCATE of a system-generated sub-partition if the sub-partition is empty.
ALTER TABLE DDL commands that change the rowid of a table can affect subsequent DML operations if the primary or unique keys of the tables in replication are not being logged. When the keys are not logged, SharePlex fetches their values based on the rowid. Any operation that changes the rowid, such as ALTER TABLE...MOVE, can cause the wrong key values to be used for subsequent DML operations.
© ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
