某开发库的一个undo数据文件莫名丢失,前台Java程序报错
Caused by: java.sql.SQLException: ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u03/oradata/SCOUTBV/undotbs01.dbf'
数据库alert log满屏都是
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u03/oradata/SCOUTBV/undotbs01.dbf'
该数据库没有备份,数据文件无法恢复,因此考虑将该undo表空间删除重建
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS01
数据库默认undo表空间为UNDOTBS01,而出问题的是UNDOTBS1,其中file 2在OS上被物理删除,之前已经有DBA已经执行了offline drop
SQL> select FILE_NAME,FILE_ID,status,ONLINE_STATUS from dba_data_files where TABLESPACE_NAME ='UNDOTBS1';
FILE_NAME FILE_ID STATUS ONLINE_
-------------------------------------------------------------------------------- ---------- --------- -------
/u03/oradata/SCOUTBV/undotbs01.dbf 2 AVAILABLE RECOVER
/u05/oradata/SCOUTBV/undotbs01_1.dbf 56 AVAILABLE ONLINE
/u03/oradata/SCOUTBV/undotbs1.dbf 58 AVAILABLE ONLINE
SQL> alter tablespace UNDOTBS1 offline;
alter tablespace UNDOTBS1 offline
*
ERROR at line 1:
ORA-01191: file 2 is already offline - cannot do a normal offline
ORA-01110: data file 2: '/u03/oradata/SCOUTBV/undotbs01.dbf'
SQL> select SEGMENT_NAME,TABLESPACE_NAME