oracle redo internal 之 dump 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.