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>
