Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Create LogMiner dictionary tips

Oracle Database Tips by Donald Burleson

Building the LogMiner Dictionary

The next step in installing a logical standby database is to build the LogMiner dictionary on the primary database. The LogMiner dictionary is used on the logical Oracle instance during the SQL apply mode to build the SQL statements from the redo log entries. So why is it necessary to create Log miner dictionary on the primary database?

Internally, the archived redo log do not name the objects that have been changed.  Instead of noting the row "before image" for the customer_history table, log miner has the row "before image" for table=123.

Hence, the purpose of the LogMiner dictionary is to translate table 123 into its real name, customer_history.

The reason is that when the LogMiner dictionary is created on the primary database, the dictionary will be created on the logical standby site using the archived redo logs by the SQL apply service during the recovery.

This ensures that the dictionary will exist on both of the databases; hence, during the switchover operation, the dictionary will not have to be created on the primary database.

To build the LogMiner dictionary, connect to Oracle as SYSDBA, open the database and put the database in quiesce state.  Then the dictionary can be created using the following statement:


After building the LogMiner dictionary, find the latest archive log. This file should be the starting point for recovery. The following SQL query can be used to find the latest archived log:

set linesize 82

column name            format a60
column completion_time format a20

   to_char(completion_time,'yyyy/mm/dd hh24:mi') completion_time

order by

The resulting output will be similar to the following:

   NAME                                    COMPLETION_TIME
   ------------------------------------   ----------------
   /oracle/appsdb/arch/appsdb_1_248.dbf   2003/09/30 14:55
   /oracle/appsdb/arch/appsdb_1_564.dbf   2003/10/03 09:54
   /oracle/appsdb/arch/appsdb_1_568.dbf   2003/10/03 12:08

Building the LogMiner dictionary with the dictionary_begin command

Ideally, there should only be one row in the output, but if the dictionary has been built more than once on the primary database, there will be more than one redo log having the DICTIONARY_BEGIN entry.

The DICTIONARY_BEGIN column indicates this log contains the start of LogMiner dictionary, and the standby_dest='no' will ensure that the query returns only the local archived files. If this query returns more than one row, select the latest log name. For example, in this case it would be appsdb_1_568.dbf.

Once the LogMiner dictionary is built, un-quiesce the database to start the usual services on the database.

  Alter system unquiesce;
Alter system switch logfile;

The last switch logfile statement marks the end of the backup process on the primary database site.

If a cold backup of the primary database is used to create a logical standby database, the database does not need to be quiesced or unquiesced state.

However, the DBA might want to put the database in restricted mode in order to minimize any DML/DDL activity during the creation of the LogMiner dictionary.


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.