How to Recover Data files Using database backup in Oracle

At first step we will curropt the database and then try to recover it from the cold /hot backup.

Suppose your database is running go to oradatafolder and then to your database folder in my case it is /ardb.

Delete some database files as follows.
users01.dbf
system01.dbf
sysaux01.dbf


Now try to execute following command and you will receive error.

SQL> create table scott.sales as select * from sh.sales;
create table scott.sales as select * from sh.sales
                                             *
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/oracle/oradata/ardb/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

shutdown database;


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
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
[oracle@arzaidi Desktop]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 17 08:56:39 2013

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

Connected to an idle instance.

Try to start the database and you will again receive error with mean you need recovery.


SQL> startup;  
ORACLE instance started.

Total System Global Area 1590267904 bytes
Fixed Size    1336792 bytes
Variable Size 1040189992 bytes
Database Buffers  536870912 bytes
Redo Buffers   11870208 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/oradata/ardb/system01.dbf'


Shutdown immediate with following command and copy missing files to the database folder from the backup.


SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

Startup database to mount point.


SQL> startup mount;
ORACLE instance started.

Total System Global Area 1590267904 bytes
Fixed Size    1336792 bytes
Variable Size 1040189992 bytes
Database Buffers  536870912 bytes
Redo Buffers   11870208 bytes
Database mounted.

execute following command to recover the database.

SQL> recover automatic database;
 Media recovery complete.

 Now open the database.

 SQL> alter database open;

Database altered.

SQL>