Basic DDL Replication with Oracle GoldenGate - RheoData

With any type of replication configuration or replication tool, primary purpose is to move the data as transactions are committed between databases.  Any of the tools on the market are great for replicating data, but where replication starts to become interesting is when the metadata  for tables needs to be replicated. When something changes at the data definition layer occur, these changes have be shipped across the network.  Replication tools need to be able to handle the capture, shipping, and applying of an object’s data definition language (DDL).  With Oracle GoldenGate, improvements have occurred over the years to make replicating DDL easier.  Although, replicating DDLs have become easier there are items that need to be considered.  In this post, we’ll look at these common items.

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 (DML)

The components that support the replication of DDL and replication of transactional data changes (DML) are independent of each other.  Therefore, you can synchronize:

• Just DDL changes
• Just DML changes
• Both DML and DDL

This means that Oracle GoldenGate can perform both DML and DDL at the same time or independent of each other.  This provides flexibility to the overall architecture and allows the administrators the option to define what needs to be replicated and when.

Fetch-Related Inconsistencies

With everything being flexible and easy to replicate, there is a defined process to ensure that inconsistencies are minimized when DML and DDL are fetched.  For example, the following process will help prevent fetch-related inconsistencies while Oracle table columns are being modified:

1. Pause all DML on table (i.e. stop any process that is processing inserts, update, or deletes)
2. Wait for the Extract to finish capturing all remaining redo; wait for Replicat to finish processing all captured data in trail.
3. Execute the DDL on source; confirm DDL changes on target
4. Resume source DML on table

Enabling DDL Replication

DDL is useful in dynamic environments which change constantly.  By default, the status of DDL replication supports the following:

• On source (Extract), the Oracle GoldenGate DDL support is disabled by default.  Must be configured with the DDL parameter.
• On target (Replicat), DDL support is enabled by default, to maintain the integrity of transactional data that is replicated.

DDL Parameter (Extract/Replicat)

The DDL parameter can be used in both the Extract and Replicat parameter files.  By using DDL parameter in the Extract is will enable DDL capture.  It can be omitted from the Replicat parameter since DDL is enabled on the target side by default.

Sample Parameter Files

Extract:

extract EXT
useridalias SOURCE domain OracleGoldenGate
exttrail aa
ddl
sourcecatalog chip
table tstusr.random_lrg_;

Replicat:

replicat REP
useridalias PDBSOURCE domain OracleGoldenGate
ddl
map chip.tstusr.random_lrg_, target chip.tstusr1.random_lrg;

Hopefully, this quick post shows you how easy it is to get DDL enabled within Oracle GoldenGate.

Enjoy!!