Oracle redo日誌內容探索(一)
昨天,和群裡的小夥伴討論redo內容是什麼,很多人認為redo內就是存放的sql語句,甚至是解析後的sql。
例如,一條udate對錶A修改了20萬條記錄。redo內相當於形成了20萬條單一修改的sql。
原本,我個人狹隘的理解為,redo內應該是資料的變化或者是資料塊的變化。
今天我查了一下官方文件,證明了我也是錯誤的,狹隘的。
下面為官方文件:
11.1.2 Redo Log Contents
Redo log files are filled with redo records.
A redo record, also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database. For example, if you change a salary value in an employee table, you generate a redo record containing change vectors that describe changes to the data segment block for the table, the undo segment data block, and the transaction table of the undo segments.
Redo entries record data that you can use to reconstruct all changes made to the database, including the undo segments. Therefore, the redo log also protects rollback data. When you recover the database using redo data, the database reads the change vectors in the redo records and applies the changes to the relevant blocks.
Redo records are buffered in a circular fashion in the redo log buffer of the SGA (see "How Oracle Database Writes to the Redo Log") and are written to one of the redo log files by the Log Writer (LGWR) database background process. Whenever a transaction is committed, LGWR writes the transaction redo records from the redo log buffer of the SGA to a redo log file, and assigns a system change number (SCN) to identify the redo records for each committed transaction. Only when all redo records associated with a given transaction are safely on disk in the online logs is the user process notified that the transaction has been committed.
Redo records can also be written to a redo log file before the corresponding transaction is committed. If the redo log buffer fills, or another transaction commits, LGWR flushes all of the redo log entries in the redo log buffer to a redo log file, even though some redo records may not be committed. If necessary, the database can roll back these changes.
從文件中獲取 redo record ,記錄的是 redo 條目。 redo 條目,是由一組改變向量組成。那改變向量是資料庫中單個資料塊的改變的描述。
文章中繼續介紹,一個 redo record 包含對錶的資料段, undo 資料段和撤銷段的事務表的改變。
所以,上面我和其他人對 redo 內容的理解都是不正確的。
下面我dump了一個redo log
SQL> conn scott/tiger Connected. SQL> update t1 set t_name='aaaa' where t_id=113; 1 row updated. SQL> commit; Commit complete. SQL> alter system dump logfile '/u01/oracle/oradata/orcl/redo01.log'; System altered. SQL> select distinct sid from v$mystat; SID ---------- 25 SQL> select value from v$diag_info where name like 'Default%'; VALUE ------------------------------------------------------------------------ /u01/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_53340.trc
REDO RECORD - Thread:1 RBA: 0x000265.000077a5.0010 LEN: 0x0250 VLD: 0x05 SCN: 0x0000.00411b7a SUBSCN: 1 06/04/2019 17:43:08 (LWN RBA: 0x000265.000077a5.0010 LEN: 0002 NST: 0001 SCN: 0x0000.00411b7a) CHANGE #1 TYP:0 CLS:181 AFN:3 DBA:0x00c00e50 OBJ:4294967295 SCN:0x0000.0041186c SEQ:1 OP:5.2 ENC:0 RBL:0 ktudh redo: slt: 0x001a sqn: 0x00000039 flg: 0x0012 siz: 200 fbi: 0 uba: 0x00c005e6.0044.10 pxid: 0x0000.000.00000000 CHANGE #2 TYP:0 CLS:182 AFN:3 DBA:0x00c005e6 OBJ:4294967295 SCN:0x0000.0041186b SEQ:1 OP:5.1 ENC:0 RBL:0 ktudb redo: siz: 200 spc: 586 4 flg: 0x0012 seq: 0x0044 rec: 0x10 xid: 0x0053.01a.00000039 ktubl redo: slt: 26 rci: 0 opc: 11.1 [objn: 87351 objd: 87661 tsn: 4] Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No 0x00000000 prev ctl uba: 0x00c005e6.0044.0f prev ctl max cmt scn: 0x0000.0040c5aa prev tx cmt scn: 0x0000.0040c76b txn start scn: 0xffff.ffffffff logon user: 83 prev brb: 12584373 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: 0x0112.00f.00000027 uba: 0x00c02682.0036.09 flg: C--- lkc: 0 scn: 0x0000.003084cb KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x010000ab hdba: 0x010000aa itli: 1 ispac: 0 maxfr: 4858 tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0 ncol: 2 nnew: 1 size: 0 col 1: [ 4] 61 61 61 61 CHANGE #3 TYP:2 CLS:1 AFN:4 DBA:0x010000ab OBJ:87661 SCN:0x0000.00411b4c SEQ:1 OP:11.5 ENC:0 RBL:0 KTB Redo op: 0x11 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: F xid: 0x0053.01a.00000039 uba: 0x00c005e6.0044.10 Block cleanout record, scn: 0x0000.00411b7a ver: 0x01 opt: 0x02, entries follow... itli: 1 flg: 2 scn: 0x0000.003084cb itli: 2 flg: 2 scn: 0x0000.00411b4c KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x010000ab hdba: 0x010000aa itli: 1 ispac: 0 maxfr: 4858 tabn: 0 slot: 0(0x0) flag: 0x2c lock: 1 ckix: 0 ncol: 2 nnew: 1 size: 0 col 1: [ 4] 61 61 61 61 CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:5.20 ENC:0 session number = 25 serial number = 1781 transaction name = version 1866 47552 audit sessionid 720613 Client Id = login username = SCOTT REDO RECORD - Thread:1 RBA: 0x000265.000077a6.0070 LEN: 0x00a4 VLD: 0x01 SCN: 0x0000.00411b7b SUBSCN: 1 06/04/2019 17:43:08 CHANGE #1 TYP:0 CLS:181 AFN:3 DBA:0x00c00e50 OBJ:4294967295 SCN:0x0000.00411b7a SEQ:1 OP:5.4 ENC:0 RBL:0 ktucm redo: slt: 0x001a sqn: 0x00000039 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c005e6.0044.10 ext: 2 spc: 5662 fbi: 0 CHANGE #2 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:24.4 ENC:0 REDO RECORD - Thread:1 RBA: 0x000265.000077a7.0010 LEN: 0x0084 VLD: 0x05 SCN: 0x0000.00411b7e SUBSCN: 1 06/04/2019 17:43:12 (LWN RBA: 0x000265.000077a7.0010 LEN: 0001 NST: 0001 SCN: 0x0000.00411b7d) CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:24.4 ENC:0 END OF REDO DUMP ----- Redo read statistics for thread 1 ----- Read rate (SYNC): 15315Kb in 2.91s => 5.14 Mb/sec Total redo bytes: 15359Kb Longest record: 13Kb, moves: 14/26657 moved: 0Mb (0%) Longest LWN: 845Kb, reads: 1449 Last redo scn: 0x0000.00411b7e (4266878) Change vector header moves = 2687/466 41 (5%) ----------------------------------------------
從上面dumpfile的內容也可以看出,確實記錄的是改變向量的內容。
注:文章寫得倉促,後期可能會有一些補充。
修改內容:
之前對dumpfile內容提取有誤,如果有朋友發現了請海涵,已經修改。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28572479/viewspace-2646791/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle redo日誌內容探索之二Oracle Redo
- redo log日誌挖掘(一)
- 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日誌挖掘(二)
- 【TUNE_ORACLE】Oracle檢查點(四)檢查點對redo日誌的影響和redo日誌大小設定建議Oracle
- MySQLSlowlog慢查詢日誌的配置以及清空日誌內容MySql
- redo日誌檔案學習筆記(一)筆記
- 【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
- 獲取一週內的告警日誌(python|Oracle)PythonOracle
- Undo表空間與redo日誌
- 【REDO】刪除REDO LOG重做日誌組後需要手工刪除對應的日誌檔案