|Product:||Syniti Data Replication, DBMoto|
|Summary:||Possible errors and workaround in setting up Oracle 8.1.7 as a source for mirroring|
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:
SYS.DBMS_LOGMNR_D.BUILD('<dictionary name>', '<dictionary path>');
- 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.
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.
The error occurs due to [BUG:1529107], which is fixed in Oracle9i and for which a fix exists starting in the 184.108.40.206.0 patchset.
The number of objects handled by the DBMS_LOGMNR_D.BUILD procedure goes beyond the currently defined limit for VARRAY.
Two options are available:
- Obtain the fix for [BUG:1529107] by upgrading to Oracle9i or applying the 220.127.116.11.0 (or greater) patchset.
- 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;
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.