dropped table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: dropped table

  1. #1
    Join Date
    Dec 2000
    Posts
    41
    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

  2. #2
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    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

  3. #3
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    You could recover the db on another machine until the point of failure, and then export/import the table. Also consider taking exports regularly.

  4. #4
    Join Date
    Dec 2000
    Posts
    41

    dropped table

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

    Dawn

  5. #5
    Join Date
    Dec 2000
    Posts
    41

    Unhappy dropped table

    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.

  6. #6
    Join Date
    Jun 2000
    Posts
    417
    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.

  7. #7
    Join Date
    Dec 2000
    Posts
    41
    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?

  8. #8
    Join Date
    Nov 2000
    Posts
    212

    ORA-00054 "resource busy

    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)




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width