Forum Stats

  • 3,853,568 Users
  • 2,264,240 Discussions
  • 7,905,393 Comments

Discussions

How to build logmnr

user10126973
user10126973 Member Posts: 187 Blue Ribbon

HI All,

I'm trying to execute the following package from ggsci command prompt by using sqlexce

but it's giving an error, Please advise how to schedule once daily.

DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

please share your thoughts on how to implement from golden gate extract parameter.

Regards,

Best Answer

  • K.Gan
    K.Gan Member Posts: 2,815 Bronze Crown
    Answer ✓

    I take it that you can run this package from sqlplus ? If you can't resolve that first. If you can then let me know what errors you are getting. You might need to grant the gg user priv to run this.

    The syntax for standalone sqlexec is just this

    SQLEXEC 'call my_stored_proc()' every 1 day

    Try embedding the DBMS call in a stored procedure. As said I don't think doing this in extract is a great idea so I have never done it.

Answers

  • K.Gan
    K.Gan Member Posts: 2,815 Bronze Crown

    SQLEXEC is not a ggsci command. Do this from sqlplus.

  • user10126973
    user10126973 Member Posts: 187 Blue Ribbon

    Hi Gan,

    thanks for the info, it there any way I can call from the extract by using sqlexce parameter?

    example :

    EXTRACT es1e203

    SETENV (ORACLE_SID=S1E2)

    SETENV (ORACLE_HOME=/d01/app/oracleSE/product/11.2.0/dbhome_1)

    SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

    USERID ggs_admin, PASSWORD oracle

    SQLEXEC "CALL send_notify()" EVERY 1 DAYS ---- How to call here this one DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

    Regards,

    DR

  • K.Gan
    K.Gan Member Posts: 2,815 Bronze Crown

    Yes you can provided the gg user this extract is using has grants for this package. I would schedule this via cron or control-M or whatever batch system you have. If you put this in extract it is going to run this every time you need to restart extract. It is also going to run this every day at the time extract starts which can be a bad time. Building a dictionary is an expensive activity to the DB. So you want to schedule this at a quiet time.

  • user10126973
    user10126973 Member Posts: 187 Blue Ribbon

    Hi Gan,

    Thanks for all your help timely, I just won't try to extract this DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS); , Could you please share extracts syntax for this?

    I have tried but no luck for me.

    DR

  • K.Gan
    K.Gan Member Posts: 2,815 Bronze Crown
    Answer ✓

    I take it that you can run this package from sqlplus ? If you can't resolve that first. If you can then let me know what errors you are getting. You might need to grant the gg user priv to run this.

    The syntax for standalone sqlexec is just this

    SQLEXEC 'call my_stored_proc()' every 1 day

    Try embedding the DBMS call in a stored procedure. As said I don't think doing this in extract is a great idea so I have never done it.

  • user10126973
    user10126973 Member Posts: 187 Blue Ribbon

    thank you very much,