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

Logminer depends on two packages: DBMS_LOGMNR and DBMS_LOGMNR_D,Oracle 11g installed by default

Basic steps for using Logminer

. Specify a LogMiner dictionary. Specify Logminer dictionary

. Specify a list of redo log files for analysis. Specify the redo or archivelog log files to be mined

. Start LogMiner. Start log mining

. Request the redo data of interest. Query V$LOGMNR_CONTENTS to get the results of mining

. End the LogMiner session. End log mining

Examples of log mining usage:

1. Enable completion log:

SQL > ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

two。 Find out the path of the archive log file that needs to be mined

SQL > SELECT NAME FROM V$ARCHIVED_LOGWHERE FIRST_TIME between to_date ('2018-07-06 08mm and to_date hh34:mi:ss') and to_date (' 2018-07-06 09VZ 40V 40mm hh34:mi:ss') order by 1 NAME----/u01/app/archive/1_342433_904747849.dbf/u01/app/archive/1_342434_904747849.dbf/u01/app/archive/1_342435_904747849.dbf

3. Specify Logminer dictionary (one out of three)

There are three ways to specify a Logminer dictionary for Oracle 11g

1.Using the Online Catalog uses online directories

2.Extracting a LogMiner Dictionary to the Redo Log Files extracts dictionaries into redo log files

3.Extracting the LogMiner Dictionary to a Flat File extracts the dictionary to a flat file (it is not recommended to set the UTL_FILE_DIR parameter and restart the database)

EXECUTE DBMS_LOGMNR.START_LOGMNR (OPTIONS= > DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); OREXECUTE DBMS_LOGMNR_D.BUILD (OPTIONS= > DBMS_LOGMNR_D.STORE_IN_REDO_LOGS); OREXECUTE DBMS_LOGMNR_D.BUILD ('dictionary.ora',' / oracle/database/', DBMS_LOGMNR_D.STORE_IN_FLAT_FILE)

Add archive log files to 4.Logminer

EXECUTE DBMS_LOGMNR.ADD_LOGFILE (LOGFILENAME = >'/ u01 * * OPTIONS *

Continue to add logs:

EXECUTE DBMS_LOGMNR.ADD_LOGFILE (LOGFILENAME = >'/ u01 *

View the list of added logs:

Select filename from V$LOGMNR_LOGS

5. Start log mining (choose one of three, corresponding to step 3)

EXECUTE DBMS_LOGMNR.START_LOGMNR (OPTIONS = > DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); OREXECUTE DBMS_LOGMNR.START_LOGMNR (OPTIONS = > DBMS_LOGMNR.DICT_FROM_REDO_LOGS); OREXECUTE DBMS_LOGMNR.START_LOGMNR (DICTFILENAME = >'/ oracle/database/dictionary.ora')

6. Get mining results

SELECT username AS USR, (XIDUSN | |'. | | XIDSLT | |'. | | XIDSQN) AS XID, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('HR',' OE')

Save the excavation results to the specified table: create table logtab as select * from v$logmnr_contents

7. End log mining

EXECUTE DBMS_LOGMNR.END_LOGMNR

Summary

The above is a detailed explanation of the use of Oracle Log Mining (LogMiner) introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave a message for me, and the editor will reply you in time. Thank you very much for your support to the website!

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.