recherche

Maison  >  Article  >  base de données  >  oracle redo log 分析

oracle redo log 分析

WBOY
WBOYoriginal
2016-06-07 15:44:361358parcourir

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:

oracle redo log 分析

 

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

oracle redo log 分析

Déclaration：
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn
Article précédent：SQLSERVER读懂语句运行的统计信息Article suivant：sqlserver 将表内容导出insert语句

Articles Liés

Voir plus