Oracle LogMiner in 6 Steps

What is the Oracle LogMiner utility?

The Oracle LogMiner utility enables you to query redo log files through a SQL interface. Redo log files contain information about the history of activity on a database including every change made to user data and to the data dictionary in a database.  Therefore, redo log files are the only source that contains all the necessary information to perform recovery operations.

Oracle LogMiner工具能夠 SQL 指令介面查詢redo log file的內容。 redo log file包含所有資料庫執行過的指令。 因此redo log file也會拿來執行資料庫的recovery (注意非restore)。

LogMiner Benefits

Because LogMiner provides a well-defined, easy-to-use, and comprehensive relational interface to redo log files, it can be used as a powerful data auditing tool, and also as a sophisticated data analysis tool. 可以拿來做稽核，也可以分析redolog裡的資訊用來做更複雜的分析，例如效能、空間使用，AIops….

The following list describes some key capabilities of LogMiner:

• Pinpointing when a logical corruption to a database.(incorrect values in a WHERE clause). 查明資料庫何時發生邏輯損壞。 例如由於 WHERE 子句中的值不正確而刪除/更新了錯誤的行、索引等等
• Determining what actions you would have to take to perform fine-grained recovery at the transaction level.執行transaction level的恢復(需考慮先有的相依關係)
• Performance tuning and capacity planning through trend analysis.通過趨勢分析進行性能調優和容量規劃
• Performing postauditing.跟踪在資料庫上執行的任何DML/DDL

Objects in LogMiner Configuration Files

LogMiner Configuration files have four objects: the source database, the mining database, the LogMiner dictionary, and the redo log files containing the data of interest.
Source database : The source database is the database that produces all the redo log files that you want LogMiner to analyze. 來源資料庫是產生想要分析的redo log的資料庫。

Mining database : The mining database is the database that LogMiner uses when it performs the analysis. 挖掘資料庫是 LogMiner 在執行分析時使用的資料庫。

LogMiner dictionary : The LogMiner dictionary enables LogMiner to provide table and column names, instead of internal object IDs, when it presents the redo log data that you request. LogMiner 字典讓 LogMiner 在呈現請求的redo log資料時提供表名和列名，而不是object IDs。

LogMiner 使用字典將內部object identifiers和data types轉換為object names和對應的格式。 如果沒有字典，LogMiner 會返回 object IDs，並將資料呈現為二進制資料。

Redo log files : The redo log files contain the changes made to the database, or to the database dictionary. redo log file包含對資料庫或資料庫字典所做的更改。

如下圖，搭配以上四個元件，這是官方給出的一個範例，波士頓的來源資料庫生成redo log files，這些文件被歸檔並傳送到舊金山的資料庫。  LogMiner 字典提取到這些redo log files中。 LogMiner 實際分析redo log files的挖掘資料庫則位於舊金山。

此範例只顯示了一種 LogMiner 的配置方式。 其他有效配置還有例如還有，來源資料庫和挖掘資料庫使用相同資料庫，或是使用其他方法提供資料字典的配置。

LogMiner Requirements

A Source database and a Mining database, with the following characteristics:..

• The Mining database must use the same character set (or a superset of the character set) that is used by the source database Both the Source database and the Mining database must be running on the same hardware platform. Mining資料庫必須使用與Source資料庫相同的字元集（或字元集的超集合） 也必須在相同的平台上運行。
• The Mining database must run using either the same release or a later release of the Oracle Database software as the Source database. Mining資料庫必須使用與Source資料庫相同或更高版本的資料庫版本。
• The Mining database can be the same as, or completely separate from the Source database. Mining 資料庫可以與 Source 資料庫相同或完全獨立。

LogMiner dictionary

• The dictionary must be produced by the same Source database that generates the redo log files that you want LogMiner to analyze.字典必須由產生redo log file的同一 Source 資料庫生成。

All redo log files, with the following characteristics:

• The redo log files 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.  redo log files必須來自 8.0 或更高版本的 Oracle 資料庫。 但是從 9.0.1 版以上才有一些酷功能。
• The redo log files must be produced by the same source database. redo log file必須由相同的源資料庫生成。
• The redo log files must be associated with the same database RESETLOGS SCN. redo log file必須位於相同的RESETLOGS SCN基準 。
  
• The tables or column names selected for mining must not exceed 30 characters. 選擇用於挖掘的表或欄位名稱不得超過 30 個字符。

Supplemental logging 

You must enable supplemental logging before generating log files that will be analyzed by LogMiner.在生成將由 LogMiner 分析的日誌文件之前，必須啟用supplemental logging。當啟用supplemental logging時，redo log 才會多記錄一些額外的附加訊息，這些訊息是使redo log中的訊息可以完整的呈現。 因此最少要啟用minimal supplemental logging ，如以下 SQL 語句所示：

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

要確定是否啟用了補充日誌記錄，可以查詢 V$DATABASE 視圖，如以下 SQL 語句所示：

SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

如果查詢返回值 YES 或 IMPLICIT，則啟用minimal supplemental logging 。

LogMiner Dictionary Files and Redo Log Files

Before you begin using LogMiner, it is important to understand how LogMiner works with the LogMiner dictionary file (or files) and redo log files. This will help you to get accurate results and to plan the use of your system resources.

LogMiner Dictionary Options

LogMiner requires a dictionary to translate object IDs into object names when it returns redo data to you.

LogMiner 在返回redo data時需要一個字典來將object ID 轉換為物件的名稱。

LogMiner gives you three options for supplying the dictionary: (Online Catalog、Redo log 、Flat File)

Online Catalog : 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.

如果可以存取產生redolog的來源資料庫。 Online Catalog包含有關資料庫的最新訊息，可能是開始分析的最快方式。

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

由於更改重要表的 DDL 操作不常見，因此Online Catalog通常會包含分析所需的訊息。但一旦更改了表， Online Catalog就不再反映該表的先前版本。 這意味著 LogMiner 將無法重建在表的先前版本上執行的任何 SQL 語句。 LogMiner 就會在 V$LOGMNR_CONTENTS 視圖的 SQL_REDO 列中生成不可執行的 SQL（包括二進制值的十六進製到原始格式），類似於以下示例：

insert into HR.EMPLOYEES(col#1, col#2) values (hextoraw(‘4a6f686e20446f65’), hextoraw(‘c306’));”

Extracting  Dictionary to the Redo Log : 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.

要將 LogMiner 字典提取到redo log file，資料庫必須打開並處於 ARCHIVELOG 模式。在將字典提取到redo log時，不能執行任何 DDL 語句。 因此提取到redo log file的字典保證是一致的（而提取到Flat File的字典則不是）。

使用 STORE_IN_REDO_LOGS 選項執行 PL/SQL DBMS_LOGMNR_D.BUILD ，將字典訊息提取到redo log， 不用指定文件名或位置。

EXECUTE DBMS_LOGMNR_D.BUILD(  OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

該procedure會消耗資料庫資源，最好限制在非繁忙時間來執行，比提取到Flat File要快。根據字典的大小，它可能包含在多個redo log中。 如果相關的redo log已經歸檔，那麼可以找出哪些redo log包含提取字典的開頭和結尾。 查詢 V$ARCHIVED_LOG 視圖，如下所示：

SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';

當準備開始 LogMiner 會話時，使用 ADD_LOGFILE procedure指定開始和結束redo log的名稱，以及它們之間的其他redo log 。

Extracting the LogMiner Dictionary to a Flat File : This option is maintained for backward compatibility with previous releases. This option does not guarantee transactional consistency.

當 LogMiner 字典在flat file中時，使用的系統資源比它包含在redo log files中時要少。要將資料庫字典訊息提取到flat file ，使用帶有 STORE_IN_FLAT_FILE 選項的 DBMS_LOGMNR_D.BUILD procedure 。

以下步驟描述瞭如何將字典提取到flat file中。步驟 1 和 2 是準備步驟。 只需要執行一次，也可以根據需要多次將字典提取到flat file中。

SQL> CREATE DIRECTORY "my_dictionary_dir" AS '/oracle/database';

SQL> EXECUTE dbms_logmnr_d.build(dictionary_location=>'my_dictionary_dir', dictionary_filename=>'dictionary.ora', 
options => dbms_logmnr_d.store_in_flat_file);

Choosing a LogMiner Dictionary

所以要如何選擇Dictionary?官方給出了下面這張流程圖，簡單來說在最下面可以看到有三個Dictionary的選項，不論哪種都需要有啟動的instance(右上角)，不然要怎麼用SQL指令介面?，但不一定要在同一台資料庫(ming database)，若要分析的table有經過DDL異動那使用online catalog會無法顯示DDL先前的狀態，因為她只記錄了最新的metadata，當時的metadata只能當時有做snapshot將他記錄起來，不然無法映對，那就是Dictionary in Redo log的方式，那當然DB要開啟我才能將dictoinary寫進redo (中間上面的方框)

Redo Log File Options

在開始logminer之前單然需要先選定要分析哪些redo log files，在先前的版本提供了兩中方式來加入我們想要分析的redo，手動選或自動選，在自動選的部分原先是給定一段時間或SCN，那它會自動的做過濾加入這些redolog，甚至配合早已不支援的continuous_mine 可以一直無限mine，那其實是滿傷的，也可以拿來直接分析online redo log，就我在19c的分析，現在也根本無法分析online redo，只能針對archived redo。回到自動選則redo的這部分，現在只能手動加入，若在start_miner加入時間來過濾也只是基於前面手動加入的redo來過濾。可以試試看不手動加入直接執行start _logminer指定時間，看是否能成功來看各版本是否支援 ，12.2 也不行!

Automatically : (It’s not supported in 19.1 version and higher.)

You can direct LogMiner to find and create a list of redo log files for analysis automatically. Any LogMiner dictionary can be used. LogMiner uses the database control file to find and adds redo log files that satisfy your specified time or SCN range to the LogMiner redo log file list. For example:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
EXECUTE DBMS_LOGMNR.START_LOGMNR( STARTTIME => '01-Jan-2012 08:30:00', ENDTIME => '01-Jan-2012 08:45:00',  OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG );

Manually :

Use the DBMS_LOGMNR.ADD_LOGFILE procedure to manually create a list of redo log files before you start LogMiner. After the first redo log file has been added to the list, each subsequently added redo log file must be from the same database and associated with the same database RESETLOGS SCN. When using this method, LogMiner need not be connected to the source database.

在啟動 LogMiner 之前，使用 DBMS_LOGMNR.ADD_LOGFILE procedure手動創建redo log files列表。 在將第一個redo log files添加到列表後，隨後添加的每個redo log files都必須來自同一個資料庫並與同一個資料庫 RESETLOGS SCN 相關聯。

使用此方法時，LogMiner 無需連接到源資料庫。

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => ‘/oracle/logs/log1.f’, OPTIONS => DBMS_LOGMNR.NEW);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/oracle/logs/log2.f', OPTIONS => DBMS_LOGMNR.ADDFILE);

Starting LogMiner

Call the DBMS_LOGMNR.START_LOGMNR procedure to start LogMiner.

Because of the options available with the DBMS_LOGMNR.START_LOGMNR procedure allow you to control output to the V$LOGMNR_CONTENTS view, you must call DBMS_LOGMNR.START_LOGMNR before querying the V$LOGMNR_CONTENTS view

由於 DBMS_LOGMNR.START_LOGMNR 過程的可用選項允許您控制對 V$LOGMNR_CONTENTS 視圖的輸出，因此必須在查詢 V$LOGMNR_CONTENTS 視圖之前調用 DBMS_LOGMNR.START_LOGMNR

When you start LogMiner, you can:

• Specify how LogMiner should filter data it returns (for example, by starting and ending time or SCN value) 指定 LogMiner 應如何過濾它返回的資料（例如，通過開始和結束時間或 SCN 值）
• Specify options for formatting the data returned by LogMiner.  指定用於格式化 LogMiner 返回的資料的選項
• Specify the LogMiner dictionary to use. 指定用於格式化 LogMiner 返回的資料的選項

V$LOGMNR_CONTENTS

可以通過查詢 V$LOGMNR_CONTENTS 訪問感興趣的資料。

V$LOGMNR_CONTENTS 與其他view不同，它不是呈現在表上的選擇所產生的view。 它是從redo log請求的資料。

LogMiner 僅在當針對它的查詢時才會產生這個view。必須先成功啟動 LogMiner，然後才能查詢 V$LOGMNR_CONTENTS。

• The type of change made to the database: INSERT, UPDATE, DELETE, or DDL (OPERATION)
• The SCN at which a change was made (SCN)
• The SCN at which a change was committed (COMMIT_SCN)
• The transaction to which a change belongs (XIDUSN, XIDSLT, and XIDSQN).
• The table and schema name of the modified object (SEG_NAME and SEG_OWNER)
• The name of the user who issued the DDL or DML statement to make the change (USERNAME)
• the SQL DML used to generate the redo records (SQL_REDO)
• the SQL DML statements needed to undo the change (SQL_UNDO)

DBMS_LOGMNR.END_LOGMNR

沒使用此procedure 來結束logminer session可能會有內存洩漏的風險。

This procedure MUST be called prior to exiting the session that was performing the analysis. This is because of the way the PGA is used to store the dictionary definitions from the dictionary file, and the V$LOGMNR_CONTENTS output.


If you do not call end_logmnr, you will silently get ORA-00600 [723] on logoff. This OERI is triggered because the PGA is bigger at logoff than it was at logon, which is considered a space leak. The main problem from a support perspective is that it is silent, i.e. not signalled back to the user screen, because by then they have logged off.

The way to spot LogMiner leaks is that the trace file produced by the OERI 723 will have A PGA heap dumped with many Chunks of type ‘Freeable’ with a description of “KRVD:alh”

Steps in a Typical LogMiner Session

1.  Enable Supplemental Logging
2. Extract a LogMiner Dictionary (使用online catalog就不用)
3. Specify Redo Log Files for Analysis
4. Start LogMiner
5. Query V$LOGMNR_CONTENTS
6. End the LogMiner Session

The Performance Impact Of LogMiner

refer to How To Reduce The Performance Impact Of LogMiner Usage On A Production Database (Doc ID 1629300.1)

1. LogMiner functionality.
The impact on the performance depends on the amount of data in the redo logfiles that needs to be mined and how often SQL is issued against the redo logs.
Every time that a query against v$logmnr_contents is executed, the archived redo logs will be read and the view will be populated.
If GB’s of data need to be mined, then this can have an impact on the overall database performance (disk reads, memory consumption).
Note that the archived redo logs need to be created in the source database with supplemental logging enabled. Without Supplemental Logging enabled, it will be unlikely that LogMiner can extract anything meaningful from the archived redo logs.

對性能的影響取決於redo logfiles中需要挖掘的資料量，以及針對redo logfiles發出 SQL 的頻率。 每次執行對 v$logmnr_contents 的查詢時，都會讀取歸檔的redo logfiles並產生view。 如果需要挖掘 GB 的資料，那麼這可能會對整體資料庫性能（磁盤讀取、內存消耗）產生影響。

2. Queries on V$LOGMNR_CONTENTS.
Because LogMiner populates the V$LOGMNR_CONTENTS view only in response to a query and does not store the requested data in the database

因為 LogMiner 僅在回應查詢時產生 V$LOGMNR_CONTENTS 而不會將請求的資料存儲在資料庫中the following is true:

• Every time you query V$LOGMNR_CONTENTS, LogMiner analyzes the redo log files for the data you request.每次查詢 V$LOGMNR_CONTENTS 時，LogMiner 都會分析redo log files以獲取請求的資料。
• The amount of memory consumed by the query is not dependent on the number of rows that must be returned to satisfy a query.查詢消耗的內存量不取決於查詢返回的行數。
• The time it takes to return the requested data is dependent on the amount and type of redo log data that must be mined to find that data.請求資料所需的時間，取決於挖掘時找到該資料的redo log 數量和類型。

Oracle therefore recommends that you create a table to temporarily hold the results from a query of V$LOGMNR_CONTENTS if you need to maintain the data for further analysis, particularly if the amount of data returned by a query is small in comparison to the amount of redo data that LogMiner must analyze to provide that data.

CREATE TABLE my_logmnr_contents AS SELECT * FROM v$logmnr_contents;

聯繫我索取demo : [email protected]