Steps for Recovery from a COLD backup

Posted on 2006-11-14
Medium Priority
Last Modified: 2012-05-05

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

many thanks
Question by:crishna1
LVL 35

Accepted Solution

Raynard7 earned 2000 total points
ID: 17942529
Hi, this article has comprehensive instructions on how to take your cold backup and how to restore from it under windows
LVL 48

Expert Comment

ID: 17945444
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;


Author Comment

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

thanks anyways.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month16 days, 11 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question