How to dump redo log entry
How to dump redo log entry
转储重做日志Oracle databasedump redo logredo entry重做日志记录
1.转储针对特定数据块（4号文件的第10-20号数据块）修改的 redo entry
select file#,name,blocks from v$datafile;
FILE# NAME BLOCKS
---------- -------------------------------------------------- ----------
1 +DATA/test/datafile/system.269.829746597 89600
2 +DATA/test/datafile/sysaux.270.829746621 76800
3 +DATA/test/datafile/undotbs1.271.829746639 25600
4 +DATA/test/datafile/users.273.829746667 640
-转储 test 表第4行所在的数据块的修改记录
SQL> select t.rowid,t.* from scott.test t;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAAPtpAAEAAAADbAAA 10 ACCOUNTING NEW YORK
AAAPtpAAEAAAADbAAB 20 RESEARCH DALLAS
AAAPtpAAEAAAADbAAC 30 SALES CHICAGO
AAAPtpAAEAAAADbAAD 40 OPERATIONS BOSTON
SQL> select DBMS_ROWID.ROWID_OBJECT(rowid) as object#,
2 DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) as file#,
3 dbms_rowid.rowid_block_number(rowid) as block#,
4 DBMS_ROWID.ROWID_ROW_NUMBER(rowid) as row#
5 from scott.test;
OBJECT# FILE# BLOCK# ROW#
---------- ---------- ---------- ----------
64361 4 219 0
64361 4 219 1
64361 4 219 2
64361 4 219 3
--当前属于 current 状态的 online redo log
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 1 10485760 512 2 YES INACTIVE 430256 25-OCT-13 450682 28-OCT-13
2 1 2 10485760 512 2 YES INACTIVE 450682 28-OCT-13 453557 28-OCT-13
3 1 3 10485760 512 2 NO CURRENT 453557 28-OCT-13 2.8147E+14
--修改 scott.test 表中 deptno 为 40 的记录进行测试
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> update scott.test set LOC='BEIJING' where deptno=40;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from scott.test;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BEIJING
--转储 redo entry，确保 redo log 没有切换
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 1 10485760 512 2 YES INACTIVE 430256 25-OCT-13 450682 28-OCT-13
2 1 2 10485760 512 2 YES INACTIVE 450682 28-OCT-13 453557 28-OCT-13
3 1 3 10485760 512 2 NO CURRENT 453557 28-OCT-13 2.8147E+14
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE +DATA/test/onlinelog/group_1.266.829746583 NO
1 ONLINE +FRA/test/onlinelog/group_1.261.829746585 YES
2 ONLINE +DATA/test/onlinelog/group_2.267.829746587 NO
2 ONLINE +FRA/test/onlinelog/group_2.262.829746589 YES
3 ONLINE +DATA/test/onlinelog/group_3.268.829746591 NO
3 ONLINE +FRA/test/onlinelog/group_3.263.829746593 YES
6 rows selected.
SQL> alter system dump logfile '+FRA/test/onlinelog/group_3.263.829746593' dba min 4 219 dba max 4 219;
System altered.
--获取转储文件的路径
col value for a60
SQL>
SQL> select * from v$diag_info;
INST_ID NAME VALUE
---------- -------------------------------------------------- ------------------------------------------------------------
1 Diag Enabled TRUE
1 ADR Base /u01/app/oracle
1 ADR Home /u01/app/oracle/diag/rdbms/test/test
1 Diag Trace /u01/app/oracle/diag/rdbms/test/test/trace
1 Diag Alert /u01/app/oracle/diag/rdbms/test/test/alert
1 Diag Incident /u01/app/oracle/diag/rdbms/test/test/incident
1 Diag Cdump /u01/app/oracle/diag/rdbms/test/test/cdump
1 Health Monitor /u01/app/oracle/diag/rdbms/test/test/hm
1 Default Trace File /u01/app/oracle/diag/rdbms/test/test/trace/test_ora_4160.trc
1 Active Problem Count 1
1 Active Incident Count 4
转储指定 SCN 的 redo entry
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
455005
SQL> update scott.test set LOC='BOSTON' where deptno=40;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from scott.test;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
455020
alter system dump logfile '+FRA/test/onlinelog/group_3.263.829746593' scn min 455005 scn max 455020;
SQL> select * from v$diag_info where NAME ='Default Trace File';
INST_ID NAME VALUE
---------- -------------------------------------------------- ------------------------------------------------------------
1 Default Trace File /u01/app/oracle/diag/rdbms/test/test/trace/test_ora_4160.trc
转储指定 RBA 的 redo entry
SQL> select group#,sequence#,BYTES/BLOCKSIZE as block# from v$log;
GROUP# SEQUENCE# BLOCK#
---------- ---------- ----------
1 1 20480
2 2 20480
3 3 20480
alter system dump logfile '+FRA/test/onlinelog/group_3.263.829746593' rba min 3 1 rba max 3 10;
