Undo Segments are Marked as Needing Recovery .

Scenario :


1>
The application end users receive errors due to this and they are unable to start any transaction since all the undo segments are marked as needing recovery.


2>
This goal walks you through changing the default undo tablespace from UNDOTBS1 to another undo tablespace. At the end of the exercise, you will drop the existing undo tablespace and datafiles and be left with a new undo tablespace in place.
This procedure requires shutting down the database.


This note was written because of a reoccurring problem with a fractured block(reported in v$backup_corruption) during RMAN backups.



Live example on my production box :******


An issue occured on a 9.2.0.8 DB with one of the datafile which is part of the UNDO tablespace needing recovery due to an underlying file system IO issue.

Restore of the datafiles involved did not work due to issues with the backup media
and the RMAN setup on that server.

So it was not feasible to troubleshoot a restore from a backup in the time frame given to fix the issue:

The alert log reports errors such as the below:

ORA-00604: error occurred at recursive SQL level 1
ORA-01115: IO error reading block from file 2 (block # 686)
ORA-01110: data file 2: '/ora/data4/BATR1SI/BATR1SIundots01.dbf'
ORA-27091: skgfqio: unable to queue I/O
ORA-27072: skgfdisp: I/O error
SVR4 Error: 5: I/O error
Additional information: 685
Mon Mar 15 02:25:45 2010
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as needs recovery



***********

Impact :

The application end users receive errors due to this and they are unable to start any transaction since all the undo segments are marked as needing recovery

ERROR
-------------
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/ora/data4/BATR1SI/BATR1SIundots01.dbf'
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/ora/data4/BATR1SI/BATR1SIundots01.dbf'


*************************************************************


Solution employed was to


(a) Create a new UNDO tablespace

(b) Switch to manual UNDO management

(c) Offline the undo segments needing recovery

(d) Dropping the old UNDO tablespace

(e) Switching back to automatic UNDO management




1. Determine the size of the datafile(s) for your current undo tablespace "UNDOTBS1":

SQL> select bytes, name from v$datafile where name like '%UNDO%';

BYTES NAME
-------------- ----------------------------------------------------
314572800 /ora/data4/BATR1SI/BATR1SIundots01.dbf



2.

Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.

SQL> create undo tablespace UNDOTS2
datafile '/ora/data4/BATR1SI/BATR1SIundots02.dbf' size 500M autoextend on;



3.

Edit your init.ora file and change the parameter "undo_tablespace=" so it points to the newly created tablespace. You may need to create a pfile first:

SQL> create pfile='/ora/data4/BATR1SI/pfileorcl2.ora' from spfile='/ora/data4/BATR1SI/SPFILEORCL2.ORA';

File created.

Change undo_tablespace=UNDOTBS2
Change undo_management=MANUAL


Setting undo_management now ensures the old rollback segments can be taken offline and avoids editing the pfile and restarting the instance again in Step 7.


4. Arrange a time when the database can be shutdown cleanly and perform a shutdown immediate.


5. Startup the database (specify the pfile if you created one in step 3.)

SQL> startup pfile='/ora/data4/BATR1SI/pfileorcl2.ora';


6. Confirm the new tablespace is in use:

SQL> show parameter undo_tablespace

NAME TYPE VALUE
------------- -------------- -----------------
undo_tablespace string UNDOTBS2


7. Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline. The segments in the new tablespace may also show offline.


SQL>select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;

OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
----- --------------------------- ------------------------------ -----------
PUBLIC _SYSSMU3$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU2$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU19$ UNDOTBS2 OFFLINE

....etc.

If the old segments are online, then they must be taken offline:

SQL>alter rollback segment "_SYSSMU3$" offline;
SQL>alter rollback segment "_SYSSMU2$" offline;


This should be executed for all online rollback segments in the old tablespace.


8. Provided all the segments in the old undo tablespace are offline, you can now drop the old undo tablespace:

SQL>drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.


9. Recreate your spfile with the new undo_tablespace value and change undo_management to AUTO:

undo_management='AUTO'
undo_tablespace='UNDOTBS2'


SQL> create spfile='/ora/data4/BATR1SI/SPFILEORCL2.ORA' from pfile='/ora/data4/BATR1SI/pfileorcl2.ora';

File created.



10. Shutdown the database (shutdown immediate) and restart it with the spfile.