undo表空间recover状态的（无备份）故障处理

故障概述

今天项目上其他公司的运维人员找我说某数据库好像归档满了，并且他直接rman删了3天前的所有归档，然后发现二节点是宕机的，启动二节点数据库后发现2节点undo数据文件需要recover，这个数据库没有备份。（万幸的是，需要recover的只是2节点undo数据文件）。
切记：！！！！归档日志没有备份的情况下，在删除归档日志之前一定要确认是否存在需要使用归档日志recover的数据文件！！！！！否则只有丢数据了！！！

进入正题：
故障原因：
UNDOTBS2表空间上还有active状态的事务（未提交），二节点由于归档目录满了导致数据库宕机，然后Oracle会将undo表空间下的记录着active未提交的段都标志为NEEDS RECOVERY。
处理思路：
1、有备份的情况下，直接备份恢复就可以了。
2、如果没有备份，可以用新建的UNDO替代损坏的UNDO，那么损坏UNDO上的未提交事务也将不得不丢弃。当然NEEDS RECOVERY的回滚段需要先进行处理。

故障处理

检查需要recover的数据文件：

SQL> select file#,status,name from v$datafile where status ='RECOVER';

     FILE# STATUS
---------- ---------------------
NAME
---------------------------------------------------

         5 RECOVER
+XXXX/XXXX/datafile/undotbs2.267.795022137



1  rows selected

先创建一个新的undo表空间

SQL> create undo tablespace UNDOTBS02 datafile size 10g autoextend on next 2g;

Tablespace created.

查看当前节点（2节点）的表空间，还是需要recover的undotbs2。

SQL> show parameter undo

NAME                TYPE         VALUE
------------------- ------------ --------------------
undo_management      string      AUTO
undo_retention       integer     900
undo_tablespace      string      UNDOTBS2

用新创建的undotbs02替换之前的undotbs2

SQL> alter system set undo_tablespace='UNDOTBS02' scope=both;

System altered.

在删除之前的undo表空间的时候报错ora-01548

SQL> alter database datafile 5 offline;

Database altered.

SQL> drop tablespace UNDOTBS2 including contents and datafiles;
drop tablespace UNDOTBS2 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU11_3993685818$' found, terminate dropping tablespace

这个ora-01538的报错说是回滚段中还有活动的事务。
检查回滚段状态：

SQL> select segment_name,status,tablespace_name from dba_rollback_segs where status not in ('ONLINE','OFFLINE') and tablespace_name='UNDOTBS2';

SEGMENT_NAME                   STATUS               TABLESPACE_NAME
------------------------------ ------------------ -----------------------------
_SYSSMU11_3993685818$          NEEDS RECOVERY       UNDOTBS2
_SYSSMU12_3786484004$          NEEDS RECOVERY       UNDOTBS2
_SYSSMU13_4206247907$          NEEDS RECOVERY       UNDOTBS2
_SYSSMU14_3550840220$          NEEDS RECOVERY       UNDOTBS2
_SYSSMU15_1667460038$          NEEDS RECOVERY       UNDOTBS2
_SYSSMU16_102810502$           NEEDS RECOVERY       UNDOTBS2
_SYSSMU17_4197495598$          NEEDS RECOVERY       UNDOTBS2
_SYSSMU18_2803337290$          NEEDS RECOVERY       UNDOTBS2
_SYSSMU19_2100581483$          NEEDS RECOVERY       UNDOTBS2
_SYSSMU20_3702784048$          NEEDS RECOVERY       UNDOTBS2

10 rows selected.

试图将这些回滚段置于offline，然后drop掉。

SQL> alter rollback segment  "_SYSSMU11_3993685818$"  offline; 

Rollback segment altered.

SQL> alter rollback segment  "_SYSSMU12_3786484004$"  offline; 

Rollback segment altered.

SQL> alter rollback segment  "_SYSSMU13_4206247907$"  offline; 

Rollback segment altered.

SQL> alter rollback segment  "_SYSSMU14_3550840220$"  offline; 

Rollback segment altered.

SQL> alter rollback segment  "_SYSSMU15_1667460038$"  offline; 

Rollback segment altered.

SQL> alter rollback segment  "_SYSSMU16_102810502$"  offline;  

Rollback segment altered.

SQL> alter rollback segment  "_SYSSMU17_4197495598$"  offline; 

Rollback segment altered.

SQL> alter rollback segment  "_SYSSMU18_2803337290$"  offline;

Rollback segment altered.

SQL> alter rollback segment  "_SYSSMU19_2100581483$"  offline; 

Rollback segment altered.

SQL> alter rollback segment  "_SYSSMU20_3702784048$"  offline;

Rollback segment altered.

drop回滚段时报错ORA-30025：

SQL> drop rollback segment "_SYSSMU11_3993685818$"; 
drop rollback segment "_SYSSMU11_3993685818$"
*
ERROR at line 1:
ORA-30025: DROP segment '_SYSSMU11_3993685818$' (in undo tablespace) not allowed

将_smu_debug_mode设置成4。这里4代表可以在UNDO自动管理（SMU）下能够进行一些手工的回滚段管理模式下的操作（RBU）

SQL>  alter system set"_smu_debug_mode" = 4;

System altered.

使用一下两个隐含参数_OFFLINE_ROLLBACK_SEGMENTS和_CORRUPTED_ROLLBACK_SEGMENTS，将NEEDS RECOVERY的回滚段添加至这两个隐含参数列表并编辑添加至pfile中：

vi init.ora
...
_OFFLINE_ROLLBACK_SEGMENTS=(_SYSSMU11_3993685818$,_SYSSMU12_3786484004$,_SYSSMU13_4206247907$,_SYSSMU14_3550840220$,_SYSSMU15_1667460038$,_SYSSMU16_102810502$,_SYSSMU17_4197495598$,_SYSSMU18_2803337290$,_SYSSMU19_2100581483$,_SYSSMU20_3702784048$)
_CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU11_3993685818$,_SYSSMU12_3786484004$,_SYSSMU13_4206247907$,_SYSSMU14_3550840220$,_SYSSMU15_1667460038$,_SYSSMU16_102810502$,_SYSSMU17_4197495598$,_SYSSMU18_2803337290$,_SYSSMU19_2100581483$,_SYSSMU20_3702784048$)
...

关闭数据库后以init.ora文件启动数据库。

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount pfile='/home/oracle/init.ora';
ORACLE instance started.

Total System Global Area 2.6991E+10 bytes
Fixed Size                  2213976 bytes
Variable Size            2.5770E+10 bytes
Database Buffers         1073741824 bytes
Redo Buffers              145174528 bytes
Database mounted.
SQL> alter database open;

Database altered.

再次drop状态为NEEDS RECOVERY的回滚段。

SQL> drop rollback segment "_SYSSMU11_3993685818$"; 

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU12_3786484004$";

Rollback segment dropped.

SQL> drop rollback segment  "_SYSSMU13_4206247907$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU14_3550840220$";

Rollback segment dropped.

SQL> drop rollback segment  "_SYSSMU16_102810502$";

Rollback segment dropped.

SQL> drop rollback segment  "_SYSSMU15_1667460038$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU17_4197495598$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU18_2803337290$";

Rollback segment dropped.

SQL> drop rollback segment  "_SYSSMU19_2100581483$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU20_3702784048$";

Rollback segment dropped.

成功drop不再报错，然后删除之前的undo表空间和数据文件：

SQL> alter system set undo_tablespace='UNDOTBS02' scope=both;

System altered.

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.

将新建的undotbs02表空间成功替换之前的undotbs2，并将undotbs2表空间删除。

SQL> select file#,status,name from v$datafile status='ONLINE';

     FILE# STATUS                NAME
---------- --------------------- --------------------------------------------------------------------------------
         1 SYSTEM                +DHOMSAPP/dhomsapp/datafile/system.256.795021933

...
        10 ONLINE                +DHOMSAPP/dhomsapp/datafile/undotbs02.289.145649813

10 rows selected.

至此，2节点undo表空间成功恢复至online，数据库恢复正常。