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 [, ADDTRANDATA {ABEND | RETRYOP RETRYDELAY seconds MAXRETRIES retries} [, 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]
ADDTRANDATA
{ABEND | RETRYOP RETRYDELAY
seconds
MAXRETRIES
retries
}Valid for Extract (Teradata)
Not supported when Classic Extract is reading from an Active Data Guard standby database because supplemental logging must be enabled on the primary database, which is read/write. Not supported for multitenant container databases. Supplemental logging must be enabled when using ADD SCHEMATRANDATA
. This option should only be used when schema-level supplemental logging is not an option in your environment.
No longer valid for Oracle and a warning is issued if used.
Use ADDTRANDATA
to:
Enable Oracle supplemental logging automatically for new tables created with a CREATE TABLE
statement.
Update supplemental logging for tables affected by an ALTER TABLE
statement to add or drop columns.
Update supplemental logging for tables that are renamed.
Update supplemental logging for tables where unique or primary keys are added or dropped.
By default, ADDTRANDATA
is disabled. The default for ADDTRANDATA
when used without additional options is:
DDLOPTIONS ADDTRANDATA RETRYOP RETRYDELAY 10 MAXRETRIES 10
To use ADDTRANDATA
functionality, Oracle GoldenGate, the database, and the appropriate tables must be configured for DDL capture. For Oracle, the Oracle GoldenGate DDL objects must be installed and configured. For more information, see Preparing the Database for Oracle GoldenGate in Using Oracle GoldenGate for Oracle Database.
For new tables created with CREATE TABLE
, ADDTRANDATA
produces the same results as the default ADD TRANDATA
command in GGSCI by issuing the Oracle ALTER TABLE
command with the ADD SUPPLEMENTAL LOG GROUP
option. Oracle GoldenGate executes this command when the CREATE TABLE
or ALTER TABLE
is captured on the source. If you have special requirements for the supplemental logging, use the ADD TRANDATA
command, not DDLOPTIONS ADDTRANDATA
. By default, the ALTER TABLE
statement that adds the supplemental logging is not replicated to the target unless the GETREPLICATES
option is in use.
For renamed tables, ADDTRANDATA
deletes the supplemental log group for the old table and creates it for the new one. If you do not use ADDTRANDATA
and tables will be renamed, do the following to create the log group before doing the rename:
Drop the supplemental log group using the database interface or the DELETE TRANDATA
command in GGSCI.
DELETE TRANDATA table_name
Rename the table.
Create the new supplemental log group using the database interface or the ADD TRANDATA
command in GGSCI.
ADD TRANDATA table_name
There might be a lag between the time when an original DDL operation occurs and when the ADD TRANDATA
takes effect. During this time, do not allow DML operations (insert, update, delete) on the affected table if the data is to be replicated; otherwise, it will not be captured. To determine when DML can be resumed after ADDTRANDATA
, do the following:
Edit the Extract parameter file in GGSCI.
WARNING:
Do not use the VIEW PARAMS
or EDIT PARAMS
command to view or edit a parameter file that was created in a character set other than that of the local operating system. View the file from outside GGSCI; otherwise, the contents may become corrupted.
Add the REPORT
option to DDLOPTIONS
, then save and close the file.
DDLOPTIONS [, DDLOPTIONS_option] [,...] , REPORT
Stop and start Extract to activate the parameter changes.
STOP EXTRACT group_name START EXTRACT group_name
View the Extract process report.
VIEW REPORT group_name
Look for the ALTER TABLE
statement that added the log group to the table, and make a note of the time that the command took effect. The entry looks similar to the following:
Successfully added TRAN DATA for table with the key, table [MYSCHEMA1.MYTABLE], operation [ALTER TABLE "MYSCHEMA1"."MYTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_MYTABLE_53475" (MYID) ALWAYS /* GOLDENGATE_DDL_REPLICATION */ ].
Permit DML operations on the new table.
The ADDTRANDATA
options are:
ABEND
Causes Extract to abend.
RETRYOP
Causes Extract to try again based on RETRYDELAY
and MAXRETRIES
.
RETRYDELAY
seconds
Sets the delay before Extract tries again. The default is 10 seconds. The maximum delay is 10,000 seconds.
MAXRETRIES
retries
Sets the number of retries that Extract can make before abending. The default is 10 seconds. The maximum is 10,000 retries.
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.
For more information about Oracle GoldenGate security options, see Administering Oracle GoldenGate.
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. The GTT DDLs are included in Replicat, if present in trail, and are filtered by the DDL statement then if they are passed they are executed.
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.
See Administering Oracle GoldenGate for more information about Oracle GoldenGate security.
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 and Teradata). 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
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.
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