0

How to replicate - Create User, Procedure and Tablespace using Oracle GoldenGate.

I am able to replicate DDL under schema, however above is not getting replicated i.e. when I create an user, procedure or tablespace.

GoldenGate Version - 12.2.0.1 Database Version - 11.2.0.4

Extract Conf:

GGSCI (sourceGG) 36> view params ext4

extract ext4
userid ogguser, password ogguser
exttrail /u01/app/oracle/product/11.2.0.4/oggsrc/dirdat/mt
DDL INCLUDE ALL
table ogguser.*;

GGSCI (sourceGG) 37>

Dump Conf:

GGSCI (sourceGG) 37> view params dpump2

extract dpump2
userid ogguser, password ogguser
rmthost targetGG, mgrport 7809
rmttrail /u01/app/oracle/product/11.2.0.4/oggtrg/dirdat/pt
passthru
DDL INCLUDE ALL
table ogguser.*;

GGSCI (sourceGG) 38> 

Replicat Conf:

GGSCI (targetGG) 37> view params rep4
replicat rep4
assumetargetdefs
handlecollisions
userid ogguser, password ogguser
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP
map ogguser.*, target ogguser.*;

GGSCI (targetGG) 38> 

Thanks!

  • I have also tried with scott.* too, no success. – Gaurav Bhaskar Dec 19 '16 at 8:01
0

DDL replication in Oracle Goldengate is disabled for the extract process by default and enabled for the replicat process.

To enable the DDL support, you need to specify the DDL parameter for the extract process.

DDL

Use the DDL parameter to:

  • enable DDL support
  • filter DDL operations
  • configure a processing action based on a DDL record

    Syntax

    DDL [
    {INCLUDE | EXCLUDE}
      [, MAPPED | UNMAPPED | OTHER | ALL]
      [, OPTYPE type]
      [, OBJTYPE 'type']
      [, SOURCECATALOG catalog | ALLCATALOGS]
      [, ALLOWEMPTYOBJECT]
      [, ALLOWEMPTYOWNER]
      [, OBJNAME name]
      [, INSTR 'string']
      [, INSTRWORDS 'word_list']
      [, INSTRCOMMENTS 'comment_string']
      [, INSTRCOMMENTSWORDS 'word_list']
      [, STAYMETADATA]
      [, EVENTACTIONS (action)
    ]
    [...]
    
  • In order to replicate CREATE USER, ALTER TABLESPACE or CREATE ROLE kind of statement the DDL scope should be OTHER or ALL to include all kind of DDLs.

    Some other examples of OTHER scope:

    CREATE USER joe IDENTIFIED by joe;
    CREATE ROLE ggs_gguser_role IDENTIFIED GLOBALLY;
    ALTER TABLESPACE gg_user TABLESPACE GROUP gg_grp_user;
    

    References:

  • Oracle GoldenGate - DDL Replication
  • DDL: Oracle Goldengate 12c
  • Understanding DDL Scopes

    • I have added my Extract, Data Pump and Replicat Configuration. Can you see is there any problem with this setup. Thanks! – Gaurav Bhaskar Dec 19 '16 at 7:59
    • Any help in this regard? – Gaurav Bhaskar Dec 19 '16 at 19:32
    • @GunnyKC: Does ALTER TABLE for tables belong to ogguser replicate? – JSapkota Dec 20 '16 at 9:35
    • ogguser is main schema here created for GoldenGate process and which has all admin privilege. I tried with this schema because only this user has the privilege of creating users and tablespace etc., – Gaurav Bhaskar Dec 20 '16 at 13:16

    Your Answer

    By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

    Not the answer you're looking for? Browse other questions tagged or ask your own question.