Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    22

    Unanswered: How to restore the deleted data?

    Hi,

    one of our developers delete 5 rows in a table this morning by mistake. How to get the deleted rows back? I don't want to use the online backup to restore, because there are only 5 rows to restore. Does the LOG_MNR work? How can I do?


    Thanks for your help.

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    No, logminer will not work in this case. It does not shows data but dml or DDL (if 9i) operations performed against your database.

    Do the followign procedure to retirve that 5 deleted rows.

    (1) create a clone db ( from your closed backup)
    on different server.

    (2) appply all the arhive logs to clone db till to the point just before
    he/she deleted rows.

    (3) SELECT the data from the clone db which was deleted and apply to the production table. you can make a procedure which create the .sql file with the output like

    INSERT INTO tab VALUES ('col1', 'col2',..,'coln');
    INSERT INTO tab VALUES ('col1', 'col2',..,'coln');
    INSERT INTO tab VALUES ('col1', 'col2',..,'coln');
    . . . .
    . . . .

    (4) run this file on the production database.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Nov 2003
    Posts
    22
    Hi,

    Is there any other solutions? your suggestion sounds work, but it's complex, and we have no closed backup, we have only online backup by RMAN, how to create a clone database?


    Thanks for your reply.

  4. #4
    Join Date
    Nov 2003
    Posts
    19
    Do u get export of your db ?

    If u do you can restore the table from the export.

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You should be able to use either logminer or flashback query (if youve got 9i) to retrieve the data as it shows you the dml which deleted your rows and the inverse operation to insert your data back in.

    Just do a search on logminer on google or go here

    http://www.engin.umich.edu/caen/wls/...iner.htm#15019

    There are a few restrictions though which may prevent it working for you such as DDL carried out on your table after the delete.

    Alan

  6. #6
    Join Date
    Nov 2003
    Posts
    22
    Thanks all.

    Of course, I have no export dump file.

    I will try LogMiner and FlashBack Query.

    we use RMAN for backup, maybe I have to create a auxiliary instance to recover.


    Thanks for your document link of LogMiner, AlanP

Posting Permissions

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