The Importance of DBA_ROLLBACK_SEGS – Recovery

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

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.

Comments are closed.