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博客

【体系结构】dump查看update操作redo日志

最新文章