home

dbms_scheduler

scheduler wiki

permissiongs:

-rwsr-x--- 1 root dba 29078 Feb 9 2009 /oracle/product/11.1/bin/jssu
-rwsr-x--- 1 root dba 1135036 Feb 9 2009 /oracle/product/11.1/bin/extjob
-rw-r----- 1 root dba 1570 Oct 7 13:35 /oracle/product/11.1/rdbms/admin/externaljob.ora

-rwsr-s--x 1 oracle dba 196023222 Feb 10 14:59 2009 Feb 10 03:41 2009 oracle

$ORACLE_HOME/bin/extjob must be the setuid root   (permissions 4750 or -rwsr-x---)

$ORACLE_HOME/bin/jssu must be the setuid root   (permissions 4750 or -rwsr-x---)
11g
$ORACLE_HOME/bin/jssu is owned by root and the Oracle install group with
   the setuid root (permissions 4750 or   -rwsr-x---).

cd $ORACLE_HOME/bin
chmod 6750 oracle

It should now look like this:
-rwsr-s--x 1 oracle dba 196023222 Feb 10 14:59 2009 Feb 10 03:41 2009 oracle

LOGS:

External jobs with credentials write stdout and stderr to log files. Local external jobs
write to log files in the directory ORACLE_HOME/scheduler/log

 

USER_SCHEDULER_JOBS
USER_SCHEDULER_JOB_LOG
USER_SCHEDULER_PROGRAMS
USER_SCHEDULER_RUNNING_JOBS
USER_SCHEDULER_CREDENTIALS
USER_SCHEDULER_JOB_RUN_DETAILS
USER_SCHEDULER_CHAIN_RULES
USER_SCHEDULER_CHAIN_STEPS
USER_SCHEDULER_RUNNING_JOBS
USER_SCHEDULER_SCHEDULES
$ORACLE_HOME/bin/extjob

 

select * from USER_SCHEDULER_CREDENTIALS

select owner, credential_name from dba_scheduler_credentials
where credential_name='ORACLE_CREDS';

select owner, job_name, credential_name from dba_scheduler_jobs
where job_name='TEST_JOB';

 

 
select owner,job_name,job_type,job_action,state
from dba_scheduler_jobs
where OWNER LIKE 'XXMO'
 
SELECT job_name, job_class, operation, status
FROM USER_SCHEDULER_JOB_LOG; 
SELECT to_char(log_date, 'DD-MON-YY HH24:MM:SS') TIMESTAMP
,job_name,job_class
,operation, status
FROM USER_SCHEDULER_JOB_LOG   
WHERE job_name = 'JOB2' ORDER BY log_date; 
SELECT to_char(log_date, 'DD-MON-YY HH24:MM:SS') TIMESTAMP
,job_name, status,SUBSTR(additional_info, 1, 40) ADDITIONAL_INFO     
FROM user_scheduler_job_run_details ORDER BY log_date;

 

Create Credentials for an external job
BEGIN
DBMS_SCHEDULER.CREATE_CREDENTIAL('HRcredential', 'oracle', 'password');
END;
BEGIN
DBMS_SCHEDULER.DROP_CREDENTIAL ('HRcredential');
END;
select * from USER_SCHEDULER_CREDENTIALS

 

Create External Job

BEGIN DBMS_SCHEDULER.CREATE_JOB(
job_name => 'myjob',
job_type => 'EXECUTABLE',
job_action => '/u02/oracle/cleanlogs',
enabled => FALSE);

DBMS_SCHEDULER.SET_ATTRIBUTE('myjob','credential_name', 'HRcredential');
DBMS_SCHEDULER.ENABLE('myjob');
END;

 
 

 

BEGIN  DBMS_SCHEDULER.CREATE_JOB (     
job_name           =>  'update_sales'
,     job_type           =>  'STORED_PROCEDURE'
,     job_action         =>  'OPS.SALES_PKG.UPDATE_SALES_SUMMARY'
,     start_date         =>  '28-APR-08 07.00.00 PM Australia/Sydney'
,     repeat_interval    =>  'FREQ=DAILY;INTERVAL=2', /* every other day */  
end_date           =>  '20-NOV-08 07.00.00 PM Australia/Sydney'
,     job_class          =>  'batch_update_jobs'
,     comments           =>  'My new job');  
END; 
begin
DBMS_SCHEDULER.ENABLE('CLEANLOGS');
end;

/
 

 

 
BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE ( 'mytestjob', 'logging_level', DBMS_SCHEDULER.LOGGING_FULL); END;
 

 

Dropping Jobs

You drop one or more jobs using the DROP_JOB procedure or Enterprise Manager. DROP_JOB accepts a comma-delimited list of jobs and job classes. If a job class is supplied, all jobs in the job class are dropped, although the job class itself is not dropped.

For example, the following statement drops jobs job1 and job3, and all jobs in job classes jobclass1 and jobclass2:

BEGIN  
DBMS_SCHEDULER.DROP_JOB ('job1, job3, sys.jobclass1, sys.jobclass2');  
END;  
/  




Stopping Jobs

You stop one or more running jobs using the STOP_JOB procedure or Enterprise Manager. STOP_JOB accepts a comma-delimited list of jobs and job classes. If a job class is supplied, all running jobs in the job class are stopped. For example, the following statement stops job job1 and all jobs in the job class dw_jobs.

BEGIN  
DBMS_SCHEDULER.STOP_JOB('job1, sys.dw_jobs');  
END;  
/

External Jobs

Example 27-2 Creating a Local External Job


BEGIN  DBMS_SCHEDULER.CREATE_JOB(     
job_name             => 'CLEANLOGS',     
job_type             => 'EXECUTABLE',     
job_action           => '/home/logowner/cleanlogs',     
repeat_interval      => 'FREQ=DAILY; BYHOUR=23',     
enabled              => FALSE);  

DBMS_SCHEDULER.SET_ATTRIBUTE('CLEANLOGS', 'credential_name', 'LOGOWNER');  
DBMS_SCHEDULER.ENABLE('CLEANLOGS');  
END;  
/







Step 3—Create the Job and Use a Detached Program




BEGIN    
DBMS_SCHEDULER.CREATE_PROGRAM(      
program_name   => 'sys.backup_program',      
program_type   => 'executable',      
program_action => '?/scripts/coldbackup.sh',      
enabled        =>  TRUE);      


DBMS_SCHEDULER.SET_ATTRIBUTE('sys.backup_program', 'detached', TRUE);      

DBMS_SCHEDULER.CREATE_JOB(      job_name        => 'sys.backup_job',      
program_name    => 'sys.backup_program',      
repeat_interval => 'FREQ=DAILY;BYHOUR=1;BYMINUTE=0');      

DBMS_SCHEDULER.ENABLE('sys.backup_job');  
END;  
/

] Root.sh was run

2] You checked the permissions and attributes of the extjob related files, and they look fine:

-rwsr-x--- 1 root dba 29078 Feb 9 2009 /oracle/product/11.1/bin/jssu
-rwsr-x--- 1 root dba 1135036 Feb 9 2009 /oracle/product/11.1/bin/extjob
-rw-r----- 1 root dba 1570 Oct 7 13:35 /oracle/product/11.1/rdbms/admin/externaljob.ora

-rwsr-s--x 1 oracle dba 196023222 Feb 10 14:59 2009 Feb 10 03:41 2009 oracle