When executing sys.dbms_scheduler.create_event_schedule to create event schedule I hit the following error :
ERROR at line 1:
ORA-27375: valid agent name must be specified for secure queues
ORA-06512: at "SYS.DBMS_ISCHED", line 679
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1152
ORA-06512: at line 2
Found that we need to run dbms_scheduler.add_event_queue_subscriber();
But when I run dbms_scheduler.add_event_queue_subscriber() using the job owner ( not SYS ) I hit the following error :
ERROR at line 1:
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 2959
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2242
ORA-06512: at line 1
So I ran dbms_scheduler.add_event_queue_subscriber('{JOB_OWNER}'); using SYS instead and manage to create the schedule and create the job by executing sys.dbms_scheduler.create_job
but when I try to enable the job ( log on as SYS ) by running sys.dbms_scheduler.enable I once again hit the error
BEGIN
*
ERROR at line 1:
ORA-27375: valid agent name must be specified for secure queues
ORA-06512: at "SYS.DBMS_ISCHED", line 2751
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1794
ORA-06512: at line 2
Eventually I found out that the only way to subscribe to the event queue ( SYS.SCHEDULER$_EVENT_QUEUE ) is to run dbms_scheduler.add_event_queue_subscriber as the job owner.
You can verify whether the agent is correctly created or not by running the following
select * from SYS.SCHEDULER$_EVTQ_SUB;
AGT_NAME UNAME
------------------------------ ------------------------------
SYS SYS
ABCD ABCD
The AGT_NAME and UNAME should be same.
After some searching in order to solve the problem we need grant CREATE JOB to the owner so that the user can subscribe to the event queue by executing dbms_scheduler.add_event_queue_subscriber.
After running the dbms_scheduler.add_event_queue_subscriber using the job owner account, I was able to enable the job.
Problem solved.
oracle 10g dbms_scheduler explained
http://downloadwest.oracle.com/docs/cd/B13789_01/server.101/b10739/schedover.htm#sthref3230
http://www.oradev.com/dbms_scheduler.jsp
http://www.oraclebase.com/articles/10g/Scheduler10g.php#jobs
http://www.psoug.org/reference/dbms_scheduler.html
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment