How to Backup Oracle RAC 11gR2 Database with RMAN Backup Utility with the help of DBMS_SCHEDULER – Part-II – RMAN Incremental Database Backup

In my previous article, I have covered step by step guide to configure full database backup of Oracle RAC 11gR2 database with RMAN Backup Utility with the help of DBMS_SCHEDULER.

Previously, We have discussed WHY to choose DBMS_SCHEDULER over traditional RMAN backups configuration with CRON jobs.

Lets consider following step by step guide:

Script for the RMAN Incremental database backup:
In my case: I am calling “daily_incrmental.rcv” RMAN script through “daily_incrmental.sh” shell executable file after setting Oracle environment variables.

Note: Same script has been copied to all possible Oracle Cluster Node with same name & path.

[oracle@database1 Scripts]$ cat daily_incrmental.sh
#!/bin/bash
#Created 17 June 2014
#Author : Jignesh Jethwa
#Version 1.0
#Set Environment Variable
export ORACLE_BASE=/u01/app/oracle;
export ORACLE_SID=kucb1
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1;
export PATH=$PATH:$ORACLE_HOME/bin
rman target / @/home/oracle/RMAN/Scripts/daily_incrmental.rcv
exit

RMAN Incremental database backup script: ( .rcv )

[oracle@database1 Scripts]$ cat daily_incrmental.rcv
#Script to run Daily Incremental Backup...
#Created 17 June 2014
#Author : Jignesh Jethwa
#Version 1.0
run
{
backup as compressed backupset incremental level 1 database plus archivelog;
delete archivelog until time 'sysdate - 1' backed up 1 times to device type disk;
#Delete Obsolte Backups
delete noprompt force obsolete;
}

Now its time to create DBMS_SCHEDULER job for RMAN Incremental backup, Here in this procedure I am going to create “RMAN_INC_BACKUP” job with required attributes.

begin
dbms_scheduler.create_job(
job_name => 'RMAN_INC_BACKUP',
job_type => 'EXECUTABLE',
job_action => '/bin/sh',
number_of_arguments => 1,
start_date => SYSTIMESTAMP,
credential_name => 'Oracle',
auto_drop => FALSE,
enabled => FALSE);
end;
/
PL/SQL procedure successfully completed.

 

Set argument_position & argument_value ( i.e. Path of the RMAN Incremental backup script ) for the same job:
begin
dbms_scheduler.set_job_argument_value(
job_name => 'RMAN_INC_BACKUP',
argument_position => 1,
argument_value => '/home/oracle/RMAN/Scripts/daily_incrmental.sh');
end;
/
PL/SQL procedure successfully completed.

 

Set start_date for the same job, In my case “RMAN_INC_BACKUP” job will execute daily instead of sunday @11pm, so job start date and its first run timing would according to my convenience.
begin
dbms_scheduler.set_attribute(
name => 'RMAN_INC_BACKUP',
attribute => 'start_date',
value => trunc(sysdate)+23/24);
end;
/
PL/SQL procedure successfully completed.

 

Test your backup job manually in SQL prompt by instantiating “RMAN_INC_BACKUP” job.

begin
dbms_scheduler.run_job('RMAN_INC_BACKUP');
end;
/
PL/SQL procedure successfully completed.

 

Verify running RMAN backup status by issuing following SQL query, It will shows you RMAN backup details with start time & end time.

select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

 

In case of any error while test run, you can ensure details of error by issuing the following query, OR You can also query to “dba_scheduler_job_run_details” dictionary view for more details.

select JOB_NAME,STATUS,STATE,ERROR#,CREDENTIAL_NAME from dba_scheduler_job_run_details where CREDENTIAL_NAME like 'RMAN%';

After successfully completion of test run, Enable & schedule it by following procedure by setting value to ‘repeat_interval’ parameter, In my case “RMAN_INC_BACKUP” job will execute daily instead of Sunday @11pm.

begin
dbms_scheduler.set_attribute(
name => 'RMAN_INC_BACKUP',
attribute => 'repeat_interval',
value => 'freq=daily;byday=mon,tue,wed,thu,fri,sat;byhour=23');
dbms_scheduler.enable( 'RMAN_INC_BACKUP' );
end;
/
PL/SQL procedure successfully completed.

 

Ensure dbms_scheduler job details by issuing the following query OR you can also query to “dba_scheduler_jobs”.
SQL> select job_name,enabled,owner, state from dba_scheduler_jobs where job_name in ('RMAN_INC_BACKUP');

Keep your eye on behavior of dbms_scheduler job by issuing the following query:
SQL> select job_name,RUN_COUNT,LAST_START_DATE,NEXT_RUN_DATE from dba_scheduler_jobs where job_name in ('RMAN_INC_BACKUP');

Cheers!! We have successfully configured Incremental database backup of Oracle RAC 11gR2 database with RMAN Backup Utility with the help of DBMS_SCHEDULER.

In my earlier article, I have covered step by step guide to configure full database backup of Oracle RAC 11gR2 database with RMAN Backup Utility with the help of DBMS_SCHEDULER.

In my next articles, I will cover, How to configure Oracle RAC Transactional log/Archive database backup with RMAN with the help of DBMS_SCHEDULER. And Delete Obsolete backup of Oracle RAC 11gR2 database with RMAN Backup Utility with the help of DBMS_SCHEDULER.

Stay tune. 🙂

One thought on “How to Backup Oracle RAC 11gR2 Database with RMAN Backup Utility with the help of DBMS_SCHEDULER – Part-II – RMAN Incremental Database Backup”

Leave a Reply