Recover non system datafile with backup

Recovering missing non system datafile.  

Prerequisites: database must be in archivelog mode, and need to have hotbackup. Click Here to know how to take hot backup.

-For non system datafile recovery no need to shutdown database.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 1 09:11:09 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select file_name from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/users01.dbf

/u01/app/oracle/oradata/orcl/undotbs01.dbf

/u01/app/oracle/oradata/orcl/sysaux01.dbf

/u01/app/oracle/oradata/orcl/system01.dbf

/u01/app/oracle/oradata/orcl/example01.dbf

/u01/app/oracle/oradata/sample01.dbf

6 rows selected.

[oracle@localhost hotbk]$ cd /u01/app/oracle/oradata/ orcl

[oracle@localhost orcl]$ ls -lrt

total 2340652

-rw-r----- 1 oracle oinstall  20979712 Apr  1 08:20 temp01.dbf

-rw-r----- 1 oracle oinstall  52429312 Apr  1 09:39 redo02.log

-rw-r----- 1 oracle oinstall   5251072 Apr  1 09:45 users01.dbf

-rw-r----- 1 oracle oinstall 260186112 Apr  1 09:51 example01.dbf

-rw-r----- 1 oracle oinstall 765468672 Apr  1 10:04 system01.dbf

-rw-r----- 1 oracle oinstall 671096832 Apr  1 10:05 undotbs01.dbf

-rw-r----- 1 oracle oinstall 576724992 Apr  1 10:05 sysaux01.dbf

-rw-r----- 1 oracle oinstall  52429312 Apr  1 10:07 redo01.log

-rw-r----- 1 oracle oinstall   9748480 Apr  1 10:07 control01.ctl

-Now remove any one non system datafile, here i am deleting example01.dbf datafile from os level. If any non system datafile is missing database will not shut down it will run as usual,   we can find error in alertlog.

[oracle@localhost orcl]$ rm example01.dbf

Lets check in alerlog file.

[oracle@localhost ~]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace/

[oracle@localhost trace]$ tail -20f alert_orcl.log

   DELETE EXPIRED commands.

************************************************************************

Wed Apr 01 09:43:59 2015

Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ckpt_3422.trc:

ORA-01171: datafile 5 going offline due to error advancing checkpoint

ORA-01116: error in opening database file 5

ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

Wed Apr 01 09:44:01 2015

Checker run found 1 new persistent data failures

-Check the datafile status in database.

[oracle@localhost trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 1 09:44:27 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select FILE#,STATUS from v$datafile;

     FILE# STATUS

---------- -------

         1 SYSTEM

         2 ONLINE

         3 ONLINE

         4 ONLINE

         5 RECOVER

         6 ONLINE

6 rows selected.

SQL> exit

-Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

-restore datafile from backup location.

[oracle@localhost trace]$ cd

[oracle@localhost ~]$ cd /u01/app/hotbk/

[oracle@localhost hotbk]$ cp example01.dbf /u01/app/oracle/oradata/orcl/

-Connect to database and recover the datafile.

[oracle@localhost hotbk]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 1 09:49:51 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select FILE#,STATUS from v$datafile;

     FILE# STATUS

---------- -------

         1 SYSTEM

         2 ONLINE

         3 ONLINE

         4 ONLINE

         5 RECOVER

         6 ONLINE

SQL> alter database datafile 5 offline;

Database altered.

SQL> recover datafile 5;

Media recovery complete.

SQL> alter database datafile 5 online;

Database altered.

SQL> select FILE#,STATUS from v$datafile;

     FILE# STATUS

---------- -------

         1 SYSTEM

         2 ONLINE

         3 ONLINE

         4 ONLINE

         5 ONLINE

         6 ONLINE

6 rows selected.

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ WRITE

===============================================================