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

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.