oracle redo internal,oracle redo internal 之 dump logfile

常使用的Dumpredo命令

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

(1)Dump日志文件的内容

SQL> selectgroup#,memberfromv$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> altersystem dumplogfile '/u01/app/oracle/oradata/gztes/redo10.log';

System altered.

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

SQL> createtabletest (id number ) tablespace yzkf;

Table created.

SQL> insertintotest(id) values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> colsegment_namefora20;

SQL> select SEGMENT_NAME,FILE_ID,BLOCK_ID,BLOCKS,RELATIVE_FNOfromdba_extents where segment_name ='TEST';

SEGMENT_NAMEFILE_IDBLOCK_IDBLOCKS RELATIVE_FNO

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

TEST14190305814

SQL> altersystemdump 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> selectSEQUENCE#,group#fromv$log;

SEQUENCE#GROUP#

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

15371

15382

15393

15404

15415

1542 6

6 rows selected.

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

System altered.

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

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

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

System altered.

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

SQL> selectdbms_flashback.get_system_change_number fromdual;

GET_SYSTEM_CHANGE_NUMBER

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

8.3844E+12

SQL> select to_char(dbms_flashback.get_system_change_number) fromdual;

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> altersystemdump logfile '/u01/app/oracle/oradata/gztes/redo51.log' scn min 8384403923900 scnmax 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 setevents'immediate trace name redohdr level 10';

Session altered.