官方地址：https://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm#SUTIL019
版本： oracle 11.2.0.4
官方資料
LogMiner Configuration
包含以下部分：
- The source database is the database that produces all the redo log files that you want LogMiner to analyze.
source database是你需要分析的產(chǎn)生所有redo log的庫(kù)粱玲。 - The mining database is the database that LogMiner uses when it performs the analysis.
mining database 是執(zhí)行l(wèi)ogminer分析的庫(kù)菊值。 - The LogMiner dictionary allows LogMiner to provide table and column names, instead of internal object IDs, when it presents the redo log data that you request.
-
The redo log files contain the changes made to the database or database dictionary.
sample logminer database configuration.jpg
Requirements
The following are requirements for the source and mining database, the data dictionary, and the redo log files that LogMiner will mine:
Source and mining database
- Both the source database and the mining database must be running on the same hardware platform.
- The mining database can be the same as, or completely separate from, the source database.
- The mining database must run the same release or a later release of the Oracle Database software as the source database.
- The mining database must use the same character set (or a superset of the character set) used by the source database.
LogMiner dictionary
- The dictionary must be produced by the same source database that generates the redo log files that LogMiner will analyze. 字典信息必須是產(chǎn)生redo log的源庫(kù)的信息，不然無(wú)法獲取字典數(shù)據(jù)鼻百。
All redo log files
- Must be produced by the same source database. 必須是同一個(gè)庫(kù)產(chǎn)生砌梆；
- Must be associated with the same database RESETLOGS SCN.
- Must be from a release 8.0 or later Oracle Database. However, several of the LogMiner features introduced as of release 9.0.1 work only with redo log files produced on an Oracle9i or later database. See "Supported Databases and Redo Log File Versions". 必須是8以后的版本，有些特性需要9i之后的版本才支持。
開(kāi)啟supplemental logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE; 如果是 YES or IMPLICIT 這兩個(gè)狀態(tài)悲伶，就表示已開(kāi)啟。
LogMiner主要步驟：
1.Specify a LogMiner dictionary.
Use the DBMS_LOGMNR_D.BUILD procedure or specify the dictionary when you start LogMiner (in Step 3), or both, depending on the type of dictionary you plan to use.
2.Specify a list of redo log files for analysis.
Use the DBMS_LOGMNR.ADD_LOGFILE procedure, or direct LogMiner to create a list of log files for analysis automatically when you start LogMiner (in Step 3).
3.Start LogMiner.
Use the DBMS_LOGMNR.START_LOGMNR procedure.
4.Request the redo data of interest.
Query the V$LOGMNR_CONTENTS view. (You must have the SELECT ANY TRANSACTION privilege to query this view.)
5.End the LogMiner session.
Use the DBMS_LOGMNR.END_LOGMNR procedure.
You must have been granted the EXECUTE_CATALOG_ROLE role to use the LogMiner PL/SQL packages and to query the V$LOGMNR_CONTENTS view.
LogMiner Dictionary Files and Redo Log Files
使用哪種字典文件
Oracle recommends that you use this option when you will have access to the source database from which the redo log files were created and when no changes to the column definitions in the tables of interest are anticipated. This is the most efficient and easy-to-use option.
Oracle recommends that you use this option when you do not expect to have access to the source database from which the redo log files were created, or if you anticipate that changes will be made to the column definitions in the tables of interest.
This option is maintained for backward compatibility with previous releases. This option does not guarantee transactional consistency. Oracle recommends that you use either the online catalog or extract the dictionary from redo log files instead.
下圖展示選擇哪種字典文件：
實(shí)操案例
用Online catalog 作為字典方式分析：
以下操作均在 PL/SQL 軟件中執(zhí)行
*** 打開(kāi)附加日志
select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
alter database add supplemental log data;
*** 模擬修改數(shù)據(jù)
SELECT * FROM v$log; # 查看當(dāng)前的活動(dòng)redo
UPDATE system.test_logmin SET status='TEST' WHERE ROWNUM < 10;
*** 開(kāi)始分析
BEGIN dbms_logmnr.add_logfile('/u01/app/oracle/oradata/testdb/redo01.log', dbms_logmnr.new); END; ## 添加在線日志住涉，第一個(gè)使用參數(shù) new
BEGIN dbms_logmnr.add_logfile('/u01/app/oracle/oradata/testdb/redo02.log', dbms_logmnr.addfile);END; ## 可添加多個(gè)
BEGIN dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);END; ## 開(kāi)始分析
### 查看分析結(jié)果麸锉，如下圖所示
SELECT SQL_REDO, SQL_UNDO, SEG_OWNER
FROM V$LOGMNR_CONTENTS
WHERE SEG_NAME = 'TEST_LOGMIN'
AND SEG_OWNER = 'SYSTEM';
*** 結(jié)束分析
BEGIN dbms_logmnr.end_logmnr; END;
如果是從歸檔日志中分析，那首先需要知道腳本執(zhí)行的大概時(shí)間范圍舆声，否則分析難度會(huì)加大花沉，導(dǎo)致分析時(shí)間較長(zhǎng)。
從操作系統(tǒng)層面根據(jù)歸檔日志文件的時(shí)間媳握，找到此區(qū)間的嫌疑日志碱屁，然后添加到分析隊(duì)列。
開(kāi)始分析蛾找。
BEGIN dbms_logmnr.add_logfile('/u01/app/oracle/oradata/testdb/archive/1_61_985361656.dbf', dbms_logmnr.new); END; ## 找到歸檔文件
## 指定分析范圍
ALTER SESSION SET NLS_DATE_FORMAT ='yyyy-mm-dd HH24:MI:SS';
BEGIN
DBMS_LOGMNR.START_LOGMNR(STARTTIME => '2018-09-20 17:00:00',
ENDTIME => '2018-09-20 17:50:00',
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG +DBMS_LOGMNR.CONTINUOUS_MINE);
END;
## 查看分析結(jié)果
SELECT SQL_REDO, SQL_UNDO, SEG_OWNER
FROM V$LOGMNR_CONTENTS
WHERE SEG_NAME = 'TEST_LOGMIN'
AND SEG_OWNER = 'SYSTEM';
## 結(jié)束分析
BEGIN dbms_logmnr.end_logmnr; END;
用DICT_FROM_REDO_LOGS作為字典方式分析：
此方式比較靈活娩脾，可以將目標(biāo)端的歸檔文件（數(shù)據(jù)變動(dòng)文件及包含字典信息的歸檔文件）拷貝到測(cè)試機(jī)器進(jìn)行分析，減少生產(chǎn)機(jī)器壓力
## 指定redo log存儲(chǔ)字典
BEGIN DBMS_LOGMNR_D.BUILD(OPTIONS => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);END;
## 找到 DICTIONARY_BEGIN='YES' 和 DICTIONARY_END='YES' 這之間的歸檔日志打毛，后續(xù)需要加到分析隊(duì)列中柿赊，因?yàn)槔锩姘值湫畔?SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';
## 找到涉及到的歸檔文件及包含字典的歸檔文件
BEGIN dbms_logmnr.add_logfile('/u01/app/oracle/oradata/testdb/archive/1_61_985361656.dbf', dbms_logmnr.new); END;
BEGIN dbms_logmnr.add_logfile('/u01/app/oracle/oradata/testdb/archive/1_69_985361656.dbf', dbms_logmnr.ADDFILE); END;
## 開(kāi)始分析
ALTER SESSION SET NLS_DATE_FORMAT ='yyyy-mm-dd HH24:MI:SS';
BEGIN
DBMS_LOGMNR.START_LOGMNR(STARTTIME => '2018-09-20 17:00:00',
ENDTIME => '2018-09-20 17:45:00',
OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS);
END;
## 查看結(jié)果
SELECT SQL_REDO, SQL_UNDO, SEG_OWNER
FROM V$LOGMNR_CONTENTS
WHERE SEG_NAME = 'TEST_LOGMIN'
AND SEG_OWNER = 'SYSTEM';
## 分析結(jié)束
BEGIN dbms_logmnr.end_logmnr; END;
用平面文件(Flat file)作為字典方式分析：
此種方式需要設(shè)置 utl_file_dir 路徑，需要重啟數(shù)據(jù)庫(kù)幻枉，實(shí)際生產(chǎn)中使用較少(默認(rèn)db不設(shè)置此參數(shù))碰声。
## 設(shè)置路徑參數(shù)
SELECT VALUE FROM v$parameter a where a.NAME ='utl_file_dir';
alter system set utl_file_dir='/u01/app/logminer' scope=spfile; ## 保證目錄/u01/app/logminer存在
## 重啟數(shù)據(jù)庫(kù)
shutdown immediate;
startup;
SELECT VALUE FROM v$parameter a where a.NAME ='utl_file_dir'; ## 檢查配置
## 指定平面文件存儲(chǔ)字典
BEGIN DBMS_LOGMNR_D.BUILD('dictionary.ora','/u01/app/logminer',DBMS_LOGMNR_D.STORE_IN_FLAT_FILE); END;
## 找到涉及到的歸檔文件
BEGIN dbms_logmnr.add_logfile('/u01/app/oracle/oradata/testdb/archive/1_61_985361656.dbf', dbms_logmnr.new); END;
## 開(kāi)始分析
ALTER SESSION SET NLS_DATE_FORMAT ='yyyy-mm-dd HH24:MI:SS';
BEGIN
DBMS_LOGMNR.START_LOGMNR(STARTTIME => '2018-09-20 17:00:00',
ENDTIME => '2018-09-20 17:45:00',
DICTFILENAME => '/u01/app/logminer/dictionary.ora');
END;
## 查看結(jié)果
SELECT SQL_REDO, SQL_UNDO, SEG_OWNER
FROM V$LOGMNR_CONTENTS
WHERE SEG_NAME = 'TEST_LOGMIN'
AND SEG_OWNER = 'SYSTEM';
## 分析結(jié)束
BEGIN dbms_logmnr.end_logmnr; END;
