I deleted Oracle table spaces for cleanup on my dev environment. After that, ORA-01157 error occurred in log file.
ERROR at line 1:
ORA-01157: cannot identify/lock data file 5 – see DBWR trace file
ORA-01110: data file 5: ‘/u01/app/oracle/oradata/TS/TS001.dbf’
Cause
This error is occurred when Oracle server can not see the data files on operating system. For example, the data file is deleted or file system is unmounted (if data file on NFS area). In my case, I deleted necessary data files by mistake.
How to fix
Step1 : Start on MOUNT mode
Shutdown and start an Instance and Mounting a Database.
SQL> shutdown immediate
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1173240615 bytes
Fixed Size 8635922 bytes
Variable Size 373223054 bytes
Database Buffers 681671552 bytes
Redo Buffers 8120081 bytes
Database mounted.
MOUNT mode means that starting instance and mount a database without opening it. Please refer the following link for detail.
https://docs.oracle.com/cd/B28359_01/server.111/b28310/start001.htm#ADMIN11142
Step2 : Alter Database Datafile Offline Drop
Alter Database data file “offline drop”.
By this command, make it offline and mark as target of deletion.
SQL> alter database datafile ‘<datafile path>’ offline drop ;
ex) alter database datafile ‘/u01/app/oracle/oradata/TS/ts001.dbf’ offline drop ;
Step3 : Open database
SQL> alter database open;
Database altered.
Step4 : Delete table space
SQL> drop tablespace ‘<table space name>’ including contents;
ex) drop tablespace ts001 including contents;
After completing the above steps, restart database just in case.
