Results 1 to 7 of 7

Thread: jobs on standby

  1. #1
    Join Date
    Jul 2005
    Posts
    25

    Unanswered: jobs on standby

    Hi all!

    Is it possible to use jobs on Oracle logical standby?
    Oracle 10
    thanks

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Why not?
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jul 2005
    Posts
    25
    I've got an error

    VARIABLE jobno number;
    BEGIN
    2 DBMS_JOB.SUBMIT(:jobno,
    3 'dbms_stats.gather_table_stats(ownname => ''user1'', tabname => ''table1'',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);',
    4 SYSDATE+1/12/24,'TRUNC(SYSDATE,''DD'') + 29/24');
    5 COMMIT;
    6 END;
    7 /
    BEGIN
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at "SYS.DBMS_INTERNAL_LOGSTDBY", line 123
    ORA-06512: at "SYS.DBMS_JOB", line 122
    ORA-06512: at line 2

    I also have a user like 'user1' on usual db and everything works fine there

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Make sure the standby is not in recovery mode.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Jul 2005
    Posts
    25
    The standby is always in recovery mode because SQL Apply works in real-time apply mode

    Why does the recovery mode prohibit working with jobs?

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    True.

    Then check the privileges? ( ORA-01031: insufficient privileges )

    In logical standby sometimes you need to be SYS (as sysdba) to make changes ('dbms_stats.gather_table_stats' changes the statistics).



    PS: You could also try to disable/enable dataguard for the session:
    alter session {disable|enable} guard;
    Last edited by LKBrwn_DBA; 03-21-08 at 14:10.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Jul 2005
    Posts
    25
    As I know there are no special privileges for jobs in Oracle.

    I have all object privileges for 'table1' because I created that table (this table exists only on standby site)

    I ran 'dbms_stats.gather_table_stats' and had no problems.

    I have only problems when I try to submit a job

    Data Guard is in STANDBY mode.
    I’ve also tried to disable Data Guard for session but it didn’t help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •