Oracle daily RMAN full backup with DBMS_SCHEDULER
Daily full RMAN backup of an Oracle database, run from a shell script and scheduled with DBMS_SCHEDULER.
A daily full RMAN backup of an Oracle database can be run from a shell script and scheduled inside the database with DBMS_SCHEDULER, so no operating system cron entry is needed.
Prerequisites
- The database runs in ARCHIVELOG mode with archiving enabled.
- The backup destination has enough free space. If the local system does not have enough space, map a folder from another system and use it as the backup destination. This is usually handled by the system or network administrator.
Steps
1. Create the RMAN backup script
Save the script with a .sh extension.
#!/bin/bash
export ORACLE_SID=ORCL
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
$ORACLE_HOME/bin/rman target /<<EOF
run {
backup database;
}
delete NOPROMPT obsolete;
EOF
exit 0
2. Make the script executable
Give the script execute permission (chmod +x).
3. Create the schedule, the program and the job
Run the following in SQL*Plus, as a user allowed to manage the scheduler.
begin
dbms_scheduler.create_schedule(
schedule_name => 'schedulerman',
repeat_interval => 'FREQ=DAILY; INTERVAL=1; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=15; BYMINUTE=45; BYSECOND=10',
comments => 'schedule to run daily at 3.45pm');
dbms_scheduler.create_program(
program_name => 'programrman',
program_type => 'EXECUTABLE',
program_action => '/scripts/rmanscript.sh',
enabled => TRUE,
comments => 'Backup of db using rman.');
dbms_scheduler.create_job(
job_name => 'daily_backup',
program_name => 'programrman',
schedule_name => 'schedulerman',
enabled => true,
comments => 'database daily backup at 3.45pm through rman.');
end;
/
Notes
- The environment variables (
ORACLE_SID,ORACLE_HOME,PATH) depend on the server. Check that the script runs correctly by hand before it is scheduled. - The script path and the Oracle home are examples: replace them with the values of the target environment.