LOGMNR简单使用

最新推荐文章于 2023-01-29 12:18:43 发布

vict_

最新推荐文章于 2023-01-29 12:18:43 发布

阅读量1.4k

点赞数

分类专栏： oracle 文章标签：数据库测试

本文链接：https://blog.csdn.net/vict_/article/details/40017145

版权

oracle 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

环境

SQL> select * from v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE   11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 -Production
NLSRTL Version 11.2.0.3.0 - Production
 
 
SQL> selectlog_mode,supplemental_log_data_min from v$database;
 
LOG_MODE    SUPPLEME
------------ --------
NOARCHIVELOG NO
 
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOGDATA;
 
Database altered.
 
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MINFROM V$DATABASE;
 
SUPPLEME
--------
YES
 
--开启归档 直接从redo log中提取，需归档模式。
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
 
Total System Global Area  418484224 bytes
Fixed Size                  1345352 bytes
Variable Size             255854776 bytes
Database Buffers          155189248 bytes
Redo Buffers                6094848 bytes
Database mounted.
SQL> alter database archivelog;
 
Database altered.
SQL> alter database open;
 
Database altered.

参考：http://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm

常用包说明

dbms_logmnr_d

SQL> desc dbms_logmnr_d
PROCEDURE BUILD
 Argument Name                  Type                    In/Out Default?
 ----------------------------------------------------- ------ --------
 DICTIONARY_FILENAME            VARCHAR2                IN     DEFAULT
 DICTIONARY_LOCATION            VARCHAR2                IN     DEFAULT
 OPTIONS                        NUMBER                  IN     DEFAULT
PROCEDURE SET_TABLESPACE
 Argument Name                  Type                    In/Out Default?
 ----------------------------------------------------- ------ --------
 NEW_TABLESPACE                 VARCHAR2                IN

例：

Example 1:Extracting the LogMiner Dictionary to a Flat File

The followingexample extracts the LogMiner dictionary file to a flat file named dictionary.ora in a specified path (/oracle/database).

SQL> EXECUTEdbms_logmnr_d.build('dictionary.ora', -
     '/oracle/database/', -
     options =>dbms_logmnr_d.store_in_flat_file);

Example 2: Extracting the LogMiner Dictionary to the RedoLog Files

The followingexample extracts the LogMiner dictionary to the redo log files.

SQL> EXECUTEdbms_logmnr_d.build( -
     options =>dbms_logmnr_d.store_in_redo_logs);

Example: Usingthe DBMS_LOGMNR_D.SET_TABLESPACE Procedure

The followingexample shows the creation of an alternate tablespace and execution of the DBMS_LOGMNR_D.SET_TABLESPACE procedure.

SQL> CREATETABLESPACE  logmnrts$ datafile'/usr/oracle/dbs/logmnrts.f'
     SIZE 25 M REUSE AUTOEXTEND ON MAXSIZEUNLIMITED;
 
SQL> EXECUTEdbms_logmnr_d.set_tablespace('logmnrts$');

DBMS_LOGMNR_D.BUILD使用时数据库应为open状态，并确保此时没有DDL发生。

LogMiner 表默认会放在sysaux表空间内，可用dbms_logmnr_d.set_tablespace改变。

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_logmnrd.htm#ARPLS025

dbms_logmnr

SQL> desc dbms_logmnr
PROCEDURE ADD_LOGFILE
 Argument Name                  Type                    In/Out Default?
 ----------------------------------------------------- ------ --------
 LOGFILENAME                    VARCHAR2                IN
 OPTIONS                        BINARY_INTEGER          IN    DEFAULT
FUNCTION COLUMN_PRESENT RETURNSBINARY_INTEGER
 Argument Name                  Type                    In/Out Default?
 ----------------------------------------------------- ------ --------
 SQL_REDO_UNDO                  NUMBER                  IN     DEFAULT
 COLUMN_NAME                    VARCHAR2                IN     DEFAULT
PROCEDURE END_LOGMNR
FUNCTION MINE_VALUE RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ----------------------------------------------------- ------ --------
 SQL_REDO_UNDO                  NUMBER                  IN     DEFAULT
 COLUMN_NAME                    VARCHAR2                IN     DEFAULT
PROCEDURE REMOVE_LOGFILE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------------- --------
 LOGFILENAME                    VARCHAR2                IN
PROCEDURE START_LOGMNR
 Argument Name                  Type                    In/Out Default?
 ----------------------------------------------------- ------ --------
 STARTSCN                       NUMBER                  IN     DEFAULT
 ENDSCN                         NUMBER                  IN     DEFAULT
 STARTTIME                      DATE                    IN     DEFAULT
 ENDTIME                        DATE                    IN     DEFAULT
 DICTFILENAME                   VARCHAR2                IN     DEFAULT
 OPTIONS                        BINARY_INTEGER          IN    DEFAULT

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_logmnr.htm#ARPLS022

操作步骤

Specify a LogMiner dictionary.

Use the DBMS_LOGMNR_D.BUILD procedureor specify the dictionary when you start LogMiner (in Step 3), or both,depending on the type of dictionary you plan to use.

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 automaticallywhen you start LogMiner (in Step 3).

Start LogMiner.

Use the DBMS_LOGMNR.START_LOGMNR procedure.

Request the redo data of interest.

Query the V$LOGMNR_CONTENTS view. (You must have the SELECT ANY TRANSACTION privilegeto query this view.)

End the LogMiner session.

Use the DBMS_LOGMNR.END_LOGMNR procedure.

You must havebeen granted the EXECUTE_CATALOG_ROLE role to use the LogMiner PL/SQL packages and toquery the V$LOGMNR_CONTENTS view.

http://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm#SUTIL1557

实验

---模拟数据

 
SQL> create table yangl.test_logmnr(idint);
 
Table created.
 
SQL> create index inx_id onyangl.test_logmnr(id);
 
Index created.
 
SQL> insert into yangl.test_logmnrvalues (1);
 
1 row created.
 
SQL> commit;
 
Commit complete.

--操作

SQL> create table yangl.test_logmnr(idint);
 
Table created.
 
SQL> create index inx_id onyangl.test_logmnr(id);
 
Index created.
 
SQL> insert into yangl.test_logmnrvalues (1);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> ALTER SESSION SET nls_date_format ='DD-MON-YYYY HH24:MI:SS';
 
Session altered.
 
SQL> select sysdate from dual;
 
SYSDATE
--------------------
11-OCT-2014 21:42:09
 
SQL> BEGIN
 2      DBMS_LOGMNR_D.BUILD (
 3          options =>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS
   );
 4    5  END;
 6  /
 
PL/SQL procedure successfully completed.
 
SQL> alter system switch logfile;
 
System altered.
 
 
--查看归档
SQL> SET LINESIZE 1000
SQL> COLUMN name             FORMAT A86 HEAD "Log FileName"
SQL> COLUMN first_time       FORMAT A23 HEAD "First Time"
SQL> COLUMN dictionary_begin FORMATA5  HEAD "Dict|Begin"
SQL> COLUMN dictionary_end   FORMAT A5 HEAD "Dict|End"
SQL>
SQL> SELECT
 2      name
 3    , TO_CHAR(first_time,'DD-MON-YYYY HH24:MI:SS') first_time
 4    , dictionary_begin
 5    , dictionary_end
 6  FROM
 7      v$archived_log
 8  WHERE
 9        name IS NOT NULL
 10   AND first_time BETWEEN
 11       TO_DATE('11-OCT-2014 21:36:00', 'DD-MON-YYYY HH24:MI:SS') AND
 12       TO_DATE('11-OCT-2014 22:00:00', 'DD-MON-YYYY HH24:MI:SS')
 13 ORDER BY
 14     sequence#;
 
                                                                                                              Dict  Dict
Log File Name                                                                         First Time              Begin End
------------------------------------------------------------------------------------------------------------- ----- -----
/home/oracle/flash_recovery_area/VICT/archivelog/2014_10_11/o1_mf_1_49_b3n1omwy_.arc   11-OCT-2014 21:42:26    YES  YES
/home/oracle/flash_recovery_area/VICT/archivelog/2014_10_11/o1_mf_1_50_b3n1p7o4_.arc   11-OCT-2014 21:42:27    NO   NO
/home/oracle/flash_recovery_area/VICT/archivelog/2014_10_11/o1_mf_1_51_b3n1pbrl_.arc   11-OCT-2014 21:42:47    NO   NO
/home/oracle/flash_recovery_area/VICT/archivelog/2014_10_11/o1_mf_1_52_b3n1pckl_.arc   11-OCT-2014 21:42:50    NO   NO
/home/oracle/flash_recovery_area/VICT/archivelog/2014_10_11/o1_mf_1_53_b3n1pg5y_.arc   11-OCT-2014 21:42:51    NO   NO

--添加日志
SQL> BEGIN
 2      DBMS_LOGMNR.ADD_LOGFILE (
 3          LogFileName =>'/home/oracle/flash_recovery_area/VICT/archivelog/2014_10_11/o1_mf_1_49_b3n1omwy_.arc',
 4          options =>DBMS_LOGMNR.NEW
 5      );
 6  END;
 7  /
 
PL/SQL procedure successfully completed.
 
SQL> BEGIN
 2      DBMS_LOGMNR.ADD_LOGFILE (
 3          LogFileName =>'/home/oracle/flash_recovery_area/VICT/archivelog/2014_10_11/o1_mf_1_50_b3n1p7o4_.arc',
 4          options => DBMS_LOGMNR.ADDFILE
 5      );
 6  END;
 7  /
 
PL/SQL procedure successfully completed.
 
SQL> BEGIN
 2      DBMS_LOGMNR.ADD_LOGFILE (
 3          LogFileName =>'/home/oracle/flash_recovery_area/VICT/archivelog/2014_10_11/o1_mf_1_51_b3n1pbrl_.arc',
 4          options => DBMS_LOGMNR.ADDFILE
 5      );
 6  END;
 7  /
 
PL/SQL procedure successfully completed.
 
SQL> BEGIN
 2      DBMS_LOGMNR.ADD_LOGFILE (
 3          LogFileName =>'/home/oracle/flash_recovery_area/VICT/archivelog/2014_10_11/o1_mf_1_52_b3n1pckl_.arc',
 4          options =>DBMS_LOGMNR.ADDFILE
 5      );
 6  END;
 7  /
 
PL/SQL procedure successfully completed.
 
SQL> BEGIN
 2      DBMS_LOGMNR.ADD_LOGFILE (
 3          LogFileName =>'/home/oracle/flash_recovery_area/VICT/archivelog/2014_10_11/o1_mf_1_53_b3n1pg5y_.arc',
 4          options =>DBMS_LOGMNR.ADDFILE
 5      );
 6  END;
 7  /
 
PL/SQL procedure successfully completed.
 
SQL> SET LINESIZE 1000
SQL> set pagesize 100
SQL> COLUMN filename        FORMAT A66 HEAD "Log File Name"
SQL> COLUMN type            FORMAT A8  HEAD "Type"
SQL> COLUMN status          FORMAT A33 HEAD "Status"
SQL> COLUMN dictionary_begin FORMAT A6  HEAD "Dict|Begin"
SQL> COLUMN dictionary_end  FORMAT A6 HEAD "Dict|End"
SQL>
SQL> SELECT
  2      filename
  3    , type
  4    , ( CASE status
  5          WHEN 0 THEN 'Redo log file will beprocessed.'
  6          WHEN 1 THEN 'First log file to beprocessed.'
  7          WHEN 2 THEN 'Redo log file will notbe processed (pruned).'
  8          WHEN 4 THEN 'Redo log file is missingfrom LogMiner list.'
  9        END) as status
 10    , dictionary_begin
 11    , dictionary_end
 12  FROM
 13      v$logmnr_logs
 14  ORDER BY
 15      log_id;
 
                                                                                                             Dict   Dict
Log File Name                                                     Type     Status                            Begin  End
-------------------------------------------------------------------------- --------------------------------- ------ ------
/home/oracle/flash_recovery_area/VICT/archivelog/2014_10_11/o1_mf_ARCHIVE  Redo log file will beprocessed.  YES    YES
1_49_b3n1omwy_.arc
 
/home/oracle/flash_recovery_area/VICT/archivelog/2014_10_11/o1_mf_ARCHIVE  Redo log file will beprocessed.  NO     NO
1_50_b3n1p7o4_.arc
 
/home/oracle/flash_recovery_area/VICT/archivelog/2014_10_11/o1_mf_ARCHIVE  Redo log file will beprocessed.  NO     NO
1_51_b3n1pbrl_.arc
 
/home/oracle/flash_recovery_area/VICT/archivelog/2014_10_11/o1_mf_ARCHIVE  Redo log file will beprocessed.  NO     NO
1_52_b3n1pckl_.arc
 
/home/oracle/flash_recovery_area/VICT/archivelog/2014_10_11/o1_mf_ARCHIVE  Redo log file will beprocessed.  NO     NO
1_53_b3n1pg5y_.arc

SQL> BEGIN
  2      DBMS_LOGMNR.START_LOGMNR (
  3          options => DBMS_LOGMNR.DICT_FROM_REDO_LOGS +
  4                     DBMS_LOGMNR.DDL_DICT_TRACKING +
  5                     DBMS_LOGMNR.PRINT_PRETTY_SQL
  6      );
  7  END;
  8  /

PL/SQL procedure successfully completed.

--结束
SQL> BEGIN
  2      DBMS_LOGMNR.END_LOGMNR();
  3  END;
  4  /
 
PL/SQL procedure successfully completed.
 
SQL> select * from v$logmnr_contents;
select * from v$logmnr_contents
              *
ERROR at line 1:
ORA-01306: dbms_logmnr.start_logmnr() must be invoked beforeselecting from v$logmnr_contents

实验参考：

点击打开链接