Skip to content

How to Resolve ORA-01113: file needs media recovery

  • by

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.

Leave a Reply

Your email address will not be published.