User Managed Recovery - non-system datafile

Assumptions :
  • The database is in archive log mode
  • A solid cold backup is available

    • Methods for solving the ORA-01116, ORA-01110 and or ORA-27041 erros :
    Database is open :
    Users which use tablespace data from missing datafile get the follownig ORA-01116, ORA-01110 and or ORA-27041 erros:
    ORA-01116: error in opening database file 5
    ORA-01110: data file 5: '/home/oracle/tes01.dbf'
    ORA-27041: unable to open file
    Since the missing datafile is a non-system, the recovery can be done online. Users that does not use this tablespace data will continue to work as normal.

    Identify The Datafile Which Needs Recovery : 
        SQL> select * from v$recover_file;

    FILE# ONLINE  ONLINE_
    ---------- ------- -------
    ERROR								     CHANGE#
    ----------------------------------------------------------------- ----------
    TIME	      CON_ID
    --------- ----------
    5 ONLINE  ONLINE
    FILE NOT FOUND								   0
        0

    Offline The Datafile : 
        SQL> alter database datafile 5 offline; 
    
    Database altered.

    Restore The Missing Datafile From The Latest Backup : 
        [oracle@sdbt ~]$ cp /home/oracle/backup/tes01.dbf /home/oracle/

    Recover The Datafile : 
        SQL> RECOVER DATAFILE 5;
    Media recovery complete.
    online the tablespace
    SQL> alter database datafile 5 online;

    Database altered.

    Methods for solving the ORA-01157 and ORA-01110 erros :
    Database Is Close :
    We will be greeted with the ORA-01157 and ORA-01110 messages by Oracle, if any of the datafiles are missing:
    ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
    ORA-01110: data file 5: '/home/oracle/tes01.dbf'

    Mount The Database : 
        SQL> startup mount
    ORACLE instance started.

    Total System Global Area  532676608 bytes
    Fixed Size		    8622720 bytes
    Variable Size		  297799040 bytes
    Database Buffers	  218103808 bytes
    Redo Buffers		    8151040 bytes
    Database mounted.

    Identify The Datafile Which Needs Recovery : 
        SQL> select * from v$recover_file;

    FILE# ONLINE  ONLINE_
    ---------- ------- -------
    ERROR								     CHANGE#
    -----------------------------------------------------------------       ----------
    TIME	      CON_ID
    ---------       ----------
    5 OFFLINE       OFFLINE
    FILE NOT FOUND								   0
        0

    Offline The Missing Datafile : 
        SQL> ALTER DATABASE DATAFILE 5 offline;

    Database altered.

    Open The Database : 
        SQL> ALTER DATABASE OPEN;

    Database altered.

    Restore The Missing File From The backup : 
        [oracle@sdbt ~]$ cp backup/tes01.dbf /home/oracle/

    Recover The Datafile : 
        SQL> RECOVER DATAFILE 5;
    Media recovery complete.

    Online The Datafile : 
        SQL> alter database datafile 5 online;

    Database altered.


    (User Managed Recovery - undo)