How to Resolve ORA-00376: file cannot be read at this time

July 26, 2022December 12, 2022
2 Comments

ORA-00376 ORA-01110

Someone tried to perform a query, but it failed with ORA-00376 and ORA-01110.

 SQL> select first_name from employees where last_name = 'Chen';
 select first_name from employees where last_name = 'Chen'
             *
 ERROR at line 1:
 ORA-00376: file 13 cannot be read at this time
 ORA-01110: data file 13:
 '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf'

ORA-00376 means that the data file you want to use is not readable at this moment, you should check its status and solve it first.

Additionally, ORA-01110 indicates the problematic data file, in this case, it's file #13.

Let's see the status of this data file by querying V$DATAFILE.

 SQL> column name format a60;
 SQL> select name, status from v$datafile order by file#;
 
 NAME                                                         STATUS
 ------------------------------------------------------------ -------
 /u01/app/oracle/oradata/ORCLCDB/ORCLPDB/system01.dbf         SYSTEM
 /u01/app/oracle/oradata/ORCLCDB/ORCLPDB/sysaux01.dbf         ONLINE
 /u01/app/oracle/oradata/ORCLCDB/ORCLPDB/undotbs01.dbf        ONLINE
 /u01/app/oracle/oradata/ORCLCDB/ORCLPDB/users01.dbf          ONLINE
 /u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf        RECOVER
 /u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example02.dbf        ONLINE
 
 6 rows selected.

As we can see, there's one data file is at RECOVER, not ONLINE, which means, it needs to be recovered before online.

Solution

If the data file was at OFFLINE, we can just bring it online in one step. But in this case, it's at RECOVER, we need to take two steps to make the data file go back to work.

1. Recover Data File

We specifically recover this data file.

 SQL> recover datafile 13;
 Media recovery complete.
 SQL> select status from v$datafile where file# = 13;
 
 STATUS
 -------
 OFFLINE

2. Online Data File

Then we bring it online.

 SQL> alter database datafile 13 online;
 
 Database altered.
 
 SQL> select status from v$datafile where file# = 13;
 
 STATUS
 -------
 ONLINE

We issue the query again.

 SQL> select first_name from employees where last_name = 'Chen';
 
 FIRST_NAME
 --------------------
 Ed

It's working now.

JavaScript is not enabled. Please enable it and reload the page.

2 thoughts on “How to Resolve ORA-00376: file cannot be read at this time”

Nisha R October 3, 2022 at 11:36 pm
Reply
Hi,
I have Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 – Production Version 18.3.0.0.0
Originally I tried to move the datafile online I got the following message;
ORA-00439: feature not enabled: online move datafile
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
My database file is online
SQL> select status from v$datafile where file# =16;
STATUS
——-
ONLINE
I get the following error when i try to query the database:
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
Can you please help fix this issue?
1. Ed Chen October 4, 2022 at 1:40 am
  Reply
  It looks like that the file can be read, but cannot be written. You should check the file permission set and ownership. For example:
  [oracle@test ~]$ ls -l /u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf -rw-r----- 1 oracle oinstall 209723392 Jul 21 03:56 /u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf
  In which, the ownership is oracle:oinstall and permission set is 640.
  By the way, if the entire tablespace was offlined, you should bring it online after moving, don’t online datafiles individually.