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.

Step 1: Check data file Status.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME

---------- ------------------ ---------- ---------

         1 NOT ACTIVE                  0

         2 NOT ACTIVE                  0

         3 NOT ACTIVE                  0

         4 FILE NOT FOUND         0

         5 NOT ACTIVE                  0

Step 2: Restore datafile from cold backup.

Step 3: Check recover file status:

SQL> select * from V$recover_file;

FILE# ONLINE  ONLINE ERROR          CHANGE#              TIME

         4 ONLINE  ONLINE                               529340                  14-APR-11

Step 4: Recover datafile

SQL> recover datafile 4;

Step 5: Open the database.

SQL> alter database open;