数据库错误如下:
ORA-01116:error in opening database file 2
ORA-01110:datafile 2:'/u02/oradata/sfcsys/undotbs01.dbf'
ORA-27041:unable to open file
Linux Error 2:No such file or directory
Additional information:3
根据判断提示判断为UNDO损坏
决定通过重建UNDO表空间来解决这个问题
1.创建新的UNDO空间UNDOTB2
SQL> create undo tablespace undotbs2 datafile '/u02/oradata/sfcsys/undo2.dbf' size2048m;
2.将数据库的默认表空间切换到新创建的表空间
SQL> alter system set undo_tablespace=undotbs2 scope=both;
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
3.确保所有在UNDOTBS1的undo segment都已offline
SQL> select SEGMENT_NAME ,STATUS ,TABLESPACE_NAME from dba_rollback_segs;
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
_SYSSMU1$ OFFLINE UNDOTBS1
_SYSSMU2$ OFFLINE UNDOTBS1
_SYSSMU3$ OFFLINE UNDOTBS1
_SYSSMU4$ OFFLINE UNDOTBS1
_SYSSMU5$ OFFLINE UNDOTBS1
_SYSSMU6$ OFFLINE UNDOTBS1
_SYSSMU7$ OFFLINE UNDOTBS1
_SYSSMU8$ OFFLINE UNDOTBS1
_SYSSMU9$ OFFLINE UNDOTBS1
_SYSSMU10$ OFFLINE UNDOTBS1
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU11$ OFFLINE UNDOTBS1
_SYSSMU12$ OFFLINE UNDOTBS1
_SYSSMU13$ OFFLINE UNDOTBS1
_SYSSMU14$ ONLINE UNDOTBS2
_SYSSMU15$ ONLINE UNDOTBS2
_SYSSMU16$ ONLINE UNDOTBS2
17 rows selected.
上面红色记录说明oracle在没有undo tablespace时会使用system表空间作为undo tablespace.
4.删除原来的UNDO tablespace(如果不删除在备份时会报错)
SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-01116: error in opening database file 2
ORA-01110: data file 2: '/u02/oradata/sfcsys/undotbs01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
offline data file 2
SQL> alter database datafile '/u02/oradata/sfcsys/undotbs01.dbf' offline;
Database altered.
删除
SQL> drop tablespace undotbs1 including contents and datafiles;