If you cannot recover data by yourself, ask Parnassusdata, the professional ORACLE database recovery team for help.

  PURPOSE ------- This bulletin explains how to use the known hidden parameters such as _OFFLINE_ROLLBACK_SEGMENTS and _CORRUPTED_ROLLBACK_SEGMENTS with undo segments when --> Automatic Undo Management is active : UNDO_MANAGEMENT=AUTO --> Corrupted undo information prevents the database from being accessible : undo segments like _SYSSMUn$ are in NEEDS RECOVERY status --> Backup of RBS datafiles / archive redo log files are not available and therefore no recovery is possible Be aware that the use of _OFFLINE_ROLLBACK_SEGMENTS may lead to the recreation of the database, depending on whether there were active transactions in the dropped undo segments. If so, then this may lead to logical corruption, and hence to the recreation of the database. (Refer Note:106638.1 that explains how to check the transaction table : you can use the same SELECT statements) Be aware that the use of _CORRUPTED_ROLLBACK_SEGMENTS requires the recreation of the database. SCOPE & APPLICATION ------------------- For all DBAs having to manage the recovery of databases with corrupted undo segments. Example of situations --------------------- --> Situation 1 =========== After setting an UNDO datafile OFFLINE and shutting the database down normally, the following errors occur : SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 10800 undo_suppress_errors boolean FALSE undo_tablespace string UNDOTBS1 SQL> select segment_name , status from dba_rollback_segs; SEGMENT_NAME STATUS ------------------------------ ---------------- SYSTEM ONLINE _SYSSMU1$ ONLINE _SYSSMU2$ ONLINE _SYSSMU3$ ONLINE ... _SYSSMU10$ ONLINE 11 rows selected. SQL> alter database datafile 'C:\ORANT\DB1\UNDOTBS01.DBF' offline; alter database datafile 'C:\ORANT\DB1\UNDOTBS01.DBF' offline * ERROR at line 1: ORA-00603: ORACLE server session terminated by fatal error In alert.log: ------------ alter database datafile 'C:\ORANT\DB1\UNDOTBS01.DBF' offline Thu Mar 07 16:52:55 2002 ORA-376 signalled during: alter database datafile 'C:\ORANT\DB1\UNDOTBS01.DB... Thu Mar 07 16:52:55 2002 Errors in file C:\ORANT\admin\DB1\bdump\db1SMON.TRC: ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: 'C:\ORANT\DB1\UNDOTBS01.DBF'   In user trace file on NT: ------------------------ KCRA: start recovery buffer claims *** 2002-03-07 17:13:32.000 KCRA: buffers claimed = 0/0, eliminated = 0 ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: 'C:\ORANT\DB1\UNDOTBS01.DBF' In user trace file on Unix:   -------------------------- kssxdl: error deleting SO: 82af3fc0, type: 38, owner: 8320de58, flag: I/-/-/0x00: ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/filer/9.0.2/DB1/undotbs01.dbf' --> Situation 2 =========== When RBS datafiles are in a RECOVER status, and no backup is available to recover appropriately, you need to drop the UNDO tablespace. In alert.log: ------------ Successfully onlined Undo Tablespace 1. Mon May 27 17:17:14 2002 SMON: enabling tx recovery SMON: about to recover undo segment 1 SMON: mark undo segment 1 as needs recovery SMON: about to recover undo segment 2 SMON: mark undo segment 2 as needs recovery SMON: about to recover undo segment 3 SMON: mark undo segment 3 as needs recovery ... Errors in file /oracle3/djeunot/DB1/udump/ora_19462.trc: ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/oracle3/djeunot/DB1/undotbs01.dbf' Mon May 27 17:17:14 2002 Error 376 happened during db open, shutting down database USER: terminating instance due to error 376 Instance terminated by USER, pid = 19462 ORA-1092 signalled during: alter database open... --> Situation 3 =========== The datafile of the undo tablespace is removed. The database is in NOARCHIVELOG mode. $ rm undotbs01.dbf SQL> update x.t set a=1; update x.t set a=1 * ERROR at line 1: ORA-01115: IO error reading block from file 2 (block # 3) ORA-01110: data file 2: '/oracle3/djeunot/DB1/undotbs01.dbf' ORA-27091: skgfqio: unable to queue I/O ORA-27072: skgfdisp: I/O error Additional information: 2 At startup: SQL> startup pfile=/oracle3/djeunot/DB1/pfile/initDB1.ora ORACLE instance started. Total System Global Area 235693108 bytes Fixed Size 279604 bytes Variable Size 167772160 bytes Database Buffers 67108864 bytes Redo Buffers 532480 bytes Database mounted. ORA-01157: cannot identify/lock data file 2 - see DBWR trace file ORA-01110: data file 2: '/oracle3/djeunot/DB1/undotbs01.dbf' In alert.log ------------ Tue May 28 14:53:37 2002 Errors in file /oracle3/djeunot/DB1/bdump/dbw0_23154.trc: ORA-01157: cannot identify/lock data file 2 - see DBWR trace file ORA-01110: data file 2: '/oracle3/djeunot/DB1/undotbs01.dbf' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Actions ------- ------------------------------------------------ 1/ | Set the following parameters in the init.ora | ------------------------------------------------ UNDO_MANAGEMENT=MANUAL _OFFLINE_ROLLBACK_SEGMENTS=(_SYSSMU1$, _SYSSMU2$, _SYSSMU3$, ...etc) or _CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU1$, _SYSSMU2$, _SYSSMU3$, ...etc) Note: To get the list of the _SYSSMUn undo segments to OFFLINE when the database is not accessible, you can use the following : $ strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u > listSMU   where system01.dbf is the name of the datafile for the SYSTEM tablespace. ** From this list, do not forget to rename the _SYSSMU9 to _SYSSMU9$ ** a/ If you keep UNDO_MANAGEMENT=AUTO, when you want to DROP the UNDO tablespace, you get the following error: SQL> drop tablespace undotbs including contents and datafiles; drop tablespace undotbs including contents and datafiles * ERROR at line 1: ORA-30013: undo tablespace 'UNDOTBS' is currently in use though you may have dropped all undo segments. b/ Be aware that the names of the undo segments do not start back at _SYSSMU1$ once the tablespace has been dropped and recreated. The names take the next sequence numbers: if the undo tablespace dropped contained _SYSSMU1$ to _SYSSMU10$, then the creation of the new undo tablespace generates undo segments whose names start at _SYSSMU11$. c/ To know which one of the parameters _OFFLINE_ROLLBACK_SEGMENTS or _CORRUPTED_ROLLBACK_SEGMENTS to use, refer to @Note:106638.1 Handling Rollback Segment Corruptions in Oracle7.3 to 8.1.7 d/ Dumping the transaction table and undo for active transactions from undo segments such as "_SYSSMUn$" is strictly the same procedure as defined in the referenced note above. --------------------- 2/ | Open the database | --------------------- a/ If the RBS datafiles are not missing, the database may open: ----------------------------------------------------------- SQL> startup ORACLE instance started. Total System Global Area 118560016 bytes Fixed Size 451856 bytes Variable Size 100663296 bytes Database Buffers 16777216 bytes Redo Buffers 667648 bytes Database mounted. Database opened. SQL> select name, status, enabled, checkpoint_change# from v$datafile; NAME STATUS ENABLED CHECKPOINT_CHANGE# ---------------------------------- ------- ---------- ------------------ /oracle3/djeunot/DB1/system01.dbf SYSTEM READ WRITE 62315 /oracle3/djeunot/DB1/undotbs01.dbf RECOVER READ WRITE 62241 /oracle3/djeunot/DB1/users01.dbf ONLINE READ WRITE 62315 SQL> select SEGMENT_NAME, STATUS from dba_rollback_segs; SEGMENT_NAME STATUS ------------ ---------------- SYSTEM ONLINE _SYSSMU2$ NEEDS RECOVERY _SYSSMU3$ NEEDS RECOVERY ... b/ If the RBS datafiles are missing, the database does not open: ------------------------------------------------------------ Use the _OFFLINE_ROLLBACK_SEGMENTS parameter to allow the undo segments to be dropped once the database opened. SQL> startup pfile=/oracle3/djeunot/DB1/pfile/initDB1.ora ORACLE instance started. Total System Global Area 235693108 bytes Fixed Size 279604 bytes Variable Size 167772160 bytes Database Buffers 67108864 bytes Redo Buffers 532480 bytes Database mounted. ORA-01157: cannot identify/lock data file 2 - see DBWR trace file ORA-01110: data file 2: '/oracle3/djeunot/DB1/undotbs01.dbf' SQL> select * from v$recover_file; FILE# ONLINE ONLINE_STATUS ERROR CHANGE# TIME ---------- ------- ------------- --------------- ------- ---- 2 ONLINE ONLINE FILE NOT FOUND 0     Before opening the database, OFFLINE DROP the missing datafiles : SQL> alter database datafile '/oracle3/djeunot/DB1/undotbs01.dbf' 2 offline drop; Database altered. SQL> alter database open; Database altered. ----------------------------------------------------- 3/ | The Undo Segments need to be individually dropped | ----------------------------------------------------- SQL> drop rollback segment "_SYSSMU1$"; Rollback segment dropped. SQL> drop rollback segment "_SYSSMU2$"; Rollback segment dropped. ..... If you get the following error: SQL> drop rollback segment "_SYSSMU11$"; drop rollback segment "_SYSSMU11$" * ERROR at line 1: ORA-30025: DROP segment '_SYSSMU11$' (in undo tablespace) not allowed this means that you did not specify the right undo segment name in the list of the hidden parameter at startup time, and therefore the undo segment is not offlined. Define the correct list and re-startup the database. -------------------------------------------------------------------- 4/ | Once the Undo Segments are all dropped, drop the UNDO tablespace | -------------------------------------------------------------------- SQL> drop tablespace UNDOTBS including contents and datafiles; Tablespace dropped. If you get the following error: SQL> drop tablespace undotbs including contents and datafiles; drop tablespace undotbs including contents and datafiles * ERROR at line 1: ORA-01548: active rollback segment '_SYSSMU11$' found, terminate dropping tablespace this means that undo segments still exist in the undo tablespace to be dropped. -------------------------------- 5/ | Recreate the undo tablespace | -------------------------------- SQL> create undo tablespace undotbs 2 datafile '/DB1/undotbs01.dbf' size 500k reuse; Tablespace created. -------------------------------------------------- 6/ | Reset the following parameters in the init.ora | -------------------------------------------------- UNDO_MANAGEMENT=AUTO #_OFFLINE_ROLLBACK_SEGMENTS=(_SYSSMU1$, _SYSSMU2$, _SYSSMU3$, ...etc) or #_CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU1$, _SYSSMU2$, _SYSSMU3$, ...etc)     7/ If you used these hidden ROLLBACK_SEGMENTS parameter, perform a full export since the database may be in an inconsistent state. Then you MUST recreate the database and perform a full import in the case of the use of _CORRUPTED_ROLLBACK_SEGMENTS . In the case of _OFFLINE_ROLLBACK_SEGMENTS with active transactions that may lead to logical corruption, you need to recreate the database and import the data back. If there were no active transactions, then there is no need to recreate the database: an export is nevertheless a good backup.