常使用的 Dump redo 命令

Dump logfile 的方法有很多， 现总结如下，后续将对orcacle 的 redo 进行更深入的研究

(1) Dump日志文件的内容

SQL> select group#,member from v$logfile;

常使用的 Dump redo 命令

Dump logfile 的方法有很多， 现总结如下，后续将对orcacle 的 redo 进行更深入的研究

(1) Dump日志文件的内容

SQL> select group#,member from v$logfile;

GROUP#

----------

MEMBER

--------------------------------------------------------------------------------

6

/u01/app/oracle/oradata/gztes/redo60.log

6

/u01/app/oracle/oradata/gztes/redo61.log

6

/u01/app/oracle/oradata/gztes/redo62.log

SQL> alter system dump logfile '/u01/app/oracle/oradata/gztes/redo10.log';

System altered.

(2) 根据数据文件中的数据块 dump

SQL> create table test (id number ) tablespace yzkf;

Table created.

SQL> insert into test(id) values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> col segment_name for a20;

SQL> select SEGMENT_NAME,FILE_ID,BLOCK_ID,BLOCKS,RELATIVE_FNO from dba_extents where segment_name ='TEST';

SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS RELATIVE_FNO

-------------------- ---------- ---------- ---------- ------------

TEST 14 190305 8 14

SQL> alter system dump logfile '/u01/app/oracle/oradata/gztes/redo51.log' dba min 14 190305 dba max 14 190313;

System altered.

(3) 指定 RBA(redo byte addresses) dump

RBA: 0x000605.00017b82.01a4 由三部分组成：

0x000605： 日志的序列号

00017b82： redo日志中块号

01a4：日志文件中块内部的偏移量

SQL> select SEQUENCE#,group# from v$log;

SEQUENCE# GROUP#

---------- ----------

1537 1

1538 2

1539 3

1540 4

1541 5

1542 6

6 rows selected.

SQL> alter system dump logfile '/u01/app/oracle/oradata/gztes/redo51.log' Rba min 5 1541 RBA max 5 1541;

System altered.

(4) 通过具体的LAYER 和OPCODE dump 日志文件

Dump ‘/u01/app/oracle/oradata/gztes/redo51.log’中操作为OP为11.2(insert 操作)

SQL> alter system dump logfile '/u01/app/oracle/oradata/gztes/redo51.log' layer 11 opcode 2;

System altered.

(5) Dump出两个SCN 号 之间的日志

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

8.3844E+12

SQL> select to_char( dbms_flashback.get_system_change_number) from dual;

TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE

----------------------------------------

8384403923900

SQL> select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;

MAX(KTUXESCNW*POWER(2,32)+KTUXESCNB)

------------------------------------

8.3844E+12

SQL> alter system dump logfile '/u01/app/oracle/oradata/gztes/redo51.log' scn min 8384403923900 scn max 8384403923977;

System altered.

(6) Dump 日志文件头

通过oradebug 获取trace

SQL> oradebug setmypid

Statement processed.

SQL> oradebug dump redohdr 3;

Statement processed.

SQL> oradebug tracefile_name

/u01/app/oracle/admin/gztes/udump/gztes_ora_25062.trc

通过设置事件获取dump文件

SQL> alter session set events'immediate trace name redohdr level 10';

Session altered.