Steps for Recovery from a COLD backup


Can some one please provide detailed steps/commands to recover a database from a cold backup using Oracle 9i and Windows.

many thanks
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi, this article has comprehensive instructions on how to take your cold backup and how to restore from it under windows

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Complete Recovery Without Recovery Manager

The basic method used is Closed Complete Recovery. There are many other scenarios for recovery. This method is most universal and fixes all possible damages of the database.
The Closed database recovery is appropriate for fixing of damaged:
– System data files
– Rollback segment data files
– Whole database

-      The Oracle database runs in Archivelog mode – this gives guarantee that all changes after the last full backup will be available (if properly saved) and could be used for various types of recovery tasks;
-      The recommendation of the companion document “Database Backup and Recovery Strategy” are fulfilled - this gives an expectation that the backup is made over a clean stopped database, recovery files are readable and available, important written logs of the backup and archiving activities are available and the DBA could easily get oriented in the current state of the database installation;
-      The operator is provided with a set of files created by the last backup explained in the companion document  “OFFLINE COLD BACKUP” on an independent media;
-      The operator is provided with the full set of the Archived Redo Logs done after the last full cold backup on an independent media;
-      The operator is provided with the written log of the latest backup and archiving activities in order to get oriented in the current state of the database and the full available set of backed up files and archived redo logs;
-      Accurate record-keeping and media storage. The backup schedule should include dates and times, type of backup and tape labels.

Scope of application

There are various database recovery tasks (loss of disk, data file, corruption, recover database, recover tablespace, recover datafile, Point in Time recovery, recovery without control file, etc.) and many appropriate techniques to achieve the desired result.  The localization of the damage is an issue. The fix of the damage varies from case to case. The activities to fix are very different and imply skilled DBA personnel. The details could be read in the handbooks.
We decide to concentrate on general repair of the database.

Restore and recover all datafiles

The main task of this chapter is to recover the whole database what is the general step to achieve a working database. This should be undertaken if you create a new database and will import an existing DB there or if you cannot locate the scope and the object of the damage in old one. It takes relatively longer, but on other hand the administrator can concentrate on media physical repair and miss the database analyses for investigation which tablespaces and datafiles are damaged. Additional complicated task in this case is to choose the appropriate steps. This is so because the damage or lack of every type of Oracle files needs specific recovery approach.

 The steps in restoring and recovering all datafiles are:

-      stop the Oracle database;
-      make if possible a full cold database backup (if you succeed to stop clean the database) or save images of the disks;
-      repair the hardware in the case of damaged hardware components;
-      ensure a working operating system either by installing a new operating system or by fixing the installed operation system. Make sure that the parameters of the operating system fit the requirements of the installation of Oracle (companion document available);
-      analyze what caused the need of recovery. You have to figure out if the Oracle instance (mainly Oracle software reside in ORACLE_HOME) or the Oracle database is damaged. In the first case do a new installation of Oracle and creation of new database. Before this remove the old Oracle installation. The steps of removing the old installation are:
-      remove the database using Oracle Configuration Assistant (dbassist);
-      Backup all important files. (This includes the orainst.loc and init.ora files and any data files that have not already been backed up.);
-      Remove the $ORACLE_HOME directory;
-      Remove the oraInventory directory. (Most of the time it will be in the $ORACLE_BASE directory) ;
-      Remove the orainst.loc file. This file is in different places depending on which OS you have. (Sun is /var/opt/oracle, LINUX is in /etc.).
In the second case remove only the database using Oracle Configuration Assistant (dbassist) and create a new database. Follow strictly all steps of the document “ORACLE 8.1.7 Installation Guide for x86 Linux”. At the end make sure that Oracle clients can connect to the new empty database.
Close the database: SHUTDOWN IMMEDIATE
Copy Backup files to the Oracle Host:
    Copy the
·      backup datafiles
·      standby controlfile
·      all available archived redo logs    
·      !!!!!!   online redo logs
·      password file
from the backup file set to the Oracle host

To recover the database:

1. Connect to Oracle with administrator privileges, then start a new instance and
mount, but do not open, the database. For example, enter:

2. Ensure that all datafiles of the database are online. For example, to guarantee
that a datafile named /oracle/dbs/tbs_10.f is online, enter the following:
ALTER DATABASE DATAFILE '/oracle/dbs/tbs_10.f' ONLINE;
If a specified datafile is already online, Oracle ignores the statement. If you
prefer, create a script to bring all datafiles online at once as in the following:
SPOOL onlineall.sql

3. Issue the statement to recover the database.  For example, enter:
    RECOVER DATABASE        # this recovers whole database

4. If you choose not to automate the application of archived redo logs, accept or
reject each required redo log file that Oracle prompts you for. If you automated recovery, Oracle applies the necessary logs automatically. Oracle continues until
all required archived and online redo log files have been applied to the restored

5. Oracle notifies you when media recovery is complete. If no archived redo log
files are required for complete media recovery, Oracle applies all necessary
online redo log files and terminates recovery.

6. Open the database:


The statement
can only be used for a closed database recovery.
The clause [AUTOMATIC]  automatically applies archived and redo log files.

If archived logs are not restored to the LOG_ARCHIVE_DEST directory, then the Oracle server will need to be notified before or during recovery, by:
• Specifying the location and name at the recover prompt:
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
• Use the ALTER SYSTEM ARCHIVE command:
SQL> alter system archive log start to <new location>;
• Use the RECOVER FROM <LOCATION> command:
SQL> recover from‘<new location>’ database;

crishna1Author Commented:
Schwertner, i am realy sorry , i already allocated the points before i checked your responce.
the first responce was good enough.

thanks anyways.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.