Re: Recover deleted rows from a table from archive logs?

On Oct 26, 10:34 am, Steven Patterson <S.R.Patter..._at_soton.ac.uk> wrote:
> On Oct 26, 2006 at 1:21am sybrandb wrote:
>
> s> > Is the necessary information in the archive logs such that you can
> s> > reconstruct the deleted rows from the table?
> s>
> s> The purpose of archivelog is to be capable to recover from crash, so to
> s> roll forward already committed transactions that are no longer there.
>
> s> In 8i this has been made public by using the logminer facility, in 9i
> s> and higher you can flashback the table before the event.
> s> The flashback mechanism relies on archive logs.
>
> Wrong, I'm afraid. To recreate the transaction when rolling FORWARD from
> a log file, all that is required is to log the rowids which were deleted -
> therefore this is all that gets logged, as I understand it, and not the
> original content of the rows - therefore it's not possible to re-create
> deleted rows by using the archived log.
>
> Log miner in 8i will show you this (I seem to recall there's an option to
> have the database log more than just ROWID, instead having it log a
> composite primary key, but still not necessarily the full row). Oracle
> Flashback in 9i is NOT reliant upon the archive logs - it is reliant on
> the before image of the database blocks still being present in the Undo
> tablespace.
>
> So it is correct to say that a recovery option is Flashback, so long as
> the before images have not been aged out of the Undo segments, but it is
> not correct to say that the archivelogs necessarily contain the
> information to allow you to "undelete" rows.
>
> Another recovery option is of course to do a point in time database
> recovery onto another instance, and extract the required data (via export,
> or INSERT INTO ... SELECT ... FROM table_at_remote ..., or similar), but
> that's a bunch of work.
>
> Steve
> --
> Steven Patterson, MSci (Hons), Oracle Certified Professional

Could you *please* support your statements re redolog support with references from the docs or retract this misinformation? Did you ever use LogMiner?
Same with respect to flashback. Undo segments are ordinary segments, and are written to the redolog!

-- 
Sybrand Bakker
Senior Oracle DBA