14、oracle报ora-01157文件损坏问题

oracle报ora-01157文件损坏问题

一个表空间文件损坏无法删除，重启oracle数据库后，数据库无法正常启动，报

Database mounted. ORA-01157: cannot identify/lock data file 15 - see DBWR trace file ORA-01110: data file 15: '/u01/app/oracle/oradata/TEST/QTC_SPACE.DBF'

1、关闭数据库

SQL> shutdown abort;

2、启动数据库到mount状态

SQL> startup nomount;
ORACLE instance started.
 
Total System Global Area 1577054672 bytes
Fixed Size                  8896976 bytes
Variable Size             973078528 bytes
Database Buffers          587202560 bytes
Redo Buffers                7876608 bytes
SQL> alter database mount;
 
Database altered.

3、删除报错的表空间文件

15对应上面ORA-01110: data file 15

SQL> alter database datafile 15 offline drop;
 
Database altered.
 
SQL> alter database open;
 
Database altered.
 
SQL> alter tablespace QTC_SPACE offline immediate ;
 
Tablespace altered.
 
SQL> drop tablespace QTC_SPACE including contents and datafiles ;
 
Tablespace dropped.

4、关闭数据库后重启

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.