视频1 视频21 视频41 视频61 视频文章1 视频文章21 视频文章41 视频文章61 推荐1 推荐3 推荐5 推荐7 推荐9 推荐11 推荐13 推荐15 推荐17 推荐19 推荐21 推荐23 推荐25 推荐27 推荐29 推荐31 推荐33 推荐35 推荐37 推荐39 推荐41 推荐43 推荐45 推荐47 推荐49 关键词1 关键词101 关键词201 关键词301 关键词401 关键词501 关键词601 关键词701 关键词801 关键词901 关键词1001 关键词1101 关键词1201 关键词1301 关键词1401 关键词1501 关键词1601 关键词1701 关键词1801 关键词1901 视频扩展1 视频扩展6 视频扩展11 视频扩展16 文章1 文章201 文章401 文章601 文章801 文章1001 资讯1 资讯501 资讯1001 资讯1501 标签1 标签501 标签1001 关键词1 关键词501 关键词1001 关键词1501 专题2001
oracleredolog分析
2020-11-09 07:38:50 责编:小采
文档
点击下载本文 文档为doc格式


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

    下载本文
    显示全文
    专题
    动视 51dongshi.net 版权所有
    Copyright © 2019-2025