• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

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

by admin

The Problem

New Undo tablespace was created and a attempt is made to drop old undo tablespace. Dropping the old Undo tablespace give message:

ORA-01548: active rollback segment

Or

Undo segment shows status as needs recovery

The Solution

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.

Check Undo Segment

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     /[UNDO]/[FILE_NAME].dbf   56
RECOVER  /[UNDO]/[FILE_NAME].dbf     77

So clearly one file is in Recover status. If the database is in Archive log mode and you have all the required archive log mode you can do the following:

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

SQL> Select checkpoint_change# from v$datafile_header where file#=[file#(number) in RECOVER status from previous query];

2. Now find these changes are present in which Archive log:

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 /[DIRECTORY]/[FILE_NAME].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 step 2 query above];
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 execute below query:

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 "/[DIR]/newlocation" ;
SQL> recover datafile [fileno] ;

Type AUTO and hit enter. Once recovery is done, execute the below query:

SQL> alter database datafile [fileno] online ;
How to Drop Undo Tablespace in Oracle Database

Filed Under: oracle

Some more articles you might also be interested in …

  1. Oracle RAC : understanding split brain
  2. Oracle database 12c : How to Drop Partition(s)
  3. Oracle Data Guard – Tempfiles created at primary are not automatically created at standby
  4. Oracle 11g new feature – ASM Fast Rebalance
  5. Automatic Stop of Oracle Database (dbshut) not working in CentOS/RHEL 7 with systemd
  6. How to split BCV and open oracle ASM database
  7. How To Create “A CRS Managed” ACFS FileSystem On Oracle RAC Cluster (ASM/ACFS 11.2)
  8. Oracleasm Service Fails to Start After Upgrade to oracleasm-support-2.1.11-1 RPM Package
  9. Oracle 11G RMAN – Understanding UNDO backup optimisation
  10. How to Move a Datafile from Filesystem to ASM Using ASMCMD CP Command

You May Also Like

Primary Sidebar

Recent Posts

  • What are /dev/zero and /dev/null files in Linux
  • grpck command – Remove corrupt or duplicate entries in the /etc/group and /etc/gshadow files.
  • xxd command – Expressed in hexadecimal form
  • sesearch: command not found

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright