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.
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
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
ALTER DATABASE OPEN;
ALTER DATABASE QUIESCE RESTRICTED;
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
set linesize 82
column name format a60
column completion_time format a20
to_char(completion_time,'yyyy/mm/dd hh24:mi') completion_time
CODE DEPOT FOR FULL SCRIPT
The resulting output will be similar to the
Building the LogMiner dictionary with the
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
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
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.