Recovering from Undo Tablespace Corruption

TEST@orclnew >insert into test_table select * from all_objects;

43224 rows created.

TEST@orclnew >commit;

Commit complete.

TEST@orclnew >insert into test_table select * from all_objects;
insert into test_table select * from all_objects
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 44, block # 181)
ORA-01110: data file 44:
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\UNDOTBS02.DBF'


TEST@orclnew >conn / as sysdba
Connected.
SYS@orclnew AS SYSDBA>create undo tablespace undotbs1 datafile 'C:\Oracle\product\10.2.0\oradata\orclnew\UNDOTBS01.dbf' SIZE 5M;
create undo tablespace undotbs1 datafile 'C:\Oracle\product\10.2.0\oradata\orclnew\UNDOTBS01.dbf' SIZE 5M
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2032], [184549394], [184549394],
[8192], [0], [255], [0], [255]


SYS@orclnew AS SYSDBA>select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';

no rows selected

SYS@orclnew AS SYSDBA>shutdown immediate;
ORA-01122: database file 44 failed verification check
ORA-01110: data file 44: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\UNDOTBS02.DBF'
ORA-01251: Unknown File Header Version read for file number 44
SYS@orclnew AS SYSDBA>shutdown abort;
ORACLE instance shut down.
SYS@orclnew AS SYSDBA>startup mount exclusive
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1253072 bytes
Variable Size 322961712 bytes
Database Buffers 742391808 bytes
Redo Buffers 7135232 bytes
Database mounted.
SYS@orclnew AS SYSDBA>alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 44 - see DBWR trace file
ORA-01110: data file 44:
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\UNDOTBS02.DBF'


SYS@orclnew AS SYSDBA>select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';
select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY'
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only


SYS@orclnew AS SYSDBA>alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\UNDOTBS02.DBF' offline drop;

Database altered.

SYS@orclnew AS SYSDBA>alter database open;

Database altered.

SYS@orclnew AS SYSDBA>drop tablespace undotbs2 including contents and datafiles;
drop tablespace undotbs2 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS2' is currently in use

SYS@orclnew AS SYSDBA>create undo tablespace undotbs1 datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\UNDOTBS01.dbf' size 5M;
create undo tablespace undotbs1 datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\UNDOTBS01.dbf' size 5M
*
ERROR at line 1:
ORA-01543: tablespace 'UNDOTBS1' already exists


SYS@orclnew AS SYSDBA>drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SYS@orclnew AS SYSDBA>create undo tablespace undotbs1 datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\UNDOTBS01.dbf' size 5M;

Tablespace created.

SYS@orclnew AS SYSDBA>alter system set undo_tablespace=undotbs1;

System altered.

SYS@orclnew AS SYSDBA>drop tablespace undotbs2 including contents and datafiles;
drop tablespace undotbs2 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU8$' found, terminate dropping
tablespace


SYS@orclnew AS SYSDBA>select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';

SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU8$ NEEDS RECOVERY UNDOTBS2
_SYSSMU9$ NEEDS RECOVERY UNDOTBS2
_SYSSMU10$ NEEDS RECOVERY UNDOTBS2
_SYSSMU11$ NEEDS RECOVERY UNDOTBS2
_SYSSMU12$ NEEDS RECOVERY UNDOTBS2
_SYSSMU13$ NEEDS RECOVERY UNDOTBS2
_SYSSMU14$ NEEDS RECOVERY UNDOTBS2
_SYSSMU15$ NEEDS RECOVERY UNDOTBS2
_SYSSMU16$ NEEDS RECOVERY UNDOTBS2
_SYSSMU17$ NEEDS RECOVERY UNDOTBS2

10 rows selected.

SYS@orclnew AS SYSDBA>create pfile='C:\Oracle\product\10.2.0\admin\orclnew\pfile\initorclnew.ora' from spfile;

File created.

SYS@orclnew AS SYSDBA>show spfile
SP2-0158: unknown SHOW option "spfile"
SYS@orclnew AS SYSDBA>show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string C:\ORACLE\PRODUCT\10.2.0\DB_1\
DATABASE\SPFILEORCLNEW.ORA
SYS@orclnew AS SYSDBA>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Edit pfile :
#*.undo_management='AUTO'
_corrupted_rollback_segments =('_SYSSMU8$','_SYSSMU9$','_SYSSMU10$','_SYSSMU11$','_SYSSMU12$','_SYSSMU13$','_SYSSMU14$','_SYSSMU15$','_SYSSMU16$','_SYSSMU17$')

SYS@orclnew AS SYSDBA>startup mount exclusive pfile='C:\Oracle\product\10.2.0\admin\orclnew\pfile\initorclnew.ora'
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1253072 bytes
Variable Size 327156016 bytes
Database Buffers 738197504 bytes
Redo Buffers 7135232 bytes
Database mounted.
SYS@orclnew AS SYSDBA>alter database open;

Database altered.

SYS@orclnew AS SYSDBA>drop tablespace undotbs2 including contents and datafiles;

Tablespace dropped.

SYS@orclnew AS SYSDBA>show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string UNDOTBS1
SYS@orclnew AS SYSDBA>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orclnew AS SYSDBA>startup pfile='C:\Oracle\product\10.2.0\admin\orclnew\pfile\initorclnew.ora'
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1253072 bytes
Variable Size 327156016 bytes
Database Buffers 738197504 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SYS@orclnew AS SYSDBA>show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SYS@orclnew AS SYSDBA>conn test/test
Connected.
TEST@orclnew >insert into test_table select * from all_objects;

43224 rows created.