Oracle Logminer 說明

一.  Logminer 說明

 

Oracle LogMiner 的官網說明：

      Using LogMiner to Analyze Redo Log Files

       http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/logminer.htm#SUTIL019

 

 

       Logminer是oracle從8i開始提供的用於分析重做日誌資訊的工具，它包括DBMS_LOGMNR和DBMS_LOGMNR_D兩個package，後邊的D是字典的意思。它既能分析redo log file，也能分析歸檔後的archive log file。在分析日誌的過程中需要使用資料字典，一般先生成資料字典檔案後使用，10g版本還可以使用線上資料字典。

 

       Logminer也可以分析其它資料庫的重做日誌檔案，但是必須使用重做日誌所在資料庫的資料字典，否則會出現無法識別的亂碼。另外被分析資料庫的作業系統平臺最好和當前Logminer所在資料庫的執行平臺一樣，且block size相同。

 

       LogMiner是Oracle資料庫提供的一個工具，它用於分析重做日誌和歸檔日誌所記載的事務操作。

       （1）確定資料庫的邏輯損壞時間。假定某個使用者執行drop table誤刪除了重要表sales，通過LogMiner可以準確定位該誤操作的執行時間和SCN值，然後通過基於時間恢復或者基於SCN恢復可以完全恢復該表資料。

       （2）確定事務級要執行的精細邏輯恢復操作。假定某些使用者在某表上執行了一系列DML操作並提交了事務，並且其中某個使用者的DML操作存在錯誤。通過LogMiner可以取得任何使用者的DML操作及相應的UNDO操作，通過執行UNDO操作可以取消使用者的錯誤操作。

       （3）執行後續審計。通過LogMiner可以跟蹤Oracle資料庫的所有DML、DDL和DCL操作，從而取得執行這些操作的時間順序、執行這些操作的使用者等資訊。

 

LogMiner 由如下2個指令碼來安裝：

（1）建立DBMS_LOGMNR：$ORACLE_HOME/rdbms/admin/dbmslm.sql

SQL> @dbmslm.sql

程式包已建立。

授權成功。

（2）建立DBMS_LOGMNR_D：$ORACLE_HOME/rdbms/admin/dbmslmd.sql.

SQL> @dbmslmd.sql

過程已建立。

沒有錯誤。

授權成功。

PL/SQL 過程已成功完成。

程式包已建立。

 

 

1.1 Logminer 支援的資料型別和表的儲存屬性

LogMiner supports the following datatypes and table storage attributes:

（1）.      CHAR

（2）.      NCHAR

（3）.      VARCHAR2 and VARCHAR

（4）.      NVARCHAR2

（5）.      NUMBER

（6）.      DATE

（7）.      TIMESTAMP

（8）.      TIMESTAMP WITH TIME ZONE

（9）.      TIMESTAMP WITH LOCAL TIME ZONE

（10）.  INTERVAL YEAR TO MONTH

（11）.  INTERVAL DAY TO SECOND

（12）.  RAW

（13）.  CLOB

（14）.  NCLOB

（15）.  BLOB

（16）.  LONG

（17）.  LONG RAW

（18）.  BINARY_FLOAT

（19）.  BINARY_DOUBLE

（20）.  Index-organized tables (IOTs), including those with overflows or LOB columns

（21）.  Function-based indexes

（22）.  XMLTYPE data when it is stored in CLOB format

（23）.  Tables using basic table compression and OLTP table compression

 

       Support for multibyte CLOBs is available only for redo logs generated by a database with compatibility set to a value of 10.1 or higher.

       Support for LOB and LONG datatypes is available only for redo logs generated by a database with compatibility set to a value of 9.2.0.0 or higher.

       Support for index-organized tables without overflow segment or with no LOB columns in them is available only for redo logs generated by a database with compatibility set to 10.0.0.0 or higher.

       Support for index-organized tables with overflow segment or with LOB columns is available only for redo logs generated by a database with compatibility set to 10.2.0.0 or higher.

 

1.2  Logminer 不支援的資料型別和表儲存屬性

LogMiner does not support these datatypes and table storage attributes:

（1）.      BFILE datatype

（2）.      Simple and nested abstract datatypes (ADTs)

（3）.      Collections (nested tables(巢狀表) and VARRAYs)

（4）.      Object refs

（5）.      SecureFiles (unless database compatibility is set to 11.2 or higher)

 

1.3  LogMiner基本物件

　　　　　

       There are four basic objects in a LogMiner configuration that you should be familiar with: the source database, the mining database, the LogMiner dictionary, and the redo log files containing the data of interest:

       （1）The source database is the database that produces all the redo log files that you want LogMiner to analyze.

       （2）The mining database is the database that LogMiner uses when it performs the analysis.

       （3）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.

       LogMiner uses the dictionary to translate internal object identifiers and datatypes to object names and external data formats. Without a dictionary, LogMiner returns internal object IDs and presents data as binary data.

       -- LogMiner字典用於將內部物件ID號和資料型別轉換為物件名和外部資料格式。使用LogMiner分析重做日誌和歸檔日誌時，應該生成LogMiner字典，否則將無法讀懂分析結果。

 

For example, consider the following the SQL statement:

       INSERT INTO HR.JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)  VALUES('IT_WT','Technical Writer', 4000, 11000);

 

Without the dictionary, LogMiner will display:

insert into "UNKNOWN"."OBJ# 45522"("COL 1","COL 2","COL 3","COL 4") values (HEXTORAW('45465f4748'),HEXTORAW('546563686e6963616c20577269746572'),HEXTORAW('c229'),HEXTORAW('c3020b'));

      

       （4）The redo log files contain the changes made to the database or database dictionary.

 

1.4  LogMiner配置要求

       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

       （1）Both the source database and the mining database must be running on the same hardware platform.

       -- 源資料庫和分析資料庫必須執行在相同硬體平臺上；

       （2）The mining database can be the same as, or completely separate from, the source database.

       -- 分析資料庫可以是獨立資料庫或源資料庫；

       （3）The mining database must run the same release or a later release of the Oracle Database software as the source database.

       --分析資料庫的版本不能低於源資料庫的版本；

       （4）The mining database must use the same character set (or a superset of the character set) used by the source database.

       --分析資料庫與源資料庫必須具有相同的字符集。

 

LogMiner dictionary

       （1）The dictionary must be produced by the same source database that generates the redo log files that LogMiner will analyze.    

       -- LogMiner字典必須在源資料庫中生成。

 

All redo log files:

       （1）Must be produced by the same source database.

       --當分析多個重做日誌和歸檔日誌時，它們必須是同一個源資料庫的重做日誌和歸檔日誌；

       （2）Must be associated with the same database RESETLOGS SCN.

       --當分析多個重做日誌和歸檔日誌時，它們必須具有相同的resetlogs scn；

       （3）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.

       --當分析的重做日誌和歸檔日誌必須在Oracle8.0版本以上。

 

       LogMiner does not allow you to mix redo log files from different databases or to use a dictionary from a different database than the one that generated the redo log files to be analyzed.

 

 

1.5補充日誌（suppplemental logging）

 

       You must enable supplemental logging before generating log files that will be analyzed by LogMiner.

       When you enable supplemental logging, additional information is recorded in the redo stream that is needed to make the information in the redo log files useful to you.        Therefore, at the very least, you must enable minimal supplemental logging, as the following SQL statement shows:

      ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

 

       To determine whether supplemental logging is enabled, query the V$DATABASE view, as the following SQL statement shows:

      SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

 

       If the query returns a value of YES or IMPLICIT, then minimal supplemental logging is enabled.

 

 

       重做日誌用於實現例程恢復和介質恢復，這些操作所需要的資料被自動記錄在重做日誌中。但是，重做應用可能還需要記載其他列資訊到重做日誌中，記錄其他列的日誌過程被稱為補充日誌。

 

       預設情況下，Oracle資料庫沒有提供任何補充日誌，從而導致預設情況下LogMiner無法支援以下特徵：

       （1）索引簇、鏈行和遷移行；

       （2）直接路徑插入；

       （3）摘取LogMiner字典到重做日誌；

       （4）跟蹤DDL；

       （5）生成鍵列的SQL_REDO和SQL_UNDO資訊；

       （6）LONG和LOB資料型別。

 

       因此，為了充分利用LogMiner提供的特徵，必須啟用補充日誌。在資料庫級啟用補充日誌的示例如下：

       SQL> conn /as sysdba

       已連線。

       SQL> alter database add supplemental log data;

       資料庫已更改。

 

       注意：啟用不用重啟資料庫，資料庫聯機即可。

 

 

二.  一個典型的LogMiner 步驟

       This section describes the steps in a typical LogMiner session. Each step is described in its own subsection.

（1）.      Enable Supplemental Logging

（2）.      Extract a LogMiner Dictionary (unless you plan to use the online catalog)

（3）.      Specify Redo Log Files for Analysis

（4）.      Start LogMiner

（5）.      Query V$LOGMNR_CONTENTS

（6）.      End the LogMiner Session

 

       To run LogMiner, you use the DBMS_LOGMNR PL/SQL package. Additionally, you might also use the DBMS_LOGMNR_D package if you choose to extract a LogMiner dictionary rather than use the online catalog.

 

       The DBMS_LOGMNR package contains the procedures used to initialize and run LogMiner, including interfaces to specify names of redo log files, filter criteria, and session characteristics.

       The DBMS_LOGMNR_D package queries the database dictionary tables of the current database to create a LogMiner dictionary file.

 

       The LogMiner PL/SQL packages are owned by the SYS schema. Therefore, if you are not connected as user SYS, then:

       （1）You must include SYS in your call. For example:

              EXECUTE SYS.DBMS_LOGMNR.END_LOGMNR;

       （2）You must have been granted the EXECUTE_CATALOG_ROLE role.

 

2.1 Enable Supplemental Logging

       Enable the type of supplemental logging you want to use. At the very least, you must enable minimal supplemental logging, as follows:

       SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

 

2.2 Extract a LogMiner Dictionary

       To use LogMiner, you must supply it with a dictionary by doing one of the following:

（1）.      Specify use of the online catalog by using the DICT_FROM_ONLINE_CATALOG option when you start LogMiner.

       See "Using the Online Catalog".

（2）.      Extract database dictionary information to the redo log files.

       See "Extracting a LogMiner Dictionary to the Redo Log Files".

（3）.      Extract database dictionary information to a flat file.

       See "Extracting the LogMiner Dictionary to a Flat File".

 

2.3 Specify Redo Log Files for Analysis

       Before you can start LogMiner, you must specify the redo log files that you want to analyze. To do so, execute the DBMS_LOGMNR.ADD_LOGFILE procedure, as demonstrated in the following steps. You can add and remove redo log files in any order.

 

Note:

       If you will be mining in the database instance that is generating the redo log files, then you only need to specify the CONTINUOUS_MINE option and one of the following when you start LogMiner:

（1）.      The STARTSCN parameter

（2）.      The STARTTIME parameter

For more information, see "Redo Log File Options".

 

       2.3.1 Use SQL*Plus to start an Oracle instance, with the database either mounted or unmounted. For example, enter the STARTUP statement at the SQL prompt:

              SQL>STARTUP

 

       2.3.2 Create a list of redo log files. Specify the NEW option of the DBMS_LOGMNR.ADD_LOGFILE PL/SQL procedure to signal that this is the beginning of a new list. For example, enter the following to specify the /oracle/logs/log1.f redo log file:

       execute dbms_logmnr.add_logfile( logfilename => '/oracle/logs/log1.f', ptions => dbms_logmnr.new);

 

       2.3.3  If desired, add more redo log files by specifying the ADDFILE option of the DBMS_LOGMNR.ADD_LOGFILE PL/SQL procedure. For example, enter the following to add the /oracle/logs/log2.f redo log file:

       execute dbms_logmnr.add_logfile( logfilename => '/oracle/logs/log2.f', ptions => dbms_logmnr.addfile);

 

       The OPTIONS parameter is optional when you are adding additional redo log files. For example, you could simply enter the following:

       execute dbms_logmnr.add_logfile( logfilename=>'/oracle/logs/log2.f');

 

       2.3.4 If desired, remove redo log files by using the DBMS_LOGMNR.REMOVE_LOGFILE PL/SQL procedure. For example, enter the following to remove the /oracle/logs/log2.f redo log file:

       execute dbms_logmnr.remove_logfile( logfilename => '/oracle/logs/log2.f');

 

2.4 Start LogMiner

       After you have created a LogMiner dictionary file and specified which redo log files to analyze, you must start LogMiner. Take the following steps:

 

       2.4.1 Execute the DBMS_LOGMNR.START_LOGMNR procedure to start LogMiner.

 

       Oracle recommends that you specify a LogMiner dictionary option. If you do not, then LogMiner cannot translate internal object identifiers and datatypes to object names and external data formats. Therefore, it would return internal object IDs and present data as binary data. Additionally, the MINE_VALUE and COLUMN_PRESENT functions cannot be used without a dictionary.

 

       （1）If you are specifying the name of a flat file LogMiner dictionary, then you must supply a fully qualified file name for the dictionary file. For example, to start LogMiner using /oracle/database/dictionary.ora, issue the following statement:

       execute dbms_logmnr.start_logmnr( dictfilename =>'/oracle/database/dictionary.ora');

      

       （2）If you are not specifying a flat file dictionary name, then use the OPTIONS parameter to specify either the DICT_FROM_REDO_LOGS or DICT_FROM_ONLINE_CATALOG option.

       If you specify DICT_FROM_REDO_LOGS, then LogMiner expects to find a dictionary in the redo log files that you specified with the DBMS_LOGMNR.ADD_LOGFILE procedure. To determine which redo log files contain a dictionary, look at the V$ARCHIVED_LOG view.

 

       See "Extracting a LogMiner Dictionary to the Redo Log Files" for an example.

 

Note:

       If you add additional redo log files after LogMiner has been started, you must restart LogMiner.

       LogMiner will not retain options that were included in the previous call to DBMS_LOGMNR.START_LOGMNR; you must respecify the options you want to use. However, LogMiner will retain the dictionary specification from the previous call if you do not specify a dictionary in the current call to DBMS_LOGMNR.START_LOGMNR.

 

       For more information about the DICT_FROM_ONLINE_CATALOG option, see "Using the Online Catalog".

 

2.4.2     Optionally, you can filter your query by time or by SCN.

       See "Filtering Data by Time" or "Filtering Data by SCN".

 

2.4.3      You can also use the OPTIONS parameter to specify additional characteristics of your LogMiner session.

       For example, you might decide to use the online catalog as your LogMiner dictionary and to have only committed transactions shown in the V$LOGMNR_CONTENTS view, as follows:

       execute dbms_logmnr.start_logmnr(options=>

  dbms_logmnr.dict_from_online_catalog + dbms_logmnr.committed_data_only);

 

       For more information about DBMS_LOGMNR.START_LOGMNR options, see Oracle Database PL/SQL Packages and Types Reference.

 

       You can execute the DBMS_LOGMNR.START_LOGMNR procedure multiple times, specifying different options each time. This can be useful, for example, if you did not get the desired results from a query of V$LOGMNR_CONTENTS, and want to restart LogMiner with different options. Unless you need to respecify the LogMiner dictionary, you do not need to add redo log files if they were already added with a previous call to DBMS_LOGMNR.START_LOGMNR.

 

2.5  Query V$LOGMNR_CONTENTS

       At this point, LogMiner is started and you can perform. queries against the V$LOGMNR_CONTENTS view.

       See "Filtering and Formatting Data Returned to V$LOGMNR_CONTENTS" for examples of this.

 

2.6 End the LogMiner Session

       To properly end a LogMiner session, use the DBMS_LOGMNR.END_LOGMNR PL/SQL procedure, as follows:

      EXECUTE DBMS_LOGMNR.END_LOGMNR;

 

       This procedure closes all the redo log files and allows all the database and system resources allocated by LogMiner to be released.

       If this procedure is not executed, then LogMiner retains all its allocated resources until the end of the Oracle session in which it was invoked. It is particularly important to use this procedure to end the LogMiner session if either the DDL_DICT_TRACKING option or the DICT_FROM_REDO_LOGS option was used.

 

 

三.  LogMiner 資料字典和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.

 

3.1 LogMiner Dictionary Options

       LogMiner requires a dictionary to translate object IDs into object names when it returns redo data to you. LogMiner gives you three options for supplying the dictionary:

 

（1）Using the Online Catalog

       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.

 

（2）Extracting a LogMiner Dictionary to the Redo Log Files

       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.

 

（3）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. Oracle recommends that you use either the online catalog or extract the dictionary from redo log files instead.

 

3.1.1 Using the Online Catalog(一般用這種查DML操作)

       To direct LogMiner to use the dictionary currently in use for the database, specify the online catalog as your dictionary source when you start LogMiner, as follows:

 

EXECUTE DBMS_LOGMNR.START_LOGMNR(

   OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

 

       In addition to using the online catalog to analyze online redo log files, you can use it to analyze archived redo log files, if you are on the same system that generated the archived redo log files.

 

       The online catalog contains the latest information about the database and may be the fastest way to start your analysis. Because DDL operations that change important tables are somewhat rare, the online catalog generally contains the information you need for your analysis.

 

       Remember, however, that the online catalog can only reconstruct SQL statements that are executed on the latest version of a table. As soon as a table is altered, the online catalog no longer reflects the previous version of the table. This means that LogMiner will not be able to reconstruct any SQL statements that were executed on the previous version of the table.

       -- 使用源資料庫分析重做日誌或歸檔日誌時，如果要分析表的結構沒有發生任何變化，Oracle建議使用該選項分析重做日誌和歸檔日誌。

 

 Instead, LogMiner generates nonexecutable SQL (including hexadecimal-to-raw formatting of binary values) in the SQL_REDO column of the V$LOGMNR_CONTENTS view similar to the following example:

      insert into HR.EMPLOYEES(col#1, col#2) values  (hextoraw('4a6f686e20446f65'),hextoraw('c306'));"

 

       The online catalog option requires that the database be open.

       The online catalog option is not valid with the DDL_DICT_TRACKING option of DBMS_LOGMNR.START_LOGMNR.

       -- dbms_logmnr.dict_from_online_catalog要求資料庫必須處於open狀態，並且該選項只能用於跟蹤DML操作，而不能用於跟蹤DDL操作。

 

 

3.1.2 Extracting a LogMiner Dictionary to the Redo Log Files

       To extract a LogMiner dictionary to the redo log files, the database must be open and in ARCHIVELOG mode and archiving must be enabled. While the dictionary is being extracted to the redo log stream, no DDL statements can be executed. Therefore, the dictionary extracted to the redo log files is guaranteed to be consistent (whereas the dictionary extracted to a flat file is not).

 

       To extract dictionary information to the redo log files, execute the PL/SQL DBMS_LOGMNR_D.BUILD procedure with the STORE_IN_REDO_LOGS option. Do not specify a file name or location.

EXECUTE DBMS_LOGMNR_D.BUILD( 

   OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

       The process of extracting the dictionary to the redo log files does consume database resources, but if you limit the extraction to off-peak hours, then this should not be a problem, and it is faster than extracting to a flat file. Depending on the size of the dictionary, it may be contained in multiple redo log files. If the relevant redo log files have been archived, then you can find out which redo log files contain the start and end of an extracted dictionary. To do so, query the V$ARCHIVED_LOG view, as follows:

 

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

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

 

       Specify the names of the start and end redo log files, and possibly other logs in between them, with the ADD_LOGFILE procedure when you are preparing to begin a LogMiner session.

 

       Oracle recommends that you periodically back up the redo log files so that the information is saved and available at a later date. Ideally, this will not involve any extra steps because if your database is being properly managed, then there should already be a process in place for backing up and restoring archived redo log files. Again, because of the time required, it is good practice to do this during off-peak hours.

     

      使用分析資料庫分析重做日誌或歸檔日誌，或者被分析表的結構發生改變時，Oracle建議使用該選項分析重做日誌和歸檔日誌。為了摘取LogMiner字典到重做日誌，要求源資料庫必須處於archivelog模式，並且該資料庫處於open狀態。

 

3.1.3 Extracting the LogMiner Dictionary to a Flat File

--用來查DDL的操作記錄

       When the LogMiner dictionary is in a flat file, fewer system resources are used than when it is contained in the redo log files. Oracle recommends that you regularly back up the dictionary extract to ensure correct analysis of older redo log files.

 

       To extract database dictionary information to a flat file, use the DBMS_LOGMNR_D.BUILD procedure with the STORE_IN_FLAT_FILE option.

Be sure that no DDL operations occur while the dictionary is being built.

 

       The following steps describe how to extract a dictionary to a flat file. Steps 1 and 2 are preparation steps. You only need to do them once, and then you can extract a dictionary to a flat file as many times as you want to.

 

（1）The DBMS_LOGMNR_D.BUILD procedure requires access to a directory where it can place the dictionary file. Because PL/SQL procedures do not normally access user directories, you must specify a directory for use by the DBMS_LOGMNR_D.BUILD procedure or the procedure will fail. To specify a directory, set the initialization parameter, UTL_FILE_DIR, in the initialization parameter file.

 

       For example, to set UTL_FILE_DIR to use /oracle/database as the directory where the dictionary file is placed, place the following in the initialization parameter file:

       UTL_FILE_DIR = /oracle/database

      

       Remember that for the changes to the initialization parameter file to take effect, you must stop and restart the database.

--要是該引數生效需要重啟DB

 

（2）If the database is closed, then use SQL*Plus to mount and open the database whose redo log files you want to analyze. For example, entering the SQL STARTUP command mounts and opens the database:

       SQL>STARTUP

 

（3）Execute the PL/SQL procedure DBMS_LOGMNR_D.BUILD. Specify a file name for the dictionary and a directory path name for the file. This procedure creates the dictionary file.

 

For example, enter the following to create the file dictionary.ora in /oracle/database:

       EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/oracle/database/',

       DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

 

       You could also specify a file name and location without specifying the STORE_IN_FLAT_FILE option. The result would be the same.

 

 

3.2  Redo Log File Options

       To mine data in the redo log files, LogMiner needs information about which redo log files to mine. Changes made to the database that are found in these redo log files are delivered to you through the V$LOGMNR_CONTENTS view.

 

       You can direct LogMiner to automatically and dynamically create a list of redo log files to analyze, or you can explicitly specify a list of redo log files for LogMiner to analyze, as follows:

 

3.2.1 Automatically

       If LogMiner is being used on the source database, then you can direct LogMiner to find and create a list of redo log files for analysis automatically. Use the CONTINUOUS_MINE option when you start LogMiner with the DBMS_LOGMNR.START_LOGMNR procedure, and specify a time or SCN range.

       Although this example specifies the dictionary from the online catalog, any LogMiner dictionary can be used.

 

Note:

       The CONTINUOUS_MINE option requires that the database be mounted and that archiving be enabled.

 

       LogMiner will use the database control file to find and add redo log files that satisfy your specified time or SCN range to the LogMiner redo log file list. For example:

 

EXECUTE DBMS_LOGMNR.START_LOGMNR(    

       STARTTIME => '01-Jan-2003 08:30:00',   

       ENDTIME => '01-Jan-2003 08:45:00',

       OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG +         DBMS_LOGMNR.CONTINUOUS_MINE);

 

       You can also direct LogMiner to automatically build a list of redo log files to analyze by specifying just one redo log file using DBMS_LOGMNR.ADD_LOGFILE, and then specifying the CONTINUOUS_MINE option when you start LogMiner. The previously described method is more typical, however.

 

3.2.2 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.

 

       For example, to start a new list of redo log files, specify the NEW option of the DBMS_LOGMNR.ADD_LOGFILE PL/SQL procedure to signal that this is the beginning of a new list. For example, enter the following to specify /oracle/logs/log1.f:

 

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/oracle/logs/log1.f', PTIONS => DBMS_LOGMNR.NEW);

 

       If desired, add more redo log files by specifying the ADDFILE option of the PL/SQL DBMS_LOGMNR.ADD_LOGFILE procedure. For example, enter the following to add /oracle/logs/log2.f:

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( 

   LOGFILENAME => '/oracle/logs/log2.f', 

   OPTIONS => DBMS_LOGMNR.ADDFILE);

 

       To determine which redo log files are being analyzed in the current LogMiner session, you can query the V$LOGMNR_LOGS view, which contains one row for each redo log file.

 

 

四. LogMiner 示例

 

在做實驗之前，檢查下suppplemental logging：

 

 

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME

--------

YES

 

如果是YES 或者IMPLICIT則表明已經生效了，否則需要啟動：

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

 

4.1 使用源資料庫資料字典（Online Catalog）來分析DML操作

 

1、先進行DML和DDL的操作，以便下面分析。

SQL> conn /as sysdba

已連線。

SQL> show parameter utl;

NAME                                 TYPE        VALUE                         

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

create_stored_outlines               string                                    

utl_file_dir                         string   

SQL> insert into scott.dept  values('80','Dave','AnQing');

已建立 1 行。

SQL> update scott.dept set loc='shang hai' where deptno=70;

已更新 1 行。

SQL> commit;

提交完成。

SQL> delete from scott.dept where deptno=40;

已刪除 1 行。

SQL> commit;

提交完成。

SQL> alter table scott.dept add(phone varchar2(32));

表已更改。

SQL> insert into scott.dept values(50,'David','Dai','13888888888');

已建立 1 行。

SQL> commit;

提交完成。

SQL> alter table scott.dept add(address varchar2(300));

表已更改。

 

2、把線上重做日誌變成歸檔日誌，這樣分析歸檔日誌就可以了

SQL> alter system switch logfile;

系統已更改。

 

3、建立日誌分析列表：

 

----新增要分析的日誌檔案

SQL> execute dbms_logmnr.add_logfile(logfilename=>'D:/oracle/arch/TEST/ARCHIVELOG/2009_08_25/O1_MF_1_32_597FQD7B_.ARC',options=>dbms_logmnr.new);

PL/SQL 過程已成功完成。

---繼續填加，用dbms_logmnr.removefile可以刪除

SQL> execute dbms_logmnr.add_logfile(logfilename=>'D:/oracle/arch/TEST/ARCHIVELOG/2009_08_25/O1_MF_1_30_597B5P7B_.ARC',options=>dbms_logmnr.addfile);

PL/SQL 過程已成功完成。

 

4、啟動LogMiner

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL 過程已成功完成。

 

5、檢視日誌分析結果：

SQL> col username format a8

SQL> col sql_redo format a50

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

會話已更改。

 

SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='DEPT';

USERNAME        SCN TIMESTAMP           SQL_REDO

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

            1645927 2009-08-25 16:54:56 delete from "SCOTT"."DEPT" where "DEPTNO" = '40' and "DNAME" = 'OPERATIONS' and "LOC" = 'BOSTON' and "PHONE" IS NULL and "ADDRESS" IS                                           NULL and ROWID = 'AAAMfNAAEAAAAAQAAD';

SYS         1645931 2009-08-25 16:54:57 alter table scott.dept add(phone varchar2(32)) ;

SYS         1645992 2009-08-25 16:56:33 alter table scott.dept add(address varchar2(300)) ;

 

6、結束LogMiner

SQL> execute dbms_logmnr.end_logmnr;

PL/SQL 過程已成功完成。

 

 

4.2 摘取LogMiner字典到字典檔案分析DDL操作

 

1、進行DDL操作，以便分析

SQL> conn scott/admin

已連線。

SQL> drop table emp;

表已刪除。

SQL> drop table dept;

表已刪除。

SQL> conn /as sysdba

已連線。

SQL> alter system switch logfile;

系統已更改。

 

2、使用字典檔案，請檢視資料庫是否配置utl_file_dir，這個引數為字典檔案的目錄。配置該引數後，需要重啟資料庫

SQL> show user;

USER 為 "SYS"

SQL> show parameter utl;

NAME                                 TYPE        VALUE                         

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

create_stored_outlines               string                                    

utl_file_dir                         string                          

SQL> alter system set utl_file_dir='D:/oracle/logminer' scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

SQL> show parameter utl

NAME                                 TYPE        VALUE                         

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

create_stored_outlines               string                                    

utl_file_dir                         string      D:/oracle/logminer     

 

3、建立字典檔案：

SQL> execute dbms_logmnr_d.build ('dict.ora','D:/oracle/logminer',dbms_logmnr_d.store_in_flat_file);

PL/SQL 過程已成功完成。

 

4、建立日誌分析列表：

SQL> execute dbms_logmnr.add_logfile(logfilename=>'D:/oracle/arch/TEST/ARCHIVELOG/2009_08_25/O1_MF_1_32_597FQD7B_.ARC',options=>dbms_logmnr.new);

PL/SQL 過程已成功完成。

 

SQL> execute dbms_logmnr.add_logfile(logfilename=>'D:/oracle/arch/TEST/ARCHIVELOG/2009_08_25/O1_MF_1_30_597B5P7B_.ARC',options=>dbms_logmnr.addfile);

PL/SQL 過程已成功完成。

 

5、啟動LogMiner

SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'D:/oracle/logminer/dict.ora',options=>dbms_logmnr.ddl_dict_tracking);

PL/SQL 過程已成功完成。

 

6、查詢分析日誌結果：

 

SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents  where lower(sql_redo) like '%table%';

USERNAME        SCN TIMESTAMP      SQL_REDO

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

            1647538 25-8月 -09     ALTER TABLE "SCOTT"."EMP" RENAME CONSTRAINT "PK_EMP" TO "BIN$f/mFjN+nTmaYjrb17YU80w==$0" ;

            1647550 25-8月 -09     ALTER TABLE "SCOTT"."EMP" RENAME TO "BIN$E5UujHaTR+uItpLtzN0Ddw==$0" ;

            1647553 25-8月 -09     drop table emp AS "BIN$E5UujHaTR+uItpLtzN0Ddw==$0" ;

            1647565 25-8月 -09     ALTER TABLE "SCOTT"."DEPT" RENAME CONSTRAINT "PK_DEPT" TO "BIN$3imFL+/1SqONFCB7LoPcCg==$0" ;

            1647571 25-8月 -09     ALTER TABLE "SCOTT"."DEPT" RENAME TO "BIN$kYKBLvltRb+vYaT6RkaRiA==$0";

            1647574 25-8月 -09     drop table dept AS "BIN$kYKBLvltRb+vYaT6RkaRiA==$0" ;                        

                                              

或者其他的查詢：

 

SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where username='SYS';

USERNAME TIMESTAMP           SQL_REDO                                          

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

USERNAME        SCN TIMESTAMP      SQL_REDO

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

SYS         1647487 25-8月 -09     set transaction read write;

SYS         1647488 25-8月 -09     alter user scott account unlock;

SYS         1647490 25-8月 -09     Unsupported

SYS         1647492 25-8月 -09     commit;

                         

7、結束LogMiner

SQL> execute dbms_logmnr.end_logmnr;

PL/SQL 過程已成功完成。

 

       注意，v$logmnr_contents內容儲存了日誌的內容，只在當前會話有效，如果想長期儲存分析，可以在當前會話用create table tablename as select * from v$logmnr_contents語句來持久儲存。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15880878/viewspace-722992/，如需轉載，請註明出處，否則將追究法律責任。