ORA-01113 ORA-01110
Tried to bring a data file online, but it failed with ORA-01113 and ORA-01110.
SQL> alter database datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf' online;
alter database datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf' online
*
ERROR at line 1:
ORA-01113: file 13 needs media recovery
ORA-01110: data file 13:
'/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf'
Even when we tried to bring the tablespace online, we got the same error.
SQL> alter tablespace example online;
alter tablespace example online
*
ERROR at line 1:
ORA-01113: file 13 needs media recovery
ORA-01110: data file 13:
'/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf'
ORA-01113 means that the data file you want to operate on it is behind the most recent change, you should recover it before using it.
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> select status from v$datafile where file# = 13;
STATUS
-------
RECOVER
Solution
To overcome status RECOVER, we can use RECOVER statement at SQL prompt to apply the most recent change to the data file.
SQL> recover datafile 13;
Media recovery complete.
A full path filename also works, if you'd like to make sure it's exactly the file.
SQL> recover datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf';
Media recovery complete.
Then check the status again.
SQL> select status from v$datafile where file# = 13;
STATUS
-------
OFFLINE
Good, we're making some progress here. Next, let's bring it online.
SQL> alter database datafile 13 online;
Database altered.
SQL> select status from v$datafile where file# = 13;
STATUS
-------
ONLINE
The data file is online again.