这篇文章是对oracle文档中Using LogMiner to Analyze Redo Log Files的译注。

Oracle LogMiner, which is part of Oracle Database, enables you to query online and archived redo log files through a SQL interface. Redo log files contain information about the history of activity （数据库活动的历史信息）on a database.

（通过sql interface查询online 和 archive redo log files）

This chapter describes LogMiner as it is used from the command line. You can also access LogMiner through the Oracle LogMiner Viewer graphical user interface. Oracle LogMiner Viewer is a part of Oracle Enterprise Manager. See the Oracle Enterprise Manager online Help for more information about Oracle LogMiner Viewer.

LogMiner Benefits

All changes made to user data or to the database dictionary are recorded in the Oracle redo log files so that database recovery operations can be performed. （对user data 或database dictionary的所有变化都记录在redo log files中）

Because LogMiner provides a well-defined, easy-to-use, and comprehensive relational interface to redo log files, it can be used as a powerful data audit tool, as well as a tool for sophisticated data analysis. The following list describes some key capabilities of LogMiner:（logminer提供对redo log files的relational interface（即sql interface），可用作data audit tool（数据审计工具和sophisticated data analysis tool（复杂的数据分析工具））

• Pinpointing when a logical corruption to a database, such as errors made at the application level, may have begun. （定位逻辑损坏）These might include errors such as those where the wrong rows were deleted because of incorrect values in a WHERE clause, rows were updated with incorrect values, the wrong index was dropped, and so forth. For example, a user application could mistakenly update a database to give all employees 100 percent salary increases rather than 10 percent increases, or a database administrator (DBA) could accidently delete a critical system table. It is important to know exactly when an error was made so that you know when to initiate time-based or change-based recovery. This enables you to restore the database to the state it was in just before corruption. See Querying V$LOGMNR_CONTENTS Based on Column Values for details about how you can use LogMiner to accomplish this.

• Determining what actions you would have to take to perform fine-grained recovery at the transaction level. （在事务级别执行细粒度的恢复）If you fully understand and take into account existing dependencies, it may be possible to perform a table-specific undo operation to return the table to its original state. This is achieved by applying table-specific reconstructed SQL statements that LogMiner provides in the reverse order from which they were originally issued. See Scenario 1: Using LogMiner to Track Changes Made by a Specific User for an example.（执行某个表的undo operation，应用logminer提供的表的reconstructed SQL statements）

Normally you would have to restore the table to its previous state, and then apply an archived redo log file to roll it forward.

（复原表到以前的状态，接着应用an archived redo log file to roll it forward）

• Performance tuning and capacity planning through trend analysis. （趋势分析）You can determine which tables get the most updates and inserts. That information provides a historical perspective on disk access statistics, which can be used for tuning purposes. See Scenario 2: Using LogMiner to Calculate Table Access Statistics for an example.

• Performing postauditing. LogMiner can be used to track any data manipulation language (DML) and data definition language (DDL) statements executed on the database, the order in which they were executed, and who executed them. (However, to use LogMiner for such a purpose, you need to have an idea when the event occurred so that you can specify the appropriate logs for analysis; otherwise you might have to mine a large number of redo log files, which can take a long time. Consider using LogMiner as a complementary activity to auditing database use. See the Oracle Database Administrator's Guide for information about database auditing.)（跟踪dml和ddl操作）

Introduction to LogMiner

The following sections provide a brief introduction to LogMiner, including the following topics:

• LogMiner Configuration

• Directing LogMiner Operations and Retrieving Data of Interest

The remaining sections in this chapter describe these concepts and related topics in more detail.

LogMiner Configuration

There are four basic objects in a LogMiner configuration that you should be familiar with: the source database, the mining database, the LogMiner dictionary, and the redo log files containing the data of interest:

（logminer配置包括四个基本对象：source database、mining database、LogMiner dictionary、redo log files）

• The source database is the database that produces all the redo log files that you want LogMiner to analyze.

• The mining database is the database that LogMiner uses when it performs the analysis.

• The LogMiner dictionary allows LogMiner to provide table and column names, instead of internal object IDs, when it presents the redo log data that you request.

LogMiner uses the dictionary to translate internal object identifiers and datatypes to object names and external data formats. Without a dictionary, LogMiner returns internal object IDs and presents data as binary data.

（logminer使用logminer字典翻译internal object IDs和internal数据类型为对象名称和外部的数据格式）

For example, consider the following the SQL statement:

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

Without the dictionary, LogMiner will display:

insert into "UNKNOWN"."OBJ# 45522"("COL 1","COL 2","COL 3","COL 4") values

(HEXTORAW('45465f4748'),HEXTORAW('546563686e6963616c20577269746572'),

HEXTORAW('c229'),HEXTORAW('c3020b'));

• The redo log files contain the changes made to the database or database dictionary.

Sample Configuration

Figure 17-1 shows a sample LogMiner configuration. In this figure, the source database in Boston generates redo log files that are archived and shipped to a database in San Francisco. A LogMiner dictionary has been extracted to these redo log files. The mining database, where LogMiner will actually analyze the redo log files, is in San Francisco. The Boston database is running Oracle9i, and the San Francisco database is running Oracle Database 10g.

（可以远程分析日志，原因是logminer dictionary可直接extracted到redo log files中）

Figure 17-1 Sample LogMiner Database Configuration

Description of the illustration remote_config.gif

Figure 17-1 shows just one valid LogMiner configuration. Other valid configurations are those that use the same database for both the source and mining database, or use another method for providing the data dictionary. These other data dictionary options are described in the section about LogMiner Dictionary Options.

Requirements

The following are requirements for the source and mining database, the data dictionary, and the redo log files that LogMiner will mine:

• Source and mining database

• Both the source database and the mining database must be running on the same hardware platform.

• The mining database can be the same as, or completely separate from, the source database.

• The mining database must run the same version or a later version of the Oracle Database software as the source database.

• The mining database must use the same character set (or a superset of the character set) used by the source database.

• LogMiner dictionary

• The dictionary must be produced by the same source database that generates the redo log files that LogMiner will analyze.

• All redo log files:

• Must be produced by the same source database.

• Must be associated with the same database RESETLOGS SCN.

• Must be from a release 8.0 or later Oracle Database. However, several of the LogMiner features introduced as of release 9.0.1 work only with redo log files produced on an Oracle9i or later database. See Supported Databases and Redo Log File Versions.

LogMiner does not allow you to mix redo log files from different databases or to use a dictionary from a different database than the one that generated the redo log files to be analyzed.

Directing LogMiner Operations and Retrieving Data of Interest

You direct LogMiner operations using the DBMS_LOGMNR and DBMS_LOGMNR_D PL/SQL packages, and retrieve data of interest using the V$LOGMNR_CONTENTS view, as follows:

（五步法：指定字典，指定日志文件，开始分析，查询分析结果，结束分析）

1. Specify a LogMiner dictionary.

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

2. 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 automatically when you start LogMiner (in Step 3).（指定redo log files有：手动和自动两种）

3. Start LogMiner.

Use the DBMS_LOGMNR.START_LOGMNR procedure.

4. Request the redo data of interest.

Query the V$LOGMNR_CONTENTS view. (You must have the SELECT ANY TRANSACTION privilege to query this view. Per email from Diana Lorentz/Doug Voss on 3/16/05)