当前位置：Gxlcms > mysql > oracleredolog分析

oracleredolog分析

时间：2021-07-01 10:21:17 帮助过：26人阅读

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

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.

Mnemonic Value Description
KCRVOID 0 The contents are not valid
KCRVALID 1 Includes change vectors
KCRDEPND 2 Includes commit SCN
KCRVOID 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

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

Class Description
1 Data Block
2 Sort Block
3 Deferred Undo Segment Blocks
4 Segment Header Block (Table)
5 Deferred Undo Segment Header Blocks
6 Free List Blocks
7 Extent Map Blocks
8 Space Management Bitmap Blocks
9 Space Management Index Blocks
10 Unused
11 + 2r Segment Header for Undo Segment r
12 + 2r Data Blocks for Undo Segment r

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:

Level Description
4 Block Cleanout
5 Transaction Layer (Undo)
10 Index Operation
11 Table Operation (DML)
13 Block Allocation
14 Extent Allocation
17 Backup Management
18 Online Backup
19 Direct Load
20 Transaction Metadata (LogMiner)
22 Space Management (ASSM)
23 Block Write (DBWR)
24 DDL Statement

For "Table Operation(DML)", description as follows:

Code Mnemonic Description
11.1 IUR Undo
11.2 IRP Insert Row Piece
11.3 DRP Delete Row Piece
11.4 LKR Lock Row
11.5 URP Update Row Piece
11.6 ORP Overflow Row Piece
11.7 MFC
11.8 CFA
11.9 CKI
11.10 SKL
11.11 QMI Insert Row Array
11.12 QMD Delete Row Array
11.14 DSC
11.16 LMN
11.17 LLB
11.19 19 Update Row Array
11.20 SHK
11.21 21

For "Transaction Layer (Undo)", description as follows:

Code Mnemonic Description
5.1 1 Undo Recorder
5.2 2 Undo Header
5.4 4 Commit

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:

Numeric Code Alphanumeric Code
0x1 F
0x2 C
0x3 Z
0x4 L
0x5 R
0x11 F
  • 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

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！