最新文章专题视频专题问答1问答10问答100问答1000问答2000关键字专题1关键字专题50关键字专题500关键字专题1500TAG最新视频文章推荐1 推荐3 推荐5 推荐7 推荐9 推荐11 推荐13 推荐15 推荐17 推荐19 推荐21 推荐23 推荐25 推荐27 推荐29 推荐31 推荐33 推荐35 推荐37视频文章20视频文章30视频文章40视频文章50视频文章60 视频文章70视频文章80视频文章90视频文章100视频文章120视频文章140 视频2关键字专题关键字专题tag2tag3文章专题文章专题2文章索引1文章索引2文章索引3文章索引4文章索引5123456789101112131415文章专题3
科技
当前位置： 首页 - 科技 - 知识百科 - 正文

oracleredolog分析

来源：动视网 责编：小采 时间：2020-11-09 07:38:50
文档

oracleredolog分析

oracleredolog分析:Environment: OS: Ubuntu 10.10 Oracle: 11.1.0.6.0 Test table： test_user SQL desc test_user; Name Null Type ----------------------------------------- -------- ---------------------
推荐度：
点击下载本文 文档为doc格式
导读oracleredolog分析:Environment: OS: Ubuntu 10.10 Oracle: 11.1.0.6.0 Test table： test_user SQL desc test_user; Name Null Type ----------------------------------------- -------- ---------------------


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

    文档

    oracleredolog分析

    oracleredolog分析:Environment: OS: Ubuntu 10.10 Oracle: 11.1.0.6.0 Test table： test_user SQL desc test_user; Name Null Type ----------------------------------------- -------- ---------------------
    推荐度：
    点击下载本文 文档为doc格式
    标签： 日志 解析 oracle
    • 热门焦点

    最新推荐

    猜你喜欢

    热门推荐

    专题
    产品服务 发展历程 企业资讯 企业文化 关于我们 加入我们 联系我们 网站导航 网站律师

    Copyright © 2019-2025 51dongshi.net 版权所有

    赣ICP备2023002352号-34

    违法及侵权请联系：TEL:177 7030 7066 E-MAIL:11247931@qq.com 本站由北京市万商天勤律师事务所王兴未律师提供法律服务

    Top