RAC下丢失undo表空间的恢复

RAC下丢失undo表空间的恢复

测试环境：
系统：LINUX-64
数据库：10.2.0.1
二节点RAC：RACDB1，RACDB2  存储使用的ASM
 
（1）插入数据，不提交
RACDB1>insert into xuhm.test3 values (4,'aa');
 
有一个活动的事务。
RACDB1>select usn,xacts from v$rollstat;
 
      USN    XACTS
---------- ----------
        0        0
        1        0
        2        0
        3        0
        4        1
        5        0
        6        0
        7        0
        8        0
        9        0
      10        0
 
（2）关闭数据库，删除RACDB1的UNDO表空间
RACDB1>shutdown abort;
RACDB2>shutdown abort;
 
ASMCMD> rm UNDOTBS1.260.794232647
 
（3）开启数据库
RACDB1>startup
Oracle instance started.
 
Total System Global Area 184549376 bytes
Fixed Size                2019448 bytes
Variable Size            121638792 bytes
Database Buffers          58720256 bytes
Redo Buffers              2170880 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '+RAC_DISK/racdb/datafile/undotbs1.260.794232647'
 
RACDB2>startup
ORACLE instance started.
 
Total System Global Area 184549376 bytes
Fixed Size                2019448 bytes
Variable Size            155193224 bytes
Database Buffers          25165824 bytes
Redo Buffers              2170880 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '+RAC_DISK/racdb/datafile/undotbs1.260.794232647'
 
RACDB2>shutdown immediate
 
（4）因为这个文件丢失，所以只好把这个文件offline处理
RACDB1>alter database datafile '+RAC_DISK/racdb/datafile/undotbs1.260.794232647' offline drop;
 
 
（5）打开数据库
RACDB1>alter database open;
无法打开数据库，查看alert日志报错如下
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '+RAC_DISK/racdb/datafile/undotbs1.260.794232647'
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Fri Sep 28 20:32:29 2012
Errors in file /u01/app/oracle/admin/RACDB/bdump/racdb1_lms0_9732.trc:
ORA-00604: error occurred at recursive SQL level
Fri Sep 28 20:32:29 2012
Errors in file /u01/app/oracle/admin/RACDB/bdump/racdb1_lmon_9728.trc:
 
需要修改如下参数：注意，这里一定要使用_corrupted_rollback_segments，不能使用_offline_rollback_segments，要不然还是无法打开数据库。
修改在pfile文件中。
RACDB1.undo_management='MANUAL'
RACDB1.undo_tablespace='UNDO2'
RACDB1._corrupted_rollback_segments=('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$')
 
RACDB1>startup pfile='/u01/pfile';
ORACLE instance started.
 
Total System Global Area 184549376 bytes
Fixed Size                2019448 bytes
Variable Size            121638792 bytes
Database Buffers          58720256 bytes
Redo Buffers              2170880 bytes
Database mounted.
Database opened.
 
（6）删除回滚段
RACDB1>SELECT segment_name,status FROM DBA_ROLLBACK_SEGS WHERE STATUS'OFFLINE';
 
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
 
11 rows selected.
 
RACDB1>drop rollback segment "_SYSSMU1$";
 
Rollback segment dropped.
 
RACDB1>drop rollback segment "_SYSSMU2$";
 
Rollback segment dropped.
 
RACDB1>drop rollback segment "_SYSSMU3$";
 
Rollback segment dropped.
 
RACDB1>drop rollback segment "_SYSSMU4$";
 
Rollback segment dropped.
 
RACDB1>drop rollback segment "_SYSSMU5$";
 
Rollback segment dropped.
 
RACDB1>drop rollback segment "_SYSSMU6$";
 
Rollback segment dropped.
 
RACDB1>drop rollback segment "_SYSSMU7$";
 
Rollback segment dropped.
 
RACDB1>drop rollback segment "_SYSSMU8$";
 
Rollback segment dropped.
 
RACDB1>drop rollback segment "_SYSSMU9$";
 
Rollback segment dropped.
 
RACDB1>drop rollback segment "_SYSSMU10$";
 
Rollback segment dropped.
 
（7）删除旧的undo表空间，创建新undo表空间
RACDB1>drop tablespace undotbs1 including contents and datafiles;
 
Tablespace dropped.
 
RACDB1>create undo tablespace undo2 ;
 
Tablespace created.
 
（8）修改spfile参数
RACDB1>shutdown immediate
RACDB1>startup mount;
RACDB1>alter system set undo_management=auto scope=spfile sid='RACDB1';
RACDB1>alter system set undo_tablespace=UNDO2 scope=spfile sid='RACDB1';
RACDB1>shutdown immediate
RACDB1>startup
RACDB1>show parameter undo
 
NAME                                TYPE      VALUE
------------------------------------ ----------- ------------------------------
undo_management                    string    AUTO
undo_retention                      integer    900
undo_tablespace                    string    UNDO2
 
 
（9）查看最后恢复的结果
RACDB1>select * from xuhm.test3;
 
      ID NA
---------- --
        4 aa
        2 xu
        3 li
--4，aa未提交的书屋被当做提交处理了。