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.
Related articles.
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.
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.
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.
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.
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.
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; /
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.
In Oracle 9.0.1 logminer does not support the following.
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.
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 more information see:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/9i/logminer-enhancements-9i