Author: George Jucan

Copyright ©2001, Open Data Systems Inc.

1. Overview

The purpose of this document is to create a general guideline for using the Oracle LogMiner packages as a data replication mechanism, even if their main scope is to provide extensions to Oracle disaster recovery procedures.

The LogMiner utility allows the DBA to read information contained in online and archived redo logs based on selection criteria. Being composed by a set of PL-Sql packages, it provides a fully relational SQL interface to a complete historical view of the transactions performed in a database without restoring the archived redo log files.

2. Basic Functionality

In the case of a user mistake, the classical recovery procedure implied full database recovery up to a moment of time before (as close as possible) the user error. With the Log Mining technology, most of these full database recoveries can be avoided, unless the damage is too extensive to be manual recovered.

The procedure is based on querying the redo logs generated by the database since the moment of failure (including the user error) and undoing the changes performed by the user and the cascading effects.

Analyzing the logs with DBMS_LOGMNR package will output all the statements recorded by the analyzed logs into the V$LOGMNR_CONTENTS view in SCN order, which is the same order applied by default media recovery.

The Log Miner functionality specified by the Oracle documentation includes:
-    Apply the SQL_UNDO statements to roll back the original changes to the database.
-    Determine when a logical corruption to the database may have begun, pinpointing the time or SCN to which you need to perform incomplete recovery.
-    Track changes to a specific table.
-    Track changes made by a specific user.
-    Map data access patterns.
-    Use archived data for tuning and capacity planning.

3. Replication Usage

The usage of the Log Mining utility as a data transport mechanism is based on setting up an identical staging area from tables stand point. As far as the redo logs are not stamped as part of a specific database (the stand-by database concept is used for years) the log files generated by a source database can be explored by the staging/target database and the SQL_REDO statements applied for the target tables.

There are couple of restrictions in using LogMiner for replication:
-    Both source and target databases have to be Oracle version 8.1 or later, running on the same hardware platforms.
-    The block size (DB_BLOCK_SIZE) of both source and target databases has to be identical.
-    Cannot identify DML operations on non-conventional tables, such as: index-organized tables, clustered tables/indexes, tables with non-scalar data types and so on.
-    It supports conventional path insert through SQL*Loader, but does not handle direct path insert operations, even logged.

The generic LogMiner based data replication procedure consists of the following major steps:
-    Create a dictionary file for the target database.
-    Copy the redo log files from the source to the target server.
-    Indicate to the LogMiner utility what redo files it should process.
-    Analyze the specified logs with the DBMS_LOGMNR package.
-    Interrogate the V$LOGMNR_CONTENTS view and select the SQL_REDO statements to be applied.
-    Execute the selected statements.

Note: the redo log opening, analyzing and interrogating have to be performed in the same Sql session, as far as the variables created are sesion based.

Create a Dictionary File

1.    Verify the setting for the initialization parameter UTL_FILE_DIR. If not already set for the database, create a suitable OS directory (e.g. /oracle/lgmnr) and set the parameter accordingly: UTL_FILE_DIR = /oracle/lgmnr

2.    Make sure that the database is opened.

3.    Create the dictionary file by executing the PL/SQL procedure DBMS_LOGMNR_D.BUILD, specifying both a file name for the dictionary and a directory pathname for the file. For example, the following will create a file dictionary.ldd in /oracle/lgmnr: EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ldd','/oracle/logs');

Copy and Analyze the Redo Logs

1.    Use an OS transfer utility (e.g. FTP) to move the needed files from the source to the target server. If the data transfer is part of a permanent replication strategy (e.g. near real time data warehousing component) the OS shell script should manage what files are copied over.

2.    For the redo log files transferred the shell script should generate a SQL script to instruct the Log Miner utility to process these logs. Assuming that the redo logs were copied in the same /oracle/logs directory, the SQL script will look like:

begin;
dbms_logmnr.add_logfile('/oracle/logs/arch_1_719.arc',dbms_logmnr.new);
dbms_logmnr.add_logfile('/oracle/logs/arch_1_720.arc',dbms_logmnr.addfile);
dbms_logmnr.add_logfile('/oracle/logs/arch_1_721.arc', dbms_logmnr.addfile);
.......
dbms_logmnr.start_logmnr(dictfilename=>'/oracle/logs/dictionary.ldd');
end;

3.    Start a SQL session in the target database and execute the above script.

Note: do NOT close the session, as far as all the further operations will have to be executed in this session.

Apply the SQL_REDO Statements

Once the redo logs were analyzed, all the DDL (and some DML) statements applied in the source database will be found in the V$LOGMNR_CONTENTS view. It contains many useful information, as seen from the structure presented in Appendix 1, but for our purpose the most important columns are:
-    SCN and TIMESTAMP if not all the logs will be applied,
-    SEG_OWNER, SEG_NAME and SEG_TYPE if only selected tables are replicated,
-    OPERATION if only some types of statements will be applied (e.g. INSERT and UPDATE are applied but not DELETE),
-    SQL_REDO that is the actual SQL statement to perform that change.

Spooling out the information stored in SQL_REDO based on selection criteria on the other aforesaid fields, a SQL script will be generated with all the changes to be propagated from the source system in the target database.

Once the script is generated, it can be applied in the same session or executed later (e.g. in a batch job).

The Log Miner session will be closed by executing in the same session DBMS_LOGMNR.END_LOGMNR. The SQL session can now be closed normally or used for additional processing.

4. Conclusions

Even if it is a non-standard data replication mechanism and its usage is constrained by the conditions specified above, transporting data from one system to another using the Log Miner utility can be very useful due to its null impact on the source database performance.

It is, in fact, almost a manual (therefore controllable) stand-by mechanism, because it is propagating the changes performed to the data rather then the data itself. And as the stand-by mechanism, it requires an OS copy of the archived redo log files from the source system (therefore almost no performance impact) and applying them in the target database, with the same or a bit more processing as the traditional data propagation techniques.