logmnr挖掘归档日志文件

通过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;

#这样，通过挖掘归档日志文件的数据，我们就可以看到数据库的修改操作的详细记录。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31392094/viewspace-2130358/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31392094/viewspace-2130358/