ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Unable to Drop Undo tablespace Since Undo Segment is in Needs Recovery

Unable to Drop Undo tablespace Since Undo Segment is in Needs Recovery

原创 Linux操作系统 作者:spider0283 时间:2011-09-05 17:37:51 0 删除 编辑

Modified 16-FEB-2011     Type PROBLEM     Status MODERATED 

 [ID 1295294.1]

In this Document
  Symptoms
  Changes
  Cause
  Solution
     Option a 
     Option b


Platforms: 1-914CU;

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 11.2.0.2.0 - Release: 8.1.7 to 11.2
Information in this document applies to any platform.

Symptoms


Dropping a Undo tablespace give message 

ORA-01548: active rollback segment

Or

Undo segment shows status as needs recovery

Changes

New Undo tablespace was created and a attempt is made to drop old undo tablespace 

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
----------------------------------------


select segment_name,status,tablespace_name from dba_rollback_segs where status not in ('ONLINE','OFFLINE') ;

_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

Select status ,name,file# from v$datafile where ts# in (Select ts# from v$tablespace where name='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= ;

Now find these changes are present in which Archive log

Query 2
---------
SQL>Select sequence#,thread#,name from v$archived_log where
  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

Rman> recover datafile ;

Rman> sql 'alter database datafile online' ;

if using sqlplus
-------------

Ensure the archive logs are present on disk

SQL>recover datafile ;

Type AUTO and hit enter

Once recovery is done

SQL>Alter database datafile 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 ;

Type AUTO and hit enter

Once recovery is done

SQL>Alter database datafile online ;


Option b

=========

If database is in No archive log mode and the redo log has been reused

Open a ticket with oracle Support and explore the options

You can Upload the following trace file while opening the ticket

SQL>Alter session set tracefile_identifier='corrupt';

SQL>Alter system dump undo header "";

Go to udump

ls -lrt *corrupt*

Upload this trace file

Also upload the alert log file



Show Related Information Related


Products
  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
Errors
ORA-1548

Back to topBack to top

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/38267/viewspace-706685/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-29

  • 博文量
    194
  • 访问量
    627504