How to recover undo tablespace

you may want to recreate or recover unto tablespace.
or
If you get
ORA-10564: tablespace UNDOTBS2
ORA-01110: data file 85: ‘K:\ORADATA\CRM\UNDOTBS02_1.DBF’
ORA-10560: block type ‘KTU SMU HEADER BLOCK’(from in second steps)

Here is how:

Normal Recreate

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 10 14:37:43 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> CREATE UNDO TABLESPACE undotbs2 DATAFILE 2 'F:\ORACLE\APP\D01\DBA\undotb201.DBF' SIZE 600M
AUTOEXTEND OFF
3 RETENTION NOGUARANTEE
4 BLOCKSIZE 8K;
Tablespace created.

SQL> ALTER SYSTEM SET undo_tablespace = UNDOTBS2;
System altered.

SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System
Global Area 209715200 bytes
Fixed Size 1248164 bytes
Variable Size 104858716 bytes
Database Buffers 100663296 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.

SQL> drop tablespace undotbs1;
Tablespace dropped.

if normal way is not possible then need to follow the second way
-------------------
if any active rollback segment is present then
need to following step's
----------------------------------------------------------------

1, Make sure the database is shut down.
sqlplus /nologSQL>connect sys/pass@orcl as sysdba
SQL> shutdown immediate
if normaly not shutdown
SQL> shutdown abort

2. mount database in restrict mode

SQL> STARTUP RESTRICT MOUNT

3. Try to offline drop the bad datafile.

SQL> ALTER DATABASE DATAFILE 'K:\ORADATA\ORCL\UNDOTBS2_02.DBF' OFFLINE DROP;

4. Open the Database

SQL> Alter database open;

5, Use this query to see how many rollback segments were corrupted:

SQL>select segment_name,status,tablespace_name
from dba_rollback_segs
where status='NEEDS RECOVERY';


SEGMENT_NAME STATUS TABLESPACE_NAME—————————— —————-
NEED RECOVERY UNDOTBS2_SYSSMU1$
NEEDS RECOVERY UNDOTBS2_SYSSMU2$
NEEDS RECOVERY UNDOTBS2_SYSSMU3$
NEEDS RECOVERY UNDOTBS2_SYSSMU4$
NEEDS RECOVERY UNDOTBS2_SYSSMU5$
NEEDS RECOVERY UNDOTBS2_SYSSMU6$
NEEDS RECOVERY UNDOTBS2_SYSSMU7$
NEEDS RECOVERY UNDOTBS2_SYSSMU8$
NEEDS RECOVERY UNDOTBS2_SYSSMU9$
NEEDS RECOVERY UNDOTBS2_SYSSMU10$

6, Add the following line to pfile:

_corrupted_rollback_segments =('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$')

Make sure your undo_management is manul
#undo_management=AUTO

and specify you want to use UNDOTBS1(new undo tablespace name) as undo tablespace.

undo_tablespace=UNDOTBS1

7.Start the database again:

SQL> STARTUP RESTRICT MOUNT pfile=C:\Oracle\Admin\ORCL\initORCL_18.ora

8. Open the Database

SQL> Alter database open;

9. Drop bad rollback segments

SQL> drop rollback segment "_SYSSMU1$";Rollback segment dropped.…
SQL> drop rollback segment "_SYSSMU2$";Rollback segment dropped.

like this way all of above

10, Check again

SQL> select segment_name,status,tablespace_name from dba_rollback_segs;

SEGMENT_NAME STATUS TABLESPACE_NAME—————————— —————- —————SYSTEM ONLINE SYSTEM_SYSSMU2$ ONLINE
UNDOTBS1_SYSSMU3$ ONLINE UNDOTBS1_SYSSMU4$ ONLINE UNDOTBS1_SYSSMU5$ ONLINE UNDOTBS1_SYSSMU6$ ONLINE UNDOTBS1_SYSSMU7$ ONLINE UNDOTBS1_SYSSMU8$ ONLINE UNDOTBS1_SYSSMU9$ ONLINE UNDOTBS1_SYSSMU10$ ONLINE UNDOTBS1_SYSSMU11$ ONLINE UNDOTBS1

there is no file of UNDOTBS2

11. Now drop bad undo TABLESPACE UNDOTBS2;

SQL> drop TABLESPACE UNDOTBS2 (old undo tablespace) ;

12, Recreate the undo rollback tablespace with all its rollback segments

SQL>CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE 'K:\oradata\ORCL\UNDOTBS01.DBF' SIZE 2000M reuse AUTOEXTEND ON ;
----new undo tablespace which name writen in pfile

13, Change undo tablespace

ALTER SYSTEM SET undo_tablespace = UNDOTBS1 ;

14. Remove the following line from pfile

_corrupted_rollback_segments =('_SYSSMU11$','_SYSSMU12$','_SYSSMU13$','_SYSSMU14$','_SYSSMU15$','_SYSSMU16 $','_SYSSMU17$','_SYSSMU18$','_SYSSMU19$','_SYSSMU20$')
and uncomment “undo_management=AUTO”
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1 (new undo tablespace)

15. Shutdown database

SQL>shutdown immediate;

16.
sqlplus /nolog
SQL>connect sys/PASS@ORCL as sysdba

SQL> STARTUP pfile=C:\Oracle\Admin\Orcl\initORCL_18.ora
ORACLE instance started.
Total System
Global Area 1620126452 bytes
Fixed Size 457460 bytes
Variable Size 545259520 bytes
Database Buffers 1073741824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.


17. Create spfile from pfile.