Q
Problem solve Get help with specific problems with your technologies, process and projects.

Missing datafile due to hard disk failure

In Oracle 8i, one of the datafiles is missing due to a hard disk failure problem. Is it possible to drop the datafile which is missing without dropping the tablespace?

In Oracle 8i, one of the datafiles is missing due to a hard disk failure problem. We have Oracle 8i Database Server with multiple datafiles. All the Oracle files are on the first hard disk. Due to a space constraint problem we have created new datafiles two to three days back on a second hard disk. This newly created datafile was lost due to the hard disk failure. We have a logical backup of the database, whereas we do not have the physical backup of the lost datafile. Will the following commands recover the data properly?: "startup mount alter database datafile 'xxx' offline drop ; alter database open." Is it possible to drop the datafile which is missing without dropping the tablespace? Kindly advise in this matter. Thanks.
You'll have to drop the tablespace since the datafile is missing. So STARTUP MOUNT the database. Then issue the ALTER DATABASE OFFLINE DROP command for the missing datafile. You will be able to open the database and then drop the tablespace. But since you have a logical backup (export dump), all is not lost. Re-create the tablespace. Then import just that tablespace's contents from the dump file as follows:
imp userid=system file=my_dump.dmp tablespaces=MY_TS
This was last published in June 2007

Dig Deeper on Oracle database backup and recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close