[20170412]分析重做日誌.txt
[20170412]分析重做日誌.txt
--//自己很少做重做日誌轉儲，測試看看。
1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table empx as select * from emp;
Table created.
SCOTT@book> @ &r/qq empx 1
ROWID OBJECT FILE BLOCK ROW ROWID_DBA EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ------------ ------------ ------------ ------------ -------------------- ------------ ---------- --------- ------------ ------------------- ------------ ------------ ------------
AAAWGtAAEAAAAIrAAA 90541 4 555 0 0x100022B 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
SCOTT@book> select * from empx where empno=7788;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------ ---------- --------- ------------ ------------------- ------------ ------------ ------------
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
$ cat ax.sql
column member new_value v_member
column member noprint
set numw 12
--//alter system archive log current;
SELECT member FROM v$log a, v$logfile b WHERE a.group#(+) = b.group# and a.STATUS='CURRENT' and rownum=1;
column curr1 new_value v_curr1
select current_scn curr1 from v$database;
update scott.empx set sal=4000 where empno=7788;
commit;
column curr2 new_value v_curr2
select current_scn curr2 from v$database;
prompt alter system dump logfile '&&v_member' scn min &&v_curr1 scn max &&v_curr2;
alter system dump logfile '&&v_member' scn min &&v_curr1 scn max &&v_curr2;
2.執行指令碼：
--//檢查轉儲內容：
REDO RECORD - Thread:1 RBA: 0x00033a.00001aae.0010 LEN: 0x021c VLD: 0x0d
SCN: 0x0003.17659fa9 SUBSCN: 1 04/12/2017 11:13:30
(LWN RBA: 0x00033a.00001aae.0010 LEN: 0002 NST: 0001 SCN: 0x0003.17659fa6)
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x0100022b OBJ:90541 SCN:0x0003.17659f96 SEQ:2 OP:11.19 ENC:0 RBL:0
KTB Redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0008.004.000006fe uba: 0x00c05eb3.0370.32
Array Update of 1 rows:
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 2 ckix: 12
ncol: 8 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x0100022b hdba: 0x0100022a
itli: 2 ispac: 0 maxfr: 4858
vect = 30
col 5: [ 2] c2 29
CHANGE #2 TYP:0 CLS:31 AFN:3 DBA:0x00c000f0 OBJ:4294967295 SCN:0x0003.17659ec4 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0004 sqn: 0x000006fe flg: 0x0012 siz: 164 fbi: 0
uba: 0x00c05eb3.0370.32 pxid: 0x0000.000.00000000
CHANGE #3 TYP:0 CLS:31 AFN:3 DBA:0x00c000f0 OBJ:4294967295 SCN:0x0003.17659fa9 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0004 sqn: 0x000006fe srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c05eb3.0370.32 ext: 2 spc: 2046 fbi: 0
CHANGE #4 TYP:0 CLS:32 AFN:3 DBA:0x00c05eb3 OBJ:4294967295 SCN:0x0003.17659ec3 SEQ:3 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 164 spc: 2212 flg: 0x0012 seq: 0x0370 rec: 0x32
xid: 0x0008.004.000006fe
ktubl redo: slt: 4 rci: 0 opc: 11.1 [objn: 90541 objd: 90541 tsn: 4]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c05eb3.0370.2f
prev ctl max cmt scn: 0x0003.17658385 prev tx cmt scn: 0x0003.176584b8
txn start scn: 0x0003.17659f9c logon user: 83 prev brb: 12607153 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
Array Update of 1 rows:
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 0 ckix: 12
ncol: 8 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x0100022b hdba: 0x0100022a
itli: 2 ispac: 0 maxfr: 4858
vect = 30
col 5: [ 2] c2 1f
END OF REDO DUMP
$ grep OP: /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_57112.trc
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x0100022b OBJ:90541 SCN:0x0003.17659f96 SEQ:2 OP:11.19 ENC:0 RBL:0
CHANGE #2 TYP:0 CLS:31 AFN:3 DBA:0x00c000f0 OBJ:4294967295 SCN:0x0003.17659ec4 SEQ:1 OP:5.2 ENC:0 RBL:0
CHANGE #3 TYP:0 CLS:31 AFN:3 DBA:0x00c000f0 OBJ:4294967295 SCN:0x0003.17659fa9 SEQ:1 OP:5.4 ENC:0 RBL:0
CHANGE #4 TYP:0 CLS:32 AFN:3 DBA:0x00c05eb3 OBJ:4294967295 SCN:0x0003.17659ec3 SEQ:3 OP:5.1 ENC:0 RBL:0
--//redo中change vector裡面常見的op的含義,參考：http://blog.itpub.net/19602/viewspace-1060164/
11.19 沒有找到，估計更新操作。11.5更新記錄
5.2 事務開始
5.4 commit
5.1 修改undo header中的事務資訊
3.分析：
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x0100022b OBJ:90541 SCN:0x0003.17659f96 SEQ:2 OP:11.19 ENC:0 RBL:0
--//修改的資料塊。
SCOTT@book> select rowid from empx where empno=7788;
ROWID
------------------
AAAWGtAAEAAAAIrAAH
SCOTT@book> @ &r/rowid AAAWGtAAEAAAAIrAAH
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
------------ ------------ ------------ ------------ -------------------- -------------------- ----------------------------------------
90541 4 555 7 0x100022B 4,555 alter system dump datafile 4 block 555 ;
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 2 ckix: 12
ncol: 8 nnew: 1 size: 0
--//行＝7,欄位總數8,修改1個欄位，長度沒有變化。
col 5: [ 2] c2 29
SCOTT@book> select dump(4000,16) from dual ;
DUMP(4000,16)
------------------
Typ=2 Len=2: c2,29
--//第5個欄位，這裡竟然記錄的是修改後的值4000(IMU特性）。bbed觀察資料塊。
BBED> p ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0008
ub2 kxidslt @70 0x0004
ub4 kxidsqn @72 0x000006fe
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c05eb3
ub2 kubaseq @80 0x0370
ub1 kubarec @82 0x32
ub2 ktbitflg @84 0x2001 (KTBFUPB)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x17659fa9
--//op: F xid: 0x0008.004.000006fe uba: 0x00c05eb3.0370.32 都可以對上。
CHANGE #2 TYP:0 CLS:31 AFN:3 DBA:0x00c000f0 OBJ:4294967295 SCN:0x0003.17659ec4 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0004 sqn: 0x000006fe flg: 0x0012 siz: 164 fbi: 0
uba: 0x00c05eb3.0370.32 pxid: 0x0000.000.00000000
--//修改是回滾段。實際上是事務表。也就是事務開始。
SCOTT@book> @ &r/dfb16 0x00c000f0
RFILE# BLOCK# TEXT
------------ ------------ ------------------------------------------------------------
3 240 alter system dump datafile 3 block 240 ;
--//usn=8
SCOTT@book> select * from V$ROLLname where usn=8;
USN NAME
------------ --------------------------------------------------
8 _SYSSMU8_517538920$
SCOTT@book> select owner,segment_name,segment_type,header_file,header_block from dba_segments where segment_name='_SYSSMU8_517538920$';
OWNER SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK
------ -------------------- ------------------ ------------ ------------
SYS _SYSSMU8_517538920$ TYPE2 UNDO 3 240
CHANGE #3 TYP:0 CLS:31 AFN:3 DBA:0x00c000f0 OBJ:4294967295 SCN:0x0003.17659fa9 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0004 sqn: 0x000006fe srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c05eb3.0370.32 ext: 2 spc: 2046 fbi: 0
--//修改是回滾段。實際上是事務表。
--//實際上這裡是提交修改標識。
CHANGE #4 TYP:0 CLS:32 AFN:3 DBA:0x00c05eb3 OBJ:4294967295 SCN:0x0003.17659ec3 SEQ:3 OP:5.1 ENC:0 RBL:0
--//相當於在undo段儲存前映像。
col 5: [ 2] c2 1f
SCOTT@book> select dump(3000,16) from dual ;
DUMP(3000,16)
------------------
Typ=2 Len=2: c2,1f
--//10g開始引入IMU，導致在重做日誌中記錄順序發生了改變。
SYS@book> alter database flashback on ;
Database altered.
SYS@book> alter system archive log current ;
System altered.
--//這樣相當於禁用IMU。再修改會原值看看。
$ cat ax.sql
column member new_value v_member
column member noprint
set numw 12
--//alter system archive log current;
SELECT member FROM v$log a, v$logfile b WHERE a.group#(+) = b.group# and a.STATUS='CURRENT' and rownum=1;
column curr1 new_value v_curr1
select current_scn curr1 from v$database;
update scott.empx set sal=3000 where empno=7788;
commit;
column curr2 new_value v_curr2
select current_scn curr2 from v$database;
prompt alter system dump logfile '&&v_member' scn min &&v_curr1 scn max &&v_curr2;
alter system dump logfile '&&v_member' scn min &&v_curr1 scn max &&v_curr2;
SCOTT@book> @ ax.sql
...
REDO RECORD - Thread:1 RBA: 0x00033b.00000019.0010 LEN: 0x01fc VLD: 0x05
SCN: 0x0003.1765ae19 SUBSCN: 1 04/12/2017 12:06:16
(LWN RBA: 0x00033b.00000019.0010 LEN: 0002 NST: 0001 SCN: 0x0003.1765ae17)
CHANGE #1 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0003.1765ae13 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x000a sqn: 0x00004fc8 flg: 0x0012 siz: 164 fbi: 0
uba: 0x00c0028c.0fd5.39 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:36 AFN:3 DBA:0x00c0028c OBJ:4294967295 SCN:0x0003.1765ae12 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 164 spc: 1094 flg: 0x0012 seq: 0x0fd5 rec: 0x39
xid: 0x000a.00a.00004fc8
ktubl redo: slt: 10 rci: 0 opc: 11.1 [objn: 90541 objd: 90541 tsn: 4]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c0028c.0fd5.38
prev ctl max cmt scn: 0x0003.1765ac40 prev tx cmt scn: 0x0003.1765ac47
txn start scn: 0xffff.ffffffff logon user: 83 prev brb: 12583553 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
Array Update of 1 rows:
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 0 ckix: 12
ncol: 8 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x0100022b hdba: 0x0100022a
itli: 3 ispac: 0 maxfr: 4858
vect = 30
col 5: [ 2] c2 29
CHANGE #3 TYP:2 CLS:1 AFN:4 DBA:0x0100022b OBJ:90541 SCN:0x0003.17659fa9 SEQ:2 OP:11.19 ENC:0 RBL:0
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x000a.00a.00004fc8 uba: 0x00c0028c.0fd5.39
Block cleanout record, scn: 0x0003.1765ae19 ver: 0x01 opt: 0x02, entries follow...
itli: 2 flg: 2 scn: 0x0003.17659fa9
Array Update of 1 rows:
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 3 ckix: 12
ncol: 8 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x0100022b hdba: 0x0100022a
itli: 3 ispac: 0 maxfr: 4858
vect = 30
col 5: [ 2] c2 1f
REDO RECORD - Thread:1 RBA: 0x00033b.0000001a.001c LEN: 0x0060 VLD: 0x01
SCN: 0x0003.1765ae1a SUBSCN: 1 04/12/2017 12:06:16
CHANGE #1 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0003.1765ae19 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x000a sqn: 0x00004fc8 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c0028c.0fd5.39 ext: 2 spc: 928 fbi: 0
END OF REDO DUMP
$ grep "OP:" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_57447.trc
CHANGE #1 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0003.1765ae13 SEQ:1 OP:5.2 ENC:0 RBL:0
CHANGE #2 TYP:0 CLS:36 AFN:3 DBA:0x00c0028c OBJ:4294967295 SCN:0x0003.1765ae12 SEQ:1 OP:5.1 ENC:0 RBL:0
CHANGE #3 TYP:2 CLS:1 AFN:4 DBA:0x0100022b OBJ:90541 SCN:0x0003.17659fa9 SEQ:2 OP:11.19 ENC:0 RBL:0
CHANGE #1 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0003.1765ae19 SEQ:1 OP:5.4 ENC:0 RBL:0
--//redo中change vector裡面常見的op的含義,參考：http://blog.itpub.net/19602/viewspace-1060164/
5.2 事務開始
5.1 修改undo header中的事務資訊
11.19 沒有找到，估計更新操作。11.5更新記錄
5.4 commit
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2137102/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- dml操作重做日誌分析
- logmnr分析線上重做日誌
- 重做日誌
- 聯機重做日誌、歸檔日誌、備用重做日誌
- logmnr分析歸檔重做日誌
- 重做日誌管理
- 為oracle新增重做日誌組及重做日誌成員Oracle
- redo重做日誌管理
- Oracle的重做日誌Oracle
- 恢復重做日誌
- MySQL重做日誌（redo log）MySql
- 重做日誌的恢復
- 重做日誌基礎操作
- oracle 恢復重做日誌Oracle
- oracle線上調整重做日誌Oracle
- 手工建立多路重做日誌組
- 重做日誌大小的設定
- Oracle重做日誌調整技巧Oracle
- Oracle11g redo log 建立、新增、刪除（重做日誌組，重做日誌檔案）Oracle
- 16、重做日誌檔案的狀態及重做日誌組的狀態說明
- Oracle重做日誌檔案基礎Oracle
- oracle 聯機重做日誌檔案Oracle
- 重做日誌檔案中的SCN
- SQL Server重做日誌管理機制SQLServer
- 7.管理重做日誌(筆記)筆記
- [20170412]op code列表.txt
- 2 Day DBA-管理Oracle例項-管理聯機重做日誌- 多路複用聯機重做日誌Oracle
- 日誌分析-apache日誌分析Apache
- 重做日誌檔案損壞測試
- 線上修改重做日誌檔案的大小
- 修改Oracle重做日誌檔案的大小Oracle
- 重做日誌檔案的相關操作
- 重做日誌（redo log）相關總結
- 丟失重做日誌怎麼處理
- 丟失重做日誌讀書筆記筆記
- 怎樣改變重做日誌的大小
- 2 Day DBA-管理Oracle例項-管理聯機重做日誌-練習：多路複用聯機重做日誌Oracle
- MySQL重做日誌恢復資料的流程MySql