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 = 'Rowe';
select first_name from employees where last_name = 'Rowe'
*
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 = 'Rowe';
FIRST_NAME
--------------------
Scott
It's working now.
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?
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.