How To Recover Data files in oracle database without shutting down the database with Hot/Cold backup.

Connect to oracle database.


[oracle@arzaidi Desktop]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 17 09:10:26 2013

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


Create new user.


SQL> create user ali identified by ali;

User created.

Give rights.

SQL> grant resource,connect,dba to ali;

Grant succeeded.

make apps_data tablespace as default tablespace.


SQL> alter user ali default tablespace apps_data;

User altered.

connect to new created user.

SQL> connect ali/ali@ardb
Connected.


SQL> create table sales1 as select * from sh.sales;

Table created.

Go to oradata folder and then databse folder and delete APPS_DATA01.DBF file.

Now try to execute following command this will give error beacuse datafile APPS_DATA01.DBF is deleted which contains sh.sales data.


SQL> create table sales2 as select * from sh.sales;
create table sales2 as select * from sh.sales
                                        *
ERROR at line 1:
ORA-01565: error in identifying file '/oracle/oradata/ardb/apps_data01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

--But the following table is created even if apps_data01.dbf which contains data of ali user does not exists beacue it is using undo files to store new data
for the ali user.


SQL> create table dba_tables as select * from dba_tables;

Table created.

SQL> select count(0) from dba_tables;

  COUNT(0)
----------
      2784

SQL>  exit;

[oracle@arzaidi Desktop]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 17 09:32:11 2013

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> alter system checkpoint;

System altered.

SQL> exit;

[oracle@arzaidi Desktop]$ sqlplus ali/ali@ardb

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 17 09:35:48 2013

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


Do not shutdown database just login as sysdba and offline the table space.

SQL> conn sys/oracle as sysdba
Connected.

Take offline apps_data tablespace.


SQL> alter tablespace apps_data offline immediate;

Tablespace altered.


Now copy apps_data01.dbf from backup to the oradata/ardb folder.

SQL> recover automatic tablespace apps_data;
Media recovery complete.
SQL> alter tablespace apps_data online;

Tablespace altered.

SQL> conn ali/ali@ardb
Connected.
SQL> select count(0) from dba_tables;