ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: '/home/oracle/rmantbs01.dbf'

数据库启动报错

[oracle@oracle-asm:/home/oracle]$sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Apr 14 23:18:06 2021

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 893386752 bytes
Fixed Size 8626864 bytes
Variable Size 335547728 bytes
Database Buffers 545259520 bytes
Redo Buffers 3952640 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/home/oracle/rmantbs01.dbf'

 

解决办法：
SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 893386752 bytes
Fixed Size 8626864 bytes
Variable Size 335547728 bytes
Database Buffers 545259520 bytes
Redo Buffers 3952640 bytes
Database mounted.
SQL> select file#,name,status from v$datafile;

FILE#
----------
NAME
--------------------------------------------------------------------------------
STATUS
-------
1
+DATA/orcl/system01.dbf
SYSTEM

3
+DATA/orcl/sysaux01.dbf
ONLINE

FILE#
----------
NAME
--------------------------------------------------------------------------------
STATUS
-------

4
+DATA/orcl/undotbs01.dbf
ONLINE

5
/home/oracle/rmantbs01.dbf

FILE#
----------
NAME
--------------------------------------------------------------------------------
STATUS
-------
ONLINE

7
+DATA/orcl/users01.dbf
ONLINE

SQL> alter database datafile '/home/oracle/rmantbs01.dbf' offline drop;

Database altered.

SQL> select * from v$tablespace;

TS# NAME INC BIG FLA ENC CON_ID
---------- ------------------------------ --- --- --- --- ----------
1 SYSAUX YES NO YES 0
0 SYSTEM YES NO YES 0
2 UNDOTBS1 YES NO YES 0
4 USERS YES NO YES ON 0
3 TEMP NO NO YES 0
6 RMANTBS YES NO YES 0

6 rows selected.

 

SQL> drop tablespace RMANTBS including contents cascade constraints;
drop tablespace RMANTBS including contents cascade constraints
*
ERROR at line 1:
ORA-01109: database not open

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 893386752 bytes
Fixed Size 8626864 bytes
Variable Size 335547728 bytes
Database Buffers 545259520 bytes
Redo Buffers 3952640 bytes
Database mounted.
Database opened.

 

 

摘自：

解决办法：
既然出现报错的几个dbf文件已经不用，则解决办法相对简单，只要将对应的数据文件删除，并继续删除对应新增的表空间即可。操作过程如下：
SQL> shutdown immediate;
SQL> startup mount;
SQL> select file#,name,status from v$datafile;
SQL> alter database datafile '/tmp/test.dbf' offline drop;      //此处若不加drop会报错
再次查看v$datafile表会发现对应的几个dbf文件状态由ONLINE变为RECOVER
SQL> select * from v$tablespace;
SQL> drop tablespace test including contents cascade constraints;
......
删除完毕，再次执行startup成功。