LogMiner

LogMiner Enhancements

9i improves LogMiner in a variety of ways. The purpose of LogMiner is to allow you to view entries in the redo log (the log that records database changes).

LogMiner now has a GUI called the LogMiner Viewer. Use this new GUI in viewing the contents of the redo log files via the view V$LOGMNR_CONTENTS. LogMiner Viewer has Display Options that allow you to specify what information you want to view from the logs. Two of the key fields are SQL_REDO and SQL_UNDO. The former contains the redo log statement, while the latter has its “inverse” – a statement that would reverse its effects (or “undo” it).

9i now records DDL statements in the redo logs. (You no longer have to reverse-engineer them from the complex DML Oracle runs against its internal tables, as you had to when using LogMiner under 8i). You can track (but not) undo DDL commands. That is, you can still not recover from a table DROP or TRUNCATE using this feature.

Remember that LogMiner requires access to the data dictionary in order to translate object identifiers into their names and data according to proper data types. Without dictionary access, LogMiner returns object identifiers and hex data representations – not too useful. You can now extract the data dictionary to either a flat file or to the redo log files by using the procedure DBMS_LOGMNR_D.BUILD. Specify the OPTIONS parameter in this procedure as either STORE_IN_FLAT_FILE or STORE_IN_REDO_LOGS.

There is also a third OPTION – DICT_FROM_ONLINE_CATALOG. This uses the online data dictionary, which could possibly be inaccurate if the database changed since the logs were generated.

If you extract the dictionary to either a flat file or the redo logs, you can specify the additional option DDL_DICT_TRACKING. This prompts LogMiner to keep its extracted dictionary in sync with any changes to the real data dictionary by applying DDL it encounters in the redo logs. LogMiner detects that its dictionary export is stale (obsolete) through its use of object version numbers.

LogMiner stops whenever it encounters a corruption in the redo log it is viewing. Use the OPTION called SKIP CORRUPTIONS to force it to continue beyond corruptions.