Archive for August, 2007


How to perform and recover from a cold backup

Back to basic.

The cold backup is the easiest backup that could be performed.

It can’t really be used in a backup strategy for businesses with critical systems because there is a risk of loss of data up to 24h.

It is useful anyway to know how to perform it because it makes duplicating a database useful and can save lives sometimes.

Perform a cold backup

A cold back requires a clean shutdown of the database.

shutdown immediate or normal are the only shutdown allowed.

A database cold backed up after a shutdown abort will result into fuzzy files which can’t be recovered.

The reason is that the datafiles must have had a checkpoint performed (which is done with shutdown immediate).

When the clean shutdown is done, copy the oradata folders to a new location.

After the files are copied, the database can be started up.

Recover from a cold backup

Depending on the structure given on the server to recover, the recovery can involve renaming datafiles and controlfiles.

1) If the service doesn’t exist yet, issue the oradim command:

oradim -NEW -SID SAME_AS_BACKED_UP_DB -pfile FULLPATH\init.ora -syspass somepassword

If you have taken the measure to copy the password file and initialization parameter from your primary server to the server to recover in ORACLE_HOME\database, then the options -pfile and -syspass are not required.

2) Copy the oradata folders to the exact same location from primary to the server to recover.

If the oradata files are located in the same drives, same folder structure, the recovery stops here and a startup can be issued.

3) If their are hard disk constraints (more drive on primary than on recovered server or not enough disk space), it must be required to move some datafiles from one drive to another.

In the case a data file has been moved to new location, the database must be started in mount mode:

C:\set oracle_sid= mySID

C:\sqlplus / as sysdba

SQL> startup mount; (if the database doesn’t mount, the location to thepfile can be specified by adding the parameter spfile=’LOCATION\SPFILESSID.ora’)

and a rename command must be issued for each files relocated:

SQL> alter database rename file ‘OLD_LOCATION\DATAFILE_1.ora’ to ‘NEW_LOCATION\DATAFIE_1.ora’;

SQL>alter database datafile ‘NEW_LOCATION\DATAFILE1.ora’ online;

SQL>alter database open;