Error on creating Scheduler job
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Error on creating Scheduler job

  1. #1
    Join Date
    Jan 2014
    Location
    Apple Valley, CA
    Posts
    2

    Error on creating Scheduler job

    I am trying to use Scheduler sniper sample that I found on the web that I am having problems creating the job.
    I log in as RMSADM that has been granted DBA privileges. On the call to dbms_scheduler.create_job I get error "ORA-27375: valid agent name must be specified for secure queues".
    Searching for help on this says you have to be subscribed and have appropriate privileges. It looks like I do and I can't figure out why I am getting this error.

    Any help would be greatly appreciated.

    I am using Oracle 11gR2.

    Thank You,
    Mark



    -- add an event queue subscriber for this user's messages
    begin
    dbms_scheduler.add_event_queue_subscriber;
    end;

    PL/SQL procedure successfully completed

    -- create sniper procedure
    create or replace procedure RMS_Scheduler_Sniper_Proc( message IN sys.scheduler$_event_info ) as
    begin
    -- if this is not a JOB_OVER_MAX_DUR message, error out
    if message.object_owner!='RMSADM' or message.event_type!='JOB_OVER_MAX_DUR' then
    raise PROGRAM_ERROR;
    end if;

    -- stop the job
    dbms_scheduler.stop_job( job_name =>'"'||message.object_owner||'"."'||message.object_name ||'"', force=>true );
    end;

    Procedure created

    -- create sniper program
    begin
    dbms_scheduler.create_program( program_name => 'RMS_Scheduler_Sniper_Prog', program_action => 'RMS_Scheduler_Sniper_Proc', program_type => 'stored_procedure', number_of_arguments => 1, enabled => FALSE);
    dbms_scheduler.define_metadata_argument ( 'RMS_Scheduler_Sniper_Prog','event_message',1);
    dbms_scheduler.enable('RMS_Scheduler_Sniper_Prog');
    end;

    PL/SQL procedure successfully completed

    grant create job to rmsadm

    Grant succeeded

    -- create a general purpose sniper job to kill any job that has exceeded its max_run_duration
    begin
    dbms_scheduler.create_job( job_name => 'RMS_Scheduler_Sniper_Job', program_name => 'RMS_Scheduler_Sniper_Prog', event_condition => 'tab.user_data.event_type = ''JOB_OVER_MAX_DUR''', queue_spec => 'sys.scheduler$_event_queue,myagent', enabled => true);
    end;

    ORA-27375: valid agent name must be specified for secure queues
    ORA-06512: at "SYS.DBMS_ISCHED", line 124
    ORA-06512: at "SYS.DBMS_SCHEDULER", line 457
    ORA-06512: at line 3





    select * from SYS.SCHEDULER$_EVTQ_SUB

    AGT_NAME UNAME
    ------------------------------ ------------------------------
    RMSADM RMSADM

    1 row selected

    select * from DBA_AQ_AGENTS

    AGENT_NAME HTTP_ENABLED SMTP_ENABLED
    ------------------------------ ------------ ------------
    SCHEDULER$_EVENT_AGENT NO NO
    SCHEDULER$_REMDB_AGENT NO NO
    SERVER_ALERT NO NO
    HAE_SUB NO NO
    NGWPC_RMS_DB_1830_J3RMS9P1 NO NO
    NGWPC_RMS_DB_1830_IJ3RMS9P1 NO NO
    RMSADM NO NO

    7 rows selected

    select * from DBA_AQ_AGENT_PRIVS

    AGENT_NAME DB_USERNAME HTTP_ENABLED SMTP_ENABLED
    ------------------------------ ------------------------------ ------------ ------------
    NGWPC_RMS_DB_1830_IJ3RMS9P1 SYS NO NO
    NGWPC_RMS_DB_1830_J3RMS9P1 SYS NO NO
    RMSADM RMSADM NO NO
    SCHEDULER$_EVENT_AGENT SYS NO NO
    SCHEDULER$_REMDB_AGENT SYS NO NO
    SERVER_ALERT SYS NO NO
    HAE_SUB NO NO

    7 rows selected

  2. #2
    Join Date
    Sep 2015
    Posts
    1
    I followed these same steps in 11g with no issue. When I followed these steps in 12c, I had to do the following to resolve the issue. The ultimate solution was to login as A010UT or SYSTEM[A010UT] rather than using the set current_schema.

    Our OP Sheet had us login as follows:
    connect / as sysdba
    alter session set current_schema = A010UT;

    After the same failure as the original post, the following query revealed that for some reason in 12c, the SCHEDULER_AGENT was not subscribed to by A010UT.

    select * from SYS.SCHEDULER$_EVTQ_SUB

    AGT_NAME
    --------------------------------------------------------------------------------
    UNAME
    --------------------------------------------------------------------------------
    SCHEDULER_AGENT
    SYS

    A010UT
    SYS

    So I removed both of those subscriptions as sysdba:
    exec dbms_scheduler.remove_event_queue_subscriber('scheduler_agent');
    exec dbms_scheduler.remove_event_queue_subscriber('A010UT');

    select * from SYS.SCHEDULER$_EVTQ_SUB;
    no rows selected

    Then I did the following:
    alter user A010UT grant connect through system
    connect SYSTEM[A010UT]

    exec dbms_scheduler.add_event_queue_subscriber('scheduler_agent');

    I confirmed the desire results:
    select * from SYS.SCHEDULER$_EVTQ_SUB;

    AGT_NAME
    --------------------------------------------------------------------------------
    UNAME
    --------------------------------------------------------------------------------
    SCHEDULER_AGENT
    A010UT


    Then I completed the final step of the instructions successfully.

    begin
    dbms_scheduler.create_job('sniper_job',
    program_name=>'sniper_prog',
    event_condition =>
    'tab.user_data.event_type in (''JOB_OVER_MAX_DUR'',''JOB_BROKEN'',''JOB_FAILED'')',
    queue_spec =>'sys.scheduler$_event_queue,scheduler_agent',
    enabled=>true);
    end;
    /

Posting Permissions

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


Click Here to Expand Forum to Full Width