User Managed Recovery - non-system datafileMethods 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. « Previous Next Topic » (User Managed Recovery - undo) |