ORA-01157/ORA-01110: cannot identify/lock data file 12 – see DBWR trace file
Posted by Mir Sayeed Hassan on February 13th, 2018
ORA-01157/ORA-01110: cannot identify/lock data file 12 – see DBWR trace file
As you can see while starting of the database, the datafile 12 is missing/corrupted and through the ora error as shown:
sys@TESTDB> startup ORACLE instance started. Total System Global Area 2455228416 bytes Fixed Size 2255712 byt Variable Size 771753120 bytes Database Buffers 1660944384 bytes Redo Buffers 20275200 bytes Database mounted.
ORA-01157: cannot identify/lock data file 12 – see DBWR trace file
ORA-01110: data file 12: ‘/u01/app/oracle/oradata/prim/test01.dbf’
sys@TESTDB> select status from V$instance; STATUS ------------ MOUNTED
sys@TESTDB> select name from V$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/prim/system01.dbf /u01/app/oracle/oradata/prim/sysaux01.dbf /u01/app/oracle/oradata/prim/undotbs01.dbf /u01/app/oracle/oradata/prim/users01.dbf /u01/app/oracle/oradata/prim/example01.dbf /u01/app/oracle/oradata/prim/test01.dbf -- We cannot find this file in physical location
10 rows selected.
If you feel this datafile is not much important, you can make this datafile as “offline drop” and open the database or If you have backup of the database, then restore it.
sys@TESTDB> alter database datafile '/u01/app/oracle/oradata/prim/test01.dbf' offline drop; Database altered.
sys@TESTDB> alter database open; Database altered.
sys@TESTDB> select status from v$instance; STATUS ------------ OPEN