Re: Recovering Dropped Tables with no Backup

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 16 Sep 2002 07:29:43 -0700
Message-ID: <a20d28ee.0209160629.66e3ab11@posting.google.com>

tholden8_at_hotmail.com (Tom Holden) wrote in message news:<13773e73.0209160219.2afcfbc5_at_posting.google.com>...
> In NOARCHIVELOG mode, Suppose you have no Backups (Cold or Hot) and a
> user drops a table. How can we recover the table? I would think not.
> Someone asked me this question and he said he had been told in an
> interview that it can be done.
>
> As far as i know, drop and truncate information is not stored in
> redolog files.... So Logminer should not help... Am i wrong?
>
> Also drop and truncate information is not stored in RBS, so even with
> 9i Flashback dropped tables cannot be recovered. Am i wrong?
>
> Is there some way a dropped table can be recovered?
>
>
> Thanks for any clarification,
> Tom

This is exactly why one should make recurring exports. Apart from database point-in-time recovery or tablespace point-in-time recovery (which isn't available running in noarchivelog) there is no method to recover.
Drop and truncate information *is* stored in the redologs, but as a change to dictionary tables. The contents of the table to be deleted are not dumped, as there are no ordinary deletes executed.

Hth

Sybrand Bakker
Senior Oracle DBA Received on Mon Sep 16 2002 - 09:29:43 CDT