In Oracle : Oracle RMAN Cumulative backup and restore

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Database Backup and Recovery

-> Oracle RMAN Cumulative backup and restore

 

 Oracle RMAN Cumulative backup and restore

 

 

Here are the main topics for this article:

1. Cumulative Backup Overview

2. Cumulative Restore Overview

3. Taking a full backup (supposing on Sunday at 1 am ) - level 0

4. Taking a cumulative backup (supposing on Monday at 1 am) - level 1

5. Taking a cumulative backup (supposing on Tuesday at 1 am) - level 1

6. Simulate a disk crash on Tuesday at 11 am

7. Restore & recover the lost file

1. Cumulative Backup Overview

 

Sometimes we need to backup the database changes only from the last backup (only the last changes are backed up). This is an incremental backup. There are 2 types of incremental backup: DIFFERENTIAL (by default) & CUMULATIVE. 

NOTE: The incremental backups are only for the DATA files.

 

CUMULATIVE backup =  which backs up all blocks changed after the most recent incremental backup at level 0. See the picture bellow.

Oracle RMAN Cumulative Backup

The following RMAN command is used to take a CUMULATIVE database backup: 

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

 

 

2. Cumulative Restore Overview

 

Because a CUMULATIVE backup is taken each day during the week, we need to restore 2 times: 1st we have to restore the full backup and after that the last cumulative backup (for a complete restore). If differential backups are taken we have to restore all the differential backups until the database crash. 

 

3. Taking a full backup (supposing on Sunday at 1 am) - level 0

 

A database backup is taken using the following RMAN script (which is stored in the  /home/oracle/Desktop/Backup_rman/scripts/hot_database_backup_disk.sh  file):

 


#/usr/bin/ksh
export ORACLE_HOME=/DB1
export ORACLE_SID=db1
RMAN_LOG_FILE=/home/oracle/Desktop/Backup_rman/log/hot_database_backup.`date +%y%m%d%H%M`.out

# -----------------------------------------------------------------
# Initialize the log file.
# -----------------------------------------------------------------

echo>> $RMAN_LOG_FILE
chmod 666 $RMAN_LOG_FILE

echo Script $0>> $RMAN_LOG_FILE
echo ==== started on `date` ====>> $RMAN_LOG_FILE
echo>> $RMAN_LOG_FILE

ORACLE_USER=oracle
TARGET_CONNECT_STR=sys/s

RMAN=$ORACLE_HOME/bin/rman
BACKUP_TYPE="INCREMENTAL LEVEL 0"

# ---------------------------------------------------------------------------
# Print out the value of the variables set by this script.
# ---------------------------------------------------------------------------
echo>> $RMAN_LOG_FILE
echo "RMAN: $RMAN">> $RMAN_LOG_FILE
echo "ORACLE_SID: $ORACLE_SID">> $RMAN_LOG_FILE
echo "ORACLE_USER: $ORACLE_USER">> $RMAN_LOG_FILE
echo "ORACLE_HOME: $ORACLE_HOME">> $RMAN_LOG_FILE
echo "BACKUP_TYPE: $BACKUP_TYPE">> $RMAN_LOG_FILE
# ---------------------------------------------------------------------------

echo >> $RMAN_LOG_FILE
CMD_STR=""

$RMAN target $TARGET_CONNECT_STR catalog rman/r@dbr << EOF
RUN {
ALLOCATE CHANNEL ch00 TYPE disk ;
ALLOCATE CHANNEL ch01 TYPE disk ;
ALLOCATE CHANNEL ch02 TYPE disk ;
ALLOCATE CHANNEL ch03 TYPE disk ;
BACKUP
$BACKUP_TYPE
SKIP INACCESSIBLE
TAG hot_db_bk_level0
FILESPERSET 5
# recommended format
FORMAT '/home/oracle/Desktop/Backup_rman/backup/bk_%s_%p_%t'
DATABASE;
sql 'alter system archive log current';
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
RELEASE CHANNEL ch03;
# backup all archive logs
ALLOCATE CHANNEL ch00 TYPE disk;
ALLOCATE CHANNEL ch01 TYPE disk;

BACKUP
SKIP INACCESSIBLE
filesperset 20
FORMAT '/home/oracle/Desktop/Backup_rman/backup/al_%s_%p_%t'
ARCHIVELOG ALL DELETE INPUT;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
ALLOCATE CHANNEL ch00 TYPE disk;
BACKUP
# recommended format
FORMAT '/home/oracle/Desktop/Backup_rman/backup/cntrl_%s_%p_%t'
CURRENT CONTROLFILE;
RELEASE CHANNEL ch00;
}
EOF

NOTE: The database must be in archivelog mode. 

 

 

4. Taking a cumulative backup (supposing on Monday at 1 am) - level 1

 

A cumulative backup is taken using the following script

 

[oracle@PROD scripts]$ more cumulative_database_backup_disk.sh
#/usr/bin/ksh
ORACLE_HOME=/DB1
export ORACLE_HOME
ORACLE_SID=db1
export ORACLE_SID
RMAN_LOG_FILE=/home/oracle/Desktop/Backup_rman/log/cumulative_day_level1.`date +%y%m%d%H%M`.out

echo >> $RMAN_LOG_FILE
chmod 666 $RMAN_LOG_FILE

echo Script $0 >> $RMAN_LOG_FILE
echo ==== started on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE

ORACLE_USER=oracle

TARGET_CONNECT_STR=sys/s

RMAN=$ORACLE_HOME/bin/rman
BACKUP_TYPE="INCREMENTAL LEVEL 1 CUMULATIVE"

echo >> $RMAN_LOG_FILE
echo "RMAN: $RMAN" >> $RMAN_LOG_FILE
echo "ORACLE_SID: $ORACLE_SID" >> $RMAN_LOG_FILE
echo "ORACLE_USER: $ORACLE_USER" >> $RMAN_LOG_FILE
echo "ORACLE_HOME: $ORACLE_HOME" >> $RMAN_LOG_FILE
echo "BACKUP_TYPE: $BACKUP_TYPE" >> $RMAN_LOG_FILE

echo >> $RMAN_LOG_FILE
CMD_STR=""

$RMAN target $TARGET_CONNECT_STR catalog rman/r@dbr << EOF
RUN {
ALLOCATE CHANNEL ch00 TYPE disk ;

BACKUP
$BACKUP_TYPE
SKIP INACCESSIBLE
TAG day_bk_cumulative1
FILESPERSET 5
# recommended format
FORMAT '/home/oracle/Desktop/Backup_rman/backup/cumulative_bk_%s_%p_%t'
DATABASE;
RELEASE CHANNEL ch00;

ALLOCATE CHANNEL ch00 TYPE disk;
BACKUP
# recommended format
FORMAT '/home/oracle/Desktop/Backup_rman/backup/cntrl_%s_%p_%t_inc'
CURRENT CONTROLFILE;
RELEASE CHANNEL ch00;
}
EOF

 

5. Taking a cumulative backup (supposing on Tuesday at 1 am) - level 1

 

The same script (from 4. )  is used for another cumulative backup on Tuesday at 1 am.

 

 

6. Simulate a disk crash on Tuesday at noon

 

Now delete a datafile, /DB1/oradata/db1/users01.dbf for instance, SHUTDOWN the database using ABORT option and restart the database.

 

The following message will appear:

 

Oracle RMAN Cumulative Backup

 

 

7. Restore & Recover the lost file

 

a) connect to the RMAN:  

rman catalog rman/r@dbr target /

 

b) restore the  /DB1/oradata/db1/users01.dbf from the full backup:  

RMAN> restore datafile ''/DB1/oradata/db1/users01.dbf';' 

 

Oracle RMAN Cumulative Backup

So, this command restores the file from the full backup.

 

c) apply the last changes from the cumulative backup:

RMAN> recover datafile ''/DB1/oradata/db1/users01.dbf';' from tag='DAY_BK_CUMULATIVE1';

 

Oracle RMAN Cumulative Backup

 

 

In-Oracle.com  -> Oracle DBA -> Oracle Database Backup and Recovery

-> Oracle RMAN Cumulative backup and restore

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2013  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.