Recovery of undo tablespace loss under RAC

Test environment:
System: LINUX-64
Database: 10.2.0.1
Two-node RAC: RACDB1, ASM used by RACDB2 Storage
 
(1) Insert data without submitting
RACDB1> insert into xuhm. test3 values (4, 'A ');
 
There is an active transaction.
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) shut down the database and delete the UNDO tablespace of RACDB1.
RACDB1> shutdown abort;
RACDB2> shutdown abort;
 
ASMCMD> rm UNDOTBS1.260.794232647
 
(3) Enable the database
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) The file has to be processed offline because it is lost.
RACDB1> alter database datafile '+ RAC_DISK/racdb/datafile/undotbs1.260.794232647' offline drop;
 
 
(5) Open the database
RACDB1> alter database open;
The database cannot be opened. The following error is returned when you view the alert Log:
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:
 
You need to modify the following parameters: note that _ uploupted_rollback_segments must be used here, And _ offline_rollback_segments cannot be used. Otherwise, you still cannot open the database.
In the pfile file.
RACDB1.undo _ management = 'manual'
RACDB1.undo _ tablespace = 'undo2'
RACDB1. _ javasupted_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) Delete rollback segments
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) Delete the old undo tablespace and create a new undo tablespace.
RACDB1> drop tablespace undotbs1 including contents and datafiles;
 
Tablespace dropped.
 
RACDB1> create undo tablespace undo2;
 
Tablespace created.
 
(8) modify the spfile Parameters
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) view the final recovery result
RACDB1> select * from xuhm. test3;
 
ID NA
------------
4 aa
2 xu
3 li
-- 4. The unsubmitted library of aa is processed as a submission.