Configuring and Using Options for DDL Replication

As we have seen in previous article Oracle Golden Gate handles DDL operations for objects.

Objects are divided into scope for more simplicity as it shows how DDL operations on an object are handled by Oracle Golden Gate.

There are mainly three scopes :

1. Mapped :

Objects which are specified in the Map and Target clause falls into this category.

Operations allowed in this scope are: CREATE, ALTER, DROP, RENAME, REVOKE, GRANT

Objects allowed in this scope are: Table, Sequence, Trigger, Index, Materialized View

2. UnMapped :

Objects which are not specified in the Map or Target clause falls into this category.

Operations and Objects in this scope are All objects and operations defined in Mapped scope.

3. Other :

Objects which are not specified in the Map or Target clause falls into this category.

Operations allowed in this scope are All operations other than listed in Mapped scope.

Example : Alter Tablespace , Create User.

Let us see the configuration of DDL replication to understand this into depth.

Syntax :

DDL
[
{Include | Exclude }
[, Mapped | UnMapped | Other | All ]
[, OpType <type> ]
[, ObjType '<type>' ]
[, ObjName "<name>" ]
[, InStr '<string>' ]
[, InStrComments '<comment_string>' ]
]
[....]

We can define the DDL parameter setting in replicat or extract parameter file.

Include and Exclude clause: It identifies the beginning of inclusion or exclusion. We can specify a particular DDL for inclusion and exclusion. This should be done by specifying Include or Exclude clause followed by DDL name. We can specify multiple Include | Exclude clause in DDL parameter.

Note: Exclude takes priority over Include if we have specified same criteria in Include and Exclude.

Mapped | UnMapped | Other | ALL: DDL operations are replicated based on this scope.

OpType <type>: If we want to apply DDL for the specific type of operations like Alter, Rename then we can specify that operation type in this clause.

ObjName <name>: We can specify specific object name for example table name for replication in this clause. Wildcards can be used to specify the object name. Qualify user for object name else Golden Gate understands default user as Golden Gate user.

InStr and InStrComment: String specified in the InStr clause is checked in DDL statement in Include or Exclude. The string specified in InStrComment is checked in DDL statement in ddl comment.

Example :

DDL  &
INCLUDE UNMAPPED &
    OBJTYPE 'table' &
    OBJNAME 'EMP*' &
INCLUDE MAPPED OBJNAME * &
EXCLUDE MAPPED OBJNAME "ec2"

In this example, we can see that I have Included DDL for all tables starting with EMP word. Included all DDL for all mapped objects and Excluded “ec2” object from Mapped scope

If you want to be updated with all our articles send us an Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp