GoldenGate DDL Synchronization – some more examples

In one of my earlier posts we had introduced the GoldenGate synchronization and we had discussed what is required to set this up.

Configuring DDL Synchronization

Let us now look at a few more examples of DDL replication or synchronization supported by GoldenGate. We will see how the following operations on the source are replicated to the target database and will also briefly discuss how to handle any errors which may occur in the DDL replication process.

This is the extract and replicat file we have used in this case

EXTRACT ext2
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST 192.168.10.194, MGRPORT 7809
RMTTRAIL /home/oracle/goldengate/dirdat/gg
DDL INCLUDE ALL
TABLE ggs_owner.myobjects;

REPLICAT rep2
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD ggs_owner
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP
MAP ggs_owner.myobjects, TARGET ggs_owner.myobjects ;

Let us see what happens when we do not include the DDLERROR clause in the replicat parameter file.

So if we issue this DROP USER statement on the source, we will see that the replicat process on the target has abended and this is the error we will see in the report

SQL> drop user abc;

User dropped.

2010-07-10 17:20:13 GGS ERROR 2001 Fatal error executing DDL replication: error [Error code [1031], ORA-01031: insufficient privileges, SQL drop user abc /* GOLDENGATE_DDL_REPLICATION */], no error handler present.

The following DDL statements were issued on source

SQL> create user NEWUSER identified by xyz;

User created.

SQL> CREATE OR REPLACE PROCEDURE testddl_proc
(p_date testddl.COL_A%TYPE)
AS
BEGIN
INSERT INTO testddl (COL_A)
VALUES (p_date);
END testddl_proc;
/
2 3 4 5 6 7

Procedure created.

SQL> create tablespace testtbs
2 datafile ‘/u02/oracle/filesdb/testtbs01.dbf’ size 100m;

Tablespace created.

Let us now check the target database and see if these DDL commands have been applied in that database.
We will see that all the three statements have been run on the target database.

Note – all these DDL commands will require certain system privileges to execute and these have been granted to the user GGS_OWNER – the same user included in the replicat parameter file.

SQL> select count(*) from dba_users where username=’NEWUSER’;

COUNT(*)
———-
1

SQL> desc testddl_proc

PROCEDURE testddl_proc
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
P_DATE DATE IN

SQL> select count(*) from dba_tablespaces where tablespace_name=’TESTTBS’;

COUNT(*)
———-
1