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.
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.
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.