5 Configuring DDL Support
Topics:
- Prerequisites for Configuring DDL
Extract can capture DDL operations from a source Oracle database natively through the Oracle logmining server. - Overview of DDL Synchronization
Oracle GoldenGate supports the synchronization of DDL operations from one database to another. - Limitations of Oracle GoldenGate DDL Support
This topic contains some limitations of the DDL feature. - Configuration Guidelines for DDL Support
The following are guidelines to take into account when configuring Oracle GoldenGate processes to support DDL replication. - Understanding DDL Scopes
Database objects are classified into scopes. A scope is a category that defines how DDL operations on an object are handled by Oracle GoldenGate. - Correctly Identifying Unqualified Object Names in DDL
Extract captures the current schema (also called session schema) that is in effect when a DDL operation is executed. The current container is also captured if the source is a multitenant container database. - Enabling DDL Support
Data Definition Language (DDL) is useful in dynamic environments which change constantly. - Filtering DDL Replication
By default, all DDL is passed to Extract. - Special Filter Cases
This topic describes the special cases that you must consider before creating your DDL filters. - How Oracle GoldenGate Handles Derived Object Names
DDL operations can contain a base object name and also a derived object name. - Using DDL String Substitution
You can substitute strings within a DDL operation while it is being processed by Oracle GoldenGate. - Controlling the Propagation of DDL to Support Different Topologies
To support bidirectional and cascading replication configurations, it is important for Extract to be able to identify the DDL that is performed by Oracle GoldenGate and by other applications, such as the local business applications. - Adding Supplemental Log Groups Automatically
Use theDDLOPTIONS
parameter with theADDTRANDATA
option for performing tasks described in this topic. - Removing Comments from Replicated DDL
You can use theDDLOPTIONS
parameter with theREMOVECOMMENTS BEFORE
andREMOVECOMMENTS AFTER
options to prevent comments that were used in the source DDL from being included in the target DDL. - Replicating an IDENTIFIED BY Password
Use theDDLOPTIONS
parameter with theDEFAULTUSERPASSWORDALIAS
andREPLICATEPASSWORD | NOREPLICATEPASSWORD
options to control how the password of a replicated{CREATE | ALTER} USER
name
IDENTIFIED BY
password
statement is handled. These options must be used together. - How DDL is Evaluated for Processing
This topic explains how Oracle GoldenGate processes DDL statements on the source and target systems. - Viewing DDL Report Information
By default, Oracle GoldenGate shows basic statistics about DDL at the end of the Extract and Replicat reports. - Tracing DDL Processing
If you open a support case with Oracle GoldenGate Technical Support, you might be asked to turn on tracing.TRACE
andTRACE2
control DDL tracing. - Using Edition-Based Redefinition
Oracle GoldenGate supports the use of Edition-based Redefinition (EBR) with Oracle Databases enabling you to upgrade the database component of an application while it is in use, thereby minimizing or eliminating down time.
Prerequisites for Configuring DDL
Extract can capture DDL operations from a source Oracle database natively through the Oracle logmining server.
Parent topic: Configuring DDL Support
Support for DDL Capture with Extract
Extract supports the DDL capture method for Oracle 11.2.0.4 or later.
Oracle databases that have the COMPATIBLE
parameter set
to 11.2.0.4 or higher support DDL capture through the database logmining server.
This method is known as native DDL capture. Native DDL capture is the only
supported method for capturing DDL from a multitenant container database.
For downstream mining, the source database must also have database
COMPATIBLE
set to 11.2.0.4 or higher to support DDL capture
through the database logmining server.
Parent topic: Prerequisites for Configuring DDL
Overview of DDL Synchronization
Oracle GoldenGate supports the synchronization of DDL operations from one database to another.
DDL synchronization can be active when:
-
business applications are actively accessing and updating the source and target objects.
-
Oracle GoldenGate transactional data synchronization is active.
The components that support the replication of DDL and the replication of transactional data changes (DML) are independent of each other. Therefore, you can synchronize:
-
just DDL changes
-
just DML changes
-
both DDL and DML
For a list of supported objects and operations for DDL support for Oracle, see Supported Objects and Operations in Oracle DDL.
Parent topic: Configuring DDL Support
Limitations of Oracle GoldenGate DDL Support
This topic contains some limitations of the DDL feature.
For any additional limitations that were found after this documentation was published, see the Release Notes for Oracle GoldenGate.
- DDL Statement Length
- Supported Topologies
- Filtering, Mapping, and Transformation
- Renames
- Interactions Between Fetches from a Table and DDL
- Comments in SQL
- Compilation Errors
- Interval Partitioning
- DML or DDL Performed Inside a DDL Trigger
- LogMiner Data Dictionary Maintenance
Parent topic: Configuring DDL Support
DDL Statement Length
Oracle GoldenGate measures the length of a DDL statement in bytes, not in characters. The supported length is approximately 4 MB, allowing for some internal overhead that can vary in size depending on the name of the affected object and its DDL type, among other characteristics. If the DDL is longer than the supported size, Extract will issue a warning and ignore the DDL operation.
Parent topic: Limitations of Oracle GoldenGate DDL Support
Supported Topologies
Oracle GoldenGate supports DDL synchronization only in a like-to-like configuration. The source and target object definitions must be identical.
DDL replication is only supported for Oracle to Oracle replication. It is not supported between different databases, like Oracle to Teradata, or SQL Server to Oracle.
Oracle GoldenGatedoes not support DDL on a standby database.
Oracle GoldenGate supports DDL replication in all supported unidirectional configurations, and in bidirectional configurations between two, and only two, systems. For special considerations in an Oracle active-active configuration, see Propagating DDL in Active-Active (Bidirectional) Configurations.
Parent topic: Limitations of Oracle GoldenGate DDL Support
Filtering, Mapping, and Transformation
DDL operations cannot be transformed by any Oracle GoldenGate process. However, source DDL can be mapped and filtered to a different target object by a primary Extract or a Replicat process. Mapping or filtering of DDL by a data-pump Extract is not permitted, and the DDL is passed as it was received from the primary Extract.
For example, ALTER TABLE TableA
is processed by a data pump as ALTER TABLE TableA
. It cannot be mapped by that process as ALTER TABLE TableB
, regardless of any TABLE
statements that specify otherwise.
Parent topic: Limitations of Oracle GoldenGate DDL Support
Renames
RENAME
operations on tables are converted to the equivalent ALTER TABLE RENAME
so that a schema name can be included in the target DDL statement. For example RENAME tab1 TO tab2
could be changed to ALTER TABLE schema.tab1 RENAME TO schema.tab2
. The conversion is reported in the Replicat process report file.
Parent topic: Limitations of Oracle GoldenGate DDL Support
Interactions Between Fetches from a Table and DDL
Oracle GoldenGate supports some data types by identifying the modified row from the redo stream and then querying the underlying table to fetch the changed columns. For instance, partial updates on LOBs are supported by identifying the modified row and the LOB column from the redo log, and then querying for the LOB column value for the row from the base table. A similar technique is employed to support UDT.
Note:
Extract only requires fetch for UDT when not using native object support.
Such fetch-based support is implemented by issuing a flashback query to the database based on the SCN (System Change Number) at which the transaction committed. The flashback query feature has certain limitations. Certain DDL operations act as barriers such that flashback queries to get data prior to these DDLs do not succeed. Examples of such DDL are ALTER TABLE MODIFY COLUMN
and ALTER TABLE DROP COLUMN
.
Thus, in cases where there is Extract capture lag, an intervening DDL may cause fetch requests for data prior to the DDL to fail. In such cases, Extract falls back and fetches the current snapshot of the data for the modified column. There are several limitations to this approach: First, the DDL could have modified the column that Extract needs to fetch (for example, suppose the intervening DDL added a new attribute to the UDT that is being captured). Second, the DDL could have modified one of the columns that Extract uses as a logical row identifier. Third, the table could have been renamed before Extract had a chance to fetch the data.
To prevent fetch-related inconsistencies such as these, take the following precautions while modifying columns.
-
Pause all DML to the table.
-
Wait for Extract to finish capturing all remaining redo, and wait for Replicat to finish processing the captured data from trail. To determine whether Replicat is finished, issue the following command in GGSCI until you see a message that there is no more data to process.
INFO REPLICAT
group
-
Execute the DDL on the source.
-
Resume source DML operations.
Parent topic: Limitations of Oracle GoldenGate DDL Support
Comments in SQL
If a source DDL statement contains a comment in the middle of an object name, that comment will appear at the end of the object name in the target DDL statement. For example:
Source:
CREATE TABLE hr./*comment*/emp ...
Target:
CREATE TABLE hr.emp /*comment*/ ...
This does not affect the integrity of DDL synchronization. Comments in any other area of a DDL statement remain in place when replicated.
Parent topic: Limitations of Oracle GoldenGate DDL Support
Compilation Errors
If a CREATE
operation on a trigger, procedure, function, or package results in compilation errors, Oracle GoldenGate executes the DDL operation on the target anyway. Technically, the DDL operations themselves completed successfully and should be propagated to allow dependencies to be executed on the target, for example in recursive procedures.
Parent topic: Limitations of Oracle GoldenGate DDL Support
Interval Partitioning
DDL replication is unaffected by interval partitioning, because the DDL is implicit. However, this is system generated name so Replicat cannot convert this to the target.I believe this is expected behavior. You must drop the partition on the source. For example:
alter table t2 drop partition for (20);
Parent topic: Limitations of Oracle GoldenGate DDL Support
DML or DDL Performed Inside a DDL Trigger
DML or DDL operations performed from within a DDL trigger are not captured.
Parent topic: Limitations of Oracle GoldenGate DDL Support
LogMiner Data Dictionary Maintenance
Oracle recommends that you gather dictionary statistics after the Extract is registered (logminer session) and the logminer dictionary is loaded, or after any significant DDL activity on the database.
Parent topic: Limitations of Oracle GoldenGate DDL Support
Configuration Guidelines for DDL Support
The following are guidelines to take into account when configuring Oracle GoldenGate processes to support DDL replication.
- Database Privileges
- Parallel Processing
- Object Names
- Data Definitions
- Truncates
- Initial Synchronization
- Data Continuity After CREATE or RENAME
Parent topic: Configuring DDL Support
Database Privileges
For database privileges that are required for Oracle GoldenGate to support DDL capture and replication, see Establishing Oracle GoldenGate Credentials .
Parent topic: Configuration Guidelines for DDL Support
Parallel Processing
If using parallel Extract and/or Replicat processes, keep related DDL and DML together in the same process stream to ensure data integrity. Configure the processes so that:
-
all DDL and DML for any given object are processed by the same Extract group and by the same Replicat group.
-
all objects that are relational to one another are processed by the same process group.
For example, if ReplicatA
processes DML for Table1
, then it should also process the DDL for Table1
. If Table2
has a foreign key to Table1
, then its DML and DDL operations also should be processed by ReplicatA
.
If an Extract group writes to multiple trails that are read by different Replicat groups, Extract sends all of the DDL to all of the trails. Use each Replicat group to filter the DDL by using the filter options of the DDL
parameter in the Replicat parameter file.
Parent topic: Configuration Guidelines for DDL Support
Object Names
Oracle GoldenGate preserves the database-defined object name, case, and character set. This support preserves single-byte and multibyte names, symbols, and accent characters at all levels of the database hierarchy.
Object names must be fully qualified with their two-part or three-part names when
supplied as input to any parameters that support DDL synchronization. You can use
the question mark (?) and asterisk (*) wildcards to specify object
names in configuration parameters that support DDL synchronization, but the wildcard
specification also must be fully qualified as a two-part or three-part name. To
process wildcards correctly, the WILDCARDRESOLVE
parameter is set
to DYNAMIC
by default. If WILDCARDRESOLVE
is set
to anything else, the Oracle GoldenGate process that is processing DDL operations
will abend and write the error to the process report.
Parent topic: Configuration Guidelines for DDL Support
Data Definitions
Because DDL support requires a like-to-like configuration, the ASSUMETARGETDEFS
parameter must be used in the Replicat parameter file. Replicat will abend if objects are configured for DDL support and the SOURCEDEFS
parameter is being used. For more information about ASSUMETARGETDEFS
, see Reference for Oracle GoldenGate.
For more information about using a definitions file, see Administering Oracle GoldenGate.
Parent topic: Configuration Guidelines for DDL Support
Truncates
TRUNCATE
statements can be supported as follows:
-
As part of the Oracle GoldenGate full DDL support, which supports
TRUNCATE TABLE
,ALTER TABLE TRUNCATE PARTITION
, and other DDL. This is controlled by the DDL parameter (see Enabling DDL Support.) -
As standalone
TRUNCATE
support. This support enables you to replicateTRUNCATE TABLE
, but no other DDL. TheGETTRUNCATES
parameter controls the standaloneTRUNCATE
feature. For more information, see Reference for Oracle GoldenGate.
To avoid errors from duplicate operations, only one of these features can be active at the same time.
Parent topic: Configuration Guidelines for DDL Support
Initial Synchronization
To configure DDL replication, start with a target database that is synchronized with the source database. DDL support is compatible with the Replicat initial load method.
Before executing an initial load, disable DDL extraction and replication. DDL processing is controlled by the DDL parameter in the Extract and Replicat parameter files.
After initial synchronization of the source and target data, use all of the source sequence values at least once with NEXTVAL
before you run the source applications. You can use a script that selects NEXTVAL
from every sequence in the system. This must be done while Extract is running.
Parent topic: Configuration Guidelines for DDL Support
Data Continuity After CREATE or RENAME
To replicate DML operations on new Oracle tables resulting from a CREATE
or RENAME
operation, the names of the new tables must be specified in TABLE
and MAP
statements in the parameter files. You can use wildcards to make certain that they are included.
To create a new user with CREATE USER
and then move new or renamed tables into that schema, the new user name must be specified in TABLE
and MAP
statements. To create a new user fin2
and move new or renamed tables into that schema, the parameter statements could look as follows, depending on whether you want the fin2
objects mapped to the same, or different, schema on the target:
Extract:
TABLE fin2.*;
Replicat:
MAP fin2.*, TARGET different_schema
.*;
Parent topic: Configuration Guidelines for DDL Support
Understanding DDL Scopes
Database objects are classified into scopes. A scope is a category that defines how DDL operations on an object are handled by Oracle GoldenGate.
The scopes are:
-
MAPPED
-
UNMAPPED
-
OTHER
The use of scopes enables granular control over the filtering of DDL operations, string substitutions, and error handling.
Parent topic: Configuring DDL Support
Mapped Scope
Objects that are specified in TABLE
and MAP
statements are of MAPPED
scope. Extraction and replication instructions in those statements apply to both data (DML) and DDL on the specified objects, unless override rules are applied.
For objects in TABLE
and MAP
statements, the DDL operations listed in the following table are supported.
Operations | On any of these Objects(1) |
---|---|
|
|
|
|
|
|
Footnote 1 TABLE and MAP do not support some special characters that could be used in an object name affected by these operations. Objects with non-supported special characters are supported by the scopes of UNMAPPED and OTHER.
Footnote 2
Applies to COMMENT ON TABLE
, COMMENT ON COLUMN
Footnote 3
Includes AS SELECT
Footnote 4
Table name must be qualified with schema name.
For Extract, MAPPED
scope marks an object for DDL capture according to the instructions in the TABLE
statement. For Replicat, MAPPED
scope marks DDL for replication and maps it to the object specified by the schema and name in the TARGET
clause of the MAP
statement. To perform this mapping, Replicat issues ALTER SESSION
to set the schema of the Replicat session to the schema that is specified in the TARGET
clause. If the DDL contains unqualified objects, the schema that is assigned on the target depends on circumstances described in Understanding DDL Scopes.
Assume the following TABLE
and MAP
statements:
Extract (source)
TABLE fin.expen; TABLE hr.tab*;
Replicat (target)
MAP fin.expen, TARGET fin2.expen2; MAP hr.tab*, TARGET hrBackup.bak_*;
Also assume a source DDL statement of:
ALTER TABLE fin.expen ADD notes varchar2(100);
In this example, because the source table fin.expen
is in a MAP
statement with a TARGET
clause that maps to a different schema and table name, the target DDL statement becomes:
ALTER TABLE fin2.expen2 ADD notes varchar2(100);
Likewise, the following source and target DDL statements are possible for the second set of TABLE
and MAP
statements in the example:
Source:
CREATE TABLE hr.tabPayables ... ;
Target:
CREATE TABLE hrBackup.bak_tabPayables ...;
When objects are of MAPPED
scope, you can omit their names from the DDL configuration parameters, unless you want to refine their DDL support further. If you ever need to change the object names in TABLE
and MAP
statements, the changes will apply automatically to the DDL on those objects.
If you include an object in a TABLE
statement, but not in a MAP
statement, the DDL for that object is MAPPED
in scope on the source but UNMAPPED
in scope on the target.
Parent topic: Understanding DDL Scopes
Unmapped Scope
If a DDL operation is supported for use in a TABLE
or MAP
statement, but its base object name is not included in one of those parameters, it is of UNMAPPED
scope.
An object name can be of UNMAPPED
scope on the source (not in an Extract TABLE
statement), but of MAPPED
scope on the target (in a Replicat MAP
statement), or the other way around. When Oracle DDL is of UNMAPPED
scope in the Replicat configuration, Replicat will by default do the following:
- Set the current schema of the Replicat session to the schema of the source DDL object.
- Execute the DDL as that schema.
- Restore Replicat as the current schema of the Replicat session.
Parent topic: Understanding DDL Scopes
Other Scope
DDL operations that cannot be mapped are of OTHER
scope. When DDL is of OTHER
scope in the Replicat configuration, it is applied to the target with the same schema and object name as in the source DDL.
An example of OTHER
scope is a DDL operation that makes a system-specific reference, such as DDL that operates on data file names.
Some other examples of OTHER
scope:
CREATE USER joe IDENTIFIED by joe; CREATE ROLE ggs_gguser_role IDENTIFIED GLOBALLY; ALTER TABLESPACE gg_user TABLESPACE GROUP gg_grp_user;
Parent topic: Understanding DDL Scopes
Correctly Identifying Unqualified Object Names in DDL
Extract captures the current schema (also called session schema) that is in effect when a DDL operation is executed. The current container is also captured if the source is a multitenant container database.
The container and schema are used to resolve unqualified object names in the DDL.
Consider the following example:
CONNECT SCOTT/TIGER CREATE TABLE TAB1 (X NUMBER); CREATE TABLE SRC1.TAB2(X NUMBER) AS SELECT * FROM TAB1;
In both of those DDL statements, the unqualified table TAB1
is resolved as SCOTT.TAB1
based on the current schema SCOTT
that is in effect during the DDL execution.
There is another way of setting the current schema, which is to set the current_schema
for the session, as in the following example:
CONNECT SCOTT/TIGER ALTER SESSION SET CURRENT_SCHEMA=SRC; CREATE TABLE TAB1 (X NUMBER); CREATE TABLE SRC1.TAB2(X NUMBER) AS SELECT * FROM TAB1;
In both of those DDL statements, the unqualified table TAB1
is resolved as SRC.TAB1
based on the current schema SRC
that is in effect during the DDL execution.
In both classic and integrated capture modes, Extract captures the current schema that is in effect during DDL execution, and it resolves the unqualified object names (if any) by using the current schema. As a result, MAP
statements specified for Replicat work correctly for DDL with unqualified object names.
You can also map a source session schema to a different target session schema, if
that is required for the DDL to succeed on the target. This mapping is global and
overrides any other mappings that involve the same schema names. To map session
schemas, use the DDLOPTIONS
parameter with the
MAPSESSIONSCHEMA
option.
If the default or mapped session schema mapping fails, you can handle the error with the following DDLERROR
parameter statement, where error 1435 means that the schema does not exist.
DDLERROR 1435 IGNORE INCLUDE OPTYPE ALTER OBJTYPE SESSION
Parent topic: Configuring DDL Support
Enabling DDL Support
Data Definition Language (DDL) is useful in dynamic environments which change constantly.
By default, the status of DDL replication support is as follows:
-
On the source, Oracle GoldenGate DDL support is disabled by default. You must configure Extract to capture DDL by using the
DDL
parameter. -
On the target, DDL support is enabled by default, to maintain the integrity of transactional data that is replicated. By default, Replicat will process all DDL operations that the trail contains. If needed, you can use the
DDL
parameter to configure Replicat to ignore or filter DDL operations.
Parent topic: Configuring DDL Support
Filtering DDL Replication
By default, all DDL is passed to Extract.
DDL
parameter gives you control over where the filtering is performed, and it also
offers more filtering options, including the ability to filter collectively based on
the DDL scope (for example, include all MAPPED
scope).
Note:
If a DDL operation fails in the middle of a
TRANSACTION
, it forces a commit, which means that the
transaction spanning the DDL is split into two. The first half is committed
and the second half can be restarted. If a recovery occurs, the second half
of the transaction cannot be filtered since the information contained in the
header of the transaction is no longer there.
Parent topic: Configuring DDL Support
Filtering with the DDL Parameter
The DDL
parameter is the main Oracle GoldenGate parameter for filtering DDL within the Extract and Replicat processes.
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, along with other options, to filter the DDL to the required level.
-
DDL
filtering options 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 DDL filtering criteria, it is recommended that you test your configuration in a test environment before using it in production.
For DDL
parameter syntax and additional usage guidelines, see Reference for Oracle GoldenGate.
Note:
Before you configure DDL support, it might help to review How DDL is Evaluated for Processing.
Parent topic: Filtering DDL Replication
Special Filter Cases
This topic describes the special cases that you must consider before creating your DDL filters.
The following are the special cases for creating filter conditions.
Parent topic: Configuring DDL Support
DDL EXCLUDE ALL
DDL EXCLUDE ALL
is a special processing option that is intended
primarily for Extract. DDL EXCLUDE ALL
blocks the replication of
DDL operations, but ensures that Oracle GoldenGate continues to keep the object
metadata current. When Extract receives DDL directly from the logmining server
(triggerless DDL capture mode), current metadata is always maintained.
You can use DDL EXCLUDE ALL
when using a method other than Oracle GoldenGate to apply DDL to the target and 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 anINCLUDE
clause. -
When using
DDL EXCLUDE ALL
, you can set theWILDCARDRESOLVE
parameter toIMMEDIATE
to allow immediate DML resolution if required.
To prevent all DDL metadata and operations from being replicated, omit the DDL
parameter entirely.
Parent topic: Special Filter Cases
Implicit DDL
User-generated DDL operations can generate implicit DDL operations. For example, the following statement generates two distinct DDL operations.
CREATE TABLE customers (custID number, name varchar2(50), address varchar2(75), address2 varchar2(75), city varchar2(50), state (varchar2(2), zip number, contact varchar2(50), areacode number(3), phone number(7), primary key (custID));
The first (explicit) DDL operation is the CREATE TABLE
statement itself.
The second DDL operation is an implicit CREATE UNIQUE INDEX
statement that creates the index for the primary key. This operation is generated by the database engine, not a user application.
Guidelines for Filtering Implicit DDL
How to filter implicit DDL depends on the mechanism that you are using to filter DDL. See Filtering DDL Replication for more information.
-
When the
DDL
parameter is used to filter DDL operations, Oracle GoldenGate filters out any implicit DDL by default, because the explicit DDL will generate the implicit DDL on the target. For example, the target database will create the appropriate index when theCREATE TABLE
statement in the preceding example is applied by Replicat. -
-
If your filtering rules exclude the explicit DDL from being propagated, you must also create a rule to exclude the implicit DDL. For example, if you exclude the
CREATE TABLE
statement in the following example, but do not exclude the implicitCREATE UNIQUE INDEX
statement, the target database will try to create the index on a non-existent table.CREATE TABLE customers (custID number, name varchar2(50), address varchar2(75), address2 varchar2(75), city varchar2(50), state (varchar2(2), zip number, contact varchar2(50), areacode number(3), phone number(7), primary key (custID));
-
If your filtering rules permit the propagation of the explicit DDL, you do not need to exclude the implicit DDL. It will be handled correctly by Oracle GoldenGate and the target database.
-
Parent topic: Special Filter Cases
How Oracle GoldenGate Handles Derived Object Names
DDL operations can contain a base object name and also a derived object name.
A base object is an object that contains data. A derived object is an object that inherits some attributes of the base object to perform a function related to that object. DDL statements that have both base and derived objects are:
-
RENAME
andALTER RENAME
-
CREATE
andDROP
on an index, synonym, or trigger
Consider the following DDL statement:
CREATE INDEX hr.indexPayrollDate ON TABLE hr.tabPayroll (payDate);
In this case, the table is the base object. Its name (hr.tabPayroll
) is the base name and is subject to mapping with TABLE
or MAP
under the MAPPED
scope. The derived object is the index, and its name (hr.indexPayrollDate
) is the derived name.
You can map a derived name in its own TABLE
or MAP
statement, separately from that of the base object. Or, you can use one MAP
statement to handle both. In the case of MAP
, the conversion of derived object names on the target works as follows.
- MAP Exists for Base Object, But Not Derived Object
- MAP Exists for Base and Derived Objects
- MAP Exists for Derived Object, But Not Base Object
- New Tables as Derived Objects
- Disabling the Mapping of Derived Objects
Parent topic: Configuring DDL Support
MAP Exists for Base Object, But Not Derived Object
If there is a MAP
statement for the base object, but not for the derived object, the result is a schema based on the mapping that matches the derived object name. Derived objects are only mapped if the MAPDERIVED
option is enabled, which is also the default option.
For example, consider the following:
Extract (source)
Table hr.*;
Replicat (target)
MAP hr.*, TARGET hrBackup.*;
Assume the following source DDL statement:
CREATE INDEX hr.indexPayrollDate ON TABLE hr.Payroll (payDate);
The CREATE INDEX
statement is executed by Replicat on the target as follows:
CREATE INDEX hrBackup.indexPayrollDate ON TABLE hrBackup.Payroll (payDate);
In this example, the mapping is such that it matches the derived object name because of which the derived object schema is changed from hr
to hrBackup
.
Here’s another example, where there is no mapping that matches the derived object name so the derived object name remains the same.
Extract (source)
Table hr.tab*;
Replicat (target)
MAP hr.tab*, TARGET hrBackup.*;
Assume the following source DDL statement:
CREATE INDEX hr.indexPayrollDate ON TABLE hr.tabPayroll (payDate);
The CREATE INDEX
statement is executed by Replicat on the target as follows:
CREATE INDEX hr.indexPayrollDate ON TABLE hrBackup.tabPayroll (payDate);
Parent topic: How Oracle GoldenGate Handles Derived Object Names
MAP Exists for Base and Derived Objects
If there is a MAP
statement for the base object and also one for the derived object, the result is an explicit mapping. Assuming the DDL statement includes MAPPED
, Replicat converts the schema and name of each object according to its own TARGET
clause. For example, assume the following:
Extract (source)
TABLE hr.tab*; TABLE hr.index*;
Replicat (target)
MAP hr.tab*, TARGET hrBackup.*;MAP hr.index*, TARGET hrIndex.*;
Assume the following source DDL statement:
CREATE INDEX hr.indexPayrollDate ON TABLE hr.tabPayroll (payDate);
The CREATE INDEX
statement is executed by Replicat on the target as follows:
CREATE INDEX hrIndex.indexPayrollDate ON TABLE hrBackup.tabPayroll (payDate);
Use an explicit mapping when the index on the target must be owned by a different schema from that of the base object, or when the name on the target must be different from that of the source.
Parent topic: How Oracle GoldenGate Handles Derived Object Names
MAP Exists for Derived Object, But Not Base Object
If there is a MAP
statement for the derived object, but not for the base object, Replicat does not perform any name conversion for either object. The target DDL statement is the same as that of the source. To map a derived object, the choices are:
-
Use an explicit
MAP
statement for the base object. -
If names permit, map both base and derived objects in the same
MAP
statement by means of a wildcard. -
Create a
MAP
statement for each object, depending on how you want the names converted.
Parent topic: How Oracle GoldenGate Handles Derived Object Names
New Tables as Derived Objects
The following explains how Oracle GoldenGate handles new tables that are created from:
-
RENAME
andALTER RENAME
-
CREATE TABLE AS SELECT
Parent topic: How Oracle GoldenGate Handles Derived Object Names
CREATE TABLE AS SELECT
The CREATE TABLE AS SELECT
(CTAS) statements include SELECT
statements and INSERT
statements that reference any number of underlying objects. By default, Oracle GoldenGate obtains the data for the AS SELECT
clause from the target database. You can force the CTAS operation to preserve the original inserts using this parameter.
Note:
For this reason, Oracle XMLType
tables created from a CTAS
(CREATE TABLE AS SELECT
) statement cannot be supported. For XMLType
tables, the row object IDs must match between source and target, which cannot be maintained in this scenario. XMLType
tables created by an empty CTAS
statement (that does not insert data in the new table) can be maintained correctly.
In addition, you could use the GETCTASDML
parameter that allows CTAS to replay the inserts of the CTAS thus preserving OIDs during replication. This parameter is only supported with Integrated Dictionary and any downstream Replicat must be 12.1.2.1 or greater to consume the trail otherwise, there may be divergence.
The objects in the AS SELECT
clause must exist in the target database, and their names must be identical to the ones on the source.
In a MAP
statement, Oracle GoldenGate only maps the name of the new table (CREATE TABLE name
) to the TARGET
specification, but does not map the names of the underlying objects from the AS
SELECT
clause. There could be dependencies on those objects that could cause data inconsistencies if the names were converted to the TARGET
specification.
The following shows an example of a CREATE TABLE AS SELECT
statement on the source and how it would be replicated to the target by Oracle GoldenGate.
CREATE TABLE a.tab1 AS SELECT * FROM a.tab2;
The MAP
statement for Replicat is as follows:
MAP a.tab*, TARGET a.x*;
The target DDL statement that is applied by Replicat is the following:
CREATE TABLE a.xtab1 AS SELECT * FROM a.tab2;
The name of the table in the AS SELECT * FROM
clause remains as it was on the source: tab2
(rather than xtab2
).
To keep the data in the underlying objects consistent on source and target, you can configure them for data replication by Oracle GoldenGate. In the preceding example, you could use the following statements to accommodate this requirement:
Source
TABLE a.tab*;
Target
MAPEXCLUDE a.tab2 MAP a.tab*, TARGET a.x*; MAP a.tab2, TARGET a.tab2;
Parent topic: New Tables as Derived Objects
RENAME and ALTER TABLE RENAME
In RENAME
and ALTER TABLE RENAME
operations, the base object is always the new table name. In the following example, the base object name is considered to be index_paydate
.
ALTER TABLE hr.indexPayrollDate RENAME TO index_paydate;
or...
RENAME hr.indexPayrollDate TO index_paydate;
The derived object name is hr.indexPayrollDate
.
Parent topic: New Tables as Derived Objects
Disabling the Mapping of Derived Objects
Use the DDLOPTIONS
parameter with the NOMAPDERIVED
option to prevent the conversion of the name of a derived object according to a TARGET
clause of a MAP
statement that includes it. NOMAPDERIVED
overrides any explicit MAP
statements that contain the name of the base or derived object. Source DDL that contains derived objects is replicated to the target with the same schema and object names as on the source.
The following table shows the results of MAPDERIVED
compared to NOMAPDERIVED
, based on whether there is a MAP
statement just for the base object, just for the derived object, or for both.
Base Object | Derived Object | MAP/NOMAP DERIVED? | Derived object converted per a MAP? | Derived object gets schema of base object? |
---|---|---|---|---|
mappedFoot 5 |
mapped |
|
yes |
no |
mapped |
not mapped |
|
no |
yes |
not mapped |
mapped |
|
no |
no |
not mapped |
not mapped |
|
no |
no |
mapped |
mapped |
|
no |
no |
mapped |
not mapped |
|
no |
no |
not mapped |
mapped |
|
no |
no |
not mapped |
not mapped |
|
no |
no |
Footnote 5
Mapped means included in a MAP
statement.
The following examples illustrate the results of MAPDERIVED
as
compared to NOMAPDERIVED
. In the following table, both trigger and
table are owned by rpt
on the target because both base and derived
names are converted by means of MAPDERIVED
.
MAP statement | Source DDL statement captured by Extract | Target DDL statement applied by Replicat |
---|---|---|
|
|
|
In the following table, the trigger is owned by fin
, because
conversion is prevented by means of NOMAPDERIVED
.
MAP statement | Source DDL statement captured by Extract | Target DDL statement applied by Replicat |
---|---|---|
|
|
|
Note:
In the case of a RENAME
statement, the new table name is considered to be the base table name, and the old table name is considered to be the derived table name.
Parent topic: How Oracle GoldenGate Handles Derived Object Names
Using DDL String Substitution
You can substitute strings within a DDL operation while it is being processed by Oracle GoldenGate.
This feature provides a convenience for changing and mapping directory names,
comments, and other things that are not directly related to data structures. For
example, you could substitute one tablespace name for another, or substitute a
string within comments. String substitution is controlled by the
DDLSUBST
parameter. For more information, see Reference for Oracle GoldenGate.
Note:
Before you create a DDLSUBST
parameter statement, it might help to review How DDL is Evaluated for Processing in this chapter.
Parent topic: Configuring DDL Support
Controlling the Propagation of DDL to Support Different Topologies
To support bidirectional and cascading replication configurations, it is important for Extract to be able to identify the DDL that is performed by Oracle GoldenGate and by other applications, such as the local business applications.
Depending on the configuration that you want to deploy, it might be appropriate to capture one or both of these sources of DDL on the local system.
Note:
Oracle GoldenGate DDL consists of ALTER TABLE
statements performed by Extract to create log groups and the DDL that is performed by Replicat to replicate source DDL changes.
The following options of the DDLOPTIONS
parameter control whether DDL on the local system is captured by Extract and then sent to a remote system, assuming Oracle GoldenGate DDL support is configured and enabled:
-
The
GETREPLICATES
andIGNOREREPLICATES
options control whether Extract captures or ignores the DDL that is generated by Oracle GoldenGate. The default isIGNOREREPLICATES
, which does not propagate the DDL that is generated by Oracle GoldenGate. To identify the DDL operations that are performed by Oracle GoldenGate, the following comment is part of each Extract and Replicat DDL statement:/* GOLDENGATE_DDL_REPLICATION */
-
The
GETAPPLOPS
andIGNOREAPPLOPS
options control whether Extract captures or ignores the DDL that is generated by applications other than Oracle GoldenGate. The default isGETAPPLOPS
, which propagates the DDL from local applications (other than Oracle GoldenGate).
The result of these default settings is that Extract ignores its own DDL and the DDL that is applied to the local database by a local Replicat, so that the DDL is not sent back to its source, and Extract captures all other DDL that is configured for replication. The following is the default DDLOPTIONS
configuration.
DDLOPTIONS GETAPPLOPS, IGNOREREPLICATES
This behavior can be modified. See the following topics:
- Propagating DDL in Active-Active (Bidirectional) Configurations
- Propagating DDL in a Cascading Configuration
Parent topic: Configuring DDL Support
Propagating DDL in Active-Active (Bidirectional) Configurations
Oracle GoldenGate supports active-active DDL replication between two systems. For an active-active bidirectional replication, the following must be configured in the Oracle GoldenGate processes:
-
DDL that is performed by a business application on one system must be replicated to the other system to maintain synchronization. To satisfy this requirement, include the
GETAPPLOPS
option in theDDLOPTIONS
statement in the Extract parameter files on both systems. -
DDL that is applied by Replicat on one system must be captured by the local Extract and sent back to the other system. To satisfy this requirement, use the
GETREPLICATES
option in theDDLOPTIONS
statement in the Extract parameter files on both systems.Note:
An internal Oracle GoldenGate token will cause the actual Replicat DDL statement itself to be ignored to prevent loopback. The purpose of propagating Replicat DDL back to the original system is so that the Replicat on that system can update its object metadata cache, in preparation to receive incoming DML, which will have the new metadata.
-
Each Replicat must be configured to update its object metadata cache whenever the remote Extract sends over a captured Replicat DDL statement. To satisfy this requirement, use the
UPDATEMETADATA
option in theDDLOPTIONS
statement in the Replicat parameter files on both systems.
The resultant DDLOPTIONS
statements should look as follows:
Extract (primary and secondary)
DDLOPTIONS GETREPLICATES, GETAPPLOPS
Replicat (primary and secondary)
DDLOPTIONS UPDATEMETADATA
WARNING:
Before you allow new DDL or DML to be issued for the same object(s) as the original DDL, allow time for the original DDL to be replicated to the remote system and then captured again by the Extract on that system. This will ensure that the operations arrive in correct order to the Replicat on the original system, to prevent DML errors caused by metadata inconsistencies. See the following diagram for more information.
The labels in the diagrams imply the following:
-
1: ALTER TABLE Customer ADD Birth_Date date
-
2; New metadata: First_Name varchar2(50), Last_Name varchar2(50), Address varchar2(50), City varchar2(50), Country varchar2(25), Birth_Date date
-
3: ALTER TABLE
-
4: New metadata: First_Name varchar2(50), Last_Name varchar2(50), Address varchar2(50), City varchar2(50), Country varchar2(25), Birth_Date date
-
5: ALTER TABLE
-
6: DDLOPTIONS UPDATEMETADATA New metadata: First_Name varchar2(50), Last_Name varchar2(50), Address varchar2(50), City varchar2(50), Country varchar2(25), Birth_Date date
For more information about DDLOPTIONS
, see Reference for Oracle GoldenGate.
For more information about configuring a bidirectional configuration, see Administering Oracle GoldenGate.
Propagating DDL in a Cascading Configuration
In a cascading configuration, use the following setting for DDLOPTIONS
in the Extract parameter file on each intermediary system. This configuration forces Extract to capture the DDL from Replicat on an intermediary system and cascade it to the next system downstream.
DDLOPTIONS GETREPLICATES, IGNOREAPPLOPS
For more information about DDLOPTIONS
, see DDLOPTIONS
in Reference for Oracle GoldenGate.
Adding Supplemental Log Groups Automatically
Use the DDLOPTIONS
parameter with the ADDTRANDATA
option for performing tasks described in this topic.
You can perform the following tasks using the DDLOPTIONS
:
-
Enable Oracle's supplemental logging automatically for new tables created with a
CREATE TABLE
. -
Update Oracle's supplemental logging for tables affected by an
ALTER TABLE
to add or drop columns. -
Update Oracle's supplemental logging for tables that are renamed.
-
Update Oracle's supplemental logging for tables where unique or primary keys are added or dropped.
To use DDLOPTIONS ADDSCHEMATRANDATA
, the ADD SCHEMATRANDATA
command must be issued in GGSCI to enable schema-level supplemental logging.
By default, the ALTER TABLE
that adds the supplemental logging is not replicated to the target unless the GETREPLICATES
parameter is in use.
DDLOPTIONS ADDTRANDATA
is not supported for multitenant container databases, see Configuring Logging Properties for more information.
Parent topic: Configuring DDL Support
Removing Comments from Replicated DDL
You can use the DDLOPTIONS
parameter with the REMOVECOMMENTS
BEFORE
and REMOVECOMMENTS AFTER
options to prevent comments
that were used in the source DDL from being included in the target DDL.
By default, comments are not removed, so that they can be used for string substitution.
Parent topic: Configuring DDL Support
Replicating an IDENTIFIED BY Password
Use the DDLOPTIONS
parameter with the
DEFAULTUSERPASSWORDALIAS
and REPLICATEPASSWORD |
NOREPLICATEPASSWORD
options to control how the password of a replicated
{CREATE | ALTER} USER
name
IDENTIFIED
BY
password
statement is handled. These options must be
used together.
See the USEPASSWORDVERIFIERLEVEL
option of DDLOPTIONS
for important information about specifying the password verifier when Replicat operates against an Oracle 10g or 11g database.
Note:
Replication ofCREATE | 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.
Parent topic: Configuring DDL Support
How DDL is Evaluated for Processing
This topic explains how Oracle GoldenGate processes DDL statements on the source and target systems.
It shows the order in which different criteria in the Oracle GoldenGate parameters are processed, and it explains the differences between how Extract and Replicat each process the DDL.
Extract
-
Extract captures a DDL statement.
-
Extract separates comments, if any, from the main statement.
-
Extract searches for the
DDL
parameter. (This example assumes it exists.) -
Extract searches for the
IGNOREREPLICATES
parameter. If it is present, and if Replicat produced this DDL on this system, Extract ignores the DDL statement. (This example assumes no Replicat operations on this system.) -
Extract determines whether the DDL statement is a
RENAME
. If so, the rename is flagged internally. -
Extract gets the base object name and, if present, the derived object name.
-
If the statement is a
RENAME
, Extract changes it toALTER TABLE RENAME
. -
Extract searches for the
DDLOPTIONS REMOVECOMMENTS BEFORE
parameter. If it is present, Extract removes the comments from the DDL statement, but stores them in case there is aDDL INCLUDE
orDDL EXCLUDE
clause that usesINSTR
orINSTRCOMMENTS
. -
Extract determines the DDL scope:
MAPPED
,UNMAPPED
orOTHER
:-
It is
MAPPED
if the operation and object types are supported for mapping, and the base object name and/or derived object name (ifRENAME
) is in aTABLE
parameter.
-
It is
UNMAPPED
if the operation and object types are not supported for mapping, and the base object name and/or derived object name (ifRENAME
) is not in aTABLE
parameter. -
Otherwise the operation is identified as
OTHER
.
-
-
Extract checks the
DDL
parameter forINCLUDE
andEXCLUDE
clauses, and it evaluates theDDL
parameter criteria in those clauses. All options must evaluate toTRUE
in order for theINCLUDE
orEXCLUDE
to evaluate toTRUE
. The following occurs:-
If an
EXCLUDE
clause evaluates toTRUE
, Extract discards the DDL statement and evaluates another DDL statement. In this case, the processing steps start over. -
If an
INCLUDE
clause evaluates toTRUE
, or if theDDL
parameter does not have anyINCLUDE
orEXCLUDE
clauses, Extract includes the DDL statement, and the processing logic continues.
-
-
Extract searches for a
DDLSUBST
parameter and evaluates theINCLUDE
andEXCLUDE
clauses. If the criteria in those clauses add up toTRUE
, Extract performs string substitution. Extract evaluates the DDL statement against eachDDLSUBST
parameter in the parameter file. For all trueDDLSUBST
specifications, Extract performs string substitution in the order that theDDLSUBST
parameters are listed in the file. -
Now that
DDLSUBT
has been processed, Extract searches for theREMOVECOMMENTS AFTER
parameter. If it is present, Extract removes the comments from the DDL statement. -
Extract searches for
DDLOPTIONS ADDTRANDATA
. If it is present, and if the operation isCREATE TABLE
, Extract issues theALTER TABLE
name
ADD SUPPLEMENTAL LOG GROUP
command on the table. -
Extract writes the DDL statement to the trail.
Replicat
-
Replicat reads the DDL statement from the trail.
-
Replicat separates comments, if any, from the main statement.
-
Replicat searches for
DDLOPTIONS REMOVECOMMENTS BEFORE
. If it is present, Replicat removes the comments from the DDL statement. -
Replicat evaluates the DDL synchronization scope to determine if the DDL qualifies for name mapping. Anything else is of
OTHER
scope. -
Replicat evaluates the
MAP
statements in the parameter file. If the source base object name for this DDL (as read from the trail) appears in any of theMAP
statements, the operation is marked asMAPPED
in scope. Otherwise it is marked asUNMAPPED
in scope. -
Replicat replaces the source base object name with the base object name that is specified in the
TARGET
clause of theMAP
statement. -
If there is a derived object, Replicat searches for
DDLOPTIONS MAPDERIVED
. If it is present, Replicat replaces the source derived name with the target derived name from theMAP
statement. -
Replicat checks the
DDL
parameter forINCLUDE
andEXCLUDE
clauses, and it evaluates theDDL
parameter criteria contained in them. All options must evaluate toTRUE
in order for theINCLUDE
orEXCLUDE
to evaluate toTRUE
. The following occurs:-
If any
EXCLUDE
clause evaluates toTRUE
, Replicat discards the DDL statement and starts evaluating another DDL statement. In this case, the processing steps start over. -
If any
INCLUDE
clause evaluates toTRUE
, or if theDDL
parameter does not have anyINCLUDE
orEXCLUDE
clauses, Replicat includes the DDL statement, and the processing logic continues.
-
-
Replicat searches for the
DDLSUBST
parameter and evaluates theINCLUDE
andEXCLUDE
clauses. If the options in those clauses add up toTRUE
, Replicat performs string substitution. Replicat evaluates the DDL statement against eachDDLSUBST
parameter in the parameter file. For all trueDDLSUBST
specifications, Replicat performs string substitution in the order that theDDLSUBST
parameters are listed in the file. -
Now that
DDLSUBT
has been processed, Replicat searches for theREMOVECOMMENTS AFTER
parameter. If it is present, Replicat removes the comments from the DDL statement. -
Replicat executes the DDL statement on the target database.
-
If there are no errors, Replicat processes the next DDL statement. If there are errors, Replicat performs the following steps.
-
Replicat analyzes the
INCLUDE
andEXCLUDE
rules in the ReplicatDDLERROR
parameters in the order that they appear in the parameter file. If Replicat finds a rule for the error code, it applies the specified error handling; otherwise, it appliesDEFAULT
handling. -
If the error handling does not enable the DDL statement to succeed, Replicat does one of the following: abends, ignores the operation, or discards it as specified in the rules.
Note:
If there are multiple targets for the same source in a MAP
statement, the processing logic executes for each one.
Parent topic: Configuring DDL Support
Viewing DDL Report Information
By default, Oracle GoldenGate shows basic statistics about DDL at the end of the Extract and Replicat reports.
To enable expanded DDL reporting, use the DDLOPTIONS
parameter with
the REPORT
option. Expanded reporting includes the following
information about DDL processing:
-
A step-by-step history of the DDL operations that were processed by Oracle GoldenGate.
-
The DDL filtering and processing parameters that are being used.
Expanded DDL report information increases the size of the report file, but it might
be useful in certain situations, such as for troubleshooting or to determine when an
ADD TRANDATA
to add supplemental logging was applied.
To view a report, use the VIEW REPORT
command.
VIEW REPORT group
- Viewing DDL Reporting in Replicat
- Viewing DDL Reporting in Extract
- Statistics in the Process Reports
Parent topic: Configuring DDL Support
Viewing DDL Reporting in Replicat
The Replicat report lists:
-
The entire syntax and source Oracle GoldenGate SCN of each DDL operation that Replicat processed from the trail. You can use the source SCN for tracking purposes, especially when there are restores from backup and Replicat is positioned backward in the trail.
-
A subsequent entry that shows the scope of the operation (
MAPPED
,UNMAPPED
,OTHER
) and how object names were mapped in the target DDL statement, if applicable. -
Another entry that shows how processing criteria was applied.
-
Additional entries that show whether the operation succeeded or failed, and whether or not Replicat applied error handling rules.
The following excerpt from a Replicat report illustrates a sequence of steps, including error handling:
2011-01-20 15:11:45 GGS INFO 2104 DDL found, operation [drop table myTableTemp ], Source SCN [1186713.0]. 2011-01-20 15:11:45 GGS INFO 2100 DDL is of mapped scope, after mapping new operation [drop table "QATEST2"."MYTABLETEMP" ]. 2011-01-20 15:11:45 GGS INFO 2100 DDL operation included [include objname myTable*], optype [DROP], objtype [TABLE], objname [QATEST2.MYTABLETEMP]. 2011-01-20 15:11:45 GGS INFO 2100 Executing DDL operation. 2011-01-20 15:11:48 GGS INFO 2105 DDL error ignored for next retry: error code [942], filter [include objname myTableTemp], error text [ORA-00942: table or view does not exist], retry [1]. 2011-01-20 15:11:48 GGS INFO 2100 Executing DDL operation , trying again due to RETRYOP parameter. 2011-01-20 15:11:51 GGS INFO 2105 DDL error ignored for next retry: error code [942], filter [include objname myTableTemp], error text [ORA-00942: table or view does not exist], retry [2]. 2011-01-20 15:11:51 GGS INFO 2100 Executing DDL operation, trying again due to RETRYOP parameter. 2011-01-20 15:11:54 GGS INFO 2105 DDL error ignored for next retry: error code [942], filter [include objname myTableTemp], error text [ORA-00942: table or view does not exist], retry [3]. 2011-01-20 15:11:54 GGS INFO 2100 Executing DDL operation, trying again due to RETRYOP parameter. 2011-01-20 15:11:54 GGS INFO 2105 DDL error ignored: error code [942], filter [include objname myTableTemp], error text [ORA-00942: table or view does not exist].
Parent topic: Viewing DDL Report Information
Viewing DDL Reporting in Extract
The Extract report lists the following:
-
The entire syntax of each captured DDL operation, the start and end SCN, the Oracle instance, the DDL sequence number (from the
SEQNO
column of the history table), and the size of the operation in bytes. -
A subsequent entry that shows how processing criteria was applied to the operation, for example string substitution or
INCLUDE
andEXCLUDE
filtering. -
Another entry showing whether the operation was written to the trail or excluded.
The following, taken from an Extract report, shows an included operation and an excluded operation. There is a report message for the included operation, but not for the excluded one.
2011-01-20 15:11:41 GGS INFO 2100 DDL found, operation [create table myTable ( myId number (10) not null, myNumber number, myString varchar2(100), myDate date, primary key (myId) ) ], start SCN [1186754], commit SCN [1186772] instance [test11g (1)], DDL seqno [4134]. 2011-01-20 15:11:41 GGS INFO 2100 DDL operation included [INCLUDE OBJNAME myTable*], optype [CREATE], objtype [TABLE], objname [QATEST1.MYTABLE]. 2011-01-20 15:11:41 GGS INFO 2100 DDL operation written to extract trail file. 2011-01-20 15:11:42 GGS INFO 2100 Successfully added TRAN DATA for table with the key, table [QATEST1.MYTABLE], operation [ALTER TABLE "QATEST1"."MYTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_MYTABLE_53475" (MYID) ALWAYS /* GOLDENGATE_DDL_REPLICATION */ ]. 2011-01-20 15:11:43 GGS INFO 2100 DDL found, operation [create table myTableTemp ( vid varchar2(100), someDate date, primary key (vid) ) ], start SCN [1186777], commit SCN [1186795] instance [test11g (1)], DDL seqno [4137]. 2011-01-20 15:11:43 GGS INFO 2100 DDL operation excluded [EXCLUDE OBJNAME myTableTemp OPTYPE CREATE], optype [CREATE], objtype [TABLE], objname [QATEST1.MYTABLETEMP].
Parent topic: Viewing DDL Report Information
Statistics in the Process Reports
You can send current statistics for DDL processing to the Extract and Replicat reports by using the SEND
command in GGSCI.
SEND {EXTRACT | REPLICAT} group
REPORT
The statistics show totals for:
-
All DDL operations
-
Operations that are
MAPPED
in scope -
Operations that are
UNMAPPED
in scope -
Operations that are
OTHER
in scope -
Operations that were excluded (number of operations minus included ones)
-
Errors (Replicat only)
-
Retried errors (Replicat only)
-
Discarded errors (Replicat only)
-
Ignored operations (Replicat only)
Parent topic: Viewing DDL Report Information
Tracing DDL Processing
If you open a support case with Oracle GoldenGate Technical Support, you might be
asked to turn on tracing. TRACE
and TRACE2
control DDL
tracing.
Parent topic: Configuring DDL Support
Using Edition-Based Redefinition
Oracle GoldenGate supports the use of Edition-based Redefinition (EBR) with Oracle Databases enabling you to upgrade the database component of an application while it is in use, thereby minimizing or eliminating down time.
Editions are non-schema objects that Editioned objects belong to. Editions can be
thought of as owning editioned objects or as a namespace. Every database starts with
one edition named, ORA$BASE
; this includes upgraded databases. More
than one edition can exist in a database and each can only have one child. For
example, if you create three editions in succession, edition1, edition2, edition3,
then edition1 is the parent of edition2 which is the parent of edition3. This is
irrespective of the user or database session that creates them or which edition was
current when the new one is created. When you create an edition, it inherits all the
editioned objects of its parent. To use editions with Oracle GoldenGate, you must
create them.
An object is considered editioned if it is an editionable type, it is created with the EDITIONABLE
attribute, and the schema is enabled for editioning of that object type. When you create, alter, or drop an editioned object, the redo log will contain the name of the edition in which it belongs. In a container database, editions belong to the container and each container has its own default edition.
The CREATE | DROP EDITION
DDLs are captured for all Extract configurations. They fall into the OTHER
category and assigned an OBJTYPE
option value of EDITION
. The OBJTYPE
option can be used for filtering, for example:
DDL EXCLUDE OBJTYPE EDITION
DDL EXCLUDE OBJTYPE EDITION OPTYPE CREATE
DDL EXCLUDE OBJTYPE EDITION OPTYPE DROP
DDL EXCLUDE OBJTYPE EDITION OPTYPE DROP ALLOWEMPTYOWNER OBJNAME edition_name
You must use the following syntax to exclude an edition from Extract or Replicat:
EXCLUDE OBJTYPE EDITION, ALLOWEMPTYOWNER OBJNAME edition_name
Editions fall into the OTHER
category so no mapping is performed on the edition name. When applied, the USE
permission is automatically granted to the Replicat user. Replicat will also perform a grant use on edition
name
with grant option
to the original creating user if that user exists on the target database. Because editions are not mappable operations, they do not have owners so the standard EXCLUDE
statement does not work.
The DDLs used to create or alter editions does not actually enable the user for editions, rather they enable the schema for editions. This is an important distinction because it means that the Replicat user does not need to be enabled for editions to apply DDLs to editioned objects. When Replicat applies a CREATE EDITION
DDL, it grants the original creating user permission to USE
it if the original user exists on the target database. For any unreplicated CREATE EDITION
statements, you must issue a USE WITH GRANT OPTION
grant to the Replicat user.
Whether or not an editionable objects becomes editioned is controlled by the schema it is applied in. Replicat switches its current session Edition before applying a DDL if the edition name attribute exists in the trail file and it is not empty.
Container database environments are supported for both Extract and Replicat. No additional configuration is necessary. The Replicat user's schema can not be enabled for editions if it is a common user. The Replicat user's schema does not need to be enabled for editions when applying DDLs to editioned objects in other schemas.
Parent topic: Configuring DDL Support