dropped table

I am a new dba, testing the backup and recovery strategy for our new database, which will go live in a few months. I have a feeling that most of our problems will be in trying to undo user errors. We will be running in ARCHIVELOG mode, and performing complete offline backups each night, as well as hot backups daily at noon. Is there an easy way to recover just an individual table? Most of the recovery methods I've found involve taking an entire tablespace offline, which in our case, would mean most of our users wouldn't have access until the recovery is complete.

We are in an NT environment, with Oracle 8.0.5.

I would really appreciate any advice you have to offer.

Dawn

If you are running in ARCHIVE Mode you don't need to bring DB offline. You have three options for backup
1)Full Database
2)Tablespce
3)Datafile

Thanks

You could recover the db on another machine until the point of failure, and then export/import the table. Also consider taking exports regularly.

Thanks,
I will definitely look into doing some regularly scheduled exports.

Dawn

I tried out the EXPORT/IMPORT utilities. I first did a complete export as follows:

c> exp80 userid=dba/pass full=y inctype=complete file='test.dmp'

This completed with no errors. Then I made some unwanted changes to the table empl. Next, I imported the table, expecting to erase the "bad" changes.

c> imp80 userid=dba/pass file='test.dmp' tables=empl fromuser=user

This ran, and completed succesfully, but with warnings: Oracle errors ORA-00054 "resource busy and acquire with NOWAIT specified". I checked Metalink, and couldn't find anything about this error that seemed to apply to my situation.

When I go back to check the table now, the unwanted changes are still there, and the table has not been restored to the state of the full export.

Am I missing a step?

Please help.

You asked what to do with a dropped table, in which case if you imported the table the bad stuff would have been dropped. Importing won't clear the table before it inserts things, you would have to do that manually.

If something has been committed already which was unwanted (and can't easily be deleted by hand) your best bet is probably to restore a copy of the database up until just before those transactions were comitted (using the last hot backup and applying archived logs until just before that time) and then truncating the original bad table, and exporting the one you recovered and importing into the empty table. However you'll lose any transactions which happend after that commit.

You could also look into Tablespace Point in Time Recovery (TSPITR), check Oracle's documentation, it's a fairly involved procedure.

My sample was an employee table, in which I put in a dummy ssn# for an existing employee. I would have thought when I did the import, the bad ssn would have been written over with the original data. This did not happen, which made me wonder if the import was successful or not, especially in light of the ORA-53 errors.

Is my understanding of this wrong?

get rid of all session running on that table. query v$lock for that or use OEM Lock Monitor.

However if you feel you will have no time to mess with restoring database to another server and exp/imp single table, consider standby database with time lag. Very nice and very helpfull/simple option. The HW is still the same: one additional DB server (may be on the same machine, but not recomended of course)