Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Recover deleted rows from a table from archive logs?

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 13:32:15 +0100
Message-ID: <Pine.GSO.4.53.0610261307220.25599@aspen.sucs.soton.ac.uk>


On Oct 26, 2006 at 3:02am sybrandb wrote:

s> Could you *please* support your statements re redolog support with s> references from the docs or retract this misinformation?

I retract the point that not enough information is stored in the log files to recover deleted lines - it was based upon old information from a training course a long time ago, where I was told that for efficiency the redo logs only contained enough information to identify the deleted rows (and indeed, was told that this was a key limitation of LogMiner!) Certainly in Oracle 8i the Logminer does extract enough information to recover from a DELETE statement. My apologies.

s> Did you ever use LogMiner?

Yes.

s> Same with respect to flashback. Undo segments are ordinary segments, s> and are written to the redolog!

I accept that information from the DML will be written to the redo logs, and I also accept that block changes in the Undo segments will be written to the redo logs. I also accept that Logminer in 9i and above can recover delete transactions from the redo logs.

However, Flashback itself does not make use of the redo logs to work its magic, which was the point I was trying to make. It relies on the before images of the changes blocks still being available in the Undo tablespace at the time you wish to flash back.

http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96524/c21cnsis.htm#20178

"Flashback query uses Oracle's multiversion read-consistency capabilities to restore data by applying undo as needed. Administrators can configure undo retention by simply specifying how long undo should be kept in the database"

Note, then, it applies Undo which has been kept in the database, not Redo which has been kept in the redo/archived logs.

Regards, S.

-- 
Steven Patterson, MSci (Hons), Oracle Certified Professional
Received on Thu Oct 26 2006 - 07:32:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US