DDL

Valid For

Extract and Replicat

Note:

DDL replication is only supported between Oracle to Oracle databases and between MySQL to MySQL databases.

Description

Use the DDL parameter to:

enable DDL support
filter DDL operations
configure a processing action based on a DDL record

When used without options, the DDL parameter performs no filtering, and it causes all DDL operations to be propagated as follows:

As an Extract parameter, it captures all supported DDL operations that are generated on all supported database objects and sends them to the trail.
As a Replicat parameter, it replicates all DDL operations from the Oracle GoldenGate trail and applies them to the target. This is the same as the default behavior without this parameter.

When used with options, the DDL parameter acts as a filtering agent to include or exclude DDL operations based on:

scope
object type
operation type
object name
strings in the DDL command syntax or comments, or both

Only one DDL parameter can be used in a parameter file, but you can combine multiple inclusion and exclusion options to filter the DDL to the required level.

The filtering options of the DDL parameter are valid for a primary Extract that captures from the transaction source, but not for a data-pump Extract.
When combined, multiple filter option specifications are linked logically as AND statements.
All filter criteria specified with multiple options must be satisfied for a DDL statement to be replicated.
When using complex filtering criteria in a DDL parameter statement, it is recommended that you test your configuration in a test environment before using it in production.
See Example 1, "Example" for more information.

Note:

Do not use the DDL parameter for an Extract data pump. These process types do not permit the mapping or conversion of DDL and will propagate DDL records automatically in pass-through mode. DDL that is performed on a source table (for example ALTER TABLE TableA...) will be applied by Replicat with the same table name (

ALTER TABLE
                        TableA

). It cannot be mapped as

ALTER
                        TABLE TableB

.

For additional information about how to use Oracle GoldenGate DDL support, see Configuring DDL Support in Using Oracle GoldenGate for Oracle Database or Using Oracle GoldenGate for Heterogeneous Databases, as applicable.

Syntax

DDL [
{INCLUDE | EXCLUDE}
  [, MAPPED | UNMAPPED | OTHER | ALL]
  [, OPTYPE type]
  [, OBJTYPE 'type']
  [, SOURCECATALOG catalog | ALLCATALOGS]
  [, ALLOWEMPTYOBJECT]
  [, ALLOWEMPTYOWNER]
  [, OBJNAME name]
  [, INSTR 'string']
  [, INSTRWORDS 'word_list']
  [, INSTRCOMMENTS 'comment_string']
  [, INSTRCOMMENTSWORDS 'word_list']
  [, STAYMETADATA]
  [, EVENTACTIONS (action)
]
[...]

DDL Filtering Options

The following are the syntax options for filtering and operating upon the DDL that is replicated by Oracle GoldenGate. These options apply to the INCLUDE and EXCLUDE clauses of the DDL parameter and other parameters that support DDL replication.

INCLUDE | EXCLUDE

Use INCLUDE or EXCLUDE to identify the beginning of an inclusion or exclusion clause.

An inclusion clause contains filtering criteria that identifies the DDL that this parameter will affect.
An exclusion clause contains filtering criteria that excludes specific DDL from this parameter.

The inclusion or exclusion clause must consist of the INCLUDE or EXCLUDE keyword followed by any valid combination of the other filtering options of the DDL parameter.

If you use EXCLUDE, you must create a corresponding INCLUDE clause. For example, the following is invalid:

DDL EXCLUDE OBJNAME "hr".*

However, you can use either of the following:

DDL INCLUDE ALL, EXCLUDE OBJNAME "hr"."*"
DDL INCLUDE OBJNAME fin.* EXCLUDE OBJNAME "fin.ss"

An EXCLUDE takes priority over any INCLUDEs that contain the same criteria. You can use multiple inclusion and exclusion clauses.

Do not include any Oracle GoldenGate installed DDL objects in a DDL parameter, in a TABLE parameter, or in a MAP parameter, nor in a TABLEEXCLUDE or MAPEXCLUDE parameter. Make certain that wildcard specifications in those parameters do not include Oracle GoldenGate-installed DDL objects. These objects must not be part of the Oracle GoldenGate configuration, but the Extract process must be aware of operations on them, and that is why you must not explicitly exclude them from the configuration with an EXCLUDE, TABLEEXCLUDE, or MAPEXCLUDE parameter statement.

MAPPED | UNMAPPED | OTHER | ALL

Use MAPPED, UNMAPPED, OTHER, and ALL to apply INCLUDE or EXCLUDE based on the DDL operation scope.

MAPPED applies INCLUDE or EXCLUDE to DDL operations that are of MAPPED scope. MAPPED filtering is performed before filtering that is specified with other DDL parameter options.
UNMAPPED applies INCLUDE or EXCLUDE to DDL operations that are of UNMAPPED scope.
OTHER applies INCLUDE or EXCLUDE to DDL operations that are of OTHER scope.
ALL applies INCLUDE or EXCLUDE to DDL operations of all scopes.

DDL EXCLUDE ALL is a special processing option that maintains up-to-date object metadata for Oracle GoldenGate, while blocking the replication of the DDL operations themselves. You can use DDL EXCLUDE ALL when using a method other than Oracle GoldenGate to apply DDL to the target, but you want Oracle GoldenGate to replicate data changes to the target objects. It provides the current metadata to Oracle GoldenGate as objects change, thus preventing the need to stop and start the Oracle GoldenGate processes. The following special conditions apply to DDL EXCLUDE ALL:

DDL EXCLUDE ALL does not require the use of an INCLUDE clause.
When using DDL EXCLUDE ALL, you can set the WILDCARDRESOLVE parameter to IMMEDIATE to allow immediate DML resolution if required.

OPTYPEtype

Use OPTYPE to apply INCLUDE or EXCLUDE to a specific type of DDL operation, such as CREATE, ALTER, and RENAME. For type, use any DDL command that is valid for the database. For example, to include ALTER operations, the correct syntax is:

DDL INCLUDE OPTYPE ALTER

OBJTYPE 'type'

Use OBJTYPE to apply INCLUDE or EXCLUDE to a specific type of database object. For type, use any object type that is valid for the database, such as TABLE, INDEX, and TRIGGER. For an Oracle materialized view and materialized views log, the correct types are snapshot and snapshot log, respectively. Enclose the name of the object type within single quotes. For example:

DDL INCLUDE OBJTYPE 'INDEX'
DDL INCLUDE OBJTYPE 'SNAPSHOT'

For Oracle object type USER, do not use the OBJNAME option, because OBJNAME expects owner.object or container.owner.object whereas USER only has a schema.

SOURCECATALOGcatalog| ALLCATALOGS

Use these options to specify how unqualified object names in an OBJNAME clause are resolved to the correct container. Use these options when the source database is an Oracle container database.

SOURCECATALOG specifies a default container for all of the object names that are specified in the same INCLUDE or EXCLUDE clause. To take effect, SOURCECATALOG must be specified before the OBJNAME specification. See "SOURCECATALOG" for more information including using statements that contain two-part names, where three-part object names are required to fully identify an object.

ALLCATALOGS specifies that all of the containers of the database should be considered when resolving object names that are specified in the same INCLUDE or EXCLUDE clause. ALLCATALOGS can be placed before or after the OBJNAME specification.

The following is the order of precedence that is given when there are different catalog specifications in a parameter file:

ALLCATALOGS in an INCLUDE or EXCLUDE clause overrides all SOURCECATALOG specifications in the INCLUDE or EXCLUDE clause and at the root of the parameter file, and it overrides the container specification of a fully qualified object name in the OBJNAME clause.
An explicit catalog specification in the OBJNAME clause overrides all instances of SOURCECATALOG (but not ALLCATALOGS).
SOURCECATALOG in an INCLUDE or EXCLUDE clause overrides the global SOURCECATALOG parameter that is specified at the root of the TABLE or MAP statement.
The global SOURCECATALOG parameter takes effect for any unqualified object names in OBJNAME clauses if the INCLUDE or EXCLUDE clause does not specify SOURCECATALOG or ALLCATALOGS.
In the absence of any of the preceding parameters, all catalogs are considered.

ALLOWEMPTYOBJECT

Use ALLOWEMPTYOBJECT to allow an OBJNAME specification to process DDL that contains no object name. For example:

DDL INCLUDE OBJNAME sch.* ALLOWEMPTYOBJECT

ALLOWEMPTYOWNER

Use ALLOWEMPTYOWNER to allow an OBJNAME specification to process DDL that contains no owner name. For example:

DDL INCLUDE OBJNAME pdb.sch.* ALLOWEMPTYOWNER

OBJNAMEname

Use OBJNAME to apply INCLUDE or EXCLUDE to the fully qualified name of an object. To specify two-part and three-part object names and wildcards correctly, see Administering Oracle GoldenGate.

Enclose case-sensitive object names within double quote marks.

Case-insensitive example:

DDL INCLUDE OBJNAME accounts.*

Case-sensitive example:

DDL INCLUDE OBJNAME accounts."cust"

Do not use OBJNAME for the Oracle USER object, because OBJNAME expects owner.object or container.owner.object, whereas USER only has a schema.

When using OBJNAME with MAPPED in a Replicat parameter file, the value for OBJNAME must refer to the name specified with the TARGET clause of the MAP statement. For example, given the following MAP statement, the correct value is OBJNAME fin2.*.

MAP fin.exp_*, TARGET fin2.*;

In the following example, a CREATE TABLE statement executes as follows on the source:

CREATE TABLE fin.exp_phone;

That same statement executes as follows on the target:

CREATE TABLE fin2.exp_phone;

If a target owner is not specified in the MAP statement, Replicat maps it to the database user that is specified with the USERID or USERIDALIAS parameter.

For DDL that creates derived objects, such as a trigger, the value for OBJNAME must be the name of the base object, not the name of the derived object.

For example, to include the following DDL statement, the correct value is hr.accounts, not hr.insert_trig.

CREATE TRIGGER hr.insert_trig ON hr.accounts;

For RENAME operations, the value for OBJNAME must be the new table name. For example, to include the following DDL statement, the correct value is hr.acct.

ALTER TABLE hr.accounts RENAME TO acct;

INSTR 'string'

Use INSTR to apply INCLUDE or EXCLUDE to DDL statements that contain a specific character string within the command syntax itself, but not within comments. For example, the following excludes DDL that creates an index.

DDL INCLUDE ALL EXCLUDE INSTR 'CREATE INDEX'

Enclose the string within single quotes. The string search is not case sensitive.

INSTR does not support single quotation marks (' ') that are within the string, nor does it support NULL values.

INSTRCOMMENTS 'comment_string'

(Valid for Oracle) Use INSTRCOMMENTS to apply INCLUDE or EXCLUDE to DDL statements that contain a specific character string within a comment, but not within the DDL command itself. By using INSTRCOMMENTS, you can use comments as a filtering agent.

For example, the following excludes DDL statements that include the string 'source only' in the comments.

DDL INCLUDE ALL EXCLUDE INSTRCOMMENTS 'SOURCE ONLY'

In this example, DDL statements such as the following are not replicated.

CREATE USER john IDENTIFIED BY john /*source only*/;

Enclose the string within single quotes. The string search is not case sensitive. You can combine INSTR and INSTRCOMMENTS to filter on a string in the command syntax and in the comments of the same DDL statement.

INSTRCOMMENTS does not support single quotation marks (' ') that are within the string, nor does it support NULL values.

INSTRWORDS 'word_list'

Use INSTRWORDS to apply INCLUDE or EXCLUDE to DDL statements that contain the specified words.

For word_list, supply the words in any order, within single quotes. To include spaces, put the space (and the word, if applicable) in double quotes. Double quotes also can be used to enclose sentences.

All specified words must be present in the DDL for INSTRWORDS to take effect.

Example:

DDL INCLUDE OPTYPE ALTER OBJTEYP 'TABLE' INSTRWORDS 'ALTER CONSTRAINT " xyz"'

This example matches the following DDL statements:

ALTER TABLE ADD CONSTRAINT xyz CHECK

ALTER TABLE DROP CONSTRAINT xyz

INSTRWORDS does not support single quotation marks (' ') that are within the string, nor does it support NULL values.

INSTRCOMMENTSWORDS 'word_list'

(Valid for Oracle) Works the same way as INSTRWORDS, but only applies to comments within a DDL statement, not the DDL syntax itself. By using INSTRCOMMENTS, you can use comments as a filtering agent.

INSTRCOMMENTSWORDS does not support single quotation marks (' ') that are within the string, nor does it support NULL values.

You can combine INSTRWORDS and INSTRCOMMENTSWORDS to filter on a string in the command syntax and in the comments of the same DDL statement.

STAYMETADATA

(Valid for Oracle). Prevents metadata from being captured by Extract or applied by Replicat.

When Extract first encounters DML on a table, it retrieves the metadata for that table. When DDL is encountered on that table, the old metadata is invalidated. The next DML on that table is matched to the new metadata so that the target table structure always is up-to-date with that of the source.

However, if you know that a particular DDL operation will not affect the table's metadata, you can use STAYMETADATA so that the current metadata is not retrieved or replicated. This is a performance improvement that has benefit for such operations as imports and exports, where such DDL as truncates and the disabling of constraints are often performed. These operations do not affect table structure, as it relates to the integrity of subsequent data replication, so they can be ignored in such cases. For example ALTER TABLE ADD FOREIGN KEY does not affect table metadata.

An example of how this can be applied selectively is as follows:

DDL INCLUDE ALL INCLUDE STAYMETADATA OBJNAME xyz

This example states that all DDL is to be included for replication, but only DDL that operates on object xyz will be subject to STAYMETADATA.

STAYMETADATA also can be used the same way in an EXCLUDE clause.

STAYMETADATA must be used the same way on the source and target to ensure metadata integrity.

When STAYMETADATA is in use, a message is added to the report file. DDL reporting is controlled by the DDLOPTIONS parameter with the REPORT option.

This same functionality can be applied globally to all DDL that occurs on the source by using the @ddl_staymetadata scripts:

@ddl_staymetadata_on globally turns off metadata versioning.
@ddl_staymetadata_offglobally enables metadata versioning again.

This option should be used with the assistance of Oracle GoldenGate technical support staff, because it might not always be apparent which DDL affects object metadata. If improperly used, it can compromise the integrity of the replication environment.

EVENTACTIONS (action)

Causes the Extract or Replicat process take a defined action based on a DDL record in the transaction log or trail, which is known as the event record. The DDL event is triggered if the DDL record is eligible to be written to the trail by Extract or a data pump, or to be executed by Replicat, as determined by the other filtering options of the DDL parameter. You can use this system to customize processing based on database events.

For action, see EVENTACTIONS under the MAP and TABLE parameters.

Guidelines for using EVENTACTIONS on DDL records:

CHECKPOINTBEFORE: Since each DDL record is autonomous, the DDL record is guaranteed to be the start of a transaction; therefore, the CHECKPOINT BEFORE event action is implied for a DDL record.
IGNORE: This option is not valid for DDL records. Because DDL operations are autonomous, ignoring a record is equivalent to ignoring the entire transaction.

EVENTACTIONS does not support the following DDL objects because they are derived objects:

indexes
triggers
synonyms
RENAME on a table and ALTER TABLE RENAME

Examples

Example 1 Combining DDL Parameter Options

The following is an example of how to combine the options of the DDL parameter.

DDL  &
INCLUDE UNMAPPED &
    OPTYPE alter &
    OBJTYPE 'table' &
    OBJNAME users.tab* &
INCLUDE MAPPED OBJNAME * &
EXCLUDE MAPPED OBJNAME temporary.tab

The combined filter criteria in this statement specify the following:

INCLUDE all ALTER TABLE statements for tables that are not mapped with a TABLE or MAP statement (UNMAPPED scope), but only if those tables are owned by users and their names start with tab,
INCLUDE all DDL operation types for all tables that are mapped with a TABLE or MAP statement (MAPPED scope),
EXCLUDE all DDL operation types for all tables that are MAPPED in scope, but only if those tables are owned by temporary and only if their names begin with tab.

Example 2 Including an Event Action

The following example specifies an event action of REPORT for all DDL records.

DDL INCLUDE ALL EVENTACTIONS (REPORT)

Example 3 Using an Event Action on a Subset of DDL

The following example shows how EVENTACTIONS can be used on a subset of the DDL. All DDL is to be replicated, but only the DDL that is executed on explicitly named objects qualifies to trigger the event actions of REPORT and LOG.

DDL INCLUDE ALL &
    INCLUDE OBJNAME sales.t* EVENTACTIONS (REPORT) &
    INCLUDE OBJNAME fin.my_tab EVENTACTIONS (LOG) &

Example 4

The following example demonstrates the different ways to specify catalog names for DDL that is issued on objects in a source Oracle container database.

This includes pdb1.sch1.obj1 and pdb2.sch2.obj2 for DDL processing.

SOURCECATALOG pdb1 
DDL INCLUDE OBJNAME sch1.obj1 INCLUDE SOURCECATALOG pdb2 OBJNAME sch2.obj2

This includes all objects with the name sch.obj in any catalog for DDL processing.
```
DDL INCLUDE ALLCATALOGS OBJNAME sch.obj
```
This also includes all objects with the name sch.obj in any catalog for DDL processing, because ALLCATALOGS overrides any other catalog specification.
```
DDL INCLUDE ALLCATALOGS OBJNAME pdb.sch.obj
```

Example 5

The following shows the combined use of ALLOWEMPTYOBJECT and ALLOWEMPTYOWNER.

DDL INCLUDE pdb.*.* ALLOWEMPTYOWNER ALLOWEMPTYOBJECT