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@anar]$ rm –i /u01/app/oracle/oradata/disk5/anardb/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/anardb/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;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: