LogMiner相關知識(摘自網路)

官方文件關於LogMiner介紹：

以下摘自官網：

LogMiner Benefits

All changes made to user data or to the database dictionary are recorded in the Oracle redo log files so that database recovery operations can be performed.

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. The following list describes some key capabilities of LogMiner:
Pinpointing when a logical corruption to a database, such as errors made at the application level, may have begun. These might include errors such as those where
the wrong rows were deleted because of incorrect values in a WHERE clause, rows were updated with incorrect values, the wrong index was dropped, and so forth.
For example, a user application could mistakenly update a database to give all employees 100 percent salary increases rather than 10 percent increases,
or a database administrator (DBA) could accidently delete a critical system table. It is important to know exactly when an error was made so that you know
when to initiate time-based or change-based recovery. This enables you to restore the database to the state it was in just before corruption. See
 "Querying V$LOGMNR_CONTENTS Based on Column Values" for details about how you can use LogMiner to accomplish this.

    Determining what actions you would have to take to perform fine-grained recovery at the transaction level. If you fully understand and take into account
    existing dependencies, then it may be possible to perform a table-specific undo operation to return the table to its original state. This is achieved by
    applying table-specific reconstructed SQL statements that LogMiner provides in the reverse order from which they were originally issued. See "Scenario 1:
    Using LogMiner to Track Changes Made by a Specific User" for an example.

    Normally you would have to restore the table to its previous state, and then apply an archived redo log file to roll it forward.

    Performance tuning and capacity planning through trend analysis. You can determine which tables get the most updates and inserts. That information
    provides a historical perspective on disk access statistics, which can be used for tuning purposes. See "Scenario 2: Using LogMiner to Calculate Table
    Access Statistics" for an example.

    Performing postauditing. LogMiner can be used to track any data manipulation language (DML) and data definition language (DDL) statements executed
    on the database, the order in which they were executed, and who executed them. (However, to use LogMiner for such a purpose, you need to have an idea
    when the event occurred so that you can specify the appropriate logs for analysis; otherwise you might have to mine a large number of redo log files,
    which can take a long time. Consider using LogMiner as a complementary activity to auditing database use. See the Oracle Database Administrator's Guide
    for information about database auditing.)
為什麼會用到LogMiner?
主要出於以下幾個緣由:

    當資料庫發生了誤操作，需要不完全恢復，為確認誤操作準確的時間點或SCN號，此時需用到LogMiner。
    傳統恢復一個上TB或是恢復一個幾百GB表空間中的一個小表，標準的操作是把整個表空間恢復到之前的狀態，然後再應用歸檔日誌，加上搭建恢復環境的時間，整個時間會很長。透過LogMiner可以換一種恢復思維，能透過它解析到這個表上所有的DML操作，可以做反向修復。合理的使用會大幅降低此情況恢復的複雜度和時間。
    可以把LogMiner當成最強大的資料庫審計工具。
    在過去的某個時間資料庫很忙或是產生了大量的歸檔日誌，透過LogMiner可以知道是哪些操作，哪個表佔比較大，主要矛盾在哪。
       Logminer是oracle從8i開始提供的用於分析重做日誌資訊的工具，它包括DBMS_LOGMNR和DBMS_LOGMNR_D兩個package，後邊的D是字典的意思。它既能分析redo log file，也能分析歸檔後的archive log file。在分析日誌的過程中需要使用資料字典，一般先生成資料字典檔案後使用，10g版本還可以使用線上資料字典。

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

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

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

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

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)