DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Recover a dropped schema

  1. #1
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257

    Recover a dropped schema

    DBAs,

    One of our dbas just dropped a schema by accident. What is the best way to recover it? We put each schema in its own tablespace.

    If we want to do a point-in-time recovery using the scn.
    How can we find the exact scn at the time she dropped that schema?

    Thanks.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    good luck with that.
    Jeff Hunter

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Can you look at the archive logs to see if there is a large number of archived logs generated around the time that you think the schema was dropped. You are in archivelog mode aren't you? You may want to restore to a separate database and move over the data that you can recover. As Jeff said, good luck with that.

  4. #4
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257
    Thanks.
    Yes, It is running in archivelog mode. How can we find the scn at
    that time?

  5. #5
    Join Date
    Jul 2000
    Posts
    521
    How was the schema dropped ? DROP USER ... CASCADE ?

    You will not see any dramatic increase in # of archived logs generated as this is DDL activity.

    Look at V$ARCHIVED_LOG for SCN info.

    Better will doing time based/cancel based incomplete recovery if the DBA knows when the schema was dropped.

    I assume you don't have export backup ?
    svk

  6. #6
    Join Date
    Jul 2000
    Posts
    521
    If the objects for this schema were indeed located in isolated tablespace(s), your recovery work is simpler. You will only need to restore datafiles for that tablespace(s) and rollback/undo.
    svk

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    but the user will still be gone

  8. #8
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Can you?

    re-create the user
    take the tablespace offline
    copy the db files from backup
    recover tablespace until time
    bring tablespace online
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  9. #9
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Next to worst case scenario is that you have to recover the whole database to PIT and loose anything between the user drop and now.

    I'd be on the phone w/ Oracle if it's important. It could get worse.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  10. #10
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257
    Yes, it is dropped by DDL. So, we decided to use recover till time.
    The issue is that it might erase all the work happened to the other schemas from that time on.

    So, is it feasible that we duplicate the current database to another database and then recover that new database using point-in-time recovery and then export the schema and import back into the original database?

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