| 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.2Information 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
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 tablespaceCause
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 offlineOr
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 ;
Rman>List backup of archivelog from sequence
Rman> recover datafile
Rman> sql 'alter database datafile
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
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
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
Products
| ||
Related
Back to top