Hello,

I would like to know if a datafile recovery is possible in this case:

1.
I make a cold backup of my database. My database is running in ARCHIVELOG MODE. And all the log files are available.

2.
Start the database, Mount it and then open it.

3.
Create a tablespace DUMMY which has one associated datafile: d1.dbf.

4.
Create a table t1 in DUMMY tablespace.

5.
Insert 3 rows into it.

6. Perform log switch.
Alter system switch logfile;

7. Finally delete d1.dbf datafile using UNIX rm command. There is no backup for this datafile as it got created after the coldbackup. Assume that we cant get this file in any possible way.
//My database is still running. Delete operation was performed using another terminal.

8. If we run a select statement>>> "select * from t1;" we will get an error saying that the d1.dbf datafile is missing.

9. Can we regenerate this datafile from archivelogs? Or what needs to be done so that on issuing the select I can get back all the three rows I inserted.

How about the same situation while the back up was done using RMAN?

Will anything change if the backup was hot backup instead of cold backup in step 1?

Thank you
Regards,
Gokul

I bevlieve that if you lose a datafile for which there is no backup than you've permanently lost that data. Oracle will always be looking for that specific datafile, and you will not be able to replicate the file or the information in the header block.

Hi ,

Assuming that you have all other files intact (including control files). And The datafile you have delected is still listed in control files.

First you can recreate a datafile without backup as :

ALTER DATABASE CREATE DATAFILE 'path of .dbf' ;

Then perform recovery of the datafile as :

RECOVER DATAFILE 'path of .dbf' ;


Remember

You cannot re-create any of the datafiles for the SYSTEM tablespace by using the CREATE DATAFILE clause of the ALTER DATABASE statement because the necessary redo is not available.