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未提交的書屋被當做提交處理了。