04 | 03 | 2021
Latest Articles
Popular Articles

Backup & Recovery

Lost Undo Datafile

User Rating:  / 1

In any Oracle database we have

1. A tempfile ( check dba_temp_files or v$tempfile ) belonging to a temporary tablespace. (In older versions of the Oracle database there were no real tempfiles neither temporary tablespaces. The temp tablespace was just an ordinary tablespace the tempfile used to be an ordinary datafile.
2. A system tablespace with at least 1 datafile ( check dba_data_files or v$datafile )
3. from 10G R1 onwards the sysaux tablespace with at least 1 datafile ( check dba_data_files or v$datafile )
4. some user data tablespaces with some datafiles ( check dba_data_files or v$datafile )
5. The undo tablespace. These days almost always system managed undo segments. Oracle has succesfully introduced its System Managed Undo -- SMU — from release 9iR1 onwards. Not very young oracle DBA' s will certainly remember the manually managed rollback segments. Rollback segments used to be manually configured in a dedicated tablespace often RBS.

Am I nostalgic today ? Why still thinking about the old fashionned manual undo when — and I confirm it once again — I am pleased with System Managed Undo ?

Believe it or not this year I used twice very temporary manual undo in a very specific case : the recovery of a lost undo datafile.

Two days ago a DBA asked me to come to his office and showed me his undo datafile was lost. A nightly export filled up the disk ... and how it happened I don' t know but the undo datafile was gone. The DBA had in mind — I must admit it is rather logic --- since the database was open

1. create another undo tablespace UNDOTBS2 "SQL> create undo tablespace undotbs2"
2. make this the active undo tablespace "SQL> alter system set undo_tablespace=UNDOTBS2 scope=spfile;"
3. shutdown and startup the database.
4. drop the physically absent old UNDOTBS1 "SQL> drop tablespace UNDOTBS1;"

And at this stage there was an issue, the DBA did not manage to drop the old undo tablespace.

Up to me to advice, what was the situation ?

1. database was open
2. system management undo, so we cannot drop system managed rollback segments (never)
3. system management undo, we cannot drop the undo tablespace since there are active rollback segments in it

I adviced to query the good old fashionned dba_rollback_segs, whether you operate in SMU or not, every rollback segment is listed, and the output is something like

SQL> select segment_name,status,tablespace_name from dba_rollback_segs;
SEGMENT_NAME                   STATUS           TABLESPACE_NAME

SYSTEM                         ONLINE           SYSTEM
_SYSSMU10_1192467665$          ONLINE           UNDOTBS1
_SYSSMU9_1192467665$           ONLINE           UNDOTBS1
_SYSSMU8_1192467665$           ONLINE           UNDOTBS1
_SYSSMU7_1192467665$           ONLINE           UNDOTBS1
_SYSSMU6_1192467665$           ONLINE           UNDOTBS1
_SYSSMU5_1192467665$           ONLINE           UNDOTBS1
_SYSSMU4_1192467665$           ONLINE           UNDOTBS1
_SYSSMU3_1192467665$           ONLINE           UNDOTBS1
_SYSSMU2_1192467665$           ONLINE           UNDOTBS1
_SYSSMU1_1192467665$           ONLINE           UNDOTBS1

11 rijen zijn geselecteerd.

But in stead of online the status of the rollback segments was NEEDS RECOVERY

Well you can' t drop the undo tablespace, you can' t drop the rollback segs, physically the undo datafile is lost ... the only good news is your database can be stopped and started again.

The workaround I succesfully used half a year ago and also succesfully adviced this time is

SQL > alter system set undo_management=manual scope=spfile;
SQL > shutdown immediate;
SQL > create pfile from spfile;

Open the text init file and add the parameter

_offline_rollback_segments=("_SYSSMU1","_SYSSMU12", ..... every rollback segment that needs recovery)

SQL> startup pfile='full_path-to_your_text_init_file'

SQL> drop tablespace UNDOTBS1;"  It was succesfull !!!

SQL> shutdown immediate;

Open the text init file and REMOVE the parameter _offline_rollback_segments

Also set undo management to AUTO again

SQL > create spfile from pfile;
SQL > startup ( we use the new spfile )

And we are happy again ... we succesfully dropped logically the old undo tablespace of which the datafile was physically lost.

Note that recovery issues related to undo

1. are always interesting
2. almost always require experience
3. can give you trouble
4. can give you deep trouble
5. can trigger a total loss of the database. ( TAKE CARE )

I faced the situation above twice this year and because of that I write it down. Depending on what happened at the moment of undo datafile loss you can face very severe situations. If you can' t open the database anymore ... you may need to use the — "dangerous" — parameter "_corrupted_rollback_segments" A total rebuild of the database afterwards becomes necessary.

In this document I wrote about "_offline_rollback_segments" and "_corrupted_rollback_segments". The recommendation however is to setup a backup strategy which avoids the need to use these underscore parameters.


1. A good database backup strategy is a hedge for a database disaster.

2. Physical Gold and Physical Silver are hedges for financial disasters.