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
Dig Deeper on Oracle database backup and recovery
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs.
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command.
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming.