敗家貓:logminer應用(查尋所操作過的sql語法)

<< General Space Management Enhancements | 回blog首頁 | 10g ASM (Automatic Storage Management) >>

2007年11月20日

logminer應用(查尋所操作過的sql語法)

主要目地:查尋所操作過的sql語法

前言:

在操作logmnr之前先提一下redo log file的作用，redo log file 主要是用來存放執行中、執行後的sql指令，所以在redo log裏可查到
1、資料改變的記錄 (insert / update / delete / ddl的動作)
2、scn值
3、commit_scn
4、owner、table name
5、redo、undo的sql語法等等

重要的view有

V$LOGMNR_CONTENTS : 存放我們解譯後的sql (注意，當我們離開這個session後，裏面的資料將會自動清除)
V$LOGMNR_DICTIONARY :如是使用字典檔時，這裏會有一筆資料；字典檔的用處主要是用於反解譯oracle的語言成我們所明了的文字，存放oracle內部的資料都是編碼過的，如 insert into Object#2581(col#1, col#2) values (hextoraw('4a6f686e20446f65'),hextoraw('c306'));"，這種東西對我們來說是很難了解的，所以我們如要看到類似INSERT INTO emp(name, salary) VALUES ('John Doe', 50000); 這種描述的話，就需要字典檔來幫忙了
V$LOGMNR_LOGFILE:記錄被分析的檔案明細
V$LOGMNR_LOGS Synonym for V_$LOGMNR_LOGS

可使用的方式大致上分成2類
1、不需要建立字典

簡單說一下測試流程
1: 新增幾筆資料或刪除幾筆資料
2: execute dbms_logmnr.add_logfile(LogFileName => '/u01/app/oracle/oradata/binhu/redo03.log',Options => dbms_logmnr.NEW);
(將現在使用的redo log 加進分析清單中，第一個檔要使用new)
execute dbms_logmnr.add_logfile(LogFileName => '/u01/app/oracle/oradata/binhu/redo02.log',Options => dbms_logmnr.ADDFILE);
(如你要再加入別的檔的話，就要使用addfile了)
3: execute dbms_logmnr.start_logmnr(Options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
(進行分析)
4:select v$logmnr_contents
(查尋相關的資料)

細步操作如下:

1、先建立一些測試用的資料

SQL> create table test_log (id number,id2 number) tablespace tbs;
SQL> insert into test_log values (1,1);
SQL> insert into test_log values (2,2);
SQL> insert into test_log values (3,3);
SQL> commit;

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1612491

SQL> insert into test_log values (4,4);
SQL> insert into test_log values (5,5);

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1612590

SQL> insert into test_log values (6,6);

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1612672

SQL> commit;

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1612749

2、查一下現在用的redo log file是那一個(最好看一下alter log檔裏是否在上列操作中有產生archive的動作)
SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
         1          1         89 104857600          1 YES INACTIVE
      1612040 17-JUL-07

         2          1         90 104857600          1 YES INACTIVE
      1612043 17-JUL-07

         3          1         91 104857600          1 NO CURRENT
      1612046 17-JUL-07

SQL> select * from v$logfile;

    GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
         3         ONLINE
/u01/app/oracle/oradata/binhu/redo03.log

         2         ONLINE
/u01/app/oracle/oradata/binhu/redo02.log

         1         ONLINE
/u01/app/oracle/oradata/binhu/redo01.log

3、得知redo03為現在所使用的，所以把他列為起始點，當然了也可不必管他，全把redo log file全加入後再一次分析也可，只是因為量很多的話，查尋起來會非常的慢，所以多半是一個檔一個檔的新增、刪除後再進行分析

SQL> execute dbms_logmnr.add_logfile(LogFileName => '/u01/app/oracle/oradata/binhu/redo03.log',Options => dbms_logmnr.NEW);
SQL> execute dbms_logmnr.start_logmnr(Options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG);

4、因為是直接使用字典檔，所以這裏沒有記錄
SQL> select count(*) from v$logmnr_dictionary;

COUNT(*)
----------
         0

5、查一下現在被分析的檔案清單
SQL> select * from v$logmnr_logfile;

    LOG_ID FILENAME                                                                           LOW_TIME NEXT_TIME      DB_ID DB_NAME RESET_SCNWRP RESET_SCNBAS RESET_SCN_TIME THREAD_ID THREAD_SQN LOW_SCNWRP LOW_SCNBAS NEXT_SCNWRP NEXT_SCNBAS FILE_STATE
---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- -------- ------------ ------------ -------------- ---------- ---------- ---------- ---------- ----------- ----------- -------------
        91 /u01/app/oracle/oradata/binhu/redo03.log                                          628186336          0 645032534 BINHU               0       744591      626551237          1         91          0    1612046       65535 4294967295 FILE_OPEN

6、測一下再加入一個檔
SQL> execute dbms_logmnr.add_logfile(LogFileName => '/u01/app/oracle/oradata/binhu/redo02.log',Options => dbms_logmnr.ADDFILE);

7、再次查看，會看到2個檔案已被加入了
SQL> select * from v$logmnr_logfile;

    LOG_ID FILENAME                                                                           LOW_TIME NEXT_TIME      DB_ID DB_NAME RESET_SCNWRP RESET_SCNBAS RESET_SCN_TIME THREAD_ID THREAD_SQN LOW_SCNWRP LOW_SCNBAS NEXT_SCNWRP NEXT_SCNBAS FILE_STATE
---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- -------- ------------ ------------ -------------- ---------- ---------- ---------- ---------- ----------- ----------- -------------
        90 /u01/app/oracle/oradata/binhu/redo02.log                                          628186334 628186336 645032534 BINHU               0       744591      626551237          1         90          0    1612043           0     1612046
        91 /u01/app/oracle/oradata/binhu/redo03.log                                          628186336          0 645032534 BINHU               0       744591      626551237          1         91          0    1612046       65535 4294967295 FILE_OPEN

8、再次執行分析
SQL> execute dbms_logmnr.start_logmnr(Options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG);

SQL> select count(*) from v$logmnr_contents;

COUNT(*)
----------
      4616

9、查尋一下我們sql是否有存在

SQL> select scn,cscn,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'),sql_redo,sql_undo from v$logmnr_contents where SQL_REDO like '%TEST_LOG%';

       SCN       CSCN TO_CHAR(TIMESTAMP,'YYYY-MM-DDH SQL_REDO                                                                         SQL_UNDO
---------- ---------- ------------------------------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
   1612154            2007-07-17 16:12:50            insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME" delete from "SYS"."OBJ$" where "OBJ#" = '33717' and "DATAOBJ#" = '33717' and "OW
   1612275            2007-07-17 16:13:34            insert into "SYS"."TEST_LOG"("ID","ID2") values ('1','1');                       delete from "SYS"."TEST_LOG" where "ID" = '1' and "ID2" = '1' and ROWID = 'AAAIO
   1612279            2007-07-17 16:13:46            insert into "SYS"."TEST_LOG"("ID","ID2") values ('2','2');                       delete from "SYS"."TEST_LOG" where "ID" = '2' and "ID2" = '2' and ROWID = 'AAAIO
   1612391            2007-07-17 16:13:55            insert into "SYS"."TEST_LOG"("ID","ID2") values ('3','3');                       delete from "SYS"."TEST_LOG" where "ID" = '3' and "ID2" = '3' and ROWID = 'AAAIO
   1612494            2007-07-17 16:14:35            insert into "SYS"."TEST_LOG"("ID","ID2") values ('4','4');                       delete from "SYS"."TEST_LOG" where "ID" = '4' and "ID2" = '4' and ROWID = 'AAAIO
   1612498            2007-07-17 16:14:48            insert into "SYS"."TEST_LOG"("ID","ID2") values ('5','5');                       delete from "SYS"."TEST_LOG" where "ID" = '5' and "ID2" = '5' and ROWID = 'AAAIO
   1612666            2007-07-17 16:15:27            insert into "SYS"."TEST_LOG"("ID","ID2") values ('6','6');                       delete from "SYS"."TEST_LOG" where "ID" = '6' and "ID2" = '6' and ROWID = 'AAAIO

7 rows selected

10、我們也可查尋一個範圍，就可看到 commit 的scn值了，就可由這樣子做到精準點的還原

select * from v$logmnr_contents where scn between 1612491 and 1612590;

如要分析的是archive log 檔呢，其實方法就跟上列相同，只是加入的檔案指到archive log檔而已

簡單說一下測試流程
1: insert / update / delete 資料
2: alter system archive log current;
(產生 archive log 檔)
3:execute dbms_logmnr.add_logfile(logfilename=>'/xxxxxx',options=>dbms_logmnr.new);
(xxxxx : 為你的archive log 檔)
4:execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.direct_form_redo_logs);
(採用的分析方法)
5:select v$logmnr_contents;
(查看是否真的存在)

細步操作如下:

1、建立幾測試資料
SQL> insert into test1 values ('ok?');
SQL> insert into test1 values ('ok~~');
SQL> commit;

2、手動產生archive log檔
SQL> alter system archive log current;

3、把產生的archive log檔加入分析清單中
SQL> execute dbms_logmnr.add_logfile(LogFileName => '/u01/arch/1_172.dbf',Options => dbms_logmnr.NEW);

4、開始解譯
SQL> execute dbms_logmnr.start_logmnr(Options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG);

5、查尋資料
SQL> select sql_redo from v$logmnr_contents where v$logmnr_contents.SQL_REDO like '%TEST1%';

SQL_REDO
--------------------------------------------------------------------------------
insert into "SYS"."TEST1"("ID") values ('ok? ');
insert into "SYS"."TEST1"("ID") values ('ok~~ ');

2、使用字典檔
什情況需要用到字典檔，我們可以想一下第1種方式應該是直接讀取oracle內部的字典檔，如我們刪除了一個tablespace、datafile後，相對而言oracle也會刪除在字典檔裏對應的資料，這時我們再去分析archive log檔時，就無法反解譯成我們了解的語言了，所以使用字典檔的方法還是有必要存在的，所以當做了重大的改變時，可考慮是否把字典檔給保留一份，以利往後的需求
測試流程我共有二類，最大的差別是其中一種需要重開機，另一種是把字典檔存在archive log 檔中

把字典檔存在archive log檔中的操作流程:

1、先清除一下環境
SQL> alter system switch logfile;
SQL> alter system archive log current;

2、產生字典檔
SQL> execute dbms_logmnr_d.build(options => dbms_logmnr_d.STORE_IN_REDO_LOGS);

3、查看一下所以有的字典檔清單 (只要取其中一份，我是取最新的日期的檔案 1_118.dbf)
SQL> select al.NAME from v$archived_log al where dictionary_begin = 'YES';

NAME
--------------------------------------------------------------------------------
/u01/arch/1_107.dbf
/u01/arch/1_109.dbf
/u01/arch/1_113.dbf
/u01/arch/1_118.dbf

SQL> select al.NAME from v$archived_log al where dictionary_end = 'YES';

NAME
--------------------------------------------------------------------------------
/u01/arch/1_107.dbf
/u01/arch/1_109.dbf
/u01/arch/1_113.dbf
/u01/arch/1_118.dbf

4、先把字典檔給加進去
SQL> execute dbms_logmnr.add_logfile(LogFileName => '/u01/arch/1_118.dbf',Options => dbms_logmnr.NEW);

5、增加一些測試資料跟產生archive log 檔
SQL> insert into test3 values (10);
SQL> insert into test3 values (11);
SQL> commit;
SQL> insert into test3 values (12);
SQL> insert into test3 values (13);
SQL> commit;
SQL> insert into test3 values (14);
SQL> insert into test3 values (15);
SQL> insert into test3 values (16);
SQL> insert into test3 values (17);
SQL> commit;

SQL> alter system archive log current;
SQL> alter system switch logfile;

6、把產生的archive log 檔加入分析清單中
SQL> execute dbms_logmnr.add_logfile(LogFileName => '/u01/arch/1_119.dbf',Options => dbms_logmnr.ADDFILE);
SQL> execute dbms_logmnr.add_logfile(LogFileName => '/u01/arch/1_120.dbf',Options => dbms_logmnr.ADDFILE);

7、進行分析時的字典檔，指定為archive log 檔裏的字典檔
SQL> execute dbms_logmnr.start_logmnr(Options => dbms_logmnr.DICT_FROM_REDO_LOGS);

8、查看一下分析檔案清單
SQL> select filename from v$logmnr_logfile;

FILENAME
--------------------------------------------------------------------------------
/u01/arch/1_118.dbf
/u01/arch/1_119.dbf
/u01/arch/1_120.dbf

SQL> select sql_redo from v$logmnr_contents where sql_redo like '%TEST3%';

SQL_REDO
--------------------------------------------------------------------------------
insert into "SYS"."LOGMNRG_OBJ$"("OBJV#","OWNER#","NAME","NAMESPACE","SUBNAME","
insert into "SYS"."TEST3"("ID") values ('10');
insert into "SYS"."TEST3"("ID") values ('11');
insert into "SYS"."TEST3"("ID") values ('12');
insert into "SYS"."TEST3"("ID") values ('13');
insert into "SYS"."TEST3"("ID") values ('14');
insert into "SYS"."TEST3"("ID") values ('15');
insert into "SYS"."TEST3"("ID") values ('16');
insert into "SYS"."TEST3"("ID") values ('17');

確時有找到

另一種就要重起db了，在7x24真實環境中，除非有先設定好，不然應該是不會採用這個種方法
操作流程請參考 http://blog.roodo.com/mywork/archives/3468629.html

重點:
1、採用第二種方式分析，db需要在archivelog mode

Posted by my_work at 樂多Roodo! │22:16 │回應(0) │引用(0) │DB

樂多分類：網路/3C 共同主題：Oracle 工具：編輯本文

引用URL

http://cgi.blog.roodo.com/trackback/4513199

姓名 :
Email :
URL :
回應狀態:	公開私密
	記住我的資訊顯示我的圖像 ( 登入或申請個人圖像 )
回應內容 :

回最上方