Tip Of Month - Jun 1999

Oracle Cold Backup

Let's discuss about cold backup this month. I shall also give the scripts to take a complete cold backup.

Cold backup is copying the physical files associated with the Oracle database to a safe location, preferably tape and/or disk. The database should be shutdown normal or immediate. The files copied are the datafiles associated with all tablespaces, the control files, the initialization parameter files and the redo log files.

Let's prepare a cold backup script now.

Step 1 - Shutdown Database

Before shutting down the database, we need to get the file names.

1. Control files

select p.value value 
from v\$parameter p 
where upper(p.name) = 'CONTROL_FILES';

2. Data files

select df.file_name
from sys.dba_data_files df;

3. Redo log files

select l.member member 
from v\$logfile l;

Sometimes shutdown immediate will hang for ever. We'll wait for 10 minutes and issue a shutdown abort. Since shutdown abort is not good for a cold backup, we will startup the database and do a shutdown immediate. Now we have database file names and shutdown the database, we are ready to do the backup.

Click here for a complete shutdown script

Step 2 - Backup the files

Use OS copy command or your backup utility commands to copy the files to the offlline device. Read through the file names generated in the shutdown step and issue a copy command. If the copy fails once, try again.

Click here for a backup copy script

Step 3 - Startup the instance

Once the copy is complete, we can bring the database up. We can aslo run scripts to pin objects in the SGA and other startup operations.

Click here for a database startup script

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP,
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. © The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.