OK, I have successfully opened my database.  I am in a lot better position than I was I was a few days ago.  But what about my priceless Dilbert collection?  It is not like I could just go to dilbert.com and download them again, could I?

SQL> select count(*) from dilbert; ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 12 cannot be read at this time ORA-01110: data file 12: '+DATA/DBINTERNALS/DATAFILE/undotbs1.260.875748227':

Anyway, after opening my database, I tried to see what was in my Dilbert table: Checking DBA_ROLLBACK_SEGS. I see some entries that have a status of “needs recovery” from my missing undo tablespace:

SQL> select segment_name, status from dba_rollback_segs; SEGMENT_NAME                   STATUS ------------------------------ ---------------- SYSTEM                         ONLINE _SYSSMU28_3586889292$          ONLINE _SYSSMU27_3341047585$          ONLINE _SYSSMU26_1539987469$          ONLINE _SYSSMU36_168785881$           OFFLINE _SYSSMU35_1475960130$          OFFLINE _SYSSMU34_908380251$           OFFLINE _SYSSMU33_2122985546$          OFFLINE _SYSSMU32_228305016$           OFFLINE _SYSSMU31_2744769455$          OFFLINE _SYSSMU30_2141723264$          OFFLINE _SYSSMU29_2359381190$          OFFLINE _SYSSMU10_732010430$           NEEDS RECOVERY _SYSSMU9_624586700$            NEEDS RECOVERY _SYSSMU8_1927760754$           NEEDS RECOVERY _SYSSMU7_3527658720$           NEEDS RECOVERY _SYSSMU6_3377313595$           NEEDS RECOVERY _SYSSMU5_1147562966$           NEEDS RECOVERY

I tried dropping the tablespace but got:

ORA-01548: active rollback segment

I have done this before. Not sure why it didn’t work, but nothing else seems to be working on this blog, including WordPress :). Anyway, I tried setting the parameter again and restarting the database cleanly. The entries are still there in DBA_ROLLBACK_SEGS but I can drop the tablespace now:

SQL> alter system set "_corrupted_rollback_segments"= 1 '_SYSSMU10_732010430$', 2 '_SYSSMU9_624586700$', 3 '_SYSSMU8_1927760754$', 4 '_SYSSMU7_3527658720$', 5 '_SYSSMU6_3377313595$', 6 '_SYSSMU5_1147562966$', 7 scope=spfile SQL> / System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 2499805184 bytes Fixed Size 2927480 bytes Variable Size 671089800 bytes Database Buffers 1811939328 bytes Redo Buffers 13848576 bytes Database mounted. Database opened. SQL> select segment_name, status from dba_rollback_segs; SEGMENT_NAME STATUS ------------------------------ ---------------- SYSTEM ONLINE _SYSSMU28_3586889292$ ONLINE _SYSSMU27_3341047585$ ONLINE _SYSSMU26_1539987469$ ONLINE _SYSSMU36_168785881$ OFFLINE _SYSSMU35_1475960130$ OFFLINE _SYSSMU34_908380251$ OFFLINE _SYSSMU33_2122985546$ OFFLINE _SYSSMU32_228305016$ OFFLINE _SYSSMU31_2744769455$ OFFLINE _SYSSMU30_2141723264$ OFFLINE _SYSSMU29_2359381190$ OFFLINE _SYSSMU10_732010430$ NEEDS RECOVERY _SYSSMU9_624586700$ NEEDS RECOVERY _SYSSMU8_1927760754$ NEEDS RECOVERY _SYSSMU7_3527658720$ NEEDS RECOVERY _SYSSMU6_3377313595$ NEEDS RECOVERY _SYSSMU5_1147562966$ NEEDS RECOVERY SQL> drop tablespace undotbs1; Tablespace dropped. SQL> select segment_name, status, tablespace_name from dba_rollback_segs; SEGMENT_NAME STATUS TABLESPACE_NAME ------------------------------ ---------------- ------------------------------ SYSTEM ONLINE SYSTEM _SYSSMU28_3586889292$ ONLINE UNDOTBS01 _SYSSMU27_3341047585$ ONLINE UNDOTBS01 _SYSSMU26_1539987469$ ONLINE UNDOTBS01 _SYSSMU25_4262984436$ ONLINE UNDOTBS01 _SYSSMU24_2903816470$ ONLINE UNDOTBS01 _SYSSMU23_4129801038$ ONLINE UNDOTBS01 _SYSSMU22_1326017728$ ONLINE UNDOTBS01 _SYSSMU21_3269488760$ ONLINE UNDOTBS01 _SYSSMU20_3537793676$ ONLINE UNDOTBS01 _SYSSMU19_3320221023$ ONLINE UNDOTBS01 11 rows selected. SQL>

So I have successfully dropped the undo tablespace.  What about my data?  I try querying the table again to see if I can access it:

SQL> select count(*) from dilbert;   COUNT(*) ----------        640

If you go back to my previous blog, you will see that 640 is the number of rows committed before the database crashed.  Apparently, I have my data,  But is it really all there with no corruptions?  I wrote a little PL/SQL program to extract them to a /tmp directory and extracted them: Screenshot-dilbert   My beloved Dilbert strips are all there!  I have all of my data even though though I lost my undo tablespace without proper backups.  Should this be considered as part of any planned backup strategy?  Other than putting a query of DBA_ROLLBACK_SEGS in your backup script, No.  But sometimes, even with the best backup strategy, you can find yourself without backups you need at the most inopportune time.  I have seen backup products that skip datafiles due to a failing hard drive, then reported the backup as successful.  I have seen a database silently write corrupt archivelogs, rendering otherwise perfectly good RMAN backups useless.  When things start to go wrong, they often tend to affect your backup systems as well.  This recovery would not have been possible without the initial query of DBA_ROLLBACK_SEGS in the backup.  I would have not known what the segment names were, and would have not been able to open the database in the first place.