table lost

[iris]

hi, Iam taking weekly full database backup using rman and daily archive log file backup. A user complained about loss of a table, no one knows at what time the table was accidently dropped.

Question: How to recover the lost table in this case?


thanks,

[hrishy]

Hi

You need to have the approximate time atleast to know when the table was dropped.

then you can mine the dictonary using log miner and then find out the time and take it from there.

regards
Hrishy

[davey23uk]

if on 10g - flashback to before drop

[hrishy]

Hi

davey hmm why didnt i think that before :-)

regards
Hrishy

[PAVB]

davey hmm why didnt i think that before :-)

Perhaps because of poster did not disclose Oracle version?

[LKBrwn_DBA]

Or perhaps because flashback only works if you actually have it enabled.

[gopu_g]

hi,

You can try this....

Shutdown immediate
Restore all datafiles from backup.
Recover database until change 123456;

the undrop table is not undo based.

in 10g, when you drop a table, the table is renamed, hidden -- but we maintain the extent mapping.
to undrop, all we do is rename it back.

extents that belong to dropped objects are eligible for reuse -- but we'll postpone reusing them
for as long as we can and we'll use them in a fifo manner (oldest extents out first). Just like
the recycle bin on your desktop for files.

If you are using the Orace 10g you can go for the flahsback recovery, if the flash is enabled...?

flashback table to before drop;

Gopu

[LKBrwn_DBA]

... in 10g, when you drop a table, the table is renamed

Only if you have "recycle bin" enabled.

[gopu_g]

Only if you have "recycle bin" enabled.

Yes...LKBrwn....

[gandolf989]

You might try restoring to another location, and allow the database to recover to just before the table gets dropped and then export that table only.

Of course if you don't know when it was deleted you might need Log Miner to get the scn of where to stop the restore.