ORA-01110 data file %s

Question: What is the cause of the error “ORA-01110 data file %s”? How do I resolve this?

    SQL> alter database open resetlogs;
    alter database open resetlogs
    *
    ERROR at line 1:
    ORA-01194: file 14 needs more recovery to be consistent
    ORA-01110: data file 14: ‘/u01/EQ2/EQ2_01.dbf’

Answer: According to the oerr utility the cause of the “ORA-01110 data file %s” error could be due to the reason below.

    01110, 00000, “data file %s: ‘%s'”
    Reporting file name for details of another error. The reported name can be of the old file if a data file move operation is in progress.
    See associated error message.

There can be a number of reasons for this error.

Reason 1 : Not all required archives were applied. Make sure that the required archives are available and restart the recovery process.

If you do not have the required archive logs, perform an incomplete cancel-based recovery.

    RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

    ORA-00279: change 9199631104 generated at 08/11/2015 12:01:10 needed for thread 1
    ORA-00289: suggestion : arch1_6066601_758161962.dbf
    ORA-00280: change 9199631104 for thread 1 is in sequence #6066601
    Specify log: {=suggested | filename | AUTO | CANCEL}
    CANCEL
    Log applied.
    Media recovery complete.

When RMAN prompts for archive log files that are missing, use CANCEL recovery option and open the database with RESTLOGS option.

Your database should open up and no longer give the ORA- error.

Reason 2 : If the “ORA-01110 data file %s” error is seen on the DataGuard or Standby Database this is probably due to the datafile being inaccessible to the database due to a storage issue.

Once the datafile is brought back online and the archives shipped and applied to the DataGuard or Standby environment the datafile will become consistent.

First verify on the PRIMARY database to make sure that the logs are being shipped and the LOG_ARCHIVE_DEST_STATE_2 is set to enabled. You can further check the log file on the destination DG database to confirm.

PRIMARY DB

    SQL> show parameter LOG_ARCHIVE_DEST_STATE

    NAME TYPE VALUE
    ———————— —— ——-
    log_archive_dest_state_1 string ENABLE
    log_archive_dest_state_2 string DEFERRED

    — Enable DataGuard Archive Destination
    SQL> alter system set log_archive_dest_state_2=’ENABLE’ scope=both;

On the Dataguard/Standby database ensure that the recovery process is started up.

    SQL> STARTUP MOUNT;
    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Once the archive logs are applied the datafiles will become consistent and the database can be opened up.

Related OraTips

Oracle Grid Cluster Health Monitor (oclumon) – The Oracle Grid Cluster Health Monitor (CHM) stores operating system metrics in the CHM repository for all nodes in a RAC cluster. It stores information on CPU, memory, process, network and other OS data.

Oracle 12c Deprecated Database Parameters – When upgrading to Oracle 12c it’s good to be familiar with the deprecated parameters. These are not really desupported, just that they will be de-supported in future versions of the Database. Read more ..

Other Articles

Oracle 12c RAC: New Features Oracle has come a long way from its inception of the 9i RAC database to its current 12c version. I remember the days when we had to deal with RAC related issues, ranging from performance to stability which gradually improved as this product matured. Learn more about the new feature now available with RAC.

Oracle 12c Database: What is Oracle 12c? There has been a lot of buzz recently about Oracle 12c. Everyone in any way related to Oracle technologies, really want to know what Oracle 12c is and why it is important. Well, simply put it is the next generation naming convention for all Oracle products. Learn more about it here.


Note: This Oracle documentation was created for reference for use by Vitalsofttech DBA’s. If you have any questions please post by clicking on the ASK A QUESTION link above.

Leave a Reply

Your email address will not be published.