How can i restore a database with a hotbackup and archivelog files

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

Prerequisites
-      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:
STARTUP MOUNT

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
SELECT 'ALTER DATABASE DATAFILE '''||name||''' ONLINE;' FROM v$datafile;
SPOOL OFF
@onlineall

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

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:

ALTER DATABASE OPEN;



The statement
RECOVER [AUTOMATIC] DATABASE
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;

In step 3 under  "To recover datase", do i have to specify the location of the backups? I am using enterprise manager (rman) to create the backups, will the new instance of the database know where to get the backups files from?

Also, are the instructions applicable to restore/recover through the Enterprise manager.

If you use RMAN to fulfill the Recovery you have to use only  RMAN (issue BACKUP command from RMAN).
There is significant difference between plain OS backup files (by the primitive DBA driven full backup)
and the more sophisticated RMA backup.

I am not sure you understood my question.

I have a database server A with oracle 9i running on it. I've setup the Enterprise Manager to use the recovery manager for backups and restores. After about 2 months, Database server A fails because of a flood in our datacenter.
In an effort to get our users back online, i have created another server, lets call it server B. It has oracle 9i installed on it and i have to restore the backups (HotBackups with Archivelogs) from server A onto server B.

Is there anything i have to do on server B in order to get it to recognize the backups from server A?

If you use the low level (primitive) backups method (i.e.) the OEM simply copy the database files, control files, parameter files, password files (seems thats enough) you have to try to recreate the old directory structure and to transfer the appropriate files in the appropriate places. The only problems will be:
1. Where to place the appropriate files (especially SPFILE, the password file)
2. How to instruct Oracle to find the .dbf files if they are not on the right place. If you face this case then
SQL>startup nomount
SQL>alter database rename file 'c:\oracle\system.dbf' to 'c:\oracle\database\system.dbf'
SQL>alter system open

If you have used the RMAN then only RMAN on the new instance can do the restore. The reason is that the RMAN backups are not plain direct copy of the above mentioned Oracle files.

Thanks for the info. I know how to do the low level backup but since i am using rman i wanted to know what the recovery process was.
Can you restore a backup with rman if the backup was taken on a different machine?

Of course you can. There are different scenarios. If you havent use catalog then the data used by rman are in the control file.

Quick Reference Guide on How to Create a Duplicate Database using RMAN on Windows NT
-------------------------------------------------------------------------------------
 
Pre-requistes
--------------
- A valid full database backup of the target database  
- The target database must be open  
 
The following example shows how to create a duplicate target database using RMAN's DUPLICATE command to a local host.  
 
The target database SID is V901
The RMAN catalog database SID RMAN901  
The duplicate database SID is V901DUP  
 
Steps Required :-
----------------
1: Create an Oracle Password File for the Auxiliary Instance
2: Ensure Oracle Net Connectivity to the Auxiliary Instance
3: Create an Initialization Parameter File for the Auxiliary Instance
4: Start the Auxiliary Instance
5: Mount or Open the Target Database
6: Make Sure You Have the Necessary Backups and Archived Redo Logs
7: Log into RMAN Allocate Auxiliary Channels if Automatic Channels Are Not Configured
8: Run the RMAN duplicate command
 
 
 
1: Create an Oracle Password File for the Auxiliary Instance
-------------------------------------------------------------
For full details on how to create a password file please refer to Oracle9i Database Administrator's Guide.
 
  For example:  orapwd file=oraV901DUP.pwd password=manager entries=10
 
 
2: Ensure Oracle Net Connectivity to the Auxiliary Instance
------------------------------------------------------------
Modify the listener and tnsnames file to to able to connect to aux db
Test the connections using sqlplus - note that you must connect to the auxiliary
instance with SYSDBA privileges, so a password file must exist.  
 
 
3: Create an Initialization Parameter File for the Auxiliary Instance
----------------------------------------------------------------------
Copy the init.ora file for target database and make the necessary changes for teh
duplicate database e.g Setup the required paremeters e.g DB_NAME, CONTROL_FILES and directories for bdump, udump,cdump etc...
 
  Parameter file 'D:\ORACLE\ORA901\DATABASE\INITV901DUP.ORA'
 
 
4: Start the Auxiliary Instance
--------------------------------
Create a new NT service for the duplicate database V901DUP using oradim.
 
  C:\>oradim -new -sid V901DUP -intpwd manager -maxusers 10 -startmode auto -pfile  'd:\oracle\admin\V901DUP\pfile\init.ora'
 
  C:\> set ORACLE_SID=V901DUP
  C:\> sqlplus "sys/manager as sysdba"
  SQL> startup nomount
 
 
5: Mount or Open the Target Database
-------------------------------------
 
  C:\> set ORACLE_SID=V901
  C:\> sqlplus "/ as sysdba"
  SQL> startup  
 
 
6: Make Sure You Have the Necessary Backups and Archived Redo Logs
-------------------------------------------------------------------
To list what backups you have - log into RMAN and query the RMAN catalog.
 
  C:\> rman catalog rman/rman@RMAN901 target sys/change_on_install@V901
  RMAN> list backup;
 
 
7: Log into RMAN Allocate Auxiliary Channels if Automatic Channels Are Not Configured
--------------------------------------------------------------------------------------
rman catalog rman/rman@RMAN901 target sys/change_on_install@V901 auxiliary sys/manager@V901DUP
 
The output from connecting to RMAN with a catalog, target and auxiliary instance will show some output similar to:-
 
  Recovery Manager: Release 9.0.1.1.1 - Production
 
  (c) Copyright 2001 Oracle Corporation.  All rights reserved.
 
  connected to target database: V901 (DBID=1452169997)
  connected to recovery catalog database
  connected to auxiliary database: V901DUP (not mounted)
 
 
Task 8: Run the RMAN duplicate command
---------------------------------------
The following run command renames the datafiles to a new location and create three
redo log files for the duplicate database.  Note the example shown below is a full
duplicate of the target database.  If you want to create a duplicate database a week
prior to current time you can use the SET UNTIL TIME 'SYSDATE-7' syntax.
 
  run {
       #Allocate the channel for the duplicate work
       Allocate auxiliary channel ch1 type disk;
       #set the new file names and locations for all datafiles  
       SET NEWNAME FOR DATAFILE 1 TO 'D:\ORACLE\ORADATA\V901DUP\SYSTEM01.DBF';  
       SET NEWNAME FOR DATAFILE 2 TO 'D:\ORACLE\ORADATA\V901DUP\UNDOTBS01.DBF';
       SET NEWNAME FOR DATAFILE 3 TO 'D:\ORACLE\ORADATA\V901DUP\CWMLITE01.DBF';  
       SET NEWNAME FOR DATAFILE 4 TO 'D:\ORACLE\ORADATA\V901DUP\DRSYS01.DBF';
       SET NEWNAME FOR DATAFILE 5 TO 'D:\ORACLE\ORADATA\V901DUP\EXAMPLE01.DBF';
       SET NEWNAME FOR DATAFILE 6 TO 'D:\ORACLE\ORADATA\V901DUP\INDX01.DBF';
       SET NEWNAME FOR DATAFILE 7 TO 'D:\ORACLE\ORADATA\V901DUP\TOOLS01.DBF';
       SET NEWNAME FOR DATAFILE 8 TO 'D:\ORACLE\ORADATA\V901DUP\USERS01.DBF';
       SET NEWNAME FOR DATAFILE 9 TO 'D:\ORACLE\ORADATA\V901DUP\OEM_REPOSITORY.DBF';
       #Duplicate the database to V901DUP
       DUPLICATE TARGET DATABASE TO V901DUP
       #Create three redo logs for the duplicated database  
       LOGFILE
         GROUP 1 ('D:\ORACLE\ORADATA\V901DUP\REDO01.LOG',  
                  'D:\ORACLE\ORADATA\V901DUP\REDO01-2.LOG') SIZE 200K,  
         GROUP 2 ('D:\ORACLE\ORADATA\V901DUP\REDO02.LOG',  
                  'D:\ORACLE\ORADATA\V901DUP\REDO02-2.LOG') SIZE 200K,
         GROUP 3 ('D:\ORACLE\ORADATA\V901DUP\REDO03.LOG',  
                  'D:\ORACLE\ORADATA\V901DUP\REDO03-2.LOG') SIZE 200K;  
      }
 
 
 
RELATED DOCUMENTS
-----------------
Oracle9i Recovery Manager User's Guide

Thanks a lot for the information.
So is there a way to re-create the database if the source is unavailable?
I am thinking about a disaster scenario where i will only be working with the backups from rman.

Yes, if you use an catalog database that holds the RMAN backups.
If you do not use catalog DB then the recovery info is stored in the DB control file and
of course you have first to get it running before restore.

Can you give me the instructions for recovery with the catalog database and without it?

I tried a recovery without the catalog and i had a tough time getting the control file out of the backup. My problems started with allocating the channel to the disk that had the backup files. I would be very grateful if you could give me as much detail as possible on how to recover with and without a catalog database.

Thanks

Can you tell me how to recover without a catalog?

There are good examples in the Oracle .pdf "recovery manager". It can be downloaded from the OTN for free.