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:
- create a pfile from the current spfile,
- change UNDO_MANAGEMENT to ‘MANUAL’ and UNDO_TABLESPACE to ‘SYSTEM’ in the pfile
- add the _corrupt_rollback_segments init parameter to the pfile “_corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)”
- startup the database using “startup pfile=’<location/name of pfile>’ “;
- drop corrupt UNDOTBS1 uncluding contents and datafiles
- create a new UNDO tablespace
- 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>
