Register Login
Python Photoshop SAP Java PHP Android C++ Hadoop Oracle Interview Questions Articles Other

Recovery of Dropped User

Updated May 18, 2018

by mistake i have run the following command
drop user hr cascade and i do not have flashback on.

please suggest how to recover it.

thanks

Comments

  • 08 Feb 2014 8:12 am rekha Best Answer

    You must have a previous backup, which you can restore on a testing server then use export & import to the DB where you have dropped the schema. This is very easy way.Your last good logical backup the export or DataPump is as good as when it was done -so all subsequent updates to the DB since the logical export cannot be recalled. Otherwise it would have been perfect to simply import your HR schema. So last resort is your backup, and then recover just to the point before you dropped the user! If it is a small enough database then just do that recovery - with all the added logfiles and that will suffice. Be sure you have a backup of the current database -before you make any recovery attempts. - Also look into the above options of doing it on another server and importing - but a schema recovery through RMAN could also work for you.
  • 26 Dec 2013 5:22 am Sushma

    from any old backup.....

     
  • 26 Dec 2013 5:22 am Sushma

    Restore the database using the latest backup and apply the archived logs and do a point in time recovery

     
  • 27 Dec 2013 5:38 am Sushma

    You need to do a schema recovery of the dropped user.
    In such cases I create a recovery db and do a duplication with point in thime recovery just before the user was dropped.
    Hope your active db is in archive log mode.
    Then you have to extract the dropped user schema from the recovery db using exp or expdp and reinject it to the active db using imp or impdp accordingly.
    For the operational details on how to do a duplication with point in time recovery check the excellent rman manual downloadable at oracle.com.
    For the detailed instructions on how to extract and inject a user schema, check the imp/exp respectively impdp/expdp sections in the online db manuals at oracle.com.

    Hope this helps.
  • 08 Feb 2014 8:05 am rekha

    When you drop a table, the database does not immediately remove the space associated with the table. The database renames the table and places it and any associated objects in a recycle bin, where, in case the table was dropped in error, it can be recovered at a later time. This feature is called Flashback Drop, and the FLASHBACK TABLE statement is used to restore the table.
Recommended Posts:
×