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

• Kiran Dalvi
• 18 Aug, 2020
• 0 Comments
• 28 Secs Read

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

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