Oracle Redo log

Here's the wikipedia definiton: Oracle redo logfiles log the history of all changes made to the database. Each redo log file consists of redo records. A redo record, also called a redo entry, holds a group of change-vectors, each of which describes or represents a change made to a single block in the database.

For example, if a user UPDATEs a salary-value in an employee-table, the DBMS generates a redo record containing change-vectors that describe changes to the data segment block for the table. And if the user then COMMITs the update, Oracle generates another redo record and assigns the change a "system change number" (SCN).

A single transaction may involve multiple changes to data blocks, so it may have more than one redo record.
Redo log files contain redo entries for both committed and uncommitted transactions.
Oracle redo log files contain the following information about database changes made by transactions:
indicators specifying when the transaction started
a transaction-identifier
the name of the data object updated (for example, an application table)
the “before image” of the transaction, i.e. the data as it existed before the changes (former versions <> SELECT s.sid, s.serial#, s.username, s.program,
2 i.block_changes
3 FROM v$session s, v$sess_io i
4 WHERE s.sid = i.sid
5 ORDER BY 5 desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence
of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.

2) Query V$TRANSACTION. This view contains information about the amount of
undo blocks and undo records accessed by the transaction (as found in the
USED_UBLK and USED_UREC columns).

The query you can use is:
SQL> SELECT s.sid, s.serial#, s.username, s.program,
2 t.used_ublk, t.used_urec
3 FROM v$session s, v$transaction t
4 WHERE s.taddr = t.addr
5 ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence
of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by
the session.

You use the first query when you need to check for programs generating lots of
redo when these programs activate more than one transaction. The latter query
can be used to find out which particular transactions are generating redo.


In Orafaq.com's whitepaper on redolog available on http://www.orafaq.com/papers/redolog.pdf they have mentioned: The redolog is one of the most powerful features of the Oracle database, since it is the mechanism by which Oracle guarantees to be able to recover the database to the last committed transaction (provided the database is in archive-log mode). What most DBA's do not know, is that the redolog is also one of the most powerful debugging tools available, allowing the DBA to see the actual transactions, including the data, that was executed against the database.

Where this differs from the well known trace facilities, is that the redolog is always on. Unlike tracing, which has be turned on in advance of a known problem, redologs faithfully capture every transaction, making them particularly useful in regulated production environments, or where problems cannot be recreated on demand. Oracle now offers a product called Log Miner for use with Oracle 8i and higher but for those of us still wanting to go deep down to oracle internals, this articles provides an introduction to how to leverage some of the untapped power of the redolog.

The redolog is written in a condensed binary form, unsuitable for text editors. The first step then is to locate and convert the logfile into ANSI format

Metalink Note 1031381.6 describes how to dump redo log file information:

PURPOSE
This article explain how to obtain a dump of the header information in the
online redo log file(s), as well as obtaining selected information from the
online or archived redo log files.

SCOPE & APPLICATION
Informational

You are working with Oracle Technical Support. As part of the diagnostic
process, you have been asked to take a dump of the redo log files. The
information in the logs is often used to help diagnose corruption issues.

The following commands will be used in this process:

1. The 'alter session' command is used to dump redo headers.

2. Use the 'alter system dump logfile' to dump log file contents.

This command requires 'ALTER SYSTEM' system privilege. The database can be in
mount, nomount or open state when the command is issued. An online log file
or an archived log file can be dumped. It is even possible to dump a
file from another database, as long as the operating systems are the same.

Output from the command is put into the session's trace file.

The following ways of dumping a redo log file are covered:

1. To dump records based in DBA (Data Block Address)
2. To dump records based on RBA (Redo Block Address)
3. To dump records based on SCN
4. To dump records based on time
5. To dump records based on layer and opcode
6. Dump the file header information
7. Dump an entire log file:


1. To dump records based on DBA (Data Block Address)
--------------------------------------------------

This will dump all redo records for the range of data
blocks specified for a given file # and block # range.

From sqlplus (sqldba or svrmgr for older versions), issue the following command:

ALTER SYSTEM DUMP LOGFILE 'filename'
DBA MIN fileno . blockno
DBA MAX fileno . blockno;

Example:
========
ALTER SYSTEM DUMP LOGFILE 'u01/oracle/V7323/dbs/arch1_76.dbf'
DBA MIN 5 . 31125
DBA MAX 5 . 31150;

This will cause all the changes to the specified range of data blocks to be
dumped to the trace file. In the example given, all redo records for file #5,
blocks 31125 thru 31150 are dumped.

Note
====
For 10g:
ALTER SYSTEM DUMP LOGFILE 'u01/oracle/V7323/dbs/arch1_76.dbf'
DBA MIN 5 . 31125 DBA MAX 5 . 31150;

will raise:
ORA-01963: Must specify a block number

In 10g we need to skip the dot '.' while doing the redo dumps
ALTER SYSTEM DUMP LOGFILE 'u01/oracle/V7323/dbs/arch1_76.dbf'
DBA MIN 5 31125 DBA MAX 5 31150;


2. To dump records based on RBA (Redo Block Address)
-------------------------------------------------

This will dump all redo records for the range of redo
addresses specified for the given sequence number and block number.

Syntax:
ALTER SYSTEM DUMP LOGFILE 'filename'
RBA MIN seqno . blockno
RBA MAX seqno . blockno;

Example:
ALTER SYSTEM DUMP LOGFILE 'u01/oracle/V7323/dbs/arch1_76.dbf'
RBA MIN 2050 . 13255
RBA MAX 2255 . 15555;

3. To dump records based on SCN
----------------------------

Using this option will cause redo records owning changes within the SCN range
specified to be dumped to the trace file.

ALTER SYSTEM DUMP LOGFILE 'filename'
SCN MIN minscn
SCN MAX maxscn;

Example:
ALTER SYSTEM DUMP LOGFILE 'u01/oracle/V7323/dbs/arch1_76.dbf'
SCN MIN 103243
SCN MAX 103294;

If the purpose is to check the dumpfile you can rather do the following,
SQL> ALTER SYSTEM DUMP LOGFILE 'filename' SCN MIN 1 SCN MAX 1;

If the above completes sucessfully it ensures no issues with the archivelog.


4. To dump records based on time.
------------------------------

Using this option will cause redo records created within the time range
specified to be dumped to the trace file.

From sqlplus (sqldba or svrmgr for older versions), issue the following command:

ALTER SYSTEM DUMP LOGFILE 'filename'
TIME MIN value
TIME MAX value;

Example:
========
ALTER SYSTEM DUMP LOGFILE 'u01/oracle/V7323/dbs/arch1_76.dbf'
TIME MIN 299425687
TIME MAX 299458800;


Please Note: the time value is given in REDO DUMP TIME



5. To dump records based on layer and opcode.
------------------------------------------

LAYER and OPCODE are used to dump all log records for a particular type of
redo record, such as all dropped row pieces.

From sqlplus (sqldba or svrmgr for older versions), issue the following command:

ALTER SYSTEM DUMP LOGFILE 'filename'
LAYER value
OPCODE value;

Example:
========
ALTER SYSTEM DUMP LOGFILE 'u01/oracle/V7323/dbs/arch1_76.dbf'
LAYER 11
OPCODE 3;

6. Dump the file header information:
---------------------------------

This will dump file header information for every
online redo log file.

From sqlplus (sqldba or svrmgr for older versions), issue the following command:

alter session set events 'immediate trace name redohdr level 10';

For dumping archivelog header,issue the following command:

ALTER SYSTEM DUMP LOGFILE 'filename' RBA MIN 1 1 RBA MAX 1 1;

7. Dump an entire log file:
------------------------

From sqlplus (sqldba or svrmgr for older versions), issue the following command:

ALTER SYSTEM DUMP LOGFILE 'filename';

Please note:
Fully qualify the filename, and include the single quotes.


Example:
========
ALTER SYSTEM DUMP LOGFILE 'u01/oracle/V7323/dbs/arch1_76.dbf';

The dump of the logfile will be written into a trace file in the udump destination.
Use the command 'show parameters dump' within an sqlplus session.
The ouput will show the location for the udump destination where
the trace file exists.