This chapter describes how to use the DBMS_SCHEDULER package to administer Oracle Scheduler. Using the Oracle Scheduler Agent to Run Remote Jobs. Purging Logs Manually. REASON='Job slave process was terminated'. REASON='Job slave process was terminated' ORACLE 11GR2,rhel 5.
Hi Tom, I followed your guidelines above but I am still encountering a somewhat strange problem. Just to set the scene, this is what is required: A Solaris script named inc0backup.sh is to be run as an external program by DBMSSCHEDULER. This was accomplished using the following methodology: 1. This server is a test server and normally DBMSSCHEDULER is not enabled. To re-enable it I did the following: exec dbmsscheduler.setschedulerattribute('schedulerdisabled','false'); 2. Create a program for the external script: BEGIN DBMSSCHEDULER.CREATEPROGRAM ( programname = 'ODBINC0PROG', programtype = 'EXECUTABLE', programaction = '/u02/oracle/fastrecoveryarea/ORCL/scripts/inc0backup.sh', enabled = TRUE, comments = 'Full database backup' ); END; / 3. Configure a scheduler job: BEGIN dbmsscheduler.createjob( jobname = 'ODBINC0JOB', jobtype = 'executable', jobaction = '/u02/oracle/fastrecoveryarea/ORCL/scripts/inc0backup.sh', startdate = todate('2012-12-21 11:23:00','yyyy-mm-dd hh24:mi:ss'), repeatinterval = 'freq=daily;interval=1', enabled = TRUE ); END; / 4.
Followup January 02, 2013 - 12:43 pm UTC your shell script is likely failing. The environment in the shell script probably isn't anything remotely similar to what the environment is when it is run form the command line. What sort of debugging have you done?
For example, have you tried to just run a shell script that does something like: #!/bin/bash /bin/echo I worked /tmp/$$.dat and remember - never never never ever rely on the environment to be set. The first line of your script should probably be: export PATH=/bin/foo;/home/oracle/bin. Tom - I would like to execute this job ( jobtest) from another user ( let us say usr2) with minimal privilege. Usr2 is only responsible for running the job; he cannot / should alter job contents. What is the appropriate privilges to achieve this? Per the documentation, To give it a try, Initially, I tried giving privilegs as alter job ( though I dont want usr2 have alter privilege ) GRANT ALTER jobtest TO usr2 Error report: SQL Error: ORA-00990: missing or invalid privilege 00990. 00000 - 'missing or invalid privilege' Test Case: drop table ttest purge; create table ttest ( c char(2000) ); create or replace procedure ptest as begin insert into ttest values ( tochar(sysdate, 'CCYY-MM-DD HH:MI:SS')); end; JOBDEFINITION BEGIN SYS.DBMSSCHEDULER.CREATEJOB ( jobname = 'USR1'.'
Jobtest', jobtype = 'PLSQLBLOCK', jobaction = 'begin ptest; end;', numberofarguments = 0, startdate = NULL, repeatinterval = NULL, enddate = NULL, jobclass = 'SYS'.' DEFAULTJOBCLASS', enabled = FALSE, autodrop = FALSE, comments = ', credentialname = NULL, destinationname = NULL); SYS.DBMSSCHEDULER.SETATTRIBUTE( name = 'USR1'.' Jobtest', attribute = 'logginglevel', value = DBMSSCHEDULER.LOGGINGOFF); SYS.DBMSSCHEDULER.SETATTRIBUTE( name = 'USR1'.' Jobtest', attribute = 'maxrunduration', value = INTERVAL '1' HOUR); SYS.DBMSSCHEDULER.SETATTRIBUTE( name = 'USR1'.' Jobtest', attribute = 'schedulelimit', value = INTERVAL '1' HOUR); SYS.DBMSSCHEDULER.enable( name = 'USR1'.' Jobtest'); END; / thanks.
Followup January 04, 2013 - 3:54 pm UTC just have sql server jobs run a stored procedure (in for a penny, in for a pound here - if you want sql server to be the job coordinator/scheduler - just do the work in there) there would be nothing gained by having it be a job in Oracle if sql server is doing the scheduling and all - just run the procedure itself. Why are you trying to complicate things by having an outside job queue run a 'job' that isn't really a job in Oracle? (I'm asking honestly, I don't see the point?). I wasn't clear. There are few steps involved.
These steps needs to happen only on a specific days ( as defined in the SQL Server table ) a) Data processing happens in Oracle ( based on the oracle data elements - that does not exist in the SQL Server) b) Data is transferred to SQL Server for further processing. C) so on and so forth.
For a) - I have oracle job b) - I have a linked server setup to pull in the processed data in step a) c) I have bunch of stored procedures that runs on the data set retrieved in step 2. Are you suggesting to invoke Oracle process ( aka package ) from SQL Server Job?.
Please let me know. In addition to the above, if I run the stored procedure, I am getting the following error in SQL Server. Execute ( 'call tpkg.pt') AT ORADBLINK OLE DB provider 'OraOLEDB.Oracle' for linked server ORADBLINK returned message 'ORA-06576: not a valid function or procedure name'. Msg 7215, Level 17, State 1, Line 1 Could not execute statement on remote server 'ORADBLINK'. But, I can however the job ( if I wrap the procedure in the job ) Execute ( 'call dbmsscheduler.runjob('JOBTEST')') AT ORADBLINK.
Hi Tom, You always rock:-) I am running some processes parallely using DBMSSCHEDULER package. For Example: begin //Lets assume, we are Starting Jobs immediatly by setting startdate=SYSDATE;enabled=TRUE for i in 1.4 loop dbmsscheduler.createjob(.); end loop; //Implement the logic for waiting for jobs to continue //More processing. End; Could you please how can i implement a logic (without looping through the allschedulerjobs) to wait for all DBMSSCHEDULER to complete before i proceed to 'More Processing' section similar to JOIN to Java or Perl multi-threading. Is that DBMSALERT or DBMSLOCK packages can be utilized? Or Is that looping is most efficient option; Since code becomes very complex, Hence looking for an Oracle's Built IN funtionality(if available). Thanks in advance. Regards, Dillip.