透過logmnrr挖掘歸檔日誌檔案，可以輔助恢復資料庫，也從某種程度說起到審計的作用，
因為日誌檔案記錄了資料庫中塊變化的資訊，但主要不是審計用途，審計還有專門的方法。

以下是用本機的資料庫挖掘其他庫的歸檔日誌檔案的實驗過程：

----挖掘歸檔日誌檔案：

---建立存放被挖掘的歸檔日誌檔案的目錄：

[oracle@enmo ~]$ mkdir logmnr

[oracle@enmo ~]$ ls

afiedt.buf backup control_bak.ctl dirhome homedir logmnr mydoc mydoc.zip mytest.doc oradata users01.dbf

[oracle@enmo ~]$ cd logmnr/

[oracle@enmo logmnr]$ pwd

/home/oracle/logmnr

[oracle@enmo logmnr]$ ls
#挖掘本機資料庫的可以不用專門設定一個存放目錄，當然也可以設定。

---傳輸目標挖掘日誌檔案到所建立的目錄：

[oracle@enmo logmnr]$ rz

rz waiting to receive.

開始zmodem傳輸。按Ctrl+C取消。

100% 16935 KB 5645 KB/s 00:00:03 0 Errorss

100% 49 KB 49 KB/s 00:00:01 0 Errors

[oracle@enmo logmnr]$ ls

o1_mf_1_1_cxljlsmd_.arc o1_mf_1_2_cxljnxht_.arc

[oracle@enmo logmnr]$ ll

total 17016

-rw-r--r-- 1 oracle oinstall 17341440 Sep 14 20:28 o1_mf_1_1_cxljlsmd_.arc

-rw-r--r-- 1 oracle oinstall 50688 Sep 14 20:29 o1_mf_1_2_cxljnxht_.arc

[oracle@enmo logmnr]$

---檢視utl_file_dir的路徑：

sys@PROD>show parameter utl_file_dir

NAME TYPE VALUE

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

utl_file_dir string

#還沒有設定，該路徑用於在PL/SQL中進行檔案I/O操作，為了讓資料庫識別並使用到儲存在該目錄下的檔案。

---修改utl_file_dir引數並重啟資料庫使引數生效：

sys@PROD>alter system set utl_file_dir='/home/oracle/logmnr' scope=spfile;

System altered.

sys@PROD>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@PROD>startup

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes

Variable Size 507513936 bytes

Database Buffers 322961408 bytes

Redo Buffers 2371584 bytes

Database mounted.

Database opened.

sys@PROD>show parameter utl_file_dir

NAME TYPE VALUE

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

utl_file_dir string /home/oracle/logmnr

---實行包生成enmo.ora檔案：

sys@PROD>exec dbms_logmnr_d.build('enmo.ora','/home/oracle/logmnr');

PL/SQL procedure successfully completed.

---執行包新增日誌檔案：

sys@PROD>exec dbms_logmnr.add_logfile('/home/oracle/logmnr/o1_mf_1_1_cxljlsmd_.arc',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

sys@PROD>exec dbms_logmnr.add_logfile('/home/oracle/logmnr/o1_mf_1_2_cxljnxht_.arc',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

---修改/home/oracle/logmnr/enmo.ora中DB_ID的值為源庫的DB_ID(示例BD_ID為763893530)：

[oracle@enmo logmnr]$ vi enmo.ora

-- *************** LOGMNR DICTIONARY FILE ***************

--

-- The LogMnr Dictionary file contains the dictionary data from the

-- target database. The dictionary data is collected from the dictionary

-- tables defined in the target database. The LogMnr_Dictionary package

-- build script queries the dictionary tables and reconstructs the table

-- contents as a set of "SQL like" commands n the dictionary file.

... ...

CREATE_TABLE DICTIONARY_TABLE (DB_NAME VARCHAR2(9), DB_ID NUMBER(20), DB_CREATED VARCHAR2(20), DB_DICT_CREATED VARCHAR2(20), DB_RESETLOGS_CHANGE# NUMBER(22), DB_RESETLOGS_TIME VARCHAR2(20), DB_VERSION_TIME VARCHAR2(20), DB_REDO_TYPE_ID VARCHAR2(8), DB_REDO_RELEASE VARCHAR2(60), DB_CHARACTER_SET VARCHAR2(30), DB_VERSION VARCHAR2(64), DB_STATUS VARCHAR2(64), DB_DICT_MAXOBJECTS NUMBER(22), DB_DICT_OBJECTCOUNT NUMBER(22), DB_DICT_SCN NUMBER(22), DB_THREAD_MAP RAW(8), DB_TXN_SCNBAS NUMBER(22), DB_TXN_SCNWRP NUMBER(22));

INSERT_INTO DICTIONARY_TABLE VALUES ('PROD',763893530,'11/07/2016 16:20:55','12/13/2016 00:04:44',2155602,'11/22/2016 20:21:59','11/07/2016 21:52:33','','','AL32UTF8','11.2.0.4.0','Production',89624,87037,2902406,,2903140,0);

---執行包開始挖掘：

sys@PROD>exec sys.dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/enmo.ora');

PL/SQL procedure successfully completed.

---建立v$logmnr_contents字典檢視的副表：

sys@PROD>create table dt as select * from v$logmnr_contents;

Table created.

---執行包結束挖掘：

sys@PROD>exec sys.dbms_logmnr.end_logmnr();

PL/SQL procedure successfully completed.

---檢視字典表 dt的結構：

sys@PROD>desc dt

Name Null? Type

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

SCN NUMBER

START_SCN NUMBER

COMMIT_SCN NUMBER

TIMESTAMP DATE

START_TIMESTAMP DATE

COMMIT_TIMESTAMP DATE

XIDUSN NUMBER

XIDSLT NUMBER

XIDSQN NUMBER

XID RAW(8)

PXIDUSN NUMBER

PXIDSLT NUMBER

PXIDSQN NUMBER

... ...

OBJECT_ID RAW(16)

EDITION_NAME VARCHAR2(30)

CLIENT_ID VARCHAR2(64)

---使用圖形化客戶端檢視錶中的記錄（檢視需要的欄位）：

sys@PROD>select SCN,START_SCN,COMMIT_SCN,TIMESTAMP,

2 TX_NAME,OPERATION,SEG_OWNER,SEG_NAME,TABLE_NAME,

3 USERNAME,SQL_REDO,SQL_UNDO

4 from dt;

#這樣，透過挖掘歸檔日誌檔案的資料，我們就可以看到資料庫的修改操作的詳細記錄。

logmnr挖掘歸檔日誌檔案

相關文章