1572 - Errors in creating dictionary file in Oracle 8.1.7

Introduction to Syniti DR/DBMoto and Oracle dictionary

Syniti DR/DBMoto allows Oracle v 8.1.7 and higher as a source for replication in refresh and mirroring modes. In mirroring, the replication is based on Oracle redo log files, which can be made usable once a support file for catalogs has been created: the dictionary file.

The dictionary file, starting from Oracle 9.0, has become “online” and it’s automatically created and managed by the DBMS, but for previous versions (8.1.x), it needs to be created manually. Once created, you need to inform Syniti DR/DBMoto about its name and location to allow the mirroring to take place.

Syniti DR/DBMoto can successfully manage the “manual” dictionary and related redo logs from Oracle version 8.1.7 (the oldest Oracle version that Syniti DR/DBMoto supports as a source for mirroring.)

Create the dictionary in the database

Here are two commands that you can use to create the dictionary. In both cases, the user needs to have Database Administrator privileges.

• Using ODBC, run the following command:

begin
SYS.DBMS_LOGMNR_D.BUILD('<dictionary name>', '<dictionary path>');
end;

• Using PL/SQL, run the following command:

EXECUTE DBMS_LOGMNR_D.BUILD('<dictionary name>', '<dictionary path>')

Check in the path specified if the dictionary file is actually created

Run the following (from either ODBC or PL/SQL):

select * from v$log

You should see at least one row (= 1 log file) with STATUS='CURRENT' to know that the dictionary file has been created.

Errors creating dictionary

Sometimes, in Oracle 8.1.7, the manual creation of the dictionary can fail.

begin;sys.dbms_logmnr_d.build('AnalyseRedo.log','c:\oracle\datadictionary');end;

ERROR at line 1:
    ORA-06532: Subscript outside of limit
    ORA-06512: at "SYS.DBMS_LOGMNR_D", line 793
    ORA-06512: at line 1

This error doesn't necessarily prevent the dictionary file from being created. 

Explanation

The error occurs due to [BUG:1529107], which is fixed in Oracle9i and for which a fix exists starting in the 8.1.7.2.0 patchset.

The number of objects handled by the DBMS_LOGMNR_D.BUILD procedure goes beyond the currently defined limit for VARRAY.

Solution

Two options are available:

1. Obtain the fix for [BUG:1529107] by upgrading to Oracle9i or applying the 8.1.7.2.0 (or greater) patchset.
2. If you are unable to apply the bug fix, you can solve the problem by modifying the package body of DBMS_LOGMNR_D.BUILD, manually editing the file dbmslmd.sql located in $ORACLE_HOME/rdbms/admin folder:
   
   Note: This workaround involves making a small change to an Oracle supplied script. You should only make changes to these scripts when directed to do so by your Oracle DBA and/or Oracle Support
   • Stop Oracle services and make a copy of the dbmslmd.sql script file.
   • In the script file, change the following line:
     
     TYPE col_desc_array IS VARRAY(513) OF col_description;
     to
     TYPE col_desc_array IS VARRAY(700) OF col_description; 
     
     
   • Save the file.
   • Recompile the package body DBMS_LOGMNR_D:
     SQLPLUS> Connect internal
     SQLPLUS> alter package DBMS_LOGMNR_D compile body; 

Further steps with Syniti DR/DBMoto

Once the dictionary is created, you can set it explicitly in the Log Setup configuration in the Oracle Connection Properties in Syniti DR/DBMoto Management Center, acquiring the log current status (SCN) . This will allow Syniti DR/DBMoto to start mirroring.