LogMiner Enhancements In Oracle9i

The basic usage of Logminer is the same between Oracle8i and Oracle9i, so for basic usage notes see the LogMiner article in the Oracle8i section.

Several new features have been added to Logminer in Oracle9i (9.0.1) to make it more flexible and robust:

DDL Statements
Data Dictionary Access
DDL Dictionary Tracking
Dictionary Staleness
Log Corruption
Committed Data Only
Primary Keys
Restrictions
Logminer Views & Viewer

DDL Statements

In previous versions of LogMiner DDL statements were viewed as a list of DML statements against the internal tables. In Oracle9i the actual DDL statement is now logged. The V$LOGMNR_CONTENTS view now contains a column called OPERATION which indicates if the operation was DDL or DML. The SQL_REDO column shows the actual DDL command.

Data Dictionary Access

The dictionary is used by Logminer to translate object IDs into meaningful object names. In Oracle8i this had to be stored as a flat file on the file system. In Oracle9i you can choose to store the dictionary in the redo logs.

BEGIN
  DBMS_LOGMNR_D.build (
    dictfilename => 'C:\Oracle\Oradata\TSH1\Archive\dictionary.ora',
    options      => DBMS_LOGMNR_D.store_in_flat_file);

  DBMS_LOGMNR_D.build (
    options => DBMS_LOGMNR_D.store_in_redo_logs);
END;
/

The latter option can only be used if the database is in ARCHIVELOG mode and will cause a considerable increase in redo.

Logminer can be started using a dictionary file, redo logs or the online dictionary.

BEGIN
  DBMS_LOGMNR.start_logmnr (
    dictfilename =>'C:\Oracle\Oradata\TSH1\Archive\dictionary.ora');

  DBMS_LOGMNR.start_logmnr (
    options => DBMS_LOGMNR.dict_from_redo_logs);

  DBMS_LOGMNR.start_logmnr (
    options => Dbms_Logmnr.Dict_From_Online_Catalog);
END;
/

If objects have been dropped and recreated the online dictionary may not be able to resolve some object IDs. Its main use is for recent redo logs.

DDL Dictionary Tracking

In Oracle8i the dictionary file was static. Any objects created after the creation of the dictionary file could not be translated. In Oracle9i the DDL_DICT_TRACKING option makes Logminer apply any DDL operations to its dictionary allowing the translation of new objects.

BEGIN
  DBMS_LOGMNR.start_logmnr (
    options => Dbms_Logmnr.DDL_Dict_Tracking);
END;
/

The database must be open for this option.

Dictionary Staleness

In Oracle9i versioning information is logged about each object. If a DDL statement is issued against an object this will alter its version. Logminer is aware of this versioning information and can detect if the current dictionary is not in sync with redo logs.

Log Corruption

Prior to Oracle9i corruption of the logfiles caused Logminer to stop. In Oracle9i the SKIP_CORRUPTION option in the DBMS_LOGMNR.start_logmnr() procedures tells Logminer to attempt to pass over corruptions:

BEGIN
  DBMS_LOGMNR.start_logmnr (
    options => DBMS_LOGMNR.skip_corruption);
END;
/

Logminer indicates the number of blocks skipped.

Committed Data Only

The COMMITTED_DATA_ONLY option means that Logminer will only display SQL_REDO and SQL_UNDO for commited transactions, greatly reducing the number of rows displayed:

BEGIN
  DBMS_LOGMNR.start_logmnr (
    options => DBMS_LOGMNR.committed_data_only);
END;
/

Primary Keys

By default SQL_REDO and SQL_UNDO statements contain rowid references in the WHERE clause. If this data is to used against a different database this rowid has no meaning. For this reason Logminer can be configured to display the primary key value, which can be used in place of the rowid. The rowid is still displayed.

To get these results supplemental redo log groups must be added at the table or database level. This feature significantly increases the amount of redo at the database level.

Restrictions

In Oracle 9.0.1 logminer does not support:

LONG and LOB datatypes
Object Types
Collections
Object Ref datatypes
Index Organized tables (IOTs)

Logminor cannot be run in a multithreaded server (MTS) environment but it can be used to analyze logs from a an MTS environment.

Logminer can now be used to view direct path loads as long as the system is in ARCHIVELOG mode and LOGGING is enabled.

Logminer Views & Viewer

Very few changes have occurred to the dictionary views. The V$LOGMNR_CONTENTS view now contains a column called OPERATION which indicates if the operation was DDL or DML. The V$LOGMNR_DICTIONARY view only contains rows if the STORE_IN_FLAT_FILE option was used to create the dictionary.

Oracle Enterprise Manager now includes a GUI tool called Logminor Viewer which allows you to build and store queries against the V$LOGMNR_CONTENTS view. Rows can be returned with the primary key values displayed and the view can be restricted to commited transactions if required.

For further information see:

Hope this helps. Regards Tim...

Back to the Top.