Details of Support for Objects and Operations in Oracle DDL

This topic outlines the Oracle objects and operation types that Oracle GoldenGate supports for the capture and replication of DDL operations.

Trigger-based capture is required for Oracle releases that are earlier than version 11.2.0.4. If Extract will run in integrated mode against a version 11.2.0.4 or later of Oracle Database, then the DDL trigger and supporting objects are not required.

Supported Objects and Operations in Oracle DDL

When the source database is Oracle 11.2.0.4 or later and Extract operates in integrated mode, DDL capture support is integrated into the database logmining server and does not require the use of a DDL trigger. You must set the database parameter compatibility to 11.2.0.4.0. Extract supports DDL that includes password-based column encryption, such as:

  • CREATE TABLE t1 (a number, b varchar2(32) ENCRYPT IDENTIFIED BY my_password);

  • ALTER TABLE t1 ADD COLUMN c varchar2(64) ENCRYPT IDENTIFIED BY my_password;

Note:

Password-based column encryption in DDL is not supported in classic capture mode.

The following additional statements apply to Extract with respect to DDL support.

  • All Oracle GoldenGate topology configurations are supported for Oracle DDL replication.

  • Active-active (bi-directional) replication of Oracle DDL is supported between two (and only two) databases that contain identical metadata.

  • Oracle GoldenGate supports DDL on the following objects:

    • clusters

    • directories

    • functions

    • indexes

    • packages

    • procedure

    • tables

    • tablespaces

    • roles

    • sequences

    • synonyms

    • triggers

    • types

    • views

    • materialized views

    • users

    • invisible columns

  • Oracle Edition-Based Redefinition (EBR) database replication of Oracle DDL is supported for integrated Extract for the following Oracle Database objects:

    • functions

    • library

    • packages (specification and body)

    • procedure

    • synonyms

    • types (specification and body)

    • views

    EBR does not support use of DDL triggers.

  • Oracle GoldenGate supports DDL operations of up to 4 MB in size. Oracle GoldenGate measures the size of DDL statement in bytes, not in characters. This size limitation includes packages, procedures, and functions. The actual size limit of the DDL support is approximate, because the size not only includes the statement text, but also Oracle GoldenGate maintenance overhead that depends on the length of the object name, the DDL type, and other characteristics of keeping a DDL record internally.

  • Oracle GoldenGate supports Global Temporary Tables (GTT) DDL operations to be visible to Extract so that they can be replicated. You must set the DDLOPTIONS parameter to enable this operation because it is not set by default.

  • Oracle GoldenGate supports Integrated Dictionary for use with NOUSERID and TRANLOGOPTIONS GETCTASDML. This means that Extract will be obtaining object metadata from the LogMiner dictionary instead of the DDL trigger and without querying the dictionary objects. Oracle GoldenGate uses Integrated Dictionary automatically when the source database compatibility parameter is greater than or equal to 11.2.0.4 and Integrated Extract is used.

    The Integrated Dictionary feature is not supported with classic Extract.

    When using Integrated Dictionary and trail format in the Oracle GoldenGate release 12.2.x, Integrated Capture requires the Logminer patch to be applied on the mining database if the Oracle Database release is earlier than 12.1.0.2.

  • Oracle GoldenGate supports replication of invisible columns in Integrated Capture mode. Trail format release 12.2 is required. Replicat must specify the MAPINVISIBLECOLUMNS parameter or explicitly map to invisible columns in the COLMAP clause of the MAP parameter.

    If SOURCEDEFS or TARGETDEFS is used, the metadata format of a definition file for Oracle tables must be compatible with the trail format. Metadata format 12.2 is compatible with trail format 12.2, and metadata format earlier than 12.2 is compatible with trail format earlier than 12.2. To specify the metadata format of a definition file, use the FORMAT RELEASE option of the DEFSFILE parameter when the definition file is generated in DEFGEN.

  • DDL statements to create a namespace context (CREATE CONTEXT) are captured by Extract and applied by Replicat.

  • Extract in pump mode supports the following DDL options:

    • DDL INCLUDE ALL

    • DDL EXCLUDE ALL

    • DDL EXCLUDE OBJNAME

    The SOURCECATALOG and ALLCATALOG option of DDL EXCLUDE is also supported.

    If no DDL parameter is specified, then all DDLs are written to trail. If DDL EXCLUDE OBJNAME is specified and the object owner is does not match an exclusion rule, then it is written to the trail.

Non-supported Objects and Operations in Oracle DDL

These statements apply to integrated and classic capture modes.

Excluded Objects

The following names or name prefixes are considered Oracle-reserved and must be excluded from the Oracle GoldenGate DDL configuration. Oracle GoldenGate will ignore objects that contain these names.

Excluded schemas:

  "ANONYMOUS", // HTTP access to XDB
  "APPQOSSYS", // QOS system user
  "AUDSYS", // audit super user
  "BI", // Business Intelligence
  "CTXSYS", // Text
  "DBSNMP", // SNMP agent for OEM
  "DIP", // Directory Integration Platform
  "DMSYS", // Data Mining
  "DVF", // Database Vault
  "DVSYS", // Database Vault
  "EXDSYS", // External ODCI System User
  "EXFSYS", // Expression Filter
  "GSMADMIN_INTERNAL", // Global Service Manager
  "GSMCATUSER", // Global Service Manager
  "GSMUSER", // Global Service Manager
  "LBACSYS", // Label Security
  "MDSYS", // Spatial
  "MGMT_VIEW", // OEM Database Control
  "MDDATA",
  "MTSSYS", // MS Transaction Server
  "ODM", // Data Mining
  "ODM_MTR", // Data Mining Repository
  "OJVMSYS", // Java Policy SRO Schema
  "OLAPSYS", // OLAP catalogs
  "ORACLE_OCM", // Oracle Configuration Manager User
  "ORDDATA", // Intermedia
  "ORDPLUGINS", // Intermedia
  "ORDSYS", // Intermedia
  "OUTLN", // Outlines (Plan Stability)
  "SI_INFORMTN_SCHEMA", // SQL/MM Still Image
  "SPATIAL_CSW_ADMIN", // Spatial Catalog Services for Web
  "SPATIAL_CSW_ADMIN_USR",
  "SPATIAL_WFS_ADMIN", // Spatial Web Feature Service
  "SPATIAL_WFS_ADMIN_USR",
  "SYS",
  "SYSBACKUP",
  "SYSDG",
  "SYSKM",
  "SYSMAN", // Adminstrator OEM
  "SYSTEM",
  "TSMSYS", // Transparent Session Migration
  "WKPROXY", // Ultrasearch
  "WKSYS", // Ultrasearch
  "WK_TEST",
  "WMSYS", // Workspace Manager
  "XDB", // XML DB
  "XS$NULL",
  "XTISYS", // Time Index

Special schemas:

  "AURORA$JIS$UTILITY$", // JSERV
  "AURORA$ORB$UNAUTHENTICATED", // JSERV
  "DSSYS", // Dynamic Services Secured Web Service
  "OSE$HTTP$ADMIN", // JSERV
  "PERFSTAT", // STATSPACK
  "REPADMIN",
  "TRACESVR" // Trace server for OEM

Excluded tables (the * wildcard indicates any schema or any character):

  "*.AQ$*", // advanced queues
  "*.DR$*$*", // oracle text
  "*.M*_*$$", // Spatial index
  "*.MLOG$*", // materialized views
  "*.OGGQT$*",
  "*.OGG$*", // AQ OGG queue table
  "*.ET$*", // Data Pump external tables
  "*.RUPD$*", // materialized views
  "*.SYS_C*", // constraints
  "*.MDR*_*$", // Spatial Sequence and Table
  "*.SYS_IMPORT_TABLE*",
  "*.CMP*$*", // space management, rdbms >= 12.1
  "*.DBMS_TABCOMP_TEMP_*", // space management, rdbms < 12.1
  "*.MDXT_*$*" // Spatial extended statistics tables

Other Non-supported DDL

Oracle GoldenGate does not support the following:

  • DDL on nested tables.

  • DDL on identity columns.

  • ALTER DATABASE and ALTER SYSTEM (these are not considered to be DDL) Using dictionary, you can replicate ALTER DATABASE DEFAULT EDITION and ALTER PLUGGABLE DATABASE DEFAULT EDITION. All other ALTER [PLUGABLE] DATABASE commands are ignored.

  • DDL on a standby database.

  • Database link DDL.

  • DDL that creates tables with the FLASHBACK ARCHIVE clause and DDL that creates, alters, or deletes the flashback data archive itself. DML on tables with FLASHBACK ARCHIVE is supported.

  • Some DDL will generate system generated object names. The names of system generated objects may not always be the same between two different databases. So, DDL operations on objects with system generated names should only be done if the name is exactly the same on the target.