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

sybrandb wrote:
> 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

The documentation supports the conclusion that flash back uses the undo segments to provide queries as of a specific time (see Database Administration Tasks Before Using Flashback Features). http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_flashback.htm

LogMiner, at least on my 10.2.0.2 system, provides access to the following columns when accessing archived redo logs:

 SCN                                 NUMBER
 CSCN                               NUMBER
 TIMESTAMP                     DATE
 COMMIT_TIMESTAMP       DATE
 THREAD#                         NUMBER
 LOG_ID                            NUMBER
 XIDUSN                            NUMBER
 XIDSLT                             NUMBER
 XIDSQN                            NUMBER
 PXIDUSN                          NUMBER
 PXIDSLT                           NUMBER
 PXIDSQN                          NUMBER
 RBASQN                          NUMBER
 RBABLK                           NUMBER
 RBABYTE                         NUMBER
 UBAFIL                             NUMBER
 UBABLK                           NUMBER
 UBAREC                           NUMBER
 UBASQN                           NUMBER
 ABS_FILE#                       NUMBER
 REL_FILE#                       NUMBER
 DATA_BLK#                     NUMBER
 DATA_OBJ#                     NUMBER
 DATA_OBJD#                   NUMBER
 SEG_OWNER                  VARCHAR2(32)
 SEG_NAME                     VARCHAR2(256)
 TABLE_NAME                  VARCHAR2(32)
 SEG_TYPE                      NUMBER
 SEG_TYPE_NAME           VARCHAR2(32)
 TABLE_SPACE                VARCHAR2(32)
 ROW_ID                           VARCHAR2(18)
 SESSION#                        NUMBER
 SERIAL#                           NUMBER
 USERNAME                      VARCHAR2(30)
 SESSION_INFO                 VARCHAR2(4000)
 TX_NAME                          VARCHAR2(256)
 ROLLBACK                        NUMBER
 OPERATION                       VARCHAR2(32)
 OPERATION_CODE            NUMBER
 SQL_REDO                        VARCHAR2(4000)
 SQL_UNDO                        VARCHAR2(4000)
 RS_ID                                VARCHAR2(32)
 SEQUENCE#                     NUMBER
 SSN                                  NUMBER
 CSF                                  NUMBER
 INFO                                 VARCHAR2(32)
 STATUS                            NUMBER
 REDO_VALUE                   NUMBER
 UNDO_VALUE                   NUMBER
 SQL_COLUMN_TYPE         VARCHAR2(30)
 SQL_COLUMN_NAME        VARCHAR2(30)
 REDO_LENGTH                  NUMBER
 REDO_OFFSET                  NUMBER
 UNDO_LENGTH                  NUMBER
 UNDO_OFFSET                  NUMBER
 DATA_OBJV#                     NUMBER
 SAFE_RESUME_SCN         NUMBER
 XID                                     RAW(8)
 PXID                                   RAW(8)
 AUDIT_SESSIONID              NUMBER

I can confirm what Sybrandb stated regarding recovering data using LogMiner. The SQL_UNDO column provides a SQL statement to reverse the delete. Those SQL statements can undo an accidental delete.

Proper use of Flashback requires modification to the database, as indicated in the above link:
  ALTER DATABASE ADD SUPPLEMENTAL LOG DATA The same modification should be used if you intend to use LogMiner. A description of this command can be found here: http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_1004.htm#i2153841

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Oct 26 2006 - 06:25:39 CDT