Recover a dropped schema
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?
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.
Yes, It is running in archivelog mode. How can we find the scn at
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 ?
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.
but the user will still be gone
re-create the user
take the tablespace offline
copy the db files from backup
recover tablespace until time
bring tablespace online
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.
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?