Set up automatic #Oracle database back-up using #RMAN

By | September 19, 2017

There is a very easy method of setting up on Linux an automatic back-up for Oracle by using database procedures, RMAN and scripts.

STEP 1: Define a back-up script

Create in /home/oracle/backup.sh a script file with the content:

#!/bin/bash
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=prod
rman target / CMDFILE=/home/oracle/bin/backup.rman LOG /data/oradata/backuplogs/rman-`date -I`.log
exit 0

Where rman parameters are configured in the following /home/oracle/bin/backup.rman configuration file:

CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/data/flash_recovery_area/backups/autobackup_control_file%F';
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
run {ALLOCATE CHANNEL RMAN_BACK_CH01 TYPE DISK;
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT '/data/flash_recovery_area/backups/databasefiles_%d_%u_%s_%T';
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL FORMAT '/data/flash_recovery_area/backups/archivelogs_%d_%u_%s_%T' DELETE INPUT;
BACKUP AS COMPRESSED BACKUPSET CURRENT CONTROLFILE FORMAT '/data/flash_recovery_area/backups/controlfile_%d_%u_%s_%T';
CROSSCHECK BACKUP;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED BACKUP;
RELEASE CHANNEL RMAN_BACK_CH01;
}

STEP 2: Create an Oracle scheduled job to run the script at 01:00 a.m. each day

BEGIN  
 DBMS_SCHEDULER.CREATE_JOB (
     job_name           => 'backupdb',     
     job_type           => 'EXECUTABLE',     
     job_action         => '/home/oracle/bin/backup.sh',         
     repeat_interval    => 'FREQ=DAILY;BYHOUR=1;BYMINUTE=00',
     enabled            => TRUE  
);
END;
/

STEP 3: Create an automatic tape/external storage backup job

The only thing left to do is to leave enough time for the rman process to complete, then copy the backups from ”
/data/flash_recovery_area/backup” on an external location.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.