Shulou(Shulou.com)06/01 Report--

Oracle LogMiner is a practical and useful analysis tool provided by Oracle Company since product 8i. Using this tool, we can easily obtain the specific contents of Oracle online / archived log files, especially this tool can analyze all DML and DDL statements for database operations. This tool is particularly useful for debugging, auditing, or backing back a particular transaction.

The LogMiner analysis tool is actually made up of a set of PL/SQL packages and some dynamic views (part of the Oracle8i built-in package). It is released as part of the Oracle database and is a completely free tool provided by the 8i product. However, this tool is a little more complex to use than other Oracle built-in tools, mainly because it does not provide any graphical user interface (GUI).

1. Install LogMiner

Before using LogMiner, you need to confirm whether Oracle comes with a package for LogMiner analysis. Generally speaking, the Windows operating system Oracle10g or above is included by default. If you cannot confirm it, you can log in to the system as DBA to see if there are dbms_logmnr and dbms_logmnr_d packages needed to run LogMiner. If you do not need to install LogMiner tools, you must first run the following two scripts:

A.$ORACLE_HOME/rdbms/admin/dbmslm.sql

B.$ORACLE_HOME/rdbms/admin/dbmslmd.sql

Both scripts must be run as the DBA user. The first script is used to create the DBMS_LOGMNR package, which is used to analyze log files. The second script is used to create the DBMS_LOGMNR_D package, which is used to create data dictionary files.

2. Modify database parameters

-- Database version sys@ORCL > select * from v$version BANNER---CORE 11.2.0.4.0 ProductionTNS for Linux: Version 11.2.0.4.0-ProductionNLSRTL Version 11.2.0.4.0-Production-- add minimum additional logs to the database sys@ORCL > alter database add supplemental log data Database altered.sys@ORCL > select supplemental_log_data_min from vested database share support for logging data collection MIQUFUR-setting the utl_file_dir parameter sys@ORCL > alter system set utl_file_dir='/home/oracle/logminer' scope=spfile System altered.-- restart database sys@ORCL > shutdown immediatesys@ORCL > startupsys@ORCL > show parameter utl_file_dirNAME TYPE VALUE -utl_file_dir string / home/oracle/logminer

3. Prepare test data

Sys@ORCL > conn zx/zxConnected.zx@ORCL > create table log_miner (id number,name varchar2 (10)); Table created.zx@ORCL > insert into log_miner values (1mcmpzx'); 1row created.zx@ORCL > insert into log_miner values (1mlx'); 1row created.zx@ORCL > insert into log_miner values (1mlx'); 1row created.zx@ORCL > commit;Commit complete.

4. Create a data dictionary

Zx@ORCL > conn / as sysdbaConnected.sys@ORCL > desc dbms_logmnr_dPROCEDURE BUILD Argument Name Type In/Out Default?-DICTIONARY_FILENAME VARCHAR2 IN DEFAULT DICTIONARY_LOCATION VARCHAR2 IN DEFAULT OPTIONS NUMBER IN DEFAULTPROCEDURE SET_TABLESPACE Argument Name Type In/Out Default? -NEW_TABLESPACE VARCHAR2 INsys@ORCL > exec dbms_logmnr_d.build (DICTIONARY_FILENAME= > 'dictionary.ora' DICTIONARY_LOCATION= >'/ home/oracle/logminer') LogMnr Dictionary Procedure startedLogMnr Dictionary File OpenedProcedure executed successfully-LogMnr Dictionary CreatedPL/SQL procedure successfully completed.sys@ORCL >! ls-l / home/oracle/logminer/dictionary.ora-rw-r--r-- 1 oracle oinstall 41483316 Nov 11 21:08 / home/oracle/logminer/dictionary.ora

The LogMiner tool actually consists of two new PL/SQL built-in packages (DBMS_LOGMNR and DBMS_LOGMNR _ D) and four V$ dynamic performance views (which are created when you start LogMiner with the procedure DBMS_LOGMNR.START_LOGMNR). Before using the LogMiner tool to analyze the redo log file, you can use the DBMS_LOGMNR_D package to export the data dictionary as a text file. The dictionary file is optional, but without it, the parts of the data dictionary (such as table names, column names, etc.) and values in the statements interpreted by LogMiner will be in hexadecimal form, which we cannot directly understand. For example, the following sql statement:

INSERT INTO dm_dj_swry (rydm, rymc) VALUES (00005, 'Zhang San')

The result explained by LogMiner would look like this:

Insert into Object#308 (col#1, col#2) values (hextoraw ('c30rte567e436'), hextoraw (' 4a6f686e20446f65'))

The purpose of creating a data dictionary is to allow LogMiner references to refer to parts of the internal data dictionary as their actual names, rather than the hexadecimal within the system. The data dictionary file is a text file created using the package DBMS_LOGMNR_D. If the table in the database we are analyzing changes and the data dictionary that affects the library also changes, we need to recreate the dictionary file. Another situation is that when analyzing the redo log of another database file, it is also necessary to regenerate the data dictionary file of the analyzed database.

5. Confirm the redo log of the current processing online status

Sys@ORCL > select group#,status from vault log; GROUP# STATUS- 1 INACTIVE 2 CURRENT 3 INACTIVEsys@ORCL > select group#,member from v$logfile GROUP# MEMBER-- 3 / u02/app/oracle/oradata/orcl/redo03.log 2/ u02/app/oracle/oradata/orcl/redo02.log 1 / u02/app/oracle/oradata/orcl/redo01.log

You can see that redo02 is in the current state.

6. Add logs to be analyzed

-- the first log file is dbms_logmnr.newsys@ORCL > exec dbms_logmnr.add_logfile (LOGFILENAME= >'/ u02 exec dbms_logmnr.add_logfile); if you need to add other logs to PL/SQL procedure successfully completed.--, use the following statement exec dbms_logmnr.add_logfile (LOGFILENAME= >'/ u02 dbms_logmnr.addfile)

7. Use LogMiner for analysis

Sys@ORCL > exec dbms_logmnr.start_logmnr (DICTFILENAME= >'/ home/oracle/logminer/dictionary.ora'); PL/SQL procedure successfully completed.sys@ORCL > col seg_name for a15sys@ORCL > col seg_owner for a15sys@ORCL > col operation for a20sys@ORCL > col sql_redo for a180sys@ORCL > set linesize 200sys@ORCL > select seg_owner,seg_name,operation,sql_redo from v$logmnr_contents where seg_owner='ZX' and seg_name='LOG_MINER' SEG_OWNER SEG_NAME OPERATION SQL_REDO-- -ZX LOG_MINER DDL create table log_miner (id number Name varchar2 (10)) ZX LOG_MINER INSERT insert into "ZX". "LOG_MINER" ("ID", "NAME") values ('1ZX'); ZX LOG_MINER INSERT insert into "ZX". "LOG_MINER" ("ID", "NAME") values ('1ZX') ZX LOG_MINER INSERT insert into "ZX". "LOG_MINER" ("ID", "NAME") values

The above is the process of analyzing online redo logs, and the steps of analyzing archived logs are the same, just change the online redo logs into archived logs when adding log files. Archive logs can also be transferred to other database servers for data analysis, but the dictionary file of the source library is required for analysis.

Considerations for using LogMiner:

Source database and Mining database must run on the same hardware platform, and windows cannot be transferred to Linux for analysis.

The version of Mining database is greater than or equal to the database version of the source database

The character set of Mining database needs to be consistent with the source database or superset of the source database.

Reference document: http://www.cnblogs.com/shishanyuan/p/3140440.html

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.