Showing posts with label Scheduler. Show all posts
Showing posts with label Scheduler. Show all posts

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



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