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

This article will explain in detail the introduction and usage of Oracle Logminer for you. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

What is Logminer?

LogMiner is a practical and useful analysis tool provided by Oracle since product 8i. Using this tool, you can easily get the specific contents of Oracle redo log files (archive log files). LogMiner analysis tool is actually composed of a set of PL/SQL packages and some dynamic views. It is released as part of the Oracle database and is a completely free tool provided by oracle.

Specifically, all changes made to the user data or database dictionary are recorded in the Oracle redo log file RedoLog. Logminer is a tool for parsing RedoLog, and the corresponding SQL data can be obtained by parsing RedoLog through Logminer.

RedoLog write process in Oracle: Oracle redo logs are written in a circular manner, and each Oracle instance has at least 2 log groups. The Oracle redo log is usually switched automatically by Oracle, and the redo log file is switched when the LGWR process stops writing and starts writing to the next log group, or when the user receives an ALTER SYSTEM SWITCH LOGFILE. If archiving is enabled in the Oracle database, the log files of the previous log group will be archived to the archive directory when the log group is switched over.

From the above, we can see that there are two types of RedoLog files in Oracle:

The currently written log group files can be obtained through v$log and v$logfile

Archived redoLog files, available through v$archived_log

Through the loop to find the latest RedoLog that meets the requirements and let Logminer load the analysis, the analyzed data is in the view v$logmnr_contents, and the real-time data of Oracle can be obtained by reading v$logmnr_contents.

II. Rapid use of Logminer

To configure Logminer, please refer to the Oracle configuration LogMiner documentation.

# 1. Prepare test data (test user) SQL > conn test/123456SQL > CREATE TABLE test (id varchar2 (100)); INSERT INTO test (id) values ('000001'); INSERT INTO test (id) values (' 000011'); commit; description: perform the following steps under logminer_test user # 2. View the database's current online log files SQL > SELECT group#, sequence#, status, first_change#, first_time FROM V$log ORDER BY first_change# # found that only redo001 is the current state. # 3. Add SQL > exec dbms_logmnr.add_logfile ('/ home/oracle/app/oracle/oradata/orcl11g/redo01.log', dbms_logmnr.new) to the online log files that need to be parsed # 4. Start LogminerSQL > beginSYS.DBMS_LOGMNR.START_LOGMNR (options = > SYS.DBMS_LOGMNR.skip_corruption + SYS.DBMS_LOGMNR.no_sql_delimiter + SYS.DBMS_LOGMNR.no_rowid_in_stmt + SYS.DBMS_LOGMNR.dict_from_online_catalog + SYS.DBMS_LOGMNR.string_literals_in_stmt), end; # 5, query the redo log SQL > select * from volume LOGMNR contacts, stop LogminerSQL > exec dbms_logmnr.end_logmnr; 3, and the specific use of Logminer. Configure Logminer

Please refer to the Oracle configuration LogMiner documentation.

two。 Use Logminer

1. Specifies the LogMiner dictionary.

two。 Specify a list of redo log files for analysis. Use the DBMS_LOGMNR.ADD_LOGFILE procedure, or instruct LogMiner to automatically create a list of log files to analyze when you start LogMiner (in step 3).

3. Start LogMiner. Use the DBMS_LOGMNR.START_LOGMNR program.

4. Request redo data of interest. Query the V$LOGMNR_CONTENTS view. (you must have permission for SELECT ANY TRANSACTION to query this view)

5. End the LogMiner session. Use the DBMS_LOGMNR.END_LOGMNR program.

3.Logminer dictionary

The function of LogMiner dictionary

The Oracle data dictionary records information about all current tables, fields, and so on. LogMiner uses dictionaries to convert internal object identifiers and data types to object names and external data formats. If there is no dictionary, LogMiner returns the internal object ID and displays the data as a binary number

INSERT INTO HR.JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES ('IT_WT','Technical Writer', 4000, 11000)

Without a dictionary, LogMiner will show:

Insert into "UNKNOWN". "OBJ# 45522" ("COL 1", "COL 2", "COL 3", "COL 4") values (HEXTORAW ('45465f4748'), HEXTORAW (' 546563686e6963616c2057726946572'), HEXTORAW ('c229'), HEXTORAW (' c3020b'))

Logminer Dictionary option

The LogMiner dictionary supports three options:

[external link image transfer failed. The origin server may have hotlink protection mechanism. It is recommended to save the image and upload it directly (http://note.youdao.com/yws/res/612/WEBRESOURCEb0b3ba1b987bc083fc09c6dae89c77e2)]).

4. Specify Logminer redo log file

To start a new redo log file list, you need to use DBMS_LOGMNR.NEW to indicate that this is the beginning of the new list

EXECUTE DBMS_LOGMNR.ADD_LOGFILE (LOGFILENAME = >'/ oracle/logs/log1.dbf', OPTIONS = > DBMS_LOGMNR.NEW)

You can add additional log files using the following statement

EXECUTE DBMS_LOGMNR.ADD_LOGFILE (LOGFILENAME = >'/ oracle/logs/log2.dbf', OPTIONS = > DBMS_LOGMNR.ADDFILE); 5. Start LogMiner

Start Logminer using DBMS_LOGMNR.START_LOGMN. You can specify parameters:

Specify how LogMiner filters the returned data (for example, by start and end times or SCN values)

Specify options for formatting data returned by LogMiner

Specify the LogMiner dictionary to use

The main parameters are:

The OPTIONS parameter states: * DBMS_LOGMNR.SKIP_CORRUPTION-redlog * DBMS_LOGMNR.NO_SQL_DELIMITER skipping errors-do not use'; 'split redo sql * DBMS_LOGMNR.NO_ROWID_IN_STMT-by default, SQL_REDO and SQL_UNDO statements for UPDATE and DELETE operations contain "ROWID =" in the where clause. * however, this is inconvenient for applications that want to re-execute the SQL statement. When this option is set, "ROWID" is not placed at the end of the refactoring statement * DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG-using online dictionary * DBMS_LOGMNR.CONTINUOUS_MINE-requires log * DBMS_LOGMNR.COMMITTED_DATA_ONLY to be used in the same instance where the redo log is generated-when this option is specified, LogMiner groups all DML operations that belong to the same transaction together. Transactions are returned in commit order. * DBMS_LOGMNR.STRING_LITERALS_IN_STMT-by default, when formatting a formatted SQL statement, the SQL_REDO and SQL_UNDO statements use the NLS settings of the database session * such as NLS_DATE_FORMAT,NLS_NUMERIC_CHARACTERS, etc.). With this option, the ANSI / ISO string text format will be used

Example

EXECUTE DBMS_LOGMNR.START_LOGMNR (STARTTIME = > '01-Jan-2003 08 01-Jan-2003 30 ENDTIME, ENDTIME = >' 01-Jan-2003 08 15 45, OPTIONS = > DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE)

6 query the interested redo data in V$LOGMNR_CONTENTS

Logminer will parse the logs in redoLog and load them into the v LOGMNRCONTENTS view. We only need to query with sql to get the corresponding data in the v LOGMNR_CONTENTS view. We only need to use the sql query to get the corresponding data in the v LOGMNRCONTENTS view. We only need to use the sql query to get the corresponding data vLOGMNR_CONTENTS view related field https://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_1154.htm

The main fields are:

The column data type describes that SCNNUMBERoracle assigns a unique scnOPERATIONVARCHAR2 (32) INSERT UPDATE DELETE DDL COMMIT ROLLBACK to each committed transaction. SEG_OWNERVARCHAR2 (32) schemaTABLE_NAMEVARCHAR2 (32) Table name TIMESTAMPDATE database change timestamp SQL_REDOVARCHAR2 (4000) reconstructed SQL statement, which is equivalent to the original SQL statement that made the change

Example

SELECT scn, timestamp, operation, seg_owner, table_name, sql_redo, row_id, csfFROM v$logmnr_contentsWHERE scn >? This is the end of the introduction and usage of Oracle Logminer. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.