-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|