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
So you need changes from sequence 68 till current redo to be present to recover this file
Step b
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 ;
Will show no entry for file 9.
2. When Database is in NOARCHIVELOG Mode
For databases running in NOARCHIVELOG mode the redo log information gets overwritten. So if the redo logs information for the sequence number that is required has been overwritten then you cannot bring the file online . If the datafile contains important data explore option of restoring the last good cold backup or explore options of getting the data present in datafile from some other means For example previous export
If the sequence required for the recovery has not been overwritten and is still present in the redo logs you can do the following
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 ;
When prompted for recovery Enter the path of the redo having sequence 68
/u01/oradata/test/REDO02.LOG
Hit Enter
Once its recovery
SQL>Alter database datafile <fileno> online ;
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )
Hi,
I am learning to become an DBA. I have
Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 – Production
Version 18.3.0.0.0
Originally when i tried to move the datafile online I got the following message; that’s why tried to rename it offline. I tried to attempt to rename datafile. I made the tablespace offline then renamed the datafile; recovered data file brought it back online.
Select status,file#,name from v$datafile where file#=16;
STATUS FILE#
——- ———-
NAME
——————————————————————————–
ONLINE 16
H:\MLAPPS_DATA\MLAPPS_DATA_DATAFILE1.DBF
When I try to query the database; I get the following error ; as you can see the DBF is ONLINE
Can you please help fix this issue? I have a
ERROR MESSAGE:
ORA-00376: file 16 cannot be read at this time
ORA-01110: data file 16: ‘H:\MLAPPS_DATA\MLAPPS_DATA_DATAFILE1.DBF’
00376. 00000 – “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
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#=16;
FILE# STATUS FUZ CHECKPOIN CHECKPOINT_CHANGE# RESETLOGS_CHANGE# RESETLOGS
———- ——- — ——— —————— —————– ———
16 ONLINE NO 03-OCT-22 52185045079 1423580 01-DEC-18
SQL> Select sequence#,name from v$archived_log where 52185045079+ 1 between first_change# and next_change# ;
no rows selected
SQL> Select name,sequence# from v$archived_log;
no rows selected
SQL> Select sequence# from v$log_history where 52185045079+ 1 between first_change# and next_change# ;
no rows selected
Sorry missed to place the error message I got when I tried to move the datafile ONLINE
ERROR at line 1:
ORA-00439: feature not enabled: online move datafile
Hi,
You can use the online move datafile above Oracle 12C as follows.
ALTER DATABASE MOVE DATAFILE FILE#;
ALTER DATABASE MOVE DATAFILE 63;
OR specify the Datafile path as follows.
ALTER DATABASE MOVE DATAFILE 63 TO ‘+RECO’ REUSE;
Hi,
I tried the move
SQL> ALTER DATABASE MOVE DATAFILE 21 to ‘H:\MLAPPS_DATA\MLAPPS_DB_UNDO\MLAPPS_UNDO_TS2.DBF’ REUSE;
ALTER DATABASE MOVE DATAFILE 21 to ‘H:\MLAPPS_DATA\MLAPPS_DB_UNDO\MLAPPS_UNDO_TS2.DBF’ REUSE
*
ERROR at line 1:
ORA-00439: feature not enabled: online move datafile