Interview Questions

Oracle - DB Startup Failure unavailability of Datafile

Consider a scenario where you have accidentally deleted a DATA-FILE in oracle when the instance is running. It will throw error when you restart the DB as follows.

1. Log-in to oracle DB server

$ sqlplus system/manager as sysdba

SQL> startup

ORACLE instance started.

Total System Global Area 1174405120 bytes
Fixed Size                  2020288 bytes
Variable Size             285215808 bytes
Database Buffers          872415232 bytes
Redo Buffers               14753792 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 20 - see DBWR trace file
ORA-01110: data file 20:

In order to resolve the above issue, perform the following and restart the database.

SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/oraPROFPRD_2875_07EB_4600p6' offline DROP;
SQL> shutdown
SQL> startup