ORA-00376: file cannot be read at this time

I got ” ORA-00376: file cannot be read at this time ” error in Oracle database.

ORA-00376: file cannot be read at this time

Details of error are as follows.

ORA-00376:   File %s cannot be read at this time

Cause: attempting to read from a file that is not readable. 
Most likely // the file is offline.

Action: Check the state of the file. Bring it online 

ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: '/u01/test.dbf'
ORACLE Instance PROD (pid = 8) - Error 376 encountered while recovering transaction (25, 37) on object 109359.
Tue Jul 12 10:35:46 2011

file cannot be read at this time

This ORA-00376 errors are related with the attempting to read from a file that is not readable.

Datafile has gone offline. To confirm, run the queries below:

SQL>Select status,file#,name from v$datafile where file#={file value reported in ORA-01110}


SQL> Select status,file#,name from v$datafile where file#=9

    STATUS  FILE#    NAME
    ---------- ------- --- ---------
    Recover  9      /u01/test.dbf  



SQL>column checkpoint_change# format 99999999999999999

SQL>select file#, status, fuzzy, checkpoint_time, checkpoint_change#,
resetlogs_change#, resetlogs_time from v$datafile_header where file#=9


FILE# STATUS FUZ CHECKPOIN CHECKPOINT_CHANGE# RESETLOGS_CHANGE# RESETLOGS
---------- ------- --- --------- ------------------ ----------------- ---------
9 OFFLINE YES 20-JUL-11 8517808305328 4263932 21-JUN-11

Note: checkpoint_change# is the SCN at which your datafile is current at. In tis case it’s 8517808305328. So we need to apply changes above this SCN number, 8517808305328,  to bring the file online.

1. When Database is in ARCHIVELOG Mode

Step a

Find the archive log which contains this SCN. You would need the archive log from this sequence number till the current changes in the database. Note you need to add 1 as we need to apply changes after this SCN:

SQL>Select sequence#,name from v$archived_log where
    {Checkpoint change# of the file from query1} + 1 between first_change# and next_change# ;


SQL>Select sequence#,name from v$archived_log where 8517808305328 + 1 between first_change# and next_change# ;


SEQUENCE# NAME
--------------------------------------------------------------------------------

68  /u01/app/archivelog/O1_MF_1_68_72FLC6FO_.ARC

SQL>Select name,sequence# from v$archived_log where sequence# >=68 ;

SQL> Select sequence# from v$log_history where 8517808305328 + 1 between first_change# and next_change# ;

SEQUENCE#
----------
68



SQL> Select a.member ,a.group# ,b.status,sequence#  from v$logfile a ,v$log b
      where sequence# >=68 and a.group#=b.group# ;

SQL> recover datafile 9;
ORA-00279: change 8517808305328 generated at 07/20/2011 14:45:13 needed for
thread 1
ORA-00289: suggestion :
/u01/app/archivelog/O1_MF_1_68_%U_.ARC

ORA-00280: change 8517808305328 for thread 1 is in sequence #68

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.

SQL>Alter database datafile 9 online ;

SQL>Select * from v$recover_file ;

SQL> Select sequence# from v$log_history where 8517808305328 + 1 between first_
change# and next_change# ;

SEQUENCE#
----------
68

Select a.member ,a.group# ,b.status,sequence# from v$logfile a ,v$log b
where sequence# >=68 and a.group#=b.group# ;


SQL> Select a.member ,a.group# ,b.status,sequence# from v$logfile a ,v$log b
2 where sequence# >=68 and a.group#=b.group# ;

MEMBER
-----------------------------------------------------------------------------

GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
/u01/oradata/test/REDO02.LOG
2 INACTIVE 67

/u01/oradata/test/REDO01.LOG
1 CURRENT 68


SQL>recover datafile 9 ;

SQL>Alter database datafile <fileno> online ;

Do you want to learn Oracle Database for Beginners, then read the following articles.