Oracle can’t start the database due to deleting DBF file by mistake

Statement

First of all, this method can not recover the data. The purpose of this paper is to restore the database to normal, data can be discarded, mostly in the test environment.

Phenomenon

Delete the data file of the table space, resulting in an error when shutting down the database and the startup database, as follows:

SQL> startup;
ORACLE instance started.


Total System Global Area 4008546304 bytes
Fixed Size		    2259440 bytes
Variable Size		  872416784 bytes
Database Buffers	 3120562176 bytes
Redo Buffers		   13307904 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/data/oracle/oradata/orclstd/PSR1.dbf'

Because an 8K table space can only have a maximum of 32g, this table space specifies multiple DBF files, and several DBF files are deleted at one time. There is an operation [drop user XXX cascade;], Delete the user’s, and the table space does not exist, but [startup;] Database, or error. Use the following statement to close the data file:

SQL> alter database datafile '/data/oracle/oradata/orclstd/PSR1.dbf' offline drop;


Database altered.


SQL>  alter database open;
 alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/data/oracle/oradata/orclstd/PSR2.dbf'




SQL> alter database datafile '/data/oracle/oradata/orclstd/PSR2.dbf' offline drop;


Database altered.


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '/data/oracle/oradata/orclstd/psr1_02.dbf'




SQL>  alter database datafile '/data/oracle/oradata/orclstd/psr1_02.dbf' offline drop;


Database altered.


SQL> alter database open;


Database altered.


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.


Total System Global Area 4008546304 bytes
Fixed Size		    2259440 bytes
Variable Size		  872416784 bytes
Database Buffers	 3120562176 bytes
Redo Buffers		   13307904 bytes
Database mounted.
Database opened.
SQL> exit

If other dBfs affect the database startup, execute [alter database open;] When the error is detected, continue the cycle until no error is detected.