Performing a cold backup.
A cold backup requires that none of the data files are being accessed during the backup. For this, the database has to be shutdown.
Each part of the database has to be copied to another location, and once complete, the database can be restarted. This does not require the database to be in archivelog mode, since all the datafiles are timestamped with the same SCN of the database.
Shutdown abort must not be used to shutdown the database, though with the online redo logs still available, oracle should be able to recover from this if need be.
If you have trouble shutting down the database, use shutdown abort, startup restricted and shutdown to allow oracle to update all the datafiles on restart.
You require the following items in a cold backup to be able to restore the database.
-
Data Files.
-
Undo files.
-
Temporary files.
-
Online redo logs.
-
Control file.
-
spfile or pfile.
My spfile is in the default location, ?/dbs/spfile@.ora. This translates to $ORACLE_HOME/dbs/spfileVDCPE01.ora
The files can be listed with the following sql statement.
select name from v$controlfile
union
select file_name from dba_data_files
union
select file_name from dba_temp_files
union
select member from v$logfile
/
Having collected all these files, including the spfile, you can shutdown the database and copy all these files to a secondary location.
The following sql statement will output the required list of files to be zipped up using gzip. Different commands can be incorporated as required (cp, compress, tar, etc). I recommend using a compression utility such as gzip if you plan to move the database elsewhere using a cold backup. gzip will remove the original files, compress, tar and cp will not.
select 'gzip '||name||' &'
from (
select name from v$controlfile
union
select file_name from dba_data_files
union
select file_name from dba_temp_files
union
select file_name from dba_temp_files
union
select member from v$logfile
)
/
This will output all the gzip commands required to backup the files, from where they can be removed from the system and stored elsewhere.
Remember to backup the spfile also using either gzip or the cp command.
Restore a cold backup.
The reverse of this is true to restore the cold backup. Each datafile should be restored to the same location as it was originally.
Renaming files on restoration.
You can rename any of the files in the database as required on restoration of the cold backup.
Rename the control files.
Using a parameter file.
Restore the pfile if there was one. Change the location of the control_Files parameter to reflect the new locations and create the spfile from pfile. The environment must be set but the database need not be open.
>. oraenv
ORACLE_SID = [VDCPE01] ? VDCPE01
>sqlplus /nolog
SQL*Plus: Release 9.2.0.8.0 – Production on Tue Feb 26 09:01:23 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
Recreate the spfile from the pfile.
SQL> create spfile from pfile='<path>/pfile_VDCPE01.ora';
SQL> startup;
Using the spfile.
Restore the spfile from the backup. You cannot alter the control_file locations in the database for the spfile, so you have to extract the information from the spfile into a pfile and startup and edit it in the same manner as detailed above.
strings spfileVDCPE01.ora > initVDCPE01.ora
Edit the initVDCPE01.ora and follow the steps detailed above.
Renaming logfiles or datafiles.
Once you have recreated the spfile above if required, you can startup mount the database. At this point the parameter file has been read. From there you can issue the command:
SQL> alter database rename file '<path/file>' to '<new path/file>';
For each datafile or logfile to be renamed.
Restore the backed up files to the location specified in renaming the files above, and start the database normally.
