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:

ALTER DATABASE OPEN;
ALTER DATABASE QUIESCE RESTRICTED;
EXEC DBMS_LOGSTDBY.BUILD;

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

select
   name,
   to_char(completion_time,'yyyy/mm/dd hh24:mi') completion_time
from
   v$archived_log
   SEE CODE DEPOT FOR FULL SCRIPT
order by
   completion_time;
 

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.