Recovery of datafile which has no backups Problem Generation

i. Create Oracle Managed File tablespace

SQL> CREATE TABLESPACE reco_test;

ii. Create table sh_sales on tablespace reco_test.
SQL> CREATE TABLE sh_sales
  TABLESPACE reco_test
  AS
SELECT *
FROM sh.sales
WHERE ROWNUM < 10;


iii. Delete RECO_TEST tablespace datafile from operating system.
[oracle@odb]$ rm –i /u01/app/oracle/oradata/disk5/OCMDB/datafile/o1_mf_reco_tes_6n8dnc7z_.dbf


iv. Select from table sh_sales.
SQL> SELECT COUNT (*) FROM sh_sales;
                 *
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5:
‘/u01/app/oracle/oradata/disk5/OCMDB/datafile/o1_mf_reco_tes_6n8dnc7z_.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3




Solution


i. Recover Datafile 5.
RMAN> 
run {
 sql ‘ alter database datafile 5 offline ‘;
 restore datafile 5;
 recover datafile 5;
 sql ‘ alter database datafile 5 online ‘;
 }
ii. Select from table sh_sales.
SQL> SELECT COUNT (*) FROM sh_sales;