Comments Off on The Importance of DBA_ROLLBACK_SEGS – 2 of 3 Yes, the original post title said “The Importance of DBA_ROLLBACK_SEGS -1 of 2”.  The second part was supposed to be where I clobbered my undo tablespace and had to use _CORRUPTED_ROLLBACK_SEGMENTS.  Apparently I can’t even clobber my tablespaces right.  My evil plan was simple:

  1. Shut the database down
  2. Nuke the rollback segment datafile from orbit with ASMCMD
  3. Attempt to bring the database back up, recovering with _CORRUPTED_ROLLBACK_SEGMENTS.

Well it didn’t work.  I shut the database down: rbs2 Then, I deleted the Undo tablespace with ASMCMD and tried to bring the database back up.  The results were predictable:

ASMCMD> rm +DATA/DBINTERNALS/DATAFILE/undotbs01.260.875483419 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. ORA-01157: cannot identify/lock data file 11 - see DBWR trace file ORA-01110: data file 11: '+DATA/DBINTERNALS/DATAFILE/undotbs01.260.875483419'

Since I don’t have the datafile, I’ll try to take it offline and open it.  Since my undo tablespace is gone, I should get an error. Wrong.

SQL> alter database datafile 11 offline; Database altered. SQL> alter database open; Database altered.

WTF?  No undo tablespace and I can open my database just fine?  Then I figured it out.  I did a SHUTDOWN IMMEDIATE, so all uncommitted transactions were rolled back during the shutdown, so no  undo is needed to open the database.  In addition, there is an old Oracle7 style rollback segment SYSTEM in the SYSTEM tablespace.  So you can actually do updates if you want to, but I wouldn’t.  Instead, I would create a new undo tablespace, update the spfile, and restart the database:

SQL> create undo tablespace undotbs1 datafile '+DATA' size 500m; Tablespace created. SQL> alter system set undo_tablespace='UNDOTBS1' scope=spfile; 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> drop tablespace undotbs01; -- Drop the old undo tablespace Tablespace dropped. SQL>

So next time, I’ll have to try a harder test.  I’ll start a SQL*Loader script to load the last 20 years of Dilbert cartoons into my database, then do something stupid like killing PMON before deleting the datafile.  That should hose my database real good.