Help - Search - Members - Calendar
Full Version: Datafile Recovery/Restoration
Oracle DBA Forums > Oracle > Oracle Forum
gokul
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
nestafaria
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.
dba@mumbai
QUOTE (gokul @ Apr 7 2005, 08:09 PM) *
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



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.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.