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.