All changes to the database are recorded by redo. Redo includes all changes to datafiles, but does not include changes to control files or the parameter file.
Redo is initially written to online redo logs. The contents of a redo log file depend on a combination of Oracle version, operating system and server architecture. In general redo logs written on one architecture cannot be read on another. There are a few exceptions to this rule. For example, in Oracle 10.2 a redo log written in Linux can be read by a Windows database.
Each online redo log has a thread number and a sequence number. The thread number is mainly relevant in RAC databases where there can be multiple threads; one for each instance. The thread number is not necessarily the same as the instance number. For single instance databases there is only one redo log thread at any time.
A redo thread consists of two or more redo log groups.
Each redo log group contains one or more physical redo log files known as members. Multiple members are configured to provide protection against media failure (mirroring). All members within a redo log group should be identical at any time.
Each redo log group has a status. Possible status values include UNUSED, CURRENT, ACTIVE and INACTIVE. Initially redo log groups are UNUSED. Only one redo log group can be CURRENT at any time. Following a log switch, redo log group continues to be ACTIVE until a checkpoint has completed. Thereafter the redo log group becomes INACTIVE until it is reused by the LGWR background process.
Log switches occur when the online redo log becomes full. Alternatively log switches can be triggered externally by commands such as:
ALTER SYSTEM SWITCH LOGFILE;
When a log switch occurs, the sequence number is incremented and redo continues to be written to the next file in the sequence. If archive logging is enabled, then following a low switch the completed online redo log will be copied to the archive log destination(s) either by the ARCH background process or the LNSn background process depending on the configuration.
A redo log file consists of a number of fixed size blocks. The overall size of the redo log file is specified when the log group is created. For most platforms including Linux and Solaris the redo log block size is 512 bytes. On other platforms including HP/UX Itanium the redo log block size can be 1024 bytes.
Each redo log file has a fixed header. In recent versions of Oracle (8.0 and above) this header is two blocks. Therefore on Linux/Solaris the header is 1024 bytes. The second block of the header contains a standard Oracle file header which includes the following information:
Other data is stored in the header. Note that the End SCN is actually the Start SCN of the next redo log file.
The body of the redo log file is used to store redo blocks. Each redo block has a 16 byte header (Oracle 9.2 and 10.2). The remainder of each redo block is used to store redo records.
Redo records are a logical structure. The upper size limit is probably 65536 bytes. Redo records can therefore span multiple physical redo blocks. A physical redo block can also contain multiple redo records.
Each redo record has a header. The VLD field in the redo record header specifies the type of the redo record. The size of the redo record header varies depending on the type.
In Oracle 9.2 the redo record header is normally 12 bytes, though they can occasionally increase in size to 28 bytes. In Oracle 10.2 the redo record header is normally 24 bytes, though under some circumstances they can increase to 68 bytes.
The following is an example of a redo record header from Oracle 10.2:
REDO RECORD - Thread:1 RBA: 0x000092.00000193.0088 LEN: 0x0050 VLD: 0x01 SCN: 0x0000.00181068 SUBSCN: 1 05/07/2009 21:53:48
The header includes the following fields
The VLD field determines the size of the redo record header. Known values for Oracle 9i are shown in the following table. These values may vary for other releases:
|KCRVOID||0||The contents are not valid|
|KCRVALID||1||Includes change vectors|
|KCRCOMIT||2||Includes commit SCN|
|KCRDEPND||4||Includes dependent SCN|
|KCRNMARK||8||New SCN mark record. SCN allocated exactly at this point in the redo log by this instance|
|KCROMARK||16||Old SCN mark record. SCN allocated at or before this point in the redo. May be allocated by another instance|
|KCRORDER||32||New SCN was allocated to ensure redo for some block would be ordered by inc/seq# when redo sorted by SCN|
A redo record consists of one or more change records known as change vectors. Each change vector consists of:
The size of the change header is 28 bytes in both Oracle 9.2 and 10.2.
The list of element lengths has a two byte header specifying the overall length of the element length list in bytes. The length of each element is stored in a two byte field. Finally if the structure does not align on a four byte boundary, a further two byte field is appended.
The list of elements consists of one or more elements aligned on a four byte boundary. Element sizes can range from four bytes to at least 32K.
If supplemental logging is enabled then for update operations (11.5), additional elements are appended to the change vector containing the primary key, unique key or column values of the row.
Each change vector has an operation code. In Oracle 9.2 there were over 150 redo log operations; this number has grown significantly in Oracle 10.2 though the exact figure is not known. The operation code consists of a major number and a minor number.
The major number describes the level in the kernel where the redo is generated. The following table shows common levels:
|5||Transaction Layer (Undo)|
|11||Table Operation (DML)|
|20||Transaction Metadata (LogMiner)|
|22||Space Management (ASSM)|
|23||Physical I/O Block Operation|
For each level there is one or more subcode. Follow the hyperlinks for more details on individual operations:
Symbolic dumps can be created for both online redo logs and archived redo logs using the following syntax:
ALTER SYSTEM DUMP LOGFILE '<filename>';
For online redo logs the filename of the current redo log can be obtained using the following SQL:
SELECT member FROM v$logfile WHERE group# = ( SELECT group# FROM v$log WHERE status = 'CURRENT' );