In my previous articles, 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.
- Step by step guide to configure INCREMENTAL database backup of Oracle RAC 11gR2 database with RMAN Backup Utility with the help of DBMS_SCHEDULER.
Above articles contains: WHY to choose DBMS_SCHEDULER over traditional RMAN backups configuration with CRON jobs, FULL Oracle RAC database backup & INCREMENTAL Oracle RAC database backed with DBMS_SCHEDULER.
Now we directly jump into configuration part of Transactional log/Archive backup.
Script for the RMAN Transactional log/Archive database backup:
In my case: I am calling “Transactional_log.rcv” RMAN script through “Transactional_log.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 Transactional_log.sh
#!/bin/bash
#Set Envirnoment 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/Transactional_log.rcv
exit
RMAN Transactional log/Archive database backup script: ( .rcv )
[oracle@database1 Scripts]$ cat Transactional_log.rcv
#script to backup transaction logs
#Created 17 June 2014
#Author : Jignesh Jethwa
#Version 1.0
run
{
backup as compressed backupset archivelog all;
#Delete Obsolte Backups
delete noprompt force obsolete;
}
Create DBMS_SCHEDULER job for RMAN Transactional log/Archive backup, Here in this procedure I am going to create “RMAN_TRANS_BACKUP” job with required attributes.
begin
dbms_scheduler.create_job(
job_name => 'RMAN_TRANS_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 Transactional log/Archive backup script ) for the same job:
begin
dbms_scheduler.set_job_argument_value(
job_name => 'RMAN_TRANS_BACKUP',
argument_position => 1,
argument_value => '/home/oracle/RMAN/Scripts/Transactional_log.sh');
end;
/
PL/SQL procedure successfully completed.
Set start_date for the same job, In my case “RMAN_TRANS_BACKUP” job will execute every day @ every 15 minute, so job start date and its first run timing would according to my convenience.
begin
dbms_scheduler.set_attribute(
name => 'RMAN_TRANS_BACKUP',
attribute => 'start_date',
value => trunc(sysdate)+16/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_TRANS_BACKUP');
end;
/
PL/SQL procedure successfully completed.
In case of any error while test run, you can make sure 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_TRANS_BACKUP” job will execute every day @ every 15 minute.
begin
dbms_scheduler.set_attribute(
name => 'RMAN_TRANS_BACKUP',
attribute => 'repeat_interval',
value => 'freq=minutely;interval=15');
dbms_scheduler.enable( 'RMAN_TRANS_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_TRANS_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_TRANS_BACKUP');
Cheers!! Finally we have successfully configured Transactional log/Archive database 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:
And Next article will be about:
Stay tune. 🙂
