Forum Stats

  • 3,769,714 Users
  • 2,253,014 Discussions
  • 7,875,157 Comments

Discussions

OGG- Replication and privs

AATP
AATP Member Posts: 324 Blue Ribbon

Hi Experts ,.

We have ogg setup between two oracle databases with only DML uni directional replication . Its working fine .

We could see some truncate operation is happening in database through with 2 to 3 tables . We need to replicate only that truncate along with DML opreration . We are trying to add GETTRUNCATE parameter to replicate this transactions, But here the OGG user doesnt have the permission in target to replicate the truncate transaction , Since we have not enabled DDL and not granted DDL related privs due to some restriction and requirement . We have checked that , for truncate the OGG user should have drop any table permission But due to audit concern dba is not ready to grant that permission . Can you please suggest how to achieve this without drop any table permission is there any alternative way ? Please help .


Thanks

Arun .

Answers

  • ORASCN
    ORASCN Member Posts: 1,825 Gold Trophy

    Hi Arun,

    Could you please list out the privileges you have provided to the OGG user?

    Regards,

    Veera

  • AATP
    AATP Member Posts: 324 Blue Ribbon

    Hi Veera ,


    Thanks .

    We Have granted the DML privilleges to OGG user , INSERT, UPDATE, DELETE, Along with Sys table select access since its not required DDL replication .

    Also we have not executed the DDL enable script , We just need Truncate only as part of DDL using get truncate . Drop any table is not allowing to grant . So would like to know any other alternative way can grant truncate and do the replication

  • ORASCN
    ORASCN Member Posts: 1,825 Gold Trophy

    Leave the privileges for DDL. Check if other privileges are granted to the GG admin user. Refer the below link,



    TRUNCATE is not a DDL replication. So, you don't need any privileges related to DDL replication for the GGADMIN user.

    It should definitely work.


    Regards,

    Veera

  • AATP
    AATP Member Posts: 324 Blue Ribbon

    Hi Veera ,

    Thanks .

    Have executed dbms_goldengate_auth.grant_admin_privilege for the GGADMIN user .

    But the below mentioned permission is not given due to restriction of process .

    NOT granted the below to OGG user as not allowed .

    SELECT ANY TRANSACTION

    LOCK ANY TABLE

    DBA

    ALTER SYSTEM

    ALTER ANY TABLE

    Just added GETTRUNCATES parameter in exptract and replicat , extract captured the truncate operation from source by ogg user but when replicate in to target table its getting failed with ORA-01031 insufficient privileges SQL TRUNCATE TABLE "SRC"."ACCIN" aborting transaction on dirdat/ex ...

    -----------

    *Is there any alter native way to achieve this , ? Can the below will work


    1. create a procedure, in the schema that owns the table, that has:

    execute immediate 'truncate table table_name'

    2.and grant execute on the procedure to the OGG user.

    Then, when the truncate operation comes from source will this proc can invoke by replicate process and can execute the procedure. ? Any option can implement in mapping like on insert , on update , filter , sqlexec ? Please suggest


    Thanks

    Arun .