19
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.

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_FILES.

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 set 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 19, "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. This only works if there was at least one invocation of ADD_LOGFILE with the Options parameter set to NEW.
REMOVEFILE	DBMS_LOGMNR.REMOVEFILE removes the logfile from the list of logfiles to be analyzed. This has no effect if the logfile was not previously added to the list.

Constants for START_LOGMNR Options flag

USER_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.

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 and column; 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: to select only changes to the EMP table, and to add the EMP table name to the SELECT;

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 (UTL_FILE_DIR).

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. 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);
  

Summary of Subprograms

Table 19-1 DBMS_LOGMNR Package Subprograms

ADD_LOGFILE procedure

This procedure adds a file to the existing or newly created list of archive files to process.

In order to select information from the V$LOGMNR_CONTENTS view, the LogMiner session must be set up with some information. This procedure tells the LogMiner session the list of logfiles to analyze.

Syntax

DBMS_LOGMNR.ADD_LOGFILE( 
   LogFileName        IN VARCHAR2,
   Options            IN BINARY_INTEGER default ADDFILE );

Parameters

Table 19-2 ADD_LOGFILE Procedure Parameters

LogFileName

Options

START_LOGMNR procedure

This procedure starts a LogMiner session.

Syntax

DBMS_LOGMNR.START_LOGMNR( 
   startScn           IN NUMBER default 0,
   endScn             IN NUMBER default 0,
   startTime          IN DATE default '01-jan-1988',
   endTime            IN DATE default '01-jan-2988',
   DictFileName       IN VARCHAR2 default '',
   Options            IN BINARY_INTEGER default 0 );

Parameters

Table 19-3 START_LOGMNR Procedure Parameters

startScn

endScn

startTime

endTime

DictFileName

Options

Exceptions

The procedure fails with ORA-1280 for the following reasons:

1. No logfile has (LOW_SCN, NEXT_SCN) range containing the startScn specified.

2. No logfile has (LOW_SCN, NEXT_SCN) range containing the endScn specified.

3. No logfile has (LOW_TIME, HIGH_TIME) range containing the startTime specified.

4. No logfile has (LOW_TIME, HIGH_TIME) range containing the endTime specified.

5. The DictFileName does not exist.

6. The database generating the redo logs is different from the one that generated the dictionary file specified by DictFilename.

7. DBMS_LOGMNR.USE_COLMAP is set without a logmnr.opt file.

8. DBMS_LOGMNR.USE_COLMAP is set and there are syntax errors in logmnr.opt file.

9. endScn is less than startScn.

10. endTime is less than startTime (and startScn and endScn were not specified).

END_LOGMNR procedure

This procedure finishes a session.

Syntax

DBMS_LOGMNR.END_LOGMNR;

Parameters

None.

Example

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( 
   LogFileName => '/oracle/logs/log1.f',
   Options => dbms_logmnr.NEW);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( 
   LogFileName => '/oracle/logs/log2.f', 
   Options => dbms_logmnr.ADDFILE);

EXECUTE DBMS_LOGMNR.START_LOGMNR(
   DictFileName =>'/oracle/dictionary.ora');

SELECT sql_redo 
FROM V$LOGMNR_CONTENTS;