SharePlex 10.2 - Administration Guide

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.

Enable DDL replication

Configure DDL replication > Enable DDL replication

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!

For the most current information about supported DDL and requirements or limitations, see the SharePlex Release Notes provided with this release. That information may supercede what is documented here.
DDL replication is supported only to Oracle targets, except for ALTER TABLE to ADD COLUMN or DROP COLUMN, which are supported for all SharePlex targets.

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.

Default support for Oracle DDL

SharePlex provides some basic DDL support by default.

DDL for existing objects

By default SharePlex replicates:

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

ADD, MODIFY, or DROP columns

when:
- the affected object exists in the source and target at the time of activation and
- its name is listed in the configuration file (explicitly or through wildcard).
DROP TABLE

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)

DDL for objects added after activation

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:

replicates the CREATE to add the object to the target
adds the object to replication
maintains that object through future DDL and DML changes

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.

Optional DDL on objects in replication

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.

Optional Auto-Add support for Oracle DDL

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

Make certain the SP_OCT_AUTOADD_ENABLE parameter is set to 1.

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

Expanded DDL support for objects outside replication

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:

Remove source objects from replication
Perform the flashback
Add or change objects in an active configuration

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.

Filter DDL Replication

Configure DDL replication > Filter DDL Replication

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.
OBJECT_FILTER filters DDL by an object name.
DDL_CODE filters by the code number of a DDL type. See DDL codes.

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.

Examples

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 codes

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

Documentos relacionados

The document was helpful.

Selecione a classificação

I easily found the information I needed.

Selecione a classificação

Selecione seu produto:

Para podermos atendê-lo melhor, informe o Propósito de seu chat:

Soluções recomendadas para seu problema