Oracle RMAN Backup

It’s been awhile not writing in this blog…more than a year :O Well, my old posting is all about Solaris, Linux, Sun Server, and Stuffs about System Administrations. Now I’m going to change the topics into Database Administration from now.

One of the most essential part of becoming a Database Administrator is able to do backup and restore, so that in a disaster event the data can be survive. My environment in this backup and recovery thing is using Oracle 10gR2, Solaris OS, SUN NAS.

The scenario is started by configuring the primary database to be able to create rman backup then store the backup into tape (using veritas backup). Then the backup data is restored into an NFS server that is hosted on SUN Storage 7110. Then the secondary database will mount the NFS server.

BACKUP

To start configuring how the backup will be done, connect to the rman console, which can be done by connect to the database server using Oracle Server and set the ORACLE_SID of the SID that is going to be backed up, by performing this command:

bash

export ORACLE_SID=DBTEST

Then type this to connect the rman console

rman target /

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;

This will make rman to keep the backup file for 30 days, then we type

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/BACKUP/PATH/%F’;

This will make rman to backup SPFILE and Control Files everytime a full database backup is performed. The second command will manage the destination folder of SPFILE and Control Files backup with default format %F (combination of DBID, Date, and Sequence number).

After this initialization then we can create script to backup the database and archivelog, there will be two script one is to be used as bash script that will be executed using crontab, and one sql script that do the backup and will be executed by bash script.

FULL_BACKUP.SH

#!/bin/sh
#full backup mode / incremental 0

USERNAME=”sys”
PASSWD=”password”

ORACLE_SID=APRDEV
export ORACLE_SID

ORACLE_HOME=”/u01/oracle/10g”
export ORACLE_HOME

CURRENT_DATE=”`date +%Y%m%d`”
export CURRENT_DATE

BACKUP_PATH=”/u01/backupDb/FULL_BACKUP”
export BACKUP_PATH

BACKUP_SCRIPT=”/u01/backupDb/FULL_BACKUP”
export BACKUP_SCRIPT

FULL_BACKUP_LOG_FILE_NAME=”FULL_DB_LOG_$CURRENT_DATE.log”

export FULL_BACKUP_LOG_FILE_NAME

${ORACLE_HOME}/bin/rman target=$USERNAME/$PASSWD cmdfile=${BACKUP_SCRIPT}/full_backup.sql log=${BACKUP_PATH}/${FULL_BACKUP_LOG_FILE_NAME}

FULL_BACKUP.SQL

######### Full backup data #########
run {
allocate channel d1 type disk maxpiecesize=2G format ‘/u01/backupDb/FULL_BACKUP/FULL_DB_%d_%Y%M%D_%t_%s_p%p’;
backup
incremental level 0
tag FULL_WEEKLY_DB_BACKUP
(database);
release channel d1;
}

######### Full archivelog backup #########
sql ‘ALTER SYSTEM ARCHIVE LOG CURRENT’;

run {
allocate channel dev1 type disk maxpiecesize=2G format ‘/u01/backupDb/FULL_BACKUP/ARC_DB_%d_%Y%M%D_%t_%s_p%p’;
backup
(archivelog all);
release channel dev1;
}

######### Crosscheck backup data #########
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;

exit;

Those scripts will make a full weekly backup of database and archivelog, and also delete backup files that is obsolete. To decide a backup file is obsolete or not it will be managed by the previous setting in retention policy, in this case is 30 days.

After the full backup you need to create incremental backup, and it can be done by creating two script that almost similar like the one for full backup.

INC_BACKUP.SH

#!/bin/sh
#incremental backup mode / incremental 1

USERNAME=”sys”
PASSWD=”password”

ORACLE_SID=APRDEV
export ORACLE_SID

ORACLE_HOME=”/u01/oracle/10g”
export ORACLE_HOME

CURRENT_DATE=”`date +%Y%m%d`”
export CURRENT_DATE

BACKUP_PATH=”/u01/backupDb/INC_BACKUP”
export BACKUP_PATH

BACKUP_SCRIPT=”/u01/backupDb/INC_BACKUP”
export BACKUP_SCRIPT

INC_BACKUP_LOG_FILE_NAME=”INC_DB_LOG_$CURRENT_DATE.log”

export INC_BACKUP_LOG_FILE_NAME

${ORACLE_HOME}/bin/rman target=$USERNAME/$PASSWD cmdfile=${BACKUP_SCRIPT}/inc_backup.sql log=${BACKUP_PATH}/${INC_BACKUP_LOG_FILE_NAME}

INC_BACKUP.SQL

run {
allocate channel d1 type disk maxpiecesize=2G format ‘/u01/backupDb/INC_BACKUP/INC_DB_%d_%Y%M%D_%t_%s_p%p’;
backup
incremental level 1
tag INC_DAILY_DB_BACKUP
(database);
release channel d1;
}
exit;

With these two new scripts the database will create daily incremental backup.

After the script is complete you can adjust to your needs when and how you want to implement it. In may case I will make the backup run by crontab jobs, where full_backup.sh will be run every Sunday at 00:00 while inc_backup.sh will be run from Monday to Saturday every 00:00. To know how to set crontab job you can find it in my earlier post.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s