Details of Support for Objects and Operations in Oracle DDL

Learn about the Oracle objects and operations that Oracle GoldenGate supports for the capture and replication of DDL operations.

Supported Objects and Operations in Oracle DDL

DDL capture support is integrated into the database logmining server. You must set the database parameter compatibility to 11.2.0.4.0 or higher. 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;

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 Extract for the following Oracle Database objects:

  • functions

  • library

  • packages (specification and body)

  • procedure

  • synonyms

  • types (specification and body)

  • views

• From Oracle GoldenGate 21c onward, DDLs that are greater than 4 MB in size will be provided replication support.

• From Oracle GoldenGate 23ai onwards, SQL domains are supported.

• Oracle GoldenGate is capable of managing tables with 4000 columns if the row size is less than 4MB.

• 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 dictionary for use with NOUSERID and TRANLOGOPTIONS GETCTASDML. This means that Extract receives object metadata from the LogMiner dictionary without querying the dictionary objects. Oracle GoldenGate uses the dictionary automatically when the source database compatibility parameter is greater than or equal to 11.2.0.4.

  When using dictionary and trail format in the Oracle GoldenGate release 12.2.x, Extract 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 Extract. 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.

• Starting with Oracle database 21c, the following DDL is available to support blocking of DML/DDL changes that are not replicated by Oracle GoldenGate:

  ALTER DATABASE [ENABLE | DISABLE] goldengate blocking mode;

  When Oracle GoldenGate blocking mode is enabled, DMLs that use support_mode NONE in tables and execute unsupported Oracle PL/SQL statements will fail with the following error:

  ORA-26981: "operation was unsupported during Oracle GoldenGate blocking mode"

  For Oracle database 21c, the following features cause a table to have support_mode NONE in Oracle GoldenGate:

  • BFILE as an attribute of ADT column, or typed table

  • Table with no scalars

  • OLAP AW$ table

  • Sharded queue table

  • Sorted Hash Cluster Table

  • Primary key constraint on ADT attribute in relational table

  • Primary key/unique key constraint on long raw/varchar (over 4000 bytes)

  • V$DATABASE column, Goldengate_Blocking_Mode can be queried to determine the current blocking mode status.

• For DDL auto capture mode:

  • It is relevant only for DDL INCLUDE MAPPED because Extract captures DDLs based on TABLE and TABLEEXCLUDE parameter.

  • Only table-related DDLs can be auto-captured.

  • DDLs to enable auto capture at table level:

    CREATE/ALTER TABLE ... ENABLE LOGICAL REPLICATION ALLKEYS;

    or

    CREATE/ALTER TABLE ... ENABLE LOGICAL REPLICATION ALLOW NOVALIDATE KEYS;

  See How to Capture Supplemental Logging for Oracle GoldenGate in Oracle Database Utilities guide.

• The following operations are supported for partition related DDLs and partition maintenance operations

  • Drop partition:

    If a partition is recreated with the same name, then it will get a new object number. The internal caches are cleared to minimize space consumption when a drop partition DDL is processed.

  • Truncate partition:

    Partition name and object number stays the same. Base table object version stays the same.

  • Rename partition:

    The partition object number stays the same but gets a new name. The base table’s object version gets bumped. In memory name cache will get invalidated upon seeing this DDL and repopulated upon the next DML. The cache, which stores if a given partition object number is interesting or not will also need to be reevaluated as a the new partition name may switch from filtered to not filtered or vice versa.

  • Exchange partition:

    Exchanges data in a partition with that in a table or vice versa. The obj# of the partition being exchanged does not change. Dataobj# does change but is not used by Extract. The partition itself still belongs to the same table.

  • Merge partition:

    Merges one or more partitions into a new partition. The DDL creates the new partition and drops the partitions from which it was merged. In memory caches should be cleared to save space and the user should ensure proper filter rules for the newly created partition.

  • Split partition:

    The partition being split keeps its original name and object number and new partition is created for the split data. The user must ensure partition filter rules are correct for the newly created partition.

  • Coalesce partition:

    Reduces the number of partitions in a hash partitioned table. The specific partition that is coalesced is selected by the database, and is dropped after its contents have been redistributed. The remaining partitions keep their same name and object number. The internal caches should be cleared to minimize space consumption.

  • Modify partition:

    Modifies default and real attributes of partitions, apart from adding or dropping of values for list partitions. All modifications leave the partitions name and object number intact.

  • Move partition:

    Partition data is moved to a new tablespace. Partition name and number remain the same.

  • Redef table:

    dbms_redefinition can be used to partition a table through the use of an interim table. The partitions are created on the interim table and after the finish_redef operation, the tables swap names. The partitions created on the interim table keep their names and object numbers when the tables are swapped. The Extract filter cache, needs to be reevaluated upon finish_redef as the partitions now belong to the base table. The user must ensure proper filter rules.

  • Redef partition:

    When redefining a table, the partitions follow from the original table to the interim table. For example, consider the case where the original table has partitions, which live in the USER tablespace, and the interim table is created with no partitions and the table lives in the NEW tablespace. In this case, after the finish_redef operation, when the tables are swapped the partition still lives in the USER tablespace. Redef partition allows a partition to be moved to the interim table’s NEW tablespace. The partition retains its name and object number.

  • System generated partition names:

    When partitions are created automatically for hash partitions and operations such as split partition, the partition name is in the form of SYS_P sequence value. Similarly, subpartitions are of the form SYS_SUBP sequence value. It is recommended that the partition is renamed before excepting DML to conform to filter rules.

Non-supported Objects and Operations in Oracle DDL

Here’s a list of non-supported objects and operations in Oracle DDL.

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*",
"*.SYS_EXPORT_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.