Logminer 从8i-10g - 人生如炼狱，不得不修行

logminer 在oracle 806中：
      1. 在oracle806中，我们不能直接使用logminer。我们可以将oracle817中的
                $ORACLE_HOME/rdbms/admin/dbmslmd.sql
                $ORACLE_HOME/rdbms/admin/dbmslm.sql
                $ORACLE_HOME/rdbms/admin/prvtlm.plb
            copy到oracle806中去执行，就可以使用了
     2. 在oracle806中，我们只能生成数据字典，然后，需要将数据字典和日志文件copy 到oracle 817的数据库中进行分析

          a. 如果直接添加日志文件，在806上不能通过编译
                   SVRMGR> begin
                        2> sys.dbms_logmnr.add_logfile('g:\orant\database\logorc11.ora',
                        3> sys.dbms_logmnr.new);
                        4> end;
                        5> /
                   ORA-04068: existing state of packages has been discarded
                   ORA-04067: not executed, package body "SYS.DBMS_LOGMNR" does not exist
                   ORA-06508: PL/SQL: could not find program unit being called
                   ORA-06512: at line 2
         b. 手工执行尝试编译包体之后：
                   SVRMGR> @g:\orant\rdbms80\admin\prvtlm.plb
                    MGR-00072: Warning: PACKAGE BODY DBMS_LOGMNR created with compilation errors.
         c. 再次添加日志文件，问题依然：
                   SVRMGR> begin
                        2> sys.dbms_logmnr.add_logfile('g:\orant\database\logorc11.ora',
                        3> sys.dbms_logmnr.new);
                        4> end;
                        5> /
                      ORA-04068: existing state of packages has been discarded
                   ORA-04063: package body "SYS.DBMS_LOGMNR" has errors
                   ORA-06508: PL/SQL: could not find program unit being called
                   ORA-06512: at line 2
           d. 再次重编译包，问题依然：
                alter package DBMS_LOGMNR_D compile body;

Logminer 在oracle817中：
     1 . 在oracle 817中执行logminer 的时候，经常会遇见下标出界的错误，解决的方法是：
          a. Edit the file "dbmslmd.sql" located in the "$ORACLE_HOME/rdbms/admin"     directory.
            Change the line:
                      TYPE col_desc_array IS VARRAY(513) OF col_description;
              to
                      TYPE col_desc_array IS VARRAY(700) OF col_description;
          b. Run the newly modified script:
             SQLPLUS> Connect internal
              SQLPLUS> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql
          c. Recompile the package body DBMS_LOGMNR_D:
              SQLPLUS> alter package DBMS_LOGMNR_D compile body;

Logminer在oracle9&10g中的新特性：
        .1 Tracking DDL Statements using option DDL_DICT_TRACKING
        .2 Executing Reconstructed SQL Statements ：将使 Undo_sql 和 Redo sql的结尾不以‘；’结尾
        .3 Formatting of Returned Data ：使所有的sql 更容易读
        .4 Extracting Actual Data Values from Redo Logs ：使用DBMS_LOGMNR.MINE_VALUE函数，可以对sql中出现的值进行查询
        .5 NULL Returns From the MINE_VALUE Function：当dbms_logmner.mine_values 返回NULL的时候，可能不存在该列，也有可能该列的值真的为NULL，所有必须用 DBMS_LOGMNR.COLUMN_PRESENT 来判断是否有该列
        .6 Filtering Data That is Returned
                .6.1 Showing Only Committed Transactions ：仅仅现实提交的sql
                .6.2 Skipping Redo Corruptions ：容许对坏的redo log 进行miner
                .6.3 Filtering Data By Time
                .6.4 Filtering Data By SCN
        .7 The summary of LogMiner settings
             当使用 DBMS_LOGMNR.START_LOGMNR 的时候，OPTIONS选项主要有以下內容可以供选择：
                  DICT_FROM_ONLINE_CATALOG
                  DICT_FROM_REDO_LOGS
                  CONTINUOUS_MINE
                  COMMITTED_DATA_ONLY
                  SKIP_CORRUPTION
                  NO_SQL_DELIMITER
                  PRINT_PRETTY_SQL
                  STARTIME, ENDTIME
                  STARSCN, ENDSCN

        .8 Supplemental Logging
             The data needed for instance and media recovery is automatically recorded in the redo log files.
             However,a redo-based application may require that additional columns be logged in the redo log files.
             The process of logging these additional columns is called supplemental logging.
             这个在9201之前，是没有的；在9201是默认打开的；在9201之后，默认是关闭的。如果我们不打开supplemental logging，
            则我们将不能使用以下功能：
                 - Index clusters, chained rows, and migrated rows
                - Direct-path inserts (also require that ARCHIVELOG mode be enabled)
                - Extracting the LogMiner dictionary into the redo log files
                - DDL tracking
                - Generating SQL_REDO and SQL_UNDO with identification key information
                - LONG and LOB data types
                .8.1 Database-Level Identification Key Logging
                       ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
                .8.2 Table-Level Identification Key Logging
                       SQL> ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; (10g)
几个必须注意的问题：

The redo logs must be from a release 8.0 or later Oracle database.
However, several of the LogMiner features introduced as of release 9.0.1 only work with redo logs produced on an Oracle9i or later database.
Support for LOB and LONG data types is available as of release 9.2, but only for redo logs generated on a release 9.2 Oracle database.
The redo logs must use a database character set that is compatible with the character set of the database on which LogMiner is running.
In general, the analysis of redo logs requires a dictionary that was generated from the same database that generated the redo logs.
If you are using the online catalog as the LogMiner dictionary, you can only analyze redo logs from the database on which LogMiner is running.
LogMiner must be running on the same hardware platform that generated the redo logs being analyzed. However, it does not have to be on the same system.
It is important to specify the correct redo logs when running LogMiner.
To determine which redo logs are being analyzed in the current LogMiner session, Query the V$LOGMNR_LOGS view, which contains one row for each redo log.