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

Your Oracle database is down and cannot be up due to following error,

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

In Oracle alert log, you will find the following error,

KCF: write/open error block=0x69 online=1
     file=2 H:\ORACLE\DEV\SAPDATA2\ROLL.DATA2
     error=27072 txt: 'OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 33) The process cannot access the file because another 
process has locked a portion of the file.'
Automatic datafile offline due to write error on
file 2: H:\ORACLE\DEV\SAPDATA2\ROLL.DATA2
Thu Sep 18 02:22:40 2014
Errors in file f:\oracle\dev\saptrace\background\dev_smon_13231.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: 'H:\ORACLE\DEV\SAPDATA2\ROLL.DATA2'

Thu Sep 18 02:22:41 2014
ORA-376 encountered when generating server alert SMG-3503
Thu Sep 18 02:22:55 2014
Errors in file f:\oracle\dev\saptrace\usertrace\dev_ora_13231.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'H:\ORACLE\DEV\SAPDATA2\ROLL.DATA2'
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'H:\ORACLE\DEV\SAPDATA1\ROLL.DATA1'

Solution

In our case, the Oracle database was used for SAP application. So, if you are using Oracle for SAP application – You can also refer to SAP Note 328785.

1. Open SQLPLUS.

sqlplus /nolog
conn /as sysdba

2. Identify which data files are in RECOVER status.

 
SQL> SELECT NAME FROM V$DATAFILE WHERE STATUS = 'RECOVER';

NAME
----------------------------------------------------------------

H:\ORACLE\DEV\SAPDATA2\ROLL.DATA2
H:\ORACLE\DEV\SAPDATA2\R2.DATA2

3. If you received the result as RECOVER, execute the following command to recover the data files. You need to recover to all RECOVER needed data files.

RECOVER DATAFILE 'H:\ORACLE\DEV\SAPDATA2\ROLL.DATA2';
ORA-00279: change 608932800 generated at 09/18/2014 01:43:05 needed 
for thread 1
ORA-00289: suggestion : H:\ORACLE\DEV\SAPARCH\DEVARCHARC12231_0668998969.001
ORA-00280: change 608932800 for thread 1 is in sequence #112231

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'H:\ORACLE\DEV\SAPDATA2\ROLL.DATA2'

ORA-01112: media recovery not started

4. Now check for any data files in OFFLINE status

SQL> SELECT NAME FROM V$DATAFILE WHERE STATUS = 'OFFLINE';

NAME
--------------------------------------------------------------------------------

H:\ORACLE\DEV\SAPDATA2\ROLL.DATA2
H:\ORACLE\DEV\SAPDATA2\R2.DATA2

5. Execute below command to switch any OFFLINE data files to ONLINE.

SQL> alter database datafile 'H:\ORACLE\DEV\SAPDATA2\ROLL.DATA2' online;

Database altered.

6. Execute command from step 2 & 4 and if no output coming out means you can now start Oracle database!

SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE STATUS = 'OFFLINE';

no rows selected

SQL> SELECT NAME FROM V$DATAFILE WHERE STATUS = 'RECOVER';

no rows selected