【體系結構】dump檢視update操作redo日誌

海星星hktk發表於2016-02-23
dump檢視update操作redo日誌


1 切日誌後檢視當前日誌組
SYS@PROD>select sequence#,group#,status from v$log;
SYS@PROD>alter system switch logfile;
SYS@PROD>select sequence#,group#,status from v$log;


SYS@PROD>select member from v$logfile where group# = 2;


2 執行dml操作

SCOTT@PROD>select * from emp;

SCOTT@PROD>update emp set sal=9999 where empno=7788;
SCOTT@PROD>select * from emp where empno=7788;
SCOTT@PROD>commit;


3 dump 日誌檔案

SYS@PROD>alter system dump logfile '/u01/app/oracle/oradata/PROD/redo02.log';


SYS@PROD>select value from v$diag_info where name = 'Default Trace File';


4 檢視dump資訊

[oracle@oracle ~]$ vi /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_3125.trc

更新後的值和原始值，轉換為16進位制檢視
SYS@PROD>select dump(9999,16) from dual;
SYS@PROD>select dump(3000,16) from dual;


vi中 搜尋  c2 64 64


變更向量#1存放改動的值，變更向量#4存放之前的值 （回滾用undo）
變更向量#2存放事務資訊，變更向量#3存放redo資訊。

當前行的rowid
SCOTT@PROD>select rowid from emp where empno=7788;

rowid轉換為檔案號、塊號、行號
SCOTT@PROD>select dbms_rowid.rowid_relative_fno(rowid) file#,
   dbms_rowid.rowid_block_number(rowid) block#,
   dbms_rowid.rowid_row_number(rowid) row#
   from emp where empno=7788;

這行資料在檔案4的第151號塊的第8行。


CHANGE1#的16進位制DBA(data block address)轉10進位制，再轉成檔案號，塊號
SCOTT@PROD>select to_number('1000097','xxxxxxxxxxxx') from dual;
檢視對應的檔案號，塊號
SCOTT@PROD>select dbms_utility.data_block_address_file(16777367) file#,
   dbms_utility.data_block_address_block(16777367) block# from dual;







呂星昊
2016.2.23

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29475508/viewspace-1993361/，如需轉載，請註明出處，否則將追究法律責任。

相關文章