Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    7

    Arrow Unanswered: Recover a Dropped Table

    Hi Gurus,

    I got a problem it is yester evening i have taken online backup.Today my office started at 10:30. At 11:45 unexpectedly one has been dropped after 1 hour my user told that application is giving runtime error.and i verified and i found one table has been dropped regarding that transaction.The remaining tables got some transactions.

    Now I want to recover that table without losing any data of that table and not disturbing our users.

    Please its very very urgent.

    Your answer is fully appriciated.

    Thanks

  2. #2
    Join Date
    Feb 2002
    Posts
    96
    What is the state of 'drop_recovery' column in your tablespace?
    Is it 'Y' or 'N' ?

    Regards
    Prakash

  3. #3
    Join Date
    Nov 2003
    Posts
    7

    Arrow

    Hi Prakash,

    The DROP_RECOVERY='Y'.

  4. #4
    Join Date
    Feb 2002
    Posts
    96
    Ok. Good.
    Try the following steps.

    1. db2 "list history dropped table since 20031117 for dbalias"
    2. db2 "restore db dbalias tablespace (tablespace_data,tablespace_index) from xxx_location without prompting"
    3. db2 "rollforward db dbalias to end of logs and stop tablespace (tablespace_data,tablespace_index) recover dropped table dropped_table_id_from_step1 to xxx_some_location"
    4. In step 3, you will get data.
    5. Create table and export data from step4.
    6. You should have all the archived log (from online backup time).

    Test these steps in development first before doing in production.
    Good luck.

    Regards
    Prakash

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    In the Useful DB2 Stuff / db2click.com , you will find a link to a FAQ titled 'Recovery of Accidentally Dropped Tables' which should be of help to you ...

    Cheers
    Sathyaram

    PS: There is a note on how to use tek-tips.com . Please read it as well


    Originally posted by subbu
    Hi Prakash,

    The DROP_RECOVERY='Y'.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Feb 2002
    Posts
    96
    That's great sathyaram. Your link is more detailed and descriptive.

    Regards
    Prakash

  7. #7
    Join Date
    Nov 2003
    Posts
    7
    Thanks for UR reply.

    Thanks
    Subbu

Posting Permissions

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