Recovering UNDO Tablespace

Hello guys,

Hope you all doing well.
What would you do if your UNDO tablespace is deleted physically.

Nothing will work and database would be hanged totally.

Here is the steps what you would follow to over come and made database up and running.

SQL> startup
Total System Global Area  753278976 bytes
Fixed Size                  1374724 bytes
Variable Size             385877500 bytes
Database Buffers          360710144 bytes
Redo Buffers                5316608 bytes
SQL>
SQL>
ERROR at line 1:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: 'D:\APP\Administrator\ORADATA\testdb\UNDOTBS01.DBF'

SQL> shutdown abort;
Oracle Instance closed.

SQL> startup mount;

Now, this time our database is in mount stage.
As of now, just go to RMAN and perform restore and recover operation for deleted UNDO tablespace.

C:\Users\Administrator>rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Oct 16 16:27:35 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TESTDB (DBID=2904859346, not open)
RMAN> restore tablespace undotbs1;
Starting restore at 16-OCT-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to D:\APP\Administrator\ORADATA\testdb\UNDOTBS01.DBF
channel ORA_DISK_1: reading from backup piece D:\APP\Administrator\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2012_10_16\O1_MF_NNNDF_TAG20121016T144421_87VO9P01_.BKP
channel ORA_DISK_1: piece handle=D:\APP\Administrator\FLASH_RECOVERY_AREA\testdb\BACKUPSE
T\2012_10_16\O1_MF_NNNDF_TAG20121016T144421_87VO9P01_.BKP tag=TAG20121016T144421
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 16-OCT-12

Now, you can check UNDO tablespace to it's relevant location and you will find all is there.

So, this is time to recover the same.

RMAN> recover tablespace undotbs1;
Starting recover at 16-OCT-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 16-OCT-12
RMAN>

Finally just open your database.

SQL> alter database open;
Database Opened.

Great.............Needful Done.

Happy Recovery................................

Regards,
Raj Kumar