oracle redo log 分析
- WBOYOriginal
- 2016-06-07 15:44:361358Durchsuche
Environment: OS: Ubuntu 10.10 Oracle: 11.1.0.6.0 Test table： test_user SQL desc test_user; Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER(38) NAME VARCHAR2(20) Current data: SQL s
Environment:
OS: Ubuntu 10.10
Oracle: 11.1.0.6.0
Test table： test_user
Current data:
Operation:
Log File Dumps:
Symbolic dumps can be created for both online redo logs and archived redo logs using the following syntax:
For online redo logs the filename of the current redo log can be obtained using the following SQL:
Output dump file can be found with this command:
if ASM, use following command
SQL> oradebug setmypid; Statement processed. SQL> oradebug tracefile_name; /u02/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_20221.trc
Find the detail log info we just operated in dump file:
1. find the object id of the table
<textarea>SQL> select object_id from user_objects where object_name='TEST_USER';
OBJECT_ID
----------
71329
</textarea>2. find log in dump file based on "object_id" and operation time.The redo infomation as follows, note the "OBJ:71329" and the timestap "12/21/2010
Redo Log Analysis:
Redo Records Structure:
Reo Record Header
- Thread - redo log thread number
value: 1
- RBA - redo byte address - address of redo record within redo log. Format is
. .
value: 0x00007.00012d3d.0010
sequence_number: 0x00007
block_number: 0x00012d3d
offset: 0x10
- LEN - length of redo record in bytes including header
value: 0x22c
- VLD -
The VLD field determines the size of the redo record header. Known values are shown in the following table. These values may vary from one release to another.
|
value: 0x0d - 1101: "Includes change vectors" & "Includes dependent SCN" & "New SCN mark record. SCN allocated exactly at this point in the redo log by this instance"
- SCN - system change number of redo record
value: 0x0000.000fddbd
- SUBSCN: Unknown
value: 1
- Timestamp
value: 12/21/2010 16:30:29
Change #1:
Change Header:
- TYP - change type
value: 2
- CLS - class
|
value: 1 - mean data block
- AFN - Absolute File No
get file info using this sql statement:
value: 4 - means to "users01.dbf"
- DBA - Database Block Address
value: 0x01000195
File No: 4
Block No: 0x195
- OBJ - object
value: 71329
- SCN - System change Number
value: 0x0000.000fdd5e
wrap: 0x0000
base: 0xfdd5e
- SEQ - Sequence Number
value: 2
- OP - Operation Code
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:
|
For "Table Operation(DML)", description as follows:
|
For "Transaction Layer (Undo)", description as follows:
|
values: 11.19
KTB Redo
KTB Redo records modifications to the transaction header of a block. The inverse operations are recorded in the undo segment
Each KTB Redo record has an operation type. Known codes include:
|
- op: 0x01 - Numeric Code
- ver: 0x01
- op: F - Alphanumeric Code
-
xid: Transaction ID. Format is usn#.slot#.wrap# Components are:
usn# Undo segment number slot# Slot number in undo segment wrap# Sequence number
value: 0x000a.005.0000035a
usn: 0x000a
slot: 0x005
wrap: 0x0000035a
- uba: uba Undo block address. Format is dba.seq#.rec# Components are:
dba Data block address of undo block seq# Sequence number of undo block rec# Record number within undo block
value: 0x00c00478.01ca.28
dba: 0x00c00478
seq: 0x01ca
rec: 0x28
- tabn - specifies the table number. For non-clustered tables this will always be 0
- slot - specifies the slot number. Each block has an variable length array of slots. Each element in this array specifies the location of a row within the block. The first slot in the block is 0. In this example the row was written to the third slot in the table (slot 2)
- flag - Unknown
- lock - probably set to 1 indicating a lock has been taken on the row header, what's the mean 2?
- ckix - Unknown
- ncol - Number of columns in row piece
- nnew - Number of changed columns in row piece
- size - (Probably) change in size of row piece
- Operation code: Update Row Piece
- row dependencies - specifies whether row dependencies are disabled (default) or enabled for this table
- xtype - transaction type. Can be XA, XR, CR or KDO_KDOM2. In this case the transaction type is KDO_KDOM2. This structure can potentially store an array of change vectors for an object.
- flags - Unknown
- bdba - block DBA. Data block address of this block
- hdba - header DBA. Probably data block address of extent header
- itli - specifes the ITL slot number of the transaction performing the operation. In this example the row is locked by the transaction in the first ITL slot.
- ispac - Unknown
- maxfr - Unknown
Update of column 1 (name) to 'ddd'
Change #2:
Change header:
- TYP: 0
- CLS: 35
- AFN: 3 - refer to "undotbs01.dbf"
- DBA: 0x00c00099
- OBJ: 4294967259
- SCN: 0x0000.000fdd85
- SEQ: 1
- OP: 5.2 - means "Undo header"
Change body:
Change #3:
Change header:
- TYP: 0
- CLS: 35
- AFN: 3 - refer to "undotbs01.dbf"
- DBA: 0x00c00039
- OBJ: 4294967295
- SCN: 0x0000.000fddc3
- SEQ: 1
- OP: 5.4 - means "Commit"
Change #4:
Change header:
- TYP: 0
- CLS: 24
- AFN: 3 - refer to "undotbs01.dbf"
- DBA: 0x00c005ba
- OBJ: 4294967295
- SCN: 0x0000.000fdd8a
- SEQ: 3
- OP: 5.1 - means "Undo Recorder"
Change Body:
- logon user: 106 - current user id, confirmed as follows:
RollBack:
Opration:
Redo Log:
Update Multiple rows:
Operation:
Redo Log:
Multi Operation One Commit:
operation:
Redo Log:
Reference:
http://www.juliandyke.com/Internals/Redo/Redo.html
