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

From: Steven Patterson <S.R.Patterson_at_soton.ac.uk>
Date: Thu, 26 Oct 2006 09:34:43 +0100
Message-ID: <Pine.GSO.4.53.0610260925430.25599@aspen.sucs.soton.ac.uk>

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

Received on Thu Oct 26 2006 - 03:34:43 CDT