It is very easy to create the RMAN backup jobs through DBMS_SCHEDULER.
Benefits of using DBMS_SCHEDLER for RMAN jobs:
- For multinode RAC configuration, executing the RMAN jobs through OS cronjobs will fail when the node is not available. While using DBMS_SCHEDULER , the job can be run from serviving instance.
- It is easy to maintain the logs at OS level as well as in the database dictionary.
- By default execution of the job can be fixed for specific instance using service and can be changed at any time.
Below is the example of creating RMAN backup job for 2 -node RAC.
Step 1: Create the OS level credential from SYS user.
SQL> exec dbms_scheduler.create_credential(credential_name => ‘oracle’,username => ‘oracle’, password => ‘xyz’);
Here username => ‘oracle’,password => ‘xyz’ are the OS level user through which we want to run the RMAN backup job.
Step 2: Create the JOB_CLASS that will use the service to run the RMAN job.
SQL> exec dbms_scheduler.create_job_class(job_class_name=> ‘RUN_RMAN_JOB’, service=> ‘rmansec’);
Here, rmansec is the service that was created with below specifications.
[oracle@rh-test-db-01 ~]$ srvctl add service -d testdb -s rmansec -r testdb2 -a testdb1
[oracle@rh-test-db-01 ~]$ srvctl config service -d testdb -s rmansec
Service name: rmansec
Service is enabled
Server pool: testdb_rmansec
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Preferred instances: testdb2
Available instances: testdb1
Step 3: Create database JOB for RMAN backup.
job_name => ‘RMAN_incr1_BACKUP’,
job_type => ‘EXECUTABLE’,
job_action => ‘/bin/ksh’,
NUMBER_OF_ARGUMENTS => 1,
start_date => SYSTIMESTAMP,
credential_name => ‘oracle’,
job_class => ‘RUN_RMAN_JOB’,
auto_drop => FALSE,
enabled => FALSE);
Note: -> Do not schedule the job at the time of creation. Please do not forget to mention the NUMBER_OF_ARGUMENTS. The number of arguments are the one which we need to pass as a parameter when job will run.I am having only .sh file name as a input so I have only 1 argument.
Step 4: Set the argument VALUE of the above created JOB.
job_name => ‘RMAN_INCR0_BACKUP’,
ARGUMENT_POSITION => 1,
argument_value => ‘/oracle/backup/rman_incr0_backup.sh’);
Note: Here the argument position is the script name which we want to run for RMAN backup. Give the absolute path of the RMAN script.
Step 5: Schedule the JOB for weekly backup.
name => ‘RMAN_INCR0_BACKUP’,
attribute => ‘repeat_interval’,
value => ‘freq=weekly; byday=wed,sun; byhour=5; byminute=0; bysecond=0;’);
dbms_scheduler.enable( ‘RMAN_INCR0_BACKUP’ );
Note: The job has been scheduled to run on every wednesday and sunday at 5 AM.
- You can purge the job run details as below :
— Only purge the log
- You can run the job manually as below :
— Only run the job
Below script have been deployed on both the nodes under same location i.e. /oracle/backup/rman_incr0_backup.sh
[oracle@rh-test-db-01 backup]$ cat rman_incr0_backup.sh
echo start >>/oracle/backup/RMAN/RMAN_incr0_BACKUP_SCRIPT.out
/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman target \/ log=$RMANLOG\/RMAN_incr0_BKP_LOG_$DTTS.out<<EOF
backup incremental level 0 database;
crosscheck archivelog all;
backup archivelog all delete input;
backup current controlfile;
echo end >>/oracle/backup/RMAN/RMAN_incr0_BACKUP_SCRIPT.out
=> You can query the job execution details as below:
set lines 180 pages 300
col RUN_DURATION for a20
col JOB_NAME for a20
col STATUS for a10
col ACTUAL_START_DATE for a35
col CPU_USED for a20
col LOG_DATE for a35
SELECT instance_id,job_name,log_date,actual_start_date, run_duration, cpu_used ,status FROM dba_scheduler_job_run_details where job_name like ‘RMAN_%’ order by 4 desc;
=> You can query the job details as below:
set lines 180 pages 300
col SCHEDULE_NAME for a20
col START_DATE for a40
col REPEAT_INTERVAL for a40
col OWNER for a5
col JOB_NAME for a10
col NEXT_RUN_DATE for a30
select OWNER , JOB_NAME , JOB_TYPE , START_DATE , REPEAT_INTERVAL, ENABLED , NEXT_RUN_DATE , INSTANCE_ID , job_class, instance_stickiness from DBA_SCHEDULER_JOBS where JOB_NAME like ‘RMAN_%’;