When Oracle's LogMiner starts it requires a copy of the Oracle Data Dictionary to decode the log records into meaningful data such as table names, column names, data types, etc. It uses this copy for the duration of the LogMiner session, updating the in-memory copy as ALTERS are encountered. The LogMiner program can get it’s initial copy of the dictionary from 3 sources:
- Flat file containing an extract of the dictionary - Available for compatibility only, has no advantage over the other options and is not recommended.
- Online catalog - Contains metadata about only the current definition of the database, tables and columns.
- Redo log containing extract of the dictionary - the Recommended option.
Mining the Oracle Redo log requires that LogMiner have a copy of the dictionary that is in synch with the log records being read. While the online catalog will generally match the "current" ReDo log, LogMining will be successful when only current changes are being mined. When an "alter table" occurs (whether explicitly issued by a user or internally issued by a stats package) at least 1 table will have a new log data format after the alter. For LogMiner to continue successfully, the new format described by the dictionary must be used to decode the log records. The following two scenarios describe the difference between using the online catalog VS a redo log dictionary:
- Online catalog
Advantage: Quick to restart.
Disadvantage: LogMiner must be current in log and cannot go back and re-mine old logs after an "alter" is performed.
The catch is that if it is even 1 log record behind for the altered table, LogMiner cannot decode that record. Historically this was not a large problem since alters are "planned/scheduled" events. With Oracle 12c however, the stats package performs these 'alters' while the table is in use, adding virtual columns, without other intervention. - Redo log dictionary - The only disadvantage is that a copy of the dictionary must be occasionally extracted to the redo logs. This however makes it possible to successfully re-capture from any point in time after loading the "point-in-time dictionary copies. Once the dictionary is read from the log, all records after that are decoded using that dictionary including updates from subsequent Alters performed after the previous dictionary was loaded.