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