dbms-notes: writing blocks to disk: Scheduler

Here are the steps to create a daily detached job that performs a cold backup of an oracle 11g database.
This example is a bit more detailed than the one provided in the administration guide.

Steps:
(1) Create the shell script that invokes RMAN.

This script will be executed by the detached job.
The script should be executable by the user who has backup privileges in the OS. (usually oracle).

(2) create the RMAN script
(3) Write a PL/SQL block that performs the following steps:

3.1 create a credential object with username/password of the OS user who should have permission to run the shell script.
3.2 create a program object, which should use the credential just created to run the shell script.
3.3 alter the program object setting its attribute to detached.
3.4 create a job object

(1) Create the script that invokes RMAN

Create a shell script that calls an RMAN script to perform a cold backup.
The shell script is located in $ORACLE_BASE/scripts/coldbackup.sh.
It must be executable by the user who installed Oracle Database (typically the user oracle).

#!/bin/sh
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=orcl
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
$ORACLE_HOME/bin/rman TARGET / @$ORACLE_HOME/scripts/coldbackup.rman trace /u01/app/oracle/backup/coldbackup.out &
exit 0

(2)Create the RMAN Script

Create an RMAN script that performs the cold backup and then ends the job.
At the end, the script should call the Scheduler routine to inform the job completion status
The script is located in $ORACLE_BASE/scripts/coldbackup.rman.

run {
# Shut down database for backups and put into MOUNT mode
shutdown immediate
startup mount

# Perform full database backup
backup full format "/u01/app/oracle/backup/%d_FULL_%U" (database) ;

# Open database after backup
alter database open;

# Call notification routine to indicate job completed successfully
sql " BEGIN DBMS_SCHEDULER.END_DETACHED_JOB_RUN(''sys.backup_job'', 0, null); END; ";
}

(3) Create credentials, detached program and job with inline schedule
Submit the following PL/SQL block:

-- 3.1 create a credential for the job to use
-- 3.2 create the program object, specifying the shell script created earlier as the external executable. Specify it as detached program.
-- 3.3 create the job, specifying the credential object created earlier.
Begin 
 dbms_scheduler.create_credential('credrman', 'oracle', '');

 dbms_scheduler.create_program (
   program_name   => 'sys.backup_program',
   program_type   => 'executable',
   program_action => '/u01/app/oracle/scripts/coldbackup.sh',
   enabled        => TRUE,
   detached       => TRUE);
   
 dbms_scheduler.create_job (
   job_name     => 'sys.backup_job',
   program_name => 'sys.backup_program',
   repeat_interval => 'FREQ=DAILY; BYHOUR=13; BYMINUTE=20',
   credential_name => 'credrman');
   
 dbms_scheduler.enable('sys.backup_job');
end;

You can check the status of the created job with the query below:

SQL> select owner, job_name, job_style, program_name, schedule_type, 
       to_char(start_date, 'yyyy-mm-dd hh:mi:ss') start_date_, enabled, state, 
       run_count runs, failure_count failures, 
from dba_scheduler_jobs
where job_name = 'BACKUP_JOB';

JOB_NAME     JOB_STYLE   PROGRAM_NAME     SCHEDULE_TYPE  START_DATE_         ENABLED STATE     RUNS FAILURES
------------ ----------- ---------------- ------------- -------------------- ------- --------- ---- --------
BACKUP_JOB   REGULAR     BACKUP_PROGRAM   CALENDAR       2011-12-09 01:12:49 TRUE    SCHEDULED 0    0

Some troubelshooting:

If a credential object is not created and assigned to the job, it will fail.
The result will be an error entry in the alert file and a trace generated:

-//- alert.log  -//-
(...)
Fri Dec 09 14:25:51 2011
Errors in file /u01/app/oracle/diag/rdbms/orcl11r2/orcl11r2/trace/orcl11r2_j000_20165.trc:
ORA-12012: error on auto execute of job 12693
ORA-27369: job of type EXECUTABLE failed with exit code: 274670
(...)

If a credential object is not created, Oracle will try to run the external program using the credentials specified in the $ORACLE_HOME/rdbms/admin/externaljob.ora file.
The credentials specified in that file (in a unix/linux installation) are user:nobody, group:nobody.
The consequence is that the job will likely fail with the error 'ORA-27369: job of type EXECUTABLE failed with exit code: 274670.'
This somewhat criptical error message suggests (as discussed here) that the problem is due to OS permissions in the execution of the external program.

SQL> select status, error#, run_duration, credential_name
     from dba_scheduler_job_run_details 
     where job_name='BACKUP_JOB';
STATUS                         ERROR#                 RUN_DURATION CREDENTIAL_NAME
------------------------------ ---------------------- ------------ ------------------ 
FAILED                         274670                 0 0:0:2.0 
SUCCEEDED                      0                      0 0:7:26.0   CREDRMAN                                                                                                                                                                                           

SQL> select log_id, operation, status, credential_name 
     from dba_scheduler_job_log 
     where job_name='BACKUP_JOB';
LOG_ID                 OPERATION                      STATUS     CREDENTIAL_NAME                                                   
---------------------- ------------------------------ ---------- ------------------ 
192                    RUN                            FAILED
194                    RUN                            SUCCEEDED  CREDRMAN

Pages

Oracle Scheduler: Use credentials for a detached job that performs cold backups