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.
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.
from any old backup.....
Restore the database using the latest backup and apply the archived logs and do a point in time recovery
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.
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.
Sign up for STechies