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
