oracle redo internal 之 dump logfile

常使用的 Dump redo 命令

Dump logfile 的方法有很多， 現總結如下，後續將對orcacle 的 redo 進行更深入的研究

(1) Dump日誌檔案的內容

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

[@more@]

常使用的 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.