Oracle redo日誌內容探索之二
一、Oracle 官方對reod內容的解釋:
二、實驗過程
1、建立新表和索引
oracle@ray115 admin]$ sqlplus raysuen/"*******"@suenpdb SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 16 20:03:59 2024 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> create table t1(tid number,tname varchar2(20)); Table created. SQL> create index t1_id on t1(tid); Index created.
2、確定當前的日誌
SQL> alter system checkpoint; System altered. SQL> set linesize 500 col group# for 999 col mb for 9999 col member for a60 col thread# for 999 col archived for a10 select a.group#,a.BYTES/1024/1024 mb,b.MEMBER,a.thread#,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_time from gv$log a,gv$logfile b where a.GROUP#=b.GROUP# group by a.group#,SQL> SQL> SQL> SQL> SQL> SQL> 2 a.thread#,a.BYTES/1024/1024,b.MEMBER,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_time order by group#; GROUP# MB MEMBER THREAD# SEQUENCE# MEMBERS ARCHIVED STATUS FIRST_TIME NEXT_TIME ------ ----- ------------------------------------------------------------ ------- ---------- ---------- ---------- ---------------- ------------------ ------------------ 1 200 /data/app/oracle/oradata/raysuen/redo01.log 1 37 1 NO CURRENT 20-DEC-23 2 200 /data/app/oracle/oradata/raysuen/redo02.log 1 35 1 NO INACTIVE 20-DEC-23 20-DEC-23 3 200 /data/app/oracle/oradata/raysuen/redo03.log 1 36 1 NO INACTIVE 20-DEC-23 20-DEC-23
3、切換當前redo到新日誌
SQL> conn / as sysdba Connected. SQL> alter system switch logfile; System altered.
4、插入資料
SQL> insert into t1 values(1,'aa'); 1 row created. SQL> commit; Commit complete.
5、DUMP日誌檔案
SQL> set linesize 500 col group# for 999 col mb for 9999 col member for a60 col thread# for 999 col archived for a10 select a.group#,a.BYTES/1024/1024 mb,b.MEMBER,a.thread#,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_time from gv$log a,gv$logfile b where a.GROUP#=b.GROUP# group by a.group#,a.thread#,a.BYTES/1024/1024,b.MEMBER,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_time order by group#;SQL> SQL> SQL> SQL> SQL> SQL> 2 GROUP# MB MEMBER THREAD# SEQUENCE# MEMBERS ARCHIVED STATUS FIRST_TIME NEXT_TIME ------ ----- ------------------------------------------------------------ ------- ---------- ---------- ---------- ---------------- ------------------ ------------------ 1 200 /data/app/oracle/oradata/raysuen/redo01.log 1 37 1 NO INACTIVE 20-DEC-23 16-JAN-24 2 200 /data/app/oracle/oradata/raysuen/redo02.log 1 38 1 NO CURRENT 16-JAN-24 3 200 /data/app/oracle/oradata/raysuen/redo03.log 1 36 1 NO INACTIVE 20-DEC-23 20-DEC-23 SQL> alter system dump logfile '/data/app/oracle/oradata/raysuen/redo02.log'; System altered. SQL> select distinct sid from v$mystat; SID ---------- 66 SQL> select value from v$diag_info where name like 'Default%'; VALUE -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /data/app/oracle/diag/rdbms/raysuen/raysuen/trace/raysuen_ora_2936.trc
6、檢視DUMP日誌檔案內容
#檢視設計的物件ID SQL> col owner for a20 SQL> select owner,object_id,object_name,object_type from dba_objects where owner='RAYSUEN'; OWNER OBJECT_ID OBJECT_NAME OBJECT_TYPE -------------------- ---------- -------------------- -------------------- RAYSUEN 76318 T1 TABLE RAYSUEN 76319 T1_ID INDEX
vi /data/app/oracle/diag/rdbms/raysuen/raysuen/trace/raysuen_ora_2936.trc REDO RECORD - Thread:1 RBA: 0x000026.0000000f.001c LEN: 0x009c VLD: 0x01 CON_UID: 2607644639 SCN: 0x000000000013ddab SUBSCN: 1 01/16/2024 20:08:10 CHANGE #1 CON_ID:3 TYP:1 CLS:4 AFN:11 DBA:0x02c00082 OBJ:76318 SCN:0x000000000013ddab SEQ:1 OP:13.17 ENC:0 RBL:0 FLG:0x0000 ktsphfredo - Format Pagetable Segment Header StartDBA 0x02c00080 nblks: 8 ForceL3 :0 Tsn: 4 objd: 76318 REDO RECORD - Thread:1 RBA: 0x000026.0000000f.00b8 LEN: 0x0060 VLD: 0x01 CON_UID: 2607644639 SCN: 0x000000000013ddab SUBSCN: 1 01/16/2024 20:08:10 CHANGE #1 CON_ID:3 TYP:1 CLS:9 AFN:11 DBA:0x02c00081 OBJ:76318 SCN:0x000000000013ddab SEQ:1 OP:13.19 ENC:0 RBL:0 FLG:0x0000 ktspsfredo - Format Level2 Bitmap Block ParentDBA: 0x02c00082 Start DBA: 0x02c00080 Number: 1incn: 0 REDO RECORD - Thread:1 RBA: 0x000026.0000000f.0118 LEN: 0x0098 VLD: 0x01 CON_UID: 2607644639 SCN: 0x000000000013ddab SUBSCN: 1 01/16/2024 20:08:10 CHANGE #1 CON_ID:3 TYP:1 CLS:8 AFN:11 DBA:0x02c00080 OBJ:76318 SCN:0x000000000013ddab SEQ:1 OP:13.18 ENC:0 RBL:0 FLG:0x0000 ktspffredo - Format Level1 Bitmap Block Start DBA of the range: 0x02c00080 Number of Blocks: 8 nbits: 4 inst: 0 nmrk: 3 ParentDBA: 0x02c00081 Offset: 0 REDO RECORD - Thread:1 RBA: 0x000026.0000000f.01b0 LEN: 0x008c VLD: 0x01 CON_UID: 2607644639 SCN: 0x000000000013ddab SUBSCN: 1 01/16/2024 20:08:10 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:11 DBA:0x02c00082 OBJ:76318 SCN:0x000000000013ddab SEQ:2 OP:13.28 ENC:0 RBL:0 FLG:0x0000 Both the HWMs Low HWM Highwater:: 0x02c00083 ext#: 0 blk#: 3 ext size: 8 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 lfdba: 0x02c00080 High HWM Highwater:: 0x02c00083 ext#: 0 blk#: 3 ext size: 8 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 lfdba: 0x02c00080 REDO RECORD - Thread:1 RBA: 0x000026.00000010.00a4 LEN: 0x01a8 VLD: 0x01 CON_UID: 2607644639 SCN: 0x000000000013ddab SUBSCN: 1 01/16/2024 20:08:10 CHANGE #1 CON_ID:3 TYP:0 CLS:31 AFN:10 DBA:0x018000f0 OBJ:4294967295 SCN:0x000000000013ddab SEQ:2 OP:5.2 ENC:0 RBL:0 FLG:0x0000 ktudh redo: slt: 0x000e sqn: 0x00000290 flg: 0x044a siz: 136 fbi: 112 uba: 0x0180636a.0072.01 pxid: 0x0000.000.00000000 pdbid:2607644639 CHANGE #2 CON_ID:3 TYP:1 CLS:32 AFN:10 DBA:0x0180636a OBJ:4294967295 SCN:0x000000000013ddab SEQ:1 OP:5.1 ENC:0 RBL:0 FLG:0x0000 ktudb redo: siz: 136 spc: 0 flg: 0x000a seq: 0x0072 rec: 0x01 xid: 0x0008.00e.00000290 ktubl redo: slt: 14 rci: 0 opc: 11.1 [objn: 14 objd: 8 tsn: 0] Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No 0x00000000 prev ctl uba: 0x01806369.0072.01 prev ctl max cmt scn: 0x000000000013cbe0 prev tx cmt scn: 0x000000000013cbea txn start scn: 0xffffffffffffffff logon user: 107 prev brb: 25191271 prev bcl: 0 BuExt idx: 0 flg2: 0 KDO undo record: KTB Redo op: 0x04 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: L itl: xid: 0x0007.001.000002b0 uba: 0x01807cdc.0085.16 flg: C--- lkc: 0 scn: 0x000000000013dd53 KDO Op code: DRP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x004083a9 hdba: 0x004000c0 itli: 1 ispac: 0 maxfr: 4863 tabn: 0 slot: 33(0x21) CHANGE #3 CON_ID:3 TYP:0 CLS:1 AFN:8 DBA:0x004083a9 OBJ:8 SCN:0x000000000013ddab SEQ:1 OP:11.2 ENC:0 RBL:0 FLG:0x0000 KTB Redo op: 0x01 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: F xid: 0x0008.00e.00000290 uba: 0x0180636a.0072.01 KDO Op code: IRP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x004083a9 hdba: 0x004000c0 itli: 1 ispac: 0 maxfr: 4863 tabn: 0 slot: 33(0x21) size/delt: 29 fb: K-H-FL-- lb: 0x1 cc: 3 curc: 0 comc: 0 pk: 0x004083a9.21 nk: 0x004083a9.21 null: --- col 0: [ 2] c1 05 col 1: [ 2] c1 0c col 2: [ 3] c2 02 1f REDO RECORD - Thread:1 RBA: 0x000026.0000021a.01cc LEN: 0x0150 VLD: 0x01 CON_UID: 2607644639 SCN: 0x000000000013e1b0 SUBSCN: 1 01/16/2024 20:08:11 CHANGE #1 CON_ID:3 TYP:0 CLS:32 AFN:10 DBA:0x01806368 OBJ:4294967295 SCN:0x000000000013ddab SEQ:2 OP:5.1 ENC:0 RBL:0 FLG:0x0000 ktudb redo: siz: 184 spc: 1390 flg: 0x0022 seq: 0x0072 rec: 0x33 xid: 0x0008.016.00000296 ktubu redo: slt: 22 rci: 50 opc: 11.1 objn: 81 objd: 81 tsn: 0 Undo type: Regular undo Undo type: Last buffer split: No Tablespace Undo: No 0x00000000 KDO undo record: KTB Redo op: 0x02 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: C uba: 0x01806368.0072.31 KDO Op code: IRP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x00407f46 hdba: 0x004003f8 itli: 1 ispac: 0 maxfr: 4863 tabn: 0 slot: 78(0x4e) size/delt: 43 fb: --H-FL-- lb: 0x0 cc: 25 null: 01234567890123456789012345678901234567890123456789012345678901234567890123456789 --NNNNNNNNNN--NNN-NN--NN- col 0: [ 4] c3 08 40 14 col 1: [ 2] c1 0b col 2: *NULL* col 3: *NULL* col 4: *NULL* col 5: *NULL* col 6: *NULL* col 7: *NULL* col 8: *NULL* col 9: *NULL* col 10: *NULL* col 11: *NULL* col 12: [ 2] c1 03 col 13: [ 3] c2 03 38 col 14: *NULL* col 15: *NULL* col 16: *NULL* col 17: [ 1] 80 col 18: *NULL* col 19: *NULL* col 20: [ 1] 80 col 21: [ 1] 80 col 22: *NULL* col 23: *NULL* col 24: [ 1] 80 CHANGE #2 CON_ID:3 TYP:0 CLS:1 AFN:8 DBA:0x00407f46 OBJ:81 SCN:0x000000000013ddab SEQ:1 OP:11.3 ENC:0 RBL:0 FLG:0x0000 KTB Redo op: 0x02 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: C uba: 0x01806368.0072.33 KDO Op code: DRP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x00407f46 hdba: 0x004003f8 itli: 1 ispac: 0 maxfr: 4863 tabn: 0 slot: 78(0x4e) REDO RECORD - Thread:1 RBA: 0x000026.0000021d.0178 LEN: 0x009c VLD: 0x01 CON_UID: 2607644639 SCN: 0x000000000013e1b1 SUBSCN: 1 01/16/2024 20:08:11 CHANGE #1 CON_ID:3 TYP:1 CLS:4 AFN:11 DBA:0x02c0008a OBJ:76319 SCN:0x000000000013e1b1 SEQ:1 OP:13.17 ENC:0 RBL:0 FLG:0x0000 ktsphfredo - Format Pagetable Segment Header StartDBA 0x02c00088 nblks: 8 ForceL3 :0 Tsn: 4 objd: 76319 REDO RECORD - Thread:1 RBA: 0x000026.0000021e.0024 LEN: 0x0060 VLD: 0x01 CON_UID: 2607644639 SCN: 0x000000000013e1b1 SUBSCN: 1 01/16/2024 20:08:11 CHANGE #1 CON_ID:3 TYP:1 CLS:9 AFN:11 DBA:0x02c00089 OBJ:76319 SCN:0x000000000013e1b1 SEQ:1 OP:13.19 ENC:0 RBL:0 FLG:0x0000 ktspsfredo - Format Level2 Bitmap Block ParentDBA: 0x02c0008a Start DBA: 0x02c00088 Number: 1incn: 0 REDO RECORD - Thread:1 RBA: 0x000026.0000021e.0084 LEN: 0x0098 VLD: 0x01 CON_UID: 2607644639 SCN: 0x000000000013e1b1 SUBSCN: 1 01/16/2024 20:08:11 CHANGE #1 CON_ID:3 TYP:1 CLS:8 AFN:11 DBA:0x02c00088 OBJ:76319 SCN:0x000000000013e1b1 SEQ:1 OP:13.18 ENC:0 RBL:0 FLG:0x0000 ktspffredo - Format Level1 Bitmap Block Start DBA of the range: 0x02c00088 Number of Blocks: 8 nbits: 2 inst: 0 nmrk: 3 ParentDBA: 0x02c00089 Offset: 0 REDO RECORD - Thread:1 RBA: 0x000026.0000021e.011c LEN: 0x008c VLD: 0x01 CON_UID: 2607644639 SCN: 0x000000000013e1b1 SUBSCN: 1 01/16/2024 20:08:11 CHANGE #1 CON_ID:3 TYP:0 CLS:4 AFN:11 DBA:0x02c0008a OBJ:76319 SCN:0x000000000013e1b1 SEQ:2 OP:13.28 ENC:0 RBL:0 FLG:0x0000 Both the HWMs Low HWM Highwater:: 0x02c0008b ext#: 0 blk#: 3 ext size: 8 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 lfdba: 0x02c00088 High HWM Highwater:: 0x02c0008b ext#: 0 blk#: 3 ext size: 8 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 lfdba: 0x02c00088
7、檢視DUMP日誌檔案內所有的Object ID對應的物件名稱
[root@ray115 ~]# egrep "OBJ:" /data/app/oracle/diag/rdbms/raysuen/raysuen/trace/raysuen_ora_2936.trc | awk '{for(i=1;i<NF;i++){if(match($i,"OBJ:")) print $i}}' | sort | uniq OBJ:10 OBJ:100 OBJ:2 OBJ:4294967295 OBJ:76318 OBJ:76319 OBJ:8 OBJ:81 OBJ:82 OBJ:9 [root@ray115 ~]# egrep "OBJ:" /data/app/oracle/diag/rdbms/raysuen/raysuen/trace/raysuen_ora_2936.trc | awk '{for(i=1;i<NF;i++){if(match($i,"OBJ:")) print $i}}' | sort | uniq | awk -F: '{printf $2","}END{printf "\n"}' 10,100,2,4294967295,76318,76319,8,81,82,9,
SQL> select owner,object_id,object_name,object_type from dba_objects where object_id in (10,100,2,4294967295,76318,76319,8,81,82,9); OWNER OBJECT_ID OBJECT_NAME OBJECT_TYPE -------------------- ---------- ------------------------------ ----------------------- SYS 2 C_OBJ# CLUSTER SYS 8 C_FILE#_BLOCK# CLUSTER SYS 9 I_FILE#_BLOCK# INDEX SYS 10 C_USER# CLUSTER SYS 81 DEFERRED_STG$ TABLE SYS 82 I_DEFERRED_STG1 INDEX SYS 100 SEQ$ TABLE RAYSUEN 76318 T1 TABLE RAYSUEN 76319 T1_ID INDEX 9 rows selected.
總結:
1、redo內記錄redo record
2、redo內容包含資料物件,及其涉及的索引。
3、redo內容包含對應底層修改的基表。
來自 “ ITPUB部落格 ” ,連結:https://blog.itpub.net/28572479/viewspace-3005628/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle redo日誌內容探索(一)Oracle Redo
- oracle redo internal (2) --- dump內容理解Oracle Redo
- oracle redo internal (2) --- dump內容理解Oracle
- Oracle調整redo log日誌大小Oracle
- Oracle 警告日誌 (alert log) 中包含哪些內容 ?Oracle
- REDO日誌管理
- oracle redo檔案記錄的內容Oracle Redo
- 【REDO】Oracle 日誌挖掘,分析歸檔日誌線上日誌主要步驟Oracle
- redo日誌損壞
- redo重做日誌管理
- MySQL 日誌 undo | redoMySql
- Oracle產生redo日誌量大小統計Oracle
- Oracle redo 日誌切換時間頻率Oracle Redo
- oracle redo 日誌刪除後的恢復Oracle Redo
- oracle丟失的是所有的redo日誌組Oracle
- 檢視Oracle的redo日誌切換頻率Oracle
- Oracle Redo(重做日誌) 模擬故障和恢復Oracle Redo
- MySQL重做日誌(redo log)MySql
- redo log日誌挖掘(二)
- redo log日誌挖掘(一)
- mysql 日誌之二進位制日誌MySql
- 【TUNE_ORACLE】Oracle檢查點(四)檢查點對redo日誌的影響和redo日誌大小設定建議Oracle
- MySQLSlowlog慢查詢日誌的配置以及清空日誌內容MySql
- 【REDO】Oracle redo內部結構Oracle Redo
- apache日誌內容匯入資料庫Apache資料庫
- 檢視oracle的redo日誌組切換頻率Oracle
- Oracle資料庫減少redo日誌產生方式Oracle資料庫
- oracle聯機日誌檔案REDO LOGFILE簡述Oracle
- MySQL redo與undo日誌解析MySql
- MySQL之事務和redo日誌MySql
- redo日誌組資訊查詢
- REDO日誌切換頻率
- 線上重建redo log日誌組
- Oracle11g redo log 建立、新增、刪除(重做日誌組,重做日誌檔案)Oracle
- oracle redo日誌產生量測試及比較1Oracle Redo
- Undo表空間與redo日誌
- 【REDO】刪除REDO LOG重做日誌組後需要手工刪除對應的日誌檔案
- MySQL 日誌系統 redo log、binlogMySql