Blog

Learnomate Technologies > Blog > Oracle DBA > Drop datafile which is already removed Locally – ORA-01157,ORA-01110

Drop datafile which is already removed Locally – ORA-01157,ORA-01110

  • August 18, 2020
  • Posted by: Ankush Thavali
  • Category: Oracle DBA
No Comments 
I got the below issue while starting the database. Below issue occurred as I have deleted the datafiles from server level and not I am unable to start the datafile.



SQL> startup
ORACLE instance started.

Total System Global Area 1325400064 bytes
Fixed Size 8792488 bytes
Variable Size 822085208 bytes
Database Buffers 486539264 bytes
Redo Buffers 7983104 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 22 - see DBWR trace file
ORA-01110: data file 22: '/data/app/oracle/oradata/bigtbs01.dbf'

We need to remove the datafile information from control file metadata.

Solution

shutdown immediate
startup mount
alter database datafile '/data/app/oracle/oradata/bigtbs01.dbf' offline drop;
alter database open
Drop datafile which is already removed Locally ORA-01110 ORA-01157