The Importance of DBA_ROLLBACK_SEGS – 3 of 4

 

 

Does anyone notice a pattern here with the post titles?   Anyway, I was loading my priceless collection of Dilbert strips into my database when it crashed and burned:

sqlldr

Trying to restart the database, my undo tablespace is gone:

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 12 - see DBWR trace file
ORA-01110: data file 12: '+DATA/DBINTERNALS/DATAFILE/undotbs1.260.875748227'

SQL> alter database datafile 12 offline;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
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'
Process ID: 23713
Session ID: 237 Serial number: 25274

So we are hosed. We will copy and paste the segment names from that DBA_ROLLBACK_SEGS into the _CORRUPTED_ROLLBACK_SEGS parameter and try to start it again:

SQL> alter system set _corrupted_rollback_segments='_SYSSMU36_168785881$',
 '_SYSSMU35_1475960130$', '_SYSSMU34_908380251$', '_SYSSMU33_2122985546$', 
'_SYSSMU32_228305016$', '_SYSSMU31_2744769455$', '_SYSSMU30_2141723264$', 
'_SYSSMU29_2359381190$', '_SYSSMU10_732010430$', '_SYSSMU9_624586700$', 
'_SYSSMU8_1927760754$', '_SYSSMU7_3527658720$', '_SYSSMU6_3377313595$', 
'_SYSSMU5_1147562966$', '_SYSSMU4_1750859549$', '_SYSSMU3_1431909199$', 
'_SYSSMU2_911282856$', '_SYSSMU1_4079383768$';
alter system set _corrupted_rollback_segments='_SYSSMU36_168785881$', 
'_SYSSMU35_1475960130$', '_SYSSMU34_908380251$', '_SYSSMU33_2122985546$', 
'_SYSSMU32_228305016$', '_SYSSMU31_2744769455$', '_SYSSMU30_2141723264$', 
'_SYSSMU29_2359381190$', '_SYSSMU10_732010430$', '_SYSSMU9_624586700$', 
'_SYSSMU8_1927760754$', '_SYSSMU7_3527658720$', '_SYSSMU6_3377313595$', 
'_SYSSMU5_1147562966$', '_SYSSMU4_1750859549$', '_SYSSMU3_1431909199$', 
'_SYSSMU2_911282856$', '_SYSSMU1_4079383768$'
                 *
ERROR at line 1:
ORA-00911: invalid character

Well, that didn’t work out so well. From this error, I have no idea where the problem is. So I save the SQL script and edited it. I added a new line after each segment and ran it again:

SQL> 

  1  alter system set _corrupted_rollback_segments=
  2  '_SYSSMU36_168785881$',
  3  '_SYSSMU35_1475960130$',
  4   '_SYSSMU34_908380251$',
  5   '_SYSSMU33_2122985546$',
  6   '_SYSSMU32_228305016$',
  7   '_SYSSMU31_2744769455$',
  8   '_SYSSMU30_2141723264$',
  9   '_SYSSMU29_2359381190$',
 10   '_SYSSMU10_732010430$',
 11   '_SYSSMU9_624586700$',
 12   '_SYSSMU8_1927760754$',
 13   '_SYSSMU7_3527658720$',
 14   '_SYSSMU6_3377313595$',
 15   '_SYSSMU5_1147562966$',
 16   '_SYSSMU4_1750859549$',
 17   '_SYSSMU3_1431909199$',
 18   '_SYSSMU2_911282856$',
 19*  '_SYSSMU1_4079383768$'

SQL> /
alter system set _corrupted_rollback_segments=
                 *
ERROR at line 1:
ORA-00911: invalid character

Much easier to figure out where the issue is. You would be surprised at the number of recovery efforts that are abandoned due to trivial issues like this. Anyway, I fixed my script, restarted the database and it is open! The table I was loading with my precious Dilbert strips still isn’t accessible, but I will work on that on my next increasingly inaccurately named blog.

  1  alter system set "_corrupted_rollback_segments"=
  2  '_SYSSMU36_168785881$',
  3  '_SYSSMU35_1475960130$',
  4   '_SYSSMU34_908380251$',
  5   '_SYSSMU33_2122985546$',
  6   '_SYSSMU32_228305016$',
  7   '_SYSSMU31_2744769455$',
  8   '_SYSSMU30_2141723264$',
  9   '_SYSSMU29_2359381190$',
 10   '_SYSSMU10_732010430$',
 11   '_SYSSMU9_624586700$',
 12   '_SYSSMU8_1927760754$',
 13   '_SYSSMU7_3527658720$',
 14   '_SYSSMU6_3377313595$',
 15   '_SYSSMU5_1147562966$',
 16   '_SYSSMU4_1750859549$',
 17   '_SYSSMU3_1431909199$',
 18   '_SYSSMU2_911282856$',
 19*  '_SYSSMU1_4079383768$' scope=spfile
SQL> /

System altered.

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> 

Comments are closed.