Recover a dropped schema

[KenEwald]

Yes. That's an excellent idea and it sounds like you have the resources.

[Lily_Liu_2004]

Originally posted by svk
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.

My concern about this way is that some objects are not physically sitting in the specified datafiles and they are in system tablespace like the synonyms or procedures.... I think only the tables and indexes in the specified datafiles.

[davey23uk]

Originally posted by KenEwald
Can you?

re-create the user
take the tablespace offline
copy the db files from backup
recover tablespace until time
bring tablespace online

im pretty sure that wouldnt work because the user id will be different and objects belong to that id

[davey23uk]

Originally posted by Lily_Liu_2004
My concern about this way is that some objects are not physically sitting in the specified datafiles and they are in system tablespace like the synonyms or procedures.... I think only the tables and indexes in the specified datafiles.

restore a full backup somewhere, roll forward to right before it was dropped (using the time)

export / import

[svk]

My concern about this way is that some objects are not physically sitting in the specified datafiles and they are in system tablespace like the synonyms or procedures.... I think only the tables and indexes in the specified datafiles.

I missed mentioning that when you do a partial recovery, you will of sourse need SYSTEM tablespace datafiles.

How big is the db ? If not too big, doing recovery work on entire database may be easier.

And, doing all this in a NEW database is the only way to do it.

[KenEwald]

Would 10g flashback recover a dropped user?

[davey23uk]

flashback a database, hmm im guessing that would

[marist89]

Originally posted by davey23uk
restore a full backup somewhere, roll forward to right before it was dropped (using the time)

export / import

Yes, this is how I would do it.

[simply_dba]

By KenEwald:

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

Why would you do that in case a schema is dropped??
The best thing would br TSPITR, where you recover the tablespace in a cloned instance use transport tablespaces option to bring it back.

[davey23uk]

Originally posted by simply_dba
By KenEwald:

Why would you do that in case a schema is dropped??
The best thing would br TSPITR, where you recover the tablespace in a cloned instance use transport tablespaces option to bring it back.

and how would that re-create the user?