This scenario deal with a situation where data file has gone missing or corrupted at the time of open the database.

This scenario deals with a situation where a datafile has gone missing, or is corrupted beyond repair. For concreteness, we look at a case where a datafile is missing. Below is a transcript of an SQL Plus session that attempts to open a database with a missing datafile (typed commands in bold, lines in italics are my comments, all other lines are feedback from SQL Plus):

--open SQL Plus from the command line without

--logging on to database

C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jan 25 14:52:41 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

--Connect to the idle Oracle process as a privileged user and start up instance

SQL> connect / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 131555128 bytes

Fixed Size 454456 bytes

Variable Size 88080384 bytes

Database Buffers 41943040 bytes

Redo Buffers 1077248 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF'

SQL>

The error message tells us that file# 4 is missing. Note that although the startup command has failed, the database is in the mount state. Thus, the database control file, which is also the RMAN repository can be accessed by the instance and by RMAN. We now recover the missing file using RMAN. The transcript of the recovery session is reproduced below (bold lines are typed commands, comments in italics, the rest is feedback from RMAN):

--logon to RMAN

C:\>rman target /

Recovery Manager: Release 9.2.0.4.0 – Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: ORCL (DBID=1507972899)

--restore missing datafile

RMAN> restore datafile 4;

--recover restored datafile - RMAN applies all logs automatically

RMAN> recover datafile 4;

media recovery complete

--open database for general use

RMAN> alter database open;

database opened

RMAN>