To extract a LogMiner dictionary to the redo log files, the database must be open and in
ARCHIVELOG mode and archiving must be enabled. While the dictionary is being extracted to the redo log stream, no DDL statements can be executed. Therefore, the dictionary extracted to the redo log files is guaranteed to be consistent (whereas the dictionary extracted to a flat file is not).
To extract dictionary information to the redo log files, execute the PL/SQL
DBMS_LOGMNR_D.BUILD procedure with the
STORE_IN_REDO_LOGS option. Do not specify a file name or location.
EXECUTE DBMS_LOGMNR_D.BUILD( - OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
Oracle Database Administrator's Guide for more information about
Oracle Database PL/SQL Packages and Types Reference for a complete description of
The process of extracting the dictionary to the redo log files does consume database resources, but if you limit the extraction to off-peak hours, then this should not be a problem, and it is faster than extracting to a flat file. Depending on the size of the dictionary, it may be contained in multiple redo log files. If the relevant redo log files have been archived, then you can find out which redo log files contain the start and end of an extracted dictionary. To do so, query the
V$ARCHIVED_LOG view, as follows:
SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES'; SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';
Specify the names of the start and end redo log files, and possibly other logs in between them, with the
ADD_LOGFILE procedure when you are preparing to begin a LogMiner session.
Oracle recommends that you periodically back up the redo log files so that the information is saved and available at a later date. Ideally, this will not involve any extra steps because if your database is being properly managed, then there should already be a process in place for backing up and restoring archived redo log files. Again, because of the time required, it is good practice to do this during off-peak hours.