LogMiner

参考文档：

DBMS_LOGMNR_D

Using LogMiner to Analyze Redo Log Files

DBMS_LOGMNR

1 logmin需要字典：

The DBMS_LOGMNR_D package, one of a set of LogMiner packages, contains two subprograms: the BUILD procedure and the SET_TABLESPACE procedure.

• The BUILD procedure extracts the LogMiner data dictionary to either the redo log files or to a flat file. This information is saved in preparation for future analysis of redo log files using the LogMiner tool.

• The SET_TABLESPACE procedure re-creates all LogMiner tables in an alternate tablespace.

  The LogMiner data dictionary consists of the memory data structures and the database tables that are used to store and retrieve information about objects and their versions. It is referred to as the LogMiner dictionary throughout the LogMiner documentation.

• The LogMiner dictionary enables 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 data types to object names and external data formats. Without a dictionary, LogMiner returns internal object IDs, and presents data as binary data.

  For example, consider the following SQL statement:

  Copy

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

  When LogMiner delivers results without the LogMiner dictionary, LogMiner displays the following output:

  Copy

  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 to the database dictionary.

• Ideally, the LogMiner dictionary file is created after all database dictionary changes have been made, and before the creation of any redo log files that you want to analyze. You can use LogMiner to dump the LogMiner dictionary to the redo log files, perform DDL operations, and dynamically apply the DDL changes to the LogMiner dictionary.

23.10.4 Tracking DDL Statements in the LogMiner Dictionary

LogMiner automatically builds its own internal dictionary from the LogMiner dictionary that you specify when you start LogMiner (either an online catalog, a dictionary in the redo log files, or a flat file).

This dictionary provides a snapshot of the database objects and their definitions.

If your LogMiner dictionary is in the redo log files or is a flat file, then you can use the DDL_DICT_TRACKING option to the PL/SQL DBMS_LOGMNR.START_LOGMNR procedure to direct LogMiner to track data definition language (DDL) statements. DDL tracking enables LogMiner to successfully track structural changes made to a database object, such as adding or dropping columns from a table. For example:

Copy

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => - DBMS_LOGMNR.DDL_DICT_TRACKING + DBMS_LOGMNR.DICT_FROM_REDO_LOGS);

如何选择字典：

Figure 23-2 Decision Tree for Choosing a LogMiner Dictionary

The following sections provide instructions on how to specify each of the available dictionary options.

• Using the Online Catalog
  To direct LogMiner to use the dictionary currently in use for the database, specify the online catalog as your dictionary source when you start LogMiner.
• Extracting a LogMiner Dictionary to the Redo Log Files
  To extract a LogMiner dictionary to the redo log files, the database must be open and in ARCHIVELOG mode and archiving must be enabled.
• Extracting the LogMiner Dictionary to a Flat File
  When the LogMiner dictionary is in a flat file, fewer system resources are used than when it is contained in the redo log files.

END