Recovering an oracle database ( 9i )

This might be useful for the Unix admins who are forced to write shell scripts for backup and recovery of Oracle databases. I did face a challenge similier to this and here goes my notes .... May be these steps are partially incorrect but it works for me.

How do we backup an Oracle Database ?.

There are many ways to backup an oracle database. Broadly they can be claffied into two , they are

1. Cold Backup
2. Hot Backup

Cold backup is 'Shutdown the database ; Backup the datafiles to tape'. Simple backup , Easy recovery. Most of the DBAs like to restore DB from a cold backup , why ? Simply , there is nothing to recover itself !. Just unpack and startup the DB. The disadvantage of a cold backup is the outage. The database must be shutdown for the backup. a 24 * 7 APPS can not be 'cold back'ed up !.

Hot backup does the trick of backing up a database whilst it is running. Now here come's a two terms "Tables spaces in Hot backup Mode' AND 'Tables spaces in End backup mode'. Ideally a database [ in archive log mode ] runs in End backup mode. When you are about to run the backup , Admin put all the table spaces into hot backup mode , backups it up to tape and then put the table spaces back to end baackup mode. Details of what these terms are ... please refer oracle documentation.

A traditional hot backup will have the following routine,

a) Put all the tablespaces of the database to 'Hot Backup Mode' & backup control file.
b) Backup to tape / BCV / Flash Copy / PPRC / SRDF.
c) Put the table spaces of the database to 'End Backup Mode'.

d). To achivve 'point in time recovery' you will need the archive logs, so a job needs to be setup which will archive logs and then delete them from the database host.

Nowadays , you can use RMAN to do a cold or a hot backup. RMAN makes things simple for the DBAs , the recovery is made simple.

I have already talked about cold backup recovery. Here I am going to discuss a hot backup recovery,

Set ulimits for oracle user to unlimited.
Create the ORACLE_HOME filesystem i.e. /oracle/product/9.2.0.1
Install ORACLE software [ refer documentation available at oracle.com , no big stuff , it is easy to setup if you have met the pre-requisits ; you need a GUI so exceed !]
Patch it to the correct level. [ Now patches can be downloaded from metalink ]

export ORACLE_HOME=/oracle/product/9.2.0.1
export ORACLE_SID=
export PATH=${PATH}:${ORACLE_HOME}/bin

a) Restore $ORACLE_HOME/dbs Directory
b) Restore all other filesystems

sqlplus '/ as sysdba'
startup nomount
alter database mount ;

recover database until cancel using backup controlfile ;

Now this step will ask for archive logs , enter till you need recovery.

cancel , till the time you need recovery

alter database open resetlogs ;

Phewwwwww........ !! Database is recovered