Oracle UNDO表空间损坏时的处理办法
前两天一客户的Oracle数据库出现故障，通过分析日志发现是UNDOTBS1表空间损坏，在没有使用RMAN备份来做恢复的情况下，通过重建UNDO表空间解决；拿到这个问题后，先尝试直接 recover database数据库，如果是在正常关闭数据库的情况下undo损坏，则这种方法应该可以修复数据库，如果不是，则可能不行。
尝试重建undo表空间：
首先可知是文件2发生错误，进而导致600错误。
因此，先查看文件2的名字，如下：
SQL> connect sys/manager as sysdba
Connected to an> SQL> startup mount pfile='d:/oracle92/ora92/database/inittest.ora'
ORACLE instance started.
Total System Global Area 93395628 bytes
Fixed>
Variable> Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> select file#,status,name from v$datafile;
FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
1 SYSTEM
D:/ORACLE92/ORADATA/TEST/SYSTEM01.DBF
2 ONLINE
D:/ORACLE92/ORADATA/TEST/UNDOTBS01.DBF
3 ONLINE
D:/ORACLE92/ORADATA/TEST/CWMLITE01.DBF
FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
4 ONLINE
D:/ORACLE92/ORADATA/TEST/DRSYS01.DBF
5 ONLINE
D:/ORACLE92/ORADATA/TEST/EXAMPLE01.DBF
6 ONLINE
D:/ORACLE92/ORADATA/TEST/INDX01.DBF
FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
7 ONLINE
D:/ORACLE92/ORADATA/TEST/ODM01.DBF
8 ONLINE
D:/ORACLE92/ORADATA/TEST/TOOLS01.DBF
9 ONLINE
D:/ORACLE92/ORADATA/TEST/USERS01.DBF
FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
10 ONLINE
D:/ORACLE92/ORADATA/TEST/XDB01.DBF
11 ONLINE
D:/ORACLE92/ORADATA/TEST/PMS.ORA
12 ONLINE
D:/ORACLE92/ORADATA/TEST/FYBX.ORA
12 rows selected.
可以看到，损坏的文件2是undotbs01.dbf,
查看资料，undotbs损坏或丢失时可以采用隐含参数临时启动数据库，然后进行修复。
修改init文件,加入
*._allow_resetlogs_corruption=true
（注：允许在数据库文件SCN不一致的情况下启动数据库）
*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
（注：允许在rollback segments损坏的情况下启动数据库）
SQL> shutdown abort
ORACLE instance shut down.
SQL>startup pfile='d:/oracle92/ora92/database/inittest.ora'
ORACLE instance started.
Total System Global Area 93395628 bytes
Fixed>
Variable> Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
启动成功，查看下当前的rollback segments
SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU1$ NEEDS RECOVERY
_SYSSMU2$ NEEDS RECOVERY
_SYSSMU3$ NEEDS RECOVERY
_SYSSMU4$ NEEDS RECOVERY
_SYSSMU5$ NEEDS RECOVERY
_SYSSMU6$ NEEDS RECOVERY
_SYSSMU7$ NEEDS RECOVERY
_SYSSMU8$ NEEDS RECOVERY
_SYSSMU9$ NEEDS RECOVERY
_SYSSMU10$ NEEDS RECOVERY
SEGMENT_NAME STATUS
------------------------------ ----------------
_SYSSMU11$ ONLINE
12 rows selected.
新建一重做表空间undo
SQL> create undo tablespace undo datafile 'D:/oracle92/oradata/test/undo01.dbf'> Tablespace created.
SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU1$ OFFLINE
_SYSSMU2$ NEEDS RECOVERY
_SYSSMU3$ NEEDS RECOVERY
_SYSSMU4$ NEEDS RECOVERY
_SYSSMU5$ NEEDS RECOVERY
_SYSSMU6$ NEEDS RECOVERY
_SYSSMU7$ NEEDS RECOVERY
_SYSSMU8$ NEEDS RECOVERY
_SYSSMU9$ NEEDS RECOVERY
_SYSSMU10$ NEEDS RECOVERY
SEGMENT_NAME STATUS
------------------------------ ----------------
_SYSSMU11$ ONLINE
_SYSSMU12$ OFFLINE
_SYSSMU13$ OFFLINE
_SYSSMU14$ OFFLINE
_SYSSMU15$ OFFLINE
_SYSSMU16$ OFFLINE
_SYSSMU17$ OFFLINE
_SYSSMU18$ OFFLINE
_SYSSMU19$ OFFLINE
_SYSSMU20$ OFFLINE
_SYSSMU21$ OFFLINE
22 rows selected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
修改init文件，指定默认表空间为新建的表空间
*.undo_tablespace=undo
SQL>startup pfile='d:/oracle92/ora92/database/inittest.ora'
ORACLE instance started.
Total System Global Area 93395628 bytes
Fixed>
Variable> Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
删除损坏的undotbs1表空间：
SQL>> 这个地方，如果查询undotbs1对应的数据文件的状态是recover状态时，是无法offline的，此时我是直接通过下面的命令删除了。
Tablespace> SQL> drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.
SQL>select * from v$recover_file;
no rows selected
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
修改init文件,注释参数
#*._allow_resetlogs_corruption=true
#*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
SQL>startup pfile='d:/oracle92/ora92/database/inittest.ora'
ORACLE instance started.
Total System Global Area 93395628 bytes
Fixed>
Variable> Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>
至此数据库已经成功修复。
**********************************************************************************
需要提醒的是，在删除损坏的重做表空间时，一定要先offline，
否则注释掉隐含参数后就会出现下面的情况。
SQL>startup pfile='d:/oracle92/ora92/database/inittest.ora'
ORACLE instance started.
Total System Global Area 93395628 bytes
Fixed>
Variable> Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
查看alert log，本例中会发现下面的信息，oracle标记刚才删除的
重做表空间需要恢复，这时就无法去掉隐含参数了。
......
drop tablespace UNDOTBS1 including contents and datafiles
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
页:
[1]