Oracle的UNDO表空间发生损坏,可用_corrupted_rollback_segments参数进行处理

除了常见的日志文件损坏之外，Oracle的UNDO表空间发生损坏也是常见的问题之一。在没有备份的情况下，也可以通过Oracle的另外一个隐含参数用来进行特殊处理，这个参数是
_corrupted_rollback_segments。

SQL> @GetParDescrb
Enter value for par: corrupted_rollback
NAME                           VALUE                DESCRIB
------------------------------ -------------------- -----------------------------
_corrupted_rollback_segments                        corrupted undo segment list

通过这个参数可以指定一个损坏的回滚段列表，Oracle在数据库启动后的回滚中，可以跳过这些回滚段，从而能够跳过某些错误。同样需要提醒的是，当你不得不使用这样的手段进行恢复尝试时，一定要注意保护现场，在备份之后再进行恢复尝试。

以下是一个实际的恢复案例。
启动数据库出现ORA-00600 4194错误，观察alert文件，主要错误日志如下：

Sat Jan 21 13:55:21 2006
Errors in file /opt/oracle/admin/conner/bdump/conner_smon_17113.trc:
ORA-00600: internal error code, arguments: [4194], [43], [46], [], [], [], [], []
Sat Jan 21 13:55:21 2006
Errors in file /opt/oracle/admin/conner/udump/conner_ora_17121.trc:
ORA-00600: internal error code, arguments: [4194], [45], [44], [], [], [], [], []  

4194错误通常说明UNDO信息和Redo信息不匹配，通常说明数据库存在损坏或不一致，针对这类问题最好的办法是通过备份进行恢复，如果没有备份，那么可以通过特殊的初始化参数进行强制启动，由于实际情况可能各不相同，进行测试前请先行备份，本例的恢复过程可能也不具广泛意义。

在使用_corrupted_rollback_segments参数前，需要确定相关回滚段名称，这可以从alert文件中获得：

Sat Jan 21 13:55:21 2006
Undo Segment 11 Onlined
Undo Segment 12 Onlined
Undo Segment 13 Onlined
Successfully onlined Undo Tablespace 16.  

对应的AUM（Auto Undo Management）下的回滚段名称为：

'_SYSSMU11$','_SYSSMU12$','_SYSSMU13$' 

修改init.ora参数文件或spfile文件，使用Oracle隐含参数_corrupted_rollback_segments将回滚段标记为损坏，此时启动数据库，Oracle会跳过对于这些回滚段的相关操作，强制启动数据库。

._corrupted_rollback_segments='_SYSSMU11$','_SYSSMU12$','_SYSSMU13$' 

使用更改后的参数文件启动数据库：

[oracle@jumper dbs]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Jan 21 13:56:47 2006
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to an idle instance.

SQL> startup pfile=initconner.ora
ORACLE instance started.
Database mounted.
Database opened.  

此时数据库正常Open。观察alert文件可以获得如下信息：

Sat Jan 21 13:57:03 2006
SMON: enabling tx recovery
SMON: about to recover undo segment 11
SMON: mark undo segment 11 as needs recovery
SMON: about to recover undo segment 12
SMON: mark undo segment 12 as needs recovery
SMON: about to recover undo segment 13
SMON: mark undo segment 13 as needs recovery
Sat Jan 21 13:57:03 2006
Database Characterset is ZHS16GBKSat 
Jan 21 13:57:03 2006
SMON: about to recover undo segment 11
SMON: mark undo segment 11 as needs recovery
SMON: about to recover undo segment 12
SMON: mark undo segment 12 as needs recovery
SMON: about to recover undo segment 13
SMON: mark undo segment 13 as needs recovery
Sat Jan 21 13:57:04 2006
Created Undo Segment _SYSSMU1$
Undo Segment 1 Onlined
Completed: ALTER DATABASE OPEN
Sat Jan 21 14:02:11 2006
SMON: about to recover undo segment 11
SMON: mark undo segment 11 as needs recovery
SMON: about to recover undo segment 12
SMON: mark undo segment 12 as needs recovery
SMON: about to recover undo segment 13
SMON: mark undo segment 13 as needs recovery  

此时可以重新创建新的UNDO表空间，删除出现问题的表空间，修改参数文件，由参数文件生成新的spfile，重新启动数据库：

SQL> create undo tablespace undotbs1
  2  datafile '/opt/oracle/oradata/conner/undotbs1.dbf' size 10M;
  Tablespace created.
SQL> alter system set undo_tablespace=undotbs1;
System altered.
SQL> drop tablespace undotbs2;
Tablespace dropped.  

此时的alert文件记录的：

Sat Jan 21 14:03:29 2006
create undo tablespace undotbs1
datafile '/opt/oracle/oradata/conner/undotbs1.dbf' size 10M
Sat Jan 21 14:03:29 2006
Created Undo Segment _SYSSMU2$
Created Undo Segment _SYSSMU3$
Created Undo Segment _SYSSMU4$
Created Undo Segment _SYSSMU5$
Created Undo Segment _SYSSMU6$
Created Undo Segment _SYSSMU7$
Created Undo Segment _SYSSMU8$
Created Undo Segment _SYSSMU9$
Created Undo Segment _SYSSMU10$
Created Undo Segment _SYSSMU14$
Starting control autobackup
Control autobackup written to DISK device
        handle '/opt/oracle/product/9.2.0/dbs/c-3152029224-20060121-00'
Completed: create undo tablespace undotbs1
datafile '/opt/oraSat 
Jan 21 14:03:43 2006
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Undo Segment 14 Onlined
Successfully onlined Undo Tablespace 1.
Undo Segment 1 Offlined
Undo Tablespace 16 successfully switched out.
Sat Jan 21 14:03:43 2006
ALTER SYSTEM SET undo_tablespace='UNDOTBS1' SCOPE=MEMORY;
Sat Jan 21 14:07:18 2006
SMON: about to recover undo segment 11
SMON: mark undo segment 11 as needs recovery
SMON: about to recover undo segment 12
SMON: mark undo segment 12 as needs recovery
SMON: about to recover undo segment 13
SMON: mark undo segment 13 as needs recovery
Sat Jan 21 14:08:06 2006
drop tablespace undotbs2
Sat Jan 21 14:08:07 2006
Starting control autobackup
Control autobackup written to DISK device
        handle '/opt/oracle/product/9.2.0/dbs/c-3152029224-20060121-01'
Completed: drop tablespace undotbs2  

修改参数文件，变更undo表空间，并取消_corrupted_rollback_segments设置：

*.undo_tablespace='UNDOTBS1'  

由参数文件创建spfile文件：

SQL> create spfile from pfile;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Database mounted.
Database opened.  

重起数据库，观察alert文件：

Sat Jan 21 14:08:36 2006
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Undo Segment 14 Onlined
Successfully onlined Undo Tablespace 1.  

此时数据库恢复正常，通常建议立即全库EXP，然后重新建库，再IMP恢复数据库。