20
DBMS_LOGMNR

DBMS_LOGMNR supplies the log analyzer tool with the list of filenames and SCNs required to initialize the tool. After this procedure completes, the server is ready to process SELECTs against the V$LOGMNR_CONTENTS view.

Redo log data is especially important for recovery, because you can use it to pinpoint when a database became corrupted.You can then use this information to recover the database to the state just prior to corruption.

See Also: Oracle8i Administrator's Guide and Oracle8i Backup and Recovery Guide

Using the LogMiner

After you have created a dictionary file with DBMS_LOGMNR_D, you can begin analyzing archived redo logs.

1. Start an Oracle instance, with the database either mounted or unmounted.

2. Specify the log file or files that you want to read by running the DBMS_LOGMNR.ADD_LOGFILE procedure. You can view information about specified log files with V$LOGMNR_LOGS.

3. Start the log reader with the DBMS_LOGMNR.START_LOGMNR procedure. You can set the start and end SCN and time parameters in the START_LOGMNR command to filter the redo records you will analyze. You can query the V$LOGMNR_PARAMETERS view to view the parameters.

4. View the output through the V$LOGMNR_CONTENTS table. LogMiner returns all rows in SCN order, which is the same order applied in media recovery.

   See Also: "Example" and Chapter 20, "DBMS_LOGMNR"

Constants

Constants for ADD_LOGFILE Options flag

NEW	DBMS_LOGMNR.NEW purges the existing list of logfiles, if any. Place the logfile specified in the list of logfiles to be analyzed.
ADDFILE	DBMS_LOGMNR.ADDFILE adds this logfile to the list of logfiles to be analyzed.
REMOVEFILE	DBMS_LOGMNR.REMOVEFILE removes the logfile from the list of logfiles to be analyzed. Attempts to remove a file that has not been previously added, raises an exception (ORA-1290).

Constants for START_LOGMNR Options flag

USE_COLMAP	DBMS_LOGMNR.USE_COLMAP uses the column map specified in the logmnr.opt file. This file must be in the same directory as the dictionary file specified by DictFileName.
SKIP_CORRUPTION	Directs LogMiner to skip corrupt redo blocks and continue processing. This option works only when a redo block (and not the header of the redo logfile) has been corrupted. Caller should check the INFO column in the V$LOGMNR_CONTENTS view to determine the corrupt blocks skipped by LogMiner.

Using Place Holder Columns

The V$LOGMNR_CONTENTS table includes multiple sets of place holder columns. Each place holder column set contains a name column, a redo value column, and an undo value column. Each place holder column set can be assigned to a table and column via an optional LogMiner assignment file (logmnr.opt). After a place holder column is assigned, it can be used to select changes to the assigned column and table from the redo log stream.

For example, the assignment "colmap = SCOTT EMP (1, EMPNO);" assigns the PH1 place holder column set to the table; SCOTT.EMP, column EMPNO. After being assigned, it is possible to select changes from the redo stream for the EMPNO column of the EMP table;

SELECT scn FROM V$LOGMNR_CONTENTS 
WHERE ph1_name=`EMPNO` 
AND ph1_redo=`12345`; 

The redo stream is processed, and any changes setting the EMPNO column of the EMP table to the value 12345 are returned.

It is possible to have multiple assignments for each place holder column set. For example:

colmap = SCOTT EMP (1, EMPNO);

followed by

colmap = ACCOUNTING CUSTOMER (1, CUSTID);

In this case, the PH1 place holder column set has two assignments. In this case, the PH1 place holder column set has two assignments. Now the user can use the place holder column to select changes either to the EMP table or the CUSTOMER table as the following queries illustrate:

SELECT scn FROM V$LOGMNR_CONTENTS 
WHERE seg_name = `EMP` 
AND ph1_name=`EMPNO` 
AND ph1_redo=`12345`;

or

SELECT scn FROM V$LOGMNR_CONTENTS 
WHERE seg_name = `CUSTOMER` 
AND ph1_name=`CUSTID` 
AND ph1_redo=`12345`;

Using the logmnr.opt Place Holder Column

The logmnr.opt file is processed when the DBMS_LOGMNR.START_LOGMNR procedure is performed and Options is set to USE_COLMAP (Options = USE_COLMAP). Setting USE_COLMAP in Options instructs the LogMiner to read and process the logmnr.opt file. The logmnr.opt file should be located in the same directory as the LogMiner dictionary file.

After the place holder column assignment file (logmnr.opt) is processed, all subsequent selects from the V$LOGMNR_CONTENTS table can use the assigned place holder columns. To change the assignments, update the logmnr.opt file, and re-start the LogMiner.

As the logmnr.opt file is processed the assigned columns are verified against the current LogMiner dictionary. If they do not exist, then the start fails.

Syntax Rules for logmnr.opt

line =  'colmap' <sp> '=' <sp> <schema> <sp> <table> <sp> '(' map ')' ';'   
map =   <num> ',' <colname> [ ',' <num> ',' <colname>] 

<sp>

Space

Words in quotes are fixed symbols:

<num>	Any number (limited to the number of place holder column sets)
<table>	Name of the table
<schema>	Schema name
<colname>	Column name in the specified <schema>.<table>

You can repeat <num> ',' <colname> inside the parentheses up to the number of place holder columns in V$LOGMNR_CONTENTS table.

<table>, <schema> and <colname> must be in all uppercase.

Valid logmnr.opt Syntax

• The user wants some columns of either table to be in the place holder:

  colmap = SCOTT EMP (1, EMPNO, 2 SAL, 3 JOB, 4 MGR, 5 COMM);  
  colmap = SCOTT DEPT (1, DEPTNO);  
  
  

• The colmap can contain duplicate values. In the following example, only the first ph1_redo, ph1_undo and ph4_redo, ph4_undo get filled.

  colmap = SCOTT EMP (1, EMPNO, 2, EMPNO, 3, EMPNO, 4, MGR);   
  

Invalid logmnr.opt Syntax

• Syntax error: No space between "colmap" and "=":

  colmap= SCOTT EMP (1, EMPNO, 2, SAL);  
  

• Syntax error: map not specified correctly:

  colmap = SCOTT EMP (1, EMPNO, 2);  
  

• Syntax error: statement does not end in ';':

  colmap = SCOTT EMP (1, EMPNO)  
  

• Error: schema in lowercase:

  colmap = scott EMP (1, EMPNO, 2 SAL);  
  

• Error: tablename in lowercase:

  colmap = SCOTT emp (1, EMPNO, 2 SAL);  
  

• Error: more than 5 entries in map:

  colmap = SCOTT EMP (1, EMPNO, 2 SAL, 3 JOB, 4 MGR, 5 COMM, 6 ENAME);  
  

• Error: column REGION is not part of the table:

  colmap = SCOTT EMP (1, EMPNO, 2 SAL, 3 JOB, 4 REGION);