Undo Segment is in Needs Recovery or Undo Segments are corrupted

Undo Segment is in Needs Recovery/Undo Segments are corrupted

 

Database is shutdown and can’t start it, asking for Undo recovery while dropping Undo tablespace

Dropping a Undo tablespace give message

ORA-01548: active rollback segment
Or
Undo segment shows status as needs recovery

Cause

The issue could happen if the datafile on which the undo segments reside is offline and the transaction cannot be rolled backed since the file is offline

Or

This could also happen if there is any issue in the Undo segment itself

Solution

Check if the Undo segment status first
—————————————-

SQL> select segment_name,status,tablespace_name
from dba_rollback_segs
where status not in (‘ONLINE’, ‘OFFLINE’) ;

SEGMENT_NAME STATUS TABLESPACE_NAME
—————– ———– —————-
_SYSSMU3$ NEEDS RECOVERY UNDO01

In the above example Undo segment _SYSSMU3$ is in Needs recovery status.
This segment belongs to Undo tablespace UNDO01

Check the status of the datafile present in the tablespace UNDO01

SQL> select status, name, file# from v$datafile where ts# in (Select ts# from v$tablespace where name=’UNDO01′ );

STATUS NAME FILE#
——- ————————————————– ———-
ONLINE     /u01/undo01_01.dbf 56
RECOVER  /u02/undo01_03.dbf 77

So clearly one file is in Recover status

Option A

=======

If the database is in Archive log mode and you have all the required archive log mode you can do the following :-

Find if you have all the required Archive logs on disk or If using Rman ensure they exist in the backup

Query 1
———

SQL> Select checkpoint_change# from v$datafile_header where file_id=<file# in Recover status from previous query> ;

Now find these changes are present in which Archive log
Query 2
———

SQL> select sequence#,thread#,name from v$archived_log

where <checkpoint_change# from query 1> between first_change# and next_change# ;

Ensure you have all the archive logs starting from this sequence# till the current sequence# in your database

For example
==========

SQL> select checkpoint_change#,file#,status from v$datafile_header where file#=77;

CHECKPOINT_CHANGE# FILE# STATUS
—————— ———- ——-
2103113 4 OFFLINE     77

SQL>Select sequence#,thread#,name from v$archived_log where 2103113
between first_change# and next_change# ;

SEQUENCE# THREAD#  NAME
——————————————————————————–

96 1 /u01/arch/O1_MF_1_96_6OKHP.Arc

 If using rman

Check if the archive log from this sequence till current sequence is available

RMAN> list backup of archivelog from sequence <No listed in query2>

RMAN> recover datafile <fileno> ;

RMAN> sql ‘alter database datafile <fileno> online’ ;

if using sqlplus
————-

Ensure the archive logs are present on disk

SQL> recover datafile <fileno> ;

Type AUTO and hit enter

Once recovery is done

SQL> alter database datafile <fileno> online ;

If the archive logs have been restored to a different location than the Default archive log destination your database is using then specify the same using set source command in sqlplus

SQL> set logsource “/u01/arch/newlocation” ;

SQL> recover datafile <fileno> ;

Type AUTO and hit enter

Once recovery is done

SQL> alter database datafile <fileno> online ;

 

Option B

If your database is in noarchivelog mode than better to open SR with Oracle, if it is test database than you can proced with below steps

If database database oradata folder size is less or manageable to take its backup while database is shutdown.

Once you took backup of oradata, now you proced for recovery

SQL> select segment_name,status,tablespace_name
from dba_rollback_segs
where status not in (‘ONLINE’, ‘OFFLINE’) ;

SEGMENT_NAME STATUS TABLESPACE_NAME
—————– ———– —————-
_SYSSMU3$ NEEDS RECOVERY UNDO01

 

Note: Add the following lines to pfile.

*.DB_BLOCK_CHECKSUM = FALSE

*._DB_ALWAYS_CHECK_SYSTEM_TS = FALSE

*.event=”10231 trace name context forever, level 10″

*.event=”10233 trace name context forever, level 10″

*._allow_resetlogs_corruption= true

_CORRUPTED_ROLLBACK_SEGMENTS=(‘_SYSSMU3$ ‘)

_offline_rollback_segments=(_SYSSMU3$)

Rename spfile.

Start database with restrict session.

SQL>Startup restrict

Create undo tablespace UNDOTBS2.

SQL> Create undo tablespace undotbs2 datafile ‘/u01/oradata/ocrl/unodtbs02.dbf’ size 1500M;

Make entry in the pfile

Modify the following parameters in pfile and restart the database.

Undo_management=auto

Undo_tablespace=’UNDOTBS2’

SQL> drop rollback segment “_SYSSMU3$”;

Rollback segment dropped.

SQL> drop tablespace undotbs1;

Tablespace dropped.

SQL> select name from v$tablespace;

NAME

——————————

SYSTEM

UNDOTBS2

SYSAUX

USERS

EXAMPLE

TEMP

6 rows selected.

Now the corrupted UNDO tablespace is droped.

SQL> select * from v$rollname;

Now take database export backup and create new database using dbca and import the database.

Advertisements

About Sher khan

Senior Oracle DBA, Oracle 10g , Oracle 11g OCE ,Oracle 12C and Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g , 11g and 12C. I am working in Oracle technology since 2004. Currently working in U.A.E Email: sher487@hotmail.com
This entry was posted in Oracle backup & recovery. Bookmark the permalink.

One Response to Undo Segment is in Needs Recovery or Undo Segments are corrupted

  1. Siva says:

    Very clear & good explanation !!

    Thanks,
    Siva.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s