How to handle lost undo datafile

As DBA we are not suppose to drop any datafile without proper analysis and backup even if it is offline datafile. But as Human we make mistakes which sometimes cost us large. In this blog i am going to share how to recover lost undo datafile.

There are 2 scenarios lost undo datafile with active transactions and without active transactions. In active transaction case, the preferred option is to restore the datafile from backup and perform media recovery. If there is no active transactions then follow these steps to recover the database.

1. Query to check for active transactions,

SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ KTUXESTA Status,KTUXECFL Flags
FROM x$ktuxe WHERE ktuxesta!=’INACTIVE’;

select local_tran_id, state from dba_2pc_pending;

Both the above query should return “no rows”.

2. Make clean shutdown of database,

shutdown immediate;

3. create a parameter file from spfile

create pfile from spfile;

4. Update pfile with “undo_management=manual”

5. Start the database

startup pfile=$ORACLE_HOME/dbs/init.ora

6. Drop undo tablespace with datafile

drop tablespace including contents and datafiles;

7. Create a new undo tablespace,

CREATE UNDO TABLESPACE DATAFILE ‘’ SIZE 2M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

8. Shutdown the database

shutdown immediate;

9. Update pfile with “undo_management=AUTO”

10. Create spfile from pfile

create spfile from pfile;

11. Start the database

startup;

Reference
*********

How to Recover from a Lost Datafile with Different Scenarios [ID 198640.1]