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.