ORA-00376: file 2 cannot be read at this time ,

ORA-01548 UNDO “Needs Recovery”:

Error:

Wed May 21 07:52:20 2014
Errors in file c:\oracle\product\10.2.0\admin\ \bdump\ _cjq0_3412.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: ‘C:\ORACLE\ORADATA\ \DATA\ \UNDOTBS01.DBF’

Wed May 21 07:52:20 2014
Errors in file c:\oracle\product\10.2.0\admin\ \bdump\ _cjq0_3412.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: ‘C:\ORACLE\ORADATA\ \DATA\ \UNDOTBS01.DBF’

SQL> select file_name from dba_data_files where tablespace_name=’UNDOTBS1′;

FILE_NAME
 --------------------------------------------------------------------------------
 C:\ORACLE\ORADATA\ \DATA\ \UNDOTBS01.DBF
 C:\ORACLE\ORADATA\ \DATA\ \UNDOTBS02.DBF

SQL>

SQL> drop tablespace UNDOTBS1 including contents and datafiles;
 drop tablespace UNDOTBS1 including contents and datafiles
 *
 ERROR at line 1:
 ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping tablespace

SQL>

Solution:

From the below SQL it shows that UNDOTBS1 contains a few corrupt undo segments

SQL> select segment_name,status,tablespace_name from  dba_rollback_segs where status not in (‘ONLINE’,’OFFLINE’);

SEGMENT_NAME                   STATUS           TABLESPACE_NAME
 ------------------------------ ---------------- ------------------------------
 _SYSSMU1$                      NEEDS RECOVERY   UNDOTBS1
 _SYSSMU2$                      NEEDS RECOVERY   UNDOTBS1
 _SYSSMU3$                      NEEDS RECOVERY   UNDOTBS1
 _SYSSMU4$                      NEEDS RECOVERY   UNDOTBS1
 _SYSSMU5$                      NEEDS RECOVERY   UNDOTBS1
 _SYSSMU6$                      NEEDS RECOVERY   UNDOTBS1
 _SYSSMU7$                      NEEDS RECOVERY   UNDOTBS1
 _SYSSMU8$                      NEEDS RECOVERY   UNDOTBS1
 _SYSSMU9$                      NEEDS RECOVERY   UNDOTBS1
 _SYSSMU10$                     NEEDS RECOVERY   UNDOTBS1

10 rows selected.

SQL>

To get past this problem:

1. create a pfile from the current spfile,
2. change UNDO_MANAGEMENT  to ‘MANUAL’ and UNDO_TABLESPACE to ‘SYSTEM’ in the pfile
3. add the _corrupt_rollback_segments init parameter to the pfile    “_corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)”
4. startup the database using “startup pfile=’<location/name of pfile>’ “;
5. drop corrupt UNDOTBS1 uncluding contents and datafiles
6. create a new UNDO tablespace
7. shutdown database again, and startup using the existing spfile (not pfile)

SQL> create pfile from spfile;

File created.

SQL>

*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS2′
_corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)

SQL> startup pfile=’C:\oracle\product\10.2.0\db_1\database\INITwicustest.ora’;

ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size                  2065408 bytes
Variable Size             788532224 bytes
Database Buffers          452984832 bytes
Redo Buffers               14708736 bytes
Database mounted.
Database opened.
SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

SQL> create UNDO tablespace UNDOTBS1 datafile ‘C:\ORACLE\ORADATA\ \DATA\ \UNDOTBS01.DBF’ size 250M;

Tablespace created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\Wicus>sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed May 21 07:56:13 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Enter password:
 Connected to an idle instance.

SQL> startup
 ORACLE instance started.

Total System Global Area 1258291200 bytes
 Fixed Size                  2065408 bytes
 Variable Size             788532224 bytes
 Database Buffers          452984832 bytes
 Redo Buffers               14708736 bytes
 Database mounted.
 Database opened.
 SQL>