How to Backup Oracle RAC 11gR2 Database with RMAN Backup Utility with the help of DBMS_SCHEDULER – Part-IV – RMAN Delete Obsolete Backup

In my previous articles, I have covered step by step guide to configure:

Above articles contains: WHY to choose DBMS_SCHEDULER over traditional RMAN backups configuration with CRON jobs, FULL, INCREMENTAL & TRANSACTIONAL Oracle RAC database backed up with DBMS_SCHEDULER.

Apart from RMAN backup configuration, House keeping job also required in RMAN after configuring FULL, INCREMENTAL & TRANSACTIONAL log RMAN backups.

In this article, I will cover what is “Delete Obsolete” command in RMAN, how it works and how to configure it with DBMS_SCHEDULER.

What is “Delete Obsolete” command & How it works:
Delete Obsolete command help to delete those archive log who are no longer needed for any kind of RMAN recovery. We can delete archive logs files that are obsolete ( Not needed for recovery ) according to default retention policy. In case any archive log file has been marked as obsolete by RMAN, than it will be removed from backup media ( Disk/Tape ), also removed entry from recovery catalog database, and marked as deleted in the control file.

Instead of theory, now we would jump into configuration part:

Script for Delete Obsolete backup:
In my case: I am calling “Delete_Obsolete.rcv” RMAN script through “Delete_Obsolete.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 Delete_Obsolete.sh
#!/bin/bash
#script to delete obsolete backup.
#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/Delete_Obsolete.rcv
exit

Delete obsolete archive log backup script: ( .rcv )
[oracle@database1 Scripts]$ cat Delete_Obsolete.rcv
#Delete Obsolte Backups

run
{
delete noprompt force obsolete;
}

Create DBMS_SCHEDULER job for RMAN Delete obsolete archive log backup, Here in this procedure I am going to create “RMAN_DELETE_OBSOLETE_BACKUP” job with required attributes.

begin
dbms_scheduler.create_job(
job_name            => 'RMAN_DELETE_OBSOLETE_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 Delete obsolete archive log backup script ) for the same job:

begin
dbms_scheduler.set_job_argument_value(
job_name            => 'RMAN_DELETE_OBSOLETE_BACKUP',
argument_position   =>  1,
argument_value      => '/home/oracle/RMAN/Scripts/Delete_Obsolete.sh');
end;
/
PL/SQL procedure successfully completed.

Set start_date for the same job, In my case “RMAN_DELETE_OBSOLETE_BACKUP” job will execute every day @ every 1 hour, so job start date and its first run timing would  according to my convenience.

begin
dbms_scheduler.set_attribute(
name      => 'RMAN_DELETE_OBSOLETE_BACKUP',
attribute => 'start_date',
value     => trunc(sysdate)+1/24);
end;
/
PL/SQL procedure successfully completed.

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

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

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_DELETE_OBSOLETE_BACKUP” job will execute every day @ every 1 hour.

begin
dbms_scheduler.set_attribute(
name      => 'RMAN_DELETE_OBSOLETE_BACKUP',
attribute => 'repeat_interval',
value     => 'freq=hourly;interval=1');
dbms_scheduler.enable( 'RMAN_DELETE_OBSOLETE_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” data dictionary view.

SQL> select job_name,enabled,owner, state from dba_scheduler_jobs where job_name in ('RMAN_DELETE_OBSOLETE_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_DELETE_OBSOLETE_BACKUP');

Cheers!! Finally we have successfully configured Delete Obsolete backup of Oracle RAC 11gR2 database with RMAN Backup Utility with the help of DBMS_SCHEDULER.

In my previous articles, I have covered step by step guide to configure:

Stay tune. 🙂

Leave a Reply