Loading....

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.

  • CREATE USER
  • CREATE PROCEDURE
  • CREATE TABLESPACE
  • 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

    Please follow and like us:

    Last Update: May 4, 2017  

    July 14, 2010 163 Gavin Soorma
    Total 0 Votes:
    0

    Tell us how can we improve this post?

    + = Verify Human or Spambot ?

    Add A Knowledge Base Question !

    You will receive an email when your question will be answered.

    + = Verify Human or Spambot ?

    , ,

    4 thoughts on “GoldenGate DDL Synchronization – some more examples

    1. Ravikumar

      Gavin,

      DDL INCLUDE ALL parameter is optional in replicat parameter file. The DDL is enabled on replicat by default. But is a mandatory for extract.

      Thanks,
      Ravi

    2. Gavin Soorma

      Yes Ravi – you are right but something to keep in mind for the Replicat process is that while DDL is enabled by default and we do not need the DDL INCLUDE ALL, we need to add the line “DDLERROR DEFAULT IGNORE RETRYOP” – otherwise the replicat process will abend when it faces some error in the DDL replication. For example a table was created on source, but the same tablespace did not exist on the target – so the replicat process will fail and unless we have the DDL error handler in the replicat parameter file it will abend. Regards Gavin

    3. satish

      Hi Gavin,

      I fallowed the steps provided by you and i am successful in doing the replication of create table, insert , update , delete on tables in to source to Target. But when i drop the table in the source , table is not getting dropped in the target.

      Both source and Target database are of 10g 10.2.0.5.0

      Can you please let me know what is the reason behind this.

      Thanks and Regards,
      Satish.G.S

    4. Balasaheb Kambale

      Hi Gavin,
      DDLERROR DEFAULT IGNORE RETRYOP :- what is mean by RETRYOP.

      If i alter table( Add column or drop column) from source database, will be reflect in target database also.

    Leave a Comment

    Your email address will not be published. Required fields are marked *

    *
    *

    Social media & sharing icons powered by UltimatelySocial
    Back To Top

    Add A Knowledge Base Question !

    You will receive an email when your question will be answered.

    + = Verify Human or Spambot ?