[ODI] chapter1 When Update statement is executed, How does oracle undo work?
-
Upload
exem -
Category
Technology
-
view
193 -
download
2
Transcript of [ODI] chapter1 When Update statement is executed, How does oracle undo work?
Oracle Deep Internal report no. 001
When Update statement is executed,
How does oracle undo work?
Research & Contents Team
When Update statement is executed,
How does oracle undo work?
01
Lee Kun Oh (이근오) [email protected]
Kwon Kon Woo (권건우) [email protected]
2011, Oracle Core Essential Internals for DBAs and Developers, Jonathan Lewis
"Undo" mechanism as explained in Jonathan Lewis's book
It's so difficult to understand only through
the book.I'd like to check internal actual situation of oracle
with my own eyes
Simple flow chart in oracle when processing Update statement
2011, Oracle Core Essential Internals for DBAs and Developers, Jonathan Lewis
UPDATE Table A SET col1 = ‘B’
AA
Redolog
undo
DataFile
AB
A
B
SGA
Undo SegmentData Block
Shared Pool Buffer Cache
1. Initial state when a value of "A" is entered (The first stage before update)
v$transaction
Undo Headerindex state cflags wrap# uel scn dba parent-xid nub -------------------------------------------------------------------------------------------
0x00 9 0x00 0x04ec 0x0001 0x0000.003d6d72 0x00000000 0x01 9 0x00 0x04ec 0x0002 0x0000.003d6d72 0x00000000 0x02 9 0x00 0x04ec 0x0003 0x0000.003d6d72 0x00000000
Undo Block
ITLItl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Rowsblock_row_dump:tab 0, row 0, @0x1f88tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
ASCII HEXA code 41=> Value ‘A’
* Rec #0x1
BH
SGA
Undo SegmentData Block
Shared Pool Buffer Cache
1_1. State in which generating CU Block into Buffer Cache by selecting one from DB block(The preparation stage before update)
v$transaction
Undo Headerindex state cflags wrap# uel scn dba parent-xid nub -------------------------------------------------------------------------------------------
0x00 9 0x00 0x04ec 0x0001 0x0000.003d6d72 0x00000000 0x01 9 0x00 0x04ec 0x0002 0x0000.003d6d72 0x00000000 0x02 9 0x00 0x04ec 0x0003 0x0000.003d6d72 0x00000000
Undo Block
ITLItl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Rowsblock_row_dump:tab 0, row 0, @0x1f88tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
* Rec #0x1
BH
ASCII HEXA code 41=> Value ‘A’
BH (0x643fa438) file#: 5 rdba: 0x0140dcad (5/56493) class: 1 ba: 0x643ba000st: XCURRENT md: EXCL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
UPDATE Table A SET col1 = ‘B’
SGA
Undo SegmentData Block
Shared Pool Buffer Cache
BH (0x643fa1c8) file#: 5 rdba: 0x0140dcad (5/56493) class: 1 ba: 0x643b6000st: XCURRENT md: EXCL fpin: 'kdswh11: kdst_fetch' tch: 1
Itl Xid Uba Flag Lck Scn/Fsc0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
BH (0x643fa438) file#: 5 rdba: 0x0140dcad (5/56493) class: 1 ba: 0x643ba000st: XCURRENT md: EXCL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
2.. When executing Update statement, generates new CU block in Buffer Cache and changes from CU block to CR block
v$transaction
CR
ITLItl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Rowsblock_row_dump:tab 0, row 0, @0x1f88tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
ASCII HEXAcode 41=> Value ‘A’
Undo Headerindex state cflags wrap# uel scn dba parent-xid nub -------------------------------------------------------------------------------------------
0x00 9 0x00 0x04ec 0x0001 0x0000.003d6d72 0x00000000 0x01 9 0x00 0x04ec 0x0002 0x0000.003d6d72 0x00000000 0x02 9 0x00 0x04ec 0x0003 0x0000.003d6d72 0x00000000
Undo Block
* Rec #0x1
BH
copy XCU block
change from XCU to CR
BH‘a438’XCU block is copied to BH‘a1c8’XCU blockBH‘a438’block is changed from XCU to CR
0x643fa438
0x643fa1c8
SGA
Undo SegmentData Block
Shared Pool Buffer Cache
BH (0x643fa438) file#: 5 rdba: 0x0140dcad (5/56493) class: 1 ba: 0x643ba000st: XCURRENT md: EXCL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
2_1. Address data is generated in v$transaction. (Allocating memory about Row Cache new tx)
CR
ITLItl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Rowsblock_row_dump:tab 0, row 0, @0x1f88tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
ASCII HEXAcode 41=> Value ‘A’
Undo Headerindex state cflags wrap# uel scn dba parent-xid nub -------------------------------------------------------------------------------------------
0x00 9 0x00 0x04ec 0x0001 0x0000.003d6d72 0x00000000 0x01 9 0x00 0x04ec 0x0002 0x0000.003d6d72 0x00000000 0x02 9 0x00 0x04ec 0x0003 0x0000.003d6d72 0x00000000
Undo Block
* Rec #0x1
BH
V$transactionXID_INFO UBA_INFO TXN_INFO-------------------------- --------------- ------------------------------addr : status : IDLExidusn : ubafil : start_date : 2015-12-10 10:52:46xidslot : ubablk : ses_addr : 000000006B21CC20xidsqn : ubasqn :
0000000069B01FB0
BH (0x643fa1c8) file#: 5 rdba: 0x0140dcad (5/56493) class: 1 ba: 0x643b6000st: XCURRENT md: EXCL fpin: 'kdswh11: kdst_fetch' tch: 1
Itl Xid Uba Flag Lck Scn/Fsc0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
For processing TX, Address is allocated in v$transactiondata at the very first
SGA
Undo SegmentData Block
Shared Pool Buffer Cache
3. Xidusn's value is allocated in v$transaction. (No. 7 usn in here)
ITLItl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Rowsblock_row_dump:tab 0, row 0, @0x1f88tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
ASCII HEXAcode 41=> Value ‘A’
Undo Headerindex state cflags wrap# uel scn dba parent-xid nub -------------------------------------------------------------------------------------------
0x00 9 0x00 0x04ec 0x0001 0x0000.003d6d72 0x00000000 0x01 9 0x00 0x04ec 0x0002 0x0000.003d6d72 0x00000000 0x02 9 0x00 0x04ec 0x0003 0x0000.003d6d72 0x00000000
Undo Block
* Rec #0x1
BH
V$transactionXID_INFO UBA_INFO TXN_INFO-------------------------- --------------- ------------------------------addr : status : IDLExidusn : ubafil : start_date : 2015-12-10 10:52:46xidslot : ubablk : ses_addr : 000000006B21CC20xidsqn : ubasqn :
0000000069B01FB07
BH (0x643fa438) file#: 5 rdba: 0x0140dcad (5/56493) class: 1 ba: 0x643ba000st: XCURRENT md: EXCL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
CR
BH (0x643fa1c8) file#: 5 rdba: 0x0140dcad (5/56493) class: 1 ba: 0x643b6000st: XCURRENT md: EXCL fpin: 'kdswh11: kdst_fetch' tch: 1
Itl Xid Uba Flag Lck Scn/Fsc0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
After allocating address, USN(Undo Segment Number) is registered in v$transaction data for the second time
Undo Segment Number
SGA
Undo SegmentData Block
Shared Pool Buffer Cache
Undo Headerindex state cflags wrap# uel scn dba parent-xid nub -------------------------------------------------------------------------------------------
0x00 9 0x00 0x04ec 0x0001 0x0000.003d6d72 0x00000000 0x01 9 0x00 0x04ec 0x0002 0x0000.003d6d72 0x00000000 0x02 9 0x00 0x04ec 0x0003 0x0000.003d6d72 0x00000000
4. In Undo segment, Slot value is changed from 9 to 10
Undo Block
* Rec #0x1 slt: 0x00 objn: 70670 (0x0001140e) objd: Undo type: Regular undo Begin trans Last buffer split: Noncol: 2 nnew: 1 size: 0Vector content:col 1: [ 1] 41
BH (0x643e4b50) file#: 6 rdba: 0x018000a2 (6/162) class: 30 ba: 0x64184000st: XCURRENT md: EXCL fpin: 'kcbwh2: kcbchg1' tch: 0
Before image value ‘A’
ITLItl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Rowsblock_row_dump:tab 0, row 0, @0x1f88tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
ASCII HEXAcode 41=> Value ‘A’
Undo Block Address
10 0x80 0x04ed 0x0000 0x0000.003dbe4d 0x018000a2
Undo slot active
V$transactionXID_INFO UBA_INFO TXN_INFO-------------------------- --------------- ------------------------------addr : status : IDLExidusn : ubafil : start_date : 2015-12-10 10:52:46xidslot : ubablk : ses_addr : 000000006B21CC20xidsqn : ubasqn :
0000000069B01FB07
BH (0x643fa438) file#: 5 rdba: 0x0140dcad (5/56493) class: 1 ba: 0x643ba000st: XCURRENT md: EXCL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
CR
BH (0x643fa1c8) file#: 5 rdba: 0x0140dcad (5/56493) class: 1 ba: 0x643b6000st: XCURRENT md: EXCL fpin: 'kdswh11: kdst_fetch' tch: 1
Itl Xid Uba Flag Lck Scn/Fsc0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
Undo Segment Number
SGA
Undo SegmentData Block
Shared Pool Buffer Cache
BH (0x643fa1c8) file#: 5 rdba: 0x0140dcad (5/56493) class: 1 ba: 0x643b6000st: XCURRENT md: EXCL fpin: 'kdswh11: kdst_fetch' tch: 1
Itl Xid Uba Flag Lck Scn/Fsc0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
5. Update value A to B of XCU block in Buffer Cache
ITLItl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Rowsblock_row_dump:tab 0, row 0, @0x1f88tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
ASCII HEXAcode 41=> Value ‘A’
Undo Headerindex state cflags wrap# uel scn dba parent-xid nub -------------------------------------------------------------------------------------------
0x00 9 0x00 0x04ec 0x0001 0x0000.003d6d72 0x00000000 0x01 9 0x00 0x04ec 0x0002 0x0000.003d6d72 0x00000000 0x02 9 0x00 0x04ec 0x0003 0x0000.003d6d72 0x00000000
Undo Block
* Rec #0x1 slt: 0x00 objn: 70670 (0x0001140e) objd: Undo type: Regular undo Begin trans Last buffer split: Noncol: 2 nnew: 1 size: 0Vector content:col 1: [ 1] 41
BH (0x643e4b50) file#: 6 rdba: 0x018000a2 (6/162) class: 30 ba: 0x64184000st: XCURRENT md: EXCL fpin: 'kcbwh2: kcbchg1' tch: 0
Before image value ‘A’
Undo Block Address
10 0x80 0x04ed 0x0000 0x0000.003dbe4d 0x018000a2
0x01 0x000a.000.00000534 0x018000d2.01e6.01 C--- 0 scn 0x0000.003dbe3a0x02 0x0007.000.000004ed 0x018000a2.01d5.01 ---- 1 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x2 cc: 2
col 1: [ 1] 42
Undo slot active
V$transactionXID_INFO UBA_INFO TXN_INFO-------------------------- --------------- ------------------------------addr : status : IDLExidusn : ubafil : start_date : 2015-12-10 10:52:46xidslot : ubablk : ses_addr : 000000006B21CC20xidsqn : ubasqn :
0000000069B01FB07
BH (0x643fa438) file#: 5 rdba: 0x0140dcad (5/56493) class: 1 ba: 0x643ba000st: XCURRENT md: EXCL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
CR
Update ‘A’ to ‘B’ of CU block
CleanOut : Itl No.1 change (--U- C---)Txn data change: Itl No.2 change (Xid,Uba)Lock Byte change: 0x1 0x2
Undo Segment Number
SGA
Undo SegmentData Block
Shared Pool Buffer Cache
V$transactionXID_INFO UBA_INFO TXN_INFO-------------------------- --------------- ------------------------------addr : 0000000069B01FB0 status : IDLExidusn : 7 ubafil : start_date : 2015-12-10 10:52:46xidslot : ubablk : ses_addr : 000000006B21CC20xidsqn : ubasqn :
BH (0x643fa438) file#: 5 rdba: 0x0140dcad (5/56493) class: 1 ba: 0x643ba000st: XCURRENT md: EXCL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
CR
6. Allocate into other column except xid and uba in v$transaction
ITLItl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Rowsblock_row_dump:tab 0, row 0, @0x1f88tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
ASCII HEXAcode 41=> Value ‘A’
Undo Headerindex state cflags wrap# uel scn dba parent-xid nub -------------------------------------------------------------------------------------------
0x00 9 0x00 0x04ec 0x0001 0x0000.003d6d72 0x00000000 0x01 9 0x00 0x04ec 0x0002 0x0000.003d6d72 0x00000000 0x02 9 0x00 0x04ec 0x0003 0x0000.003d6d72 0x00000000
Undo Block
* Rec #0x1 slt: 0x00 objn: 70670 (0x0001140e) objd: Undo type: Regular undo Begin trans Last buffer split: Noncol: 2 nnew: 1 size: 0Vector content:col 1: [ 1] 41
BH (0x643e4b50) file#: 6 rdba: 0x018000a2 (6/162) class: 30 ba: 0x64184000st: XCURRENT md: EXCL fpin: 'kcbwh2: kcbchg1' tch: 0
0x018000a2
70000000069B01FB0
11261
162
ACTIVE
Undo Block Address
Before image value ‘A’
Undo slot active
6
469
10 0x80 0x04ed 0x0000 0x0000.003dbe4d
BH (0x643fa1c8) file#: 5 rdba: 0x0140dcad (5/56493) class: 1 ba: 0x643b6000st: XCURRENT md: EXCL fpin: 'kdswh11: kdst_fetch' tch: 1
Itl Xid Uba Flag Lck Scn/Fsc0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
0x01 0x000a.000.00000534 0x018000d2.01e6.01 C--- 0 scn 0x0000.003dbe3a0x02 0x0007.000.000004ed 0x018000a2.01d5.01 ---- 1 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x2 cc: 2
col 1: [ 1] 42
Registered with the order as below in v$transaction.* addr->xidusn-> xidslot, xidsqn,
ubablk
SGA
Undo SegmentData Block
Shared Pool Buffer Cache
BH (0x643fa1c8) file#: 5 rdba: 0x0140dcad (5/56493) class: 1 ba: 0x643b6000st: XCURRENT md: EXCL fpin: 'kdswh11: kdst_fetch' tch: 1
Itl Xid Uba Flag Lck Scn/Fsc0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
0x01 0x000a.000.00000534 0x018000d2.01e6.01 C--- 0 scn 0x0000.003dbe3a0x02 0x0007.000.000004ed 0x018000a2.01d5.01 ---- 1 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x2 cc: 2
col 1: [ 1] 42
Undo Headerindex state cflags wrap# uel scn dba parent-xid nub -------------------------------------------------------------------------------------------
0x00 9 0x00 0x04ec 0x0001 0x0000.003d6d72 0x00000000 0x01 9 0x00 0x04ec 0x0002 0x0000.003d6d72 0x00000000 0x02 9 0x00 0x04ec 0x0003 0x0000.003d6d72 0x00000000
10 0x80 0x04ed 0x0000 0x0000.003dbe4d
BH (0x643fa438) file#: 5 rdba: 0x0140dcad (5/56493) class: 1 ba: 0x643ba000st: XCURRENT md: EXCL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
CR
7. Commit execute (Allocating equal scn to Itl slot and undo header slot)
ITLItl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Rowsblock_row_dump:tab 0, row 0, @0x1f88tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
ASCII HEXAcode 41=> Value ‘A’
Undo Block
* Rec #0x1 slt: 0x00 objn: 70670 (0x0001140e) objd: Undo type: Regular undo Begin trans Last buffer split: Noncol: 2 nnew: 1 size: 0Vector content:col 1: [ 1] 41
BH (0x643e4b50) file#: 6 rdba: 0x018000a2 (6/162) class: 30 ba: 0x64184000st: XCURRENT md: EXCL fpin: 'kcbwh2: kcbchg1' tch: 0
Before image value ‘A’
Undo Block Address
0x018000a20x0000.003dbe4e
--U- 1 fsc 0x0000.003dbe4e
Undo slot active
Allocating equal scn to Itl slot and undo header slot
V$transactionXID_INFO UBA_INFO TXN_INFO-------------------------- --------------- ------------------------------addr : status : IDLExidusn : ubafil : start_date : 2015-12-10 10:52:46xidslot : ubablk : ses_addr : 000000006B21CC20xidsqn : ubasqn :
After commit, TX data is deleted in v$transaction
Commit;
SGA
Undo SegmentData Block
Shared Pool Buffer Cache
8. Executing Check point (CU block reflects disk block)
ITLItl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Rowsblock_row_dump:tab 0, row 0, @0x1f88tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
Undo Headerindex state cflags wrap# uel scn dba parent-xid nub -------------------------------------------------------------------------------------------
0x00 9 0x00 0x04ec 0x0001 0x0000.003d6d72 0x00000000 0x01 9 0x00 0x04ec 0x0002 0x0000.003d6d72 0x00000000 0x02 9 0x00 0x04ec 0x0003 0x0000.003d6d72 0x00000000
Undo Block
* Rec #0x1 slt: 0x00 objn: 70670 (0x0001140e) objd: Undo type: Regular undo Begin trans Last buffer split: Noncol: 2 nnew: 1 size: 0Vector content:col 1: [ 1] 41
BH (0x643e4b50) file#: 6 rdba: 0x018000a2 (6/162) class: 30 ba: 0x64184000st: XCURRENT md: EXCL fpin: 'kcbwh2: kcbchg1' tch: 0
Before image value ‘A’
Undo Block Address
0x018000a2
tl: 16 fb: --H-FL-- lb: 0x2 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 42
Value 'B' reflects Data block
0x01 0x000a.000.00000534 0x018000d2.01e6.01 C--- 0 scn 0x0000.003dbe3a0x02 0x0007.000.000004ed 0x018000a2.01d5.01 --U- 1 fsc 0x0000.003dbe4e
Undo slot active
BH (0x643fa438) file#: 5 rdba: 0x0140dcad (5/56493) class: 1 ba: 0x643ba000st: XCURRENT md: EXCL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
CR
BH (0x643fa1c8) file#: 5 rdba: 0x0140dcad (5/56493) class: 1 ba: 0x643b6000st: XCURRENT md: EXCL fpin: 'kdswh11: kdst_fetch' tch: 1
Itl Xid Uba Flag Lck Scn/Fsc0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
0x01 0x000a.000.00000534 0x018000d2.01e6.01 C--- 0 scn 0x0000.003dbe3a0x02 0x0007.000.000004ed 0x018000a2.01d5.01 --U- 1 fsc 0x0000.003dbe4etl: 16 fb: --H-FL-- lb: 0x2 cc: 2
col 1: [ 1] 42
BH (0x643fa1c8) file#: 5 rdba: 0x0140dcad (5/56493) class: 1 ba: 0x643b6000st: XCURRENT md: EXCL fpin: 'kdswh11: kdst_fetch' tch: 1
Itl Xid Uba Flag Lck Scn/Fsc0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
0x01 0x000a.000.00000534 0x018000d2.01e6.01 C--- 0 scn 0x0000.003dbe3a0x02 0x0007.000.000004ed 0x018000a2.01d5.01 --U- 1 fsc 0x0000.003dbe4etl: 16 fb: --H-FL-- lb: 0x2 cc: 2
col 1: [ 1] 42
10 0x80 0x04ed 0x0000 0x0000.003dbe4d0x0000.003dbe4e
V$transactionXID_INFO UBA_INFO TXN_INFO-------------------------- --------------- ------------------------------addr : status : IDLExidusn : ubafil : start_date : 2015-12-10 10:52:46xidslot : ubablk : ses_addr : 000000006B21CC20xidsqn : ubasqn :
Check Point;
SGA
Undo SegmentData Block
Shared Pool Buffer Cache
ITLItl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Rowsblock_row_dump:tab 0, row 0, @0x1f88tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
tl: 16 fb: --H-FL-- lb: 0x2 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 42
0x01 0x000a.000.00000534 0x018000d2.01e6.01 C--- 0 scn 0x0000.003dbe3a0x02 0x0007.000.000004ed 0x018000a2.01d5.01 --U- 1 fsc 0x0000.003dbe4e
Summary : DML Operation in a Data Block
Undo Headerindex state cflags wrap# uel scn dba parent-xid nub -------------------------------------------------------------------------------------------
0x00 9 0x00 0x04ec 0x0001 0x0000.003d6d72 0x00000000 0x01 9 0x00 0x04ec 0x0002 0x0000.003d6d72 0x00000000 0x02 9 0x00 0x04ec 0x0003 0x0000.003d6d72 0x00000000
Undo Block
* Rec #0x1 slt: 0x00 objn: 70670 (0x0001140e) objd: Undo type: Regular undo Begin trans Last buffer split: Noncol: 2 nnew: 1 size: 0Vector content:col 1: [ 1] 41
BH (0x643e4b50) file#: 6 rdba: 0x018000a2 (6/162) class: 30 ba: 0x64184000st: XCURRENT md: EXCL fpin: 'kcbwh2: kcbchg1' tch: 0
Before image value ‘A’
Undo Block Address
0x018000a2
Undo slot active
BH (0x643fa438) file#: 5 rdba: 0x0140dcad (5/56493) class: 1 ba: 0x643ba000st: XCURRENT md: EXCL fpin: 'kdswh11: kdst_fetch' tch: 1Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
CR
BH (0x643fa1c8) file#: 5 rdba: 0x0140dcad (5/56493) class: 1 ba: 0x643b6000st: XCURRENT md: EXCL fpin: 'kdswh11: kdst_fetch' tch: 1
Itl Xid Uba Flag Lck Scn/Fsc0x01 0x000a.000.00000534 0x018000d2.01e6.01 --U- 1 fsc 0x0000.003dbe3a0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000tl: 16 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 31 20 20 20 20 20 20 20 20 20col 1: [ 1] 41
0x01 0x000a.000.00000534 0x018000d2.01e6.01 C--- 0 scn 0x0000.003dbe3a0x02 0x0007.000.000004ed 0x018000a2.01d5.01 --U- 1 fsc 0x0000.003dbe4etl: 16 fb: --H-FL-- lb: 0x2 cc: 2
col 1: [ 1] 42
10 0x80 0x04ed 0x0000 0x0000.003dbe4d0x0000.003dbe4e
Value 'B' reflects Data block
V$transactionXID_INFO UBA_INFO TXN_INFO-------------------------- --------------- ------------------------------addr : status : IDLExidusn : ubafil : start_date : 2015-12-10 10:52:46xidslot : ubablk : ses_addr : 000000006B21CC20xidsqn : ubasqn :
Oracle Deep Internal
More information https://youtu.be/s304cY8-IjMYouku
Blog
Video
NAVER http://cafe.naver.com/playexem
ITPUB http://blog.itpub.net/31135309/
Wordpress https://playexem.wordpress.com/
Youtube https://www.youtube.com/channel/UC5wKR_-A0eL_Pn_EMzoauJg
For more information, or to schedule an on-site education, contact via blog or e-mail
Kon Woo, Kwon Kun Oh, Lee
Sook jin, Kim Dae Deok, Lee