1. Reference for Oracle GoldenGate
  2. Oracle GoldenGate Common Components
  3. Oracle GoldenGate Parameters
  4. DDLOPTIONS

DDLOPTIONS

Valid For

Extract and Replicat

Description

Use the DDLOPTIONS parameter to configure aspects of DDL processing other than filtering and string substitution. You can use multiple DDLOPTIONS statements, but using one is recommended. If using multiple DDLOPTIONS statements, make each of them unique so that one does not override the other. Multiple DDLOPTIONS statements are executed in the order listed in the parameter file.

Default

See the argument descriptions

Syntax

DDLOPTIONS
[, DEFAULTUSERPASSWORD password [algorithm [ENCRYPTKEY DEFAULT | ENCRYPTKEY key_name]
[, CAPTUREGLOBALTEMPTABLE ]
[, DEFAULTUSERPASSWORDALIAS alias [DOMAIN domain] ]
[, GETAPPLOPS | IGNOREAPPLOPS]
[, GETREPLICATES | IGNOREREPLICATES]
[, IGNOREMAPPING]
[, MAPDERIVED | NOMAPDERIVED]
[, MAPSCHEMAS]
[, MAPSESSIONSCHEMA source_schema TARGET target_schema]
[, NOTAG]
[, PASSWORD algorithm ENCRYPTKEY {key_name | DEFAULT}]
[, REMOVECOMMENTS {BEFORE | AFTER}]
[, REPLICATEPASSWORD | NOREPLICATEPASSWORD]
[, REPORT | NOREPORT]
[, UPDATEMETADATA]
[, USEPASSWORDVERIFIERLEVEL {10|11}]
[, _USEOWNERFORSESSION]
DEFAULTUSERPASSWORD password [algorithm ENCRYPTKEY {key_name | DEFAULT}]

Valid for Replicat. (Oracle only)

Can be used instead of the DEFAULTUSERPASSWORDALIAS option if an Oracle GoldenGate credential store is not being used. Specifies a different password for a replicated {CREATE | ALTER} USER name IDENTIFIED BY password statement from the one used in the source statement. Replicat will replace the placeholder that Extract writes to the trail with the specified password. When using DEFAULTUSERPASSWORD, use the NOREPLICATEPASSWORD option of DDLOPTIONS for Extract.

DEFAULTUSERPASSWORD password without options specifies a clear-text password. If the password is case-sensitive, type it that way.

Note:

Replication of CREATE | ALTER PROFILE will fail as the profile/password verification function must exist in the SYS schema. To replicate these DDLs successfully, password verification function must be created manually on both source/target(s) since DDL to SYS schema is excluded.

Use the following options if the password was encrypted with the ENCRYPT PASSWORD command in GGSCI:

algorithm

Specifies the encryption algorithm that was used to encrypt the password with the ENCRYPT PASSWORD command: AES128, AES192, AES256, or BLOWFISH. Use AES unless Blowfish is required for backward compatibility. AES is more secure than Blowfish.

ENCRYPTKEY key_name

Specifies the logical name of a user-created encryption key in the ENCKEYS lookup file. Use if ENCRYPT PASSWORD was used with the KEYNAME key_name option, and specify the same key name.

ENCRYPTKEY DEFAULT

Directs Oracle GoldenGate to use a random key. Use if ENCRYPT PASSWORD was used with the KEYNAME DEFAULT option.

CAPTUREGLOBALTEMPTABLE

Valid for Oracle

Allows Global Temporary Tables (GTT) DDLs to be visible to Extract so that they can be replicated. By default, GTT DDLs are not visible to Extract so using CAPTUREGLOBALTEMPTABLE you can set Extract to include GTT DDLs that then can be filtered by the DDL statement and if passed, written to the trail.

GTT DDLs that are present in the trail will be filtered and executed by the Replicat if they pass the filtering criteria.

For trigger-version of Extract, this option is set to false regardless of whether the table is GTT or not.

DEFAULTUSERPASSWORDALIAS alias [DOMAIN domain]

Valid for Replicat. (Oracle only)

Can be used instead of the DEFAULTUSERPASSWORD option if an Oracle GoldenGate credential store is being used. Specifies the alias of a credential whose password replaces the one in the IDENTIFIED BY clause of a replicated CREATE USER or ALTER USER statement. The alias is resolved to the encrypted password in the Oracle GoldenGate credential store. Replicat replaces the placeholder that Extract writes to the trail with the resolved password before applying the DDL to the target.

When using DEFAULTUSERPASSWORDALIAS, use the NOREPLICATEPASSWORD option of DDLOPTIONS for Extract.

alias

Specifies the alias of the credential whose password will be used for the replacement password. This credential must exist in the Oracle GoldenGate credential store. If you are not sure what alias to use, you can inspect the content of the credential store by issuing the INFO CREDENTIALSTORE command.

DOMAIN domain

Specifies the domain that is assigned to the specified user in the credential store.

GETAPPLOPS | IGNOREAPPLOPS

Valid for Extract. (Oracle only)

Controls whether or not DDL operations produced by business applications except Replicat are included in the content that Extract writes to a trail or file. GETAPPLOPS and IGNOREAPPLOPS can be used together with the GETREPLICATES and IGNOREREPLICATES options to control which DDL is propagated in a bidirectional or cascading configuration.

  • For a bidirectional configuration, use GETAPPLOPS with IGNOREREPLICATES . You also must use the UPDATEMETADATA option.

  • For a cascading configuration, use IGNOREAPPLOPS with GETREPLICATES on the systems that will be cascading the DDL operations to the target.

The default is GETAPPLOPS.

GETREPLICATES | IGNOREREPLICATES

Valid for Extract (Oracle only). Controls whether or not DDL operations produced by Replicat are included in the content that Extract writes to a trail or file. The default is IGNOREREPLICATES. For more information, see the GETAPPLOPS | IGNOREAPPLOPS options of DDLOPTIONS.

IGNOREMAPPING

Valid for Replicat. Disables the evaluation of name mapping that determines whether DDL is of MAPPED or UNMAPPED scope. This option improves performance in like-to-like DDL replication configurations, where source and target schema names and object names match, and where mapping functions are therefore unnecessary. With IGNOREMAPPING enabled, MAPPED or UNMAPPED scope cannot be determined, so all DDL statements are treated as OTHER scope. Do not use this parameter when source schemas and object names are mapped to different schema and object names on the target.

MAPDERIVED | NOMAPDERIVED

Valid for Replicat (Oracle). Controls how derived object names are mapped.

MAPDERIVED

If a MAP statement exists for the derived object, the name is mapped to the name specified in that TARGET clause. Otherwise, the name is mapped to the name specified in the TARGET clause of the MAP statement that contains the base object. MAPDERIVED is the default.

NOMAPDERIVED

Prevents name mapping. NOMAPDERIVED overrides any explicit MAP statements that contain the name of the derived object.

For more information about how derived objects are handled during DDL replication, see the Managing the DDL Replication Environment in Using Oracle GoldenGate for Oracle Database.

MAPSCHEMAS

Valid for Replicat (Oracle and Teradata). Use only when MAPSESSIONSCHEMA is used.

  • MAPSESSIONSCHEMA establishes a source-target mapping for session schemas and is used for objects whose schemas are not qualified in the DDL.

  • MAPSCHEMAS maps objects that do have qualified schemas in the source DDL, but which do not qualify for mapping with MAP, to the same session-schema mapping as in MAPSESSIONSCHEMA. Examples of such objects are the Oracle CREATE TABLE AS SELECT statement, which contains a derived object in the AS SELECT clause, or the Teradata CREATE REPLICATION RULESET statement.

This mapping takes place after the mapping that is specified in the MAP statement.

As an example, suppose the following DDL statement is issued on a source Oracle database:

create table a.t as select from b.t;

Suppose the MAP statement on the target is as follows: 

MAP a.*, TARGET c.*;
DDLOPTIONS MAPSESSIONSCHEMA b, TARGET b1, MAPSCHEMAS

As a result of this mapping, Replicat issues the following DDL statement on the target:

create table c.t as select from b1.t;

  • The base table gets mapped according to the TARGET clause (to schema c).

  • The qualified derived object (table t in SELECT FROM ) gets mapped according to MAPSESSIONSCHEMA (to schema b1) because MAPSCHEMAS is present.

Without MAPSCHEMAS, the derived object would get mapped to schema c (as specified in the TARGET clause), because MAPSESSIONSCHEMA alone only maps unqualified objects.

MAPSESSIONSCHEMA source_schema TARGET target_schema

Valid for Replicat (Oracle only). Enables a source session schema to be mapped to (transformed to) a different session schema on the target.

  • source_schema is the session schema that is set with ALTER SESSION set CURRENT_SCHEMA on the source.

  • target_schema is the session schema that is set with ALTER SESSION set CURRENT_SCHEMA on the target.

Wildcards are not supported. You can use multiple MAPSESSIONSCHEMA parameters to map different schemas.

MAPSESSIONSCHEMA overrides any mapping of schema names that is based on master or derived object names

See the example at the end of this topic for usage.

See also MAPSCHEMAS.

NOTAG

Valid for Replicat

Prevents the tagging of DDL that is applied by Replicat with a redo tag (either the default tag '00' or one set with the DBOPTIONS parameter with the SETTAG option). Use this option for bidirectional configurations where GETREPLICATES is used and DDL applied by Replicat must be captured back by Extract for a metadata refresh.

PASSWORD algorithm ENCRYPTKEY {key_name | DEFAULT}

Valid for Extract (Oracle only)

Directs Extract to encrypt all passwords in source DDL before writing the DDL to the trail.

algorithm

Specifies the encryption algorithm to be used to encrypt the password. Valid values are AES128, AES192, AES256, or BLOWFISH. Use AES unless Blowfish is required for backward compatibility. AES is more secure than Blowfish.

ENCRYPTKEY key_name

Specifies the logical name of a user-created encryption key in an ENCKEYS lookup file.

ENCRYPTKEY DEFAULT

Directs Oracle GoldenGate to use a random key.

REMOVECOMMENTS {BEFORE | AFTER}

(Optional) Valid for Extract and Replicat (Oracle only). Controls whether or not comments are removed from the DDL operation. By default, comments are not removed, so that they can be used for string substitution with the DDLSUBST parameter. See "DDLSUBST" for more information.

REMOVECOMMENTS BEFORE

Removes comments before the DDL operation is processed by Extract or Replicat. They will not be available for string substitution.

REMOVECOMMENTS AFTER

Removes comments after they are used for string substitution. This is the default behavior if REMOVECOMMENTS is not specified.

REPLICATEPASSWORD | NOREPLICATEPASSWORD

Valid for Extract (Oracle only). Applies to the password in a {CREATE | ALTER} USER user IDENTIFIED BY password command.

  • By default (REPLICATEPASSWORD), Oracle GoldenGate uses the source password in the target CREATE or ALTER statement.

  • To prevent the source password from being sent to the target, use NOREPLICATEPASSWORD.

When using NOREPLICATEPASSWORD, specify a password for the target DDL statement by using a DDLOPTIONS statement with the DEFAULTUSERPASSWORD or DEFAULTUSERPASSWORDALIAS option in the Replicat parameter file.

REPORT | NOREPORT

Valid for Extract and Replicat (Oracle and Teradata). Controls whether or not expanded DDL processing information is written to the report file. The default of NOREPORT reports basic DDL statistics. REPORT adds the parameters being used and a step-by-step history of the operations that were processed.

UPDATEMETADATA

Valid for Replicat (Oracle only). Use in an active-active bi-directional configuration. This parameter notifies Replicat on the system where DDL originated that this DDL was propagated to the other system, and that Replicat should now update its object metadata cache to match the new metadata. This keeps Replicat's metadata cache synchronized with the current metadata of the local database.

USEPASSWORDVERIFIERLEVEL {10|11}

Only valid in an Oracle to Oracle configuration. Checks if the password verifier being sent in a DDL CREATE USER statement requires modifying. The reason for this check is because Oracle has different password verifiers, depending on the database version:

  • 10g: A weak verifier kept in user$.password.

  • 11g: The SHA-1 verifier.

  • 12c: The SHA-2 and HTTP digest verifiers.

The SHA-1, SHA-2 and HTTP verifiers are captured in user$.spare4 in the format of: 'S:<SHA-1-verifier>;H:<http-verifier>;T:<SHA-2-verifier>'. Integrated Extract returns the following DDL in 12c for create user DDL statements:

  • In 12.0.1.0 it returns: CREATE USER username IDENTIFIED BY VALUES 'S:SHA-1;H:http;weak'.

  • In 12.0.2.0 and later it returns: CREATE USER username IDENTIFIED BY VALUES 'S:SHA-1;H:http;T:SHA-2;weak'.

If Replicat runs against Oracle 12c, these forms of CREATE USER are handled at the RDBMS level, but if Replicat runs against Oracle 10g or 11, these forms are not handled by the RDBMS. Oracle 10g only accepts the weak verifier, whereas Oracle 11g only accepts the S:SHA-1 and weak verifiers.

To allow the CREATE USER DDL generated for an Extract connected to Oracle 12c to work with a Replicat connected to Oracle 10g or 11g, this parameter can be used to filter out the unwanted verifiers, as follows:

  • If USEPASSWORDVERIFIERLEVEL is set to 10, everything except the weak verifier is filtered out of the CREATE USER DDL verification string.

  • If USEPASSWORDVERIFIERLEVEL is set to 11, everything except the S:SHA-1 and weak verifiers is filtered out of the CREATE USER DDL verification string.

Examples

Example 1   

The following shows how MAPSESSIONSCHEMA works to allow mapping of a source session schema to another schema on the target.

Assume the following DDL capture and mapping configurations in Extract and Replicat:

Extract:

DDL INCLUDE OBJNAME SRC.* INCLUDE OBJNAME SRC1.*
TABLE SRC.*;
TABLE SRC1.*;
DDL INCLUDE OBJNAME SRC.* INCLUDE OBJNAME SRC1.*
TABLE SRC.*;
TABLE SRC1.*;

Replicat:

DDLOPTIONS MAPSESSIONSCHEMA SRC TARGET DST
DDLOPTIONS MAPSESSIONSCHEMA SRC1 TARGET DST1
MAP SRC.*, TARGET DST.*;
MAP SRC1.*, TARGET DST1.*;
DDL INCLUDE OBJNAME DST.* INCLUDE OBJNAME DST1.*

Assume that the following DDL statements are issued by the logged-in user on the source:

ALTER SESION SET CURRENT_SCHEMA=SRC;
CREATE TABLE tab (X NUMBER);
CREATE TABLE SRC1.tab (X NUMBER) AS SELECT * FROM tab;

Replicat will perform the DDL as follows (explanations precede each code segment):

-- Set session to DST, because SRC.* is mapped to DST.* in MAP statement.
ALTER SESION SET CURRENT_SCHEMA=DST;
-- Create the first TAB table in the DST schema, using the DST session schema.
CREATE TABLE DST.tab (X NUMBER);
-- Restore Replicat schema.
ALTER SESSION SET CURRENT_SCHEMA=REPUSER
-- Set session schema to DST, per MAPSESSIONSCHEMA, so that AS SELECT succeeds.
ALTER SESION SET CURRENT_SCHEMA=DST;
-- Create the DST1.TAB table AS SELECT * FROM the first table (DST.TAB).
CREATE TABLE DST1.tab (X NUMBER) AS SELECT * FROM tab;
-- Restore Replicat schema.
ALTER SESSION SET CURRENT_SCHEMA=REPUSER

Without MAPSESSIONSCHEMA, the SELECT * FROM TAB would attempt to select from a non-existent SRC.TAB table and fail. The default is to apply the source schema to unqualified objects in a target DDL statement. The DDL statement in that case would look as follows and would fail: 

-- Set session to DST, because SRC.* is mapped to DST.* in MAP statement.
ALTER SESION SET CURRENT_SCHEMA=DST;
-- Create the first TAB table in the DST schema, using the DST session schema.
CREATE TABLE DST.tab (X NUMBER);
-- Restore Replicat schema.
ALTER SESSION SET CURRENT_SCHEMA=REPUSER
-- Set session schema to SRC, because TAB in the AS SELECT is unqualified-- and SRC is the source session schema.
ALTER SESION SET CURRENT_SCHEMA=SRC;
-- Create DST1.TAB AS SELECT * from SRC.TAB (SRC=current session schema).
CREATE TABLE DST1.tab (X NUMBER) AS SELECT * FROM tab;
-- SRC.TAB does not exist.
-- Abend with an error unless the error is handled by a DDLERROR statement.
Example 2   

The following shows how to use DEFAULTUSERPASSWORDALIAS to specify a different password for a replicated {CREATE | ALTER} USER name IDENTIFIED BY password statement from the one used in the source statement. In this example, the alias ddlalias is in the target domain in the credential store. 

DDLOPTIONS DEFAULTUSERPASSWORDALIAS ddlalias DOMAIN target