DBMS_LOGMNR, 2 of 2

Summary of Subprograms

Table 20-1 DBMS_LOGMNR Package Subprograms

Subprogram	Description
ADD_LOGFILE Procedure	Adds a file to the existing or newly created list of archive files to process.
START_LOGMNR Procedure	Initializes the log analyzer tool.
END_LOGMNR Procedure	Finishes a session.

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.

Note:
If you want to analyze five logfiles, you must call the ADD_LOGFILE procedure five times.

Syntax

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

Parameters

Table 20-2 ADD_LOGFILE Procedure Parameters

Parameter Description

Parameter	Description
LogFileName	Name of the logfile that must be added to the list of logfiles to be analyzed by this session.
Options	Either: - Starts a new list (`DBMS_LOGMNR`.`NEW`) - Adds a file to an existing list (`DBMS_LOGMNR`.`ADDFILE`), or - Removes a logfile (`DBMS_LOGMNR`.`REMOVEFILE`) See "Constants for ADD_LOGFILE Options flag".

LogFileName

Name of the logfile that must be added to the list of logfiles to be analyzed by this session.

Options

Either:

- Starts a new list (DBMS_LOGMNR.NEW)

- Adds a file to an existing list (DBMS_LOGMNR.ADDFILE), or

- Removes a logfile (DBMS_LOGMNR.REMOVEFILE)

See "Constants for ADD_LOGFILE Options flag".

Exceptions

ORA-1284: logfile cannot be opened. Logfile or the directory may be non-existent or inaccessible.
ORA-1286: logfile specified is not from the database that produced other logfiles added for analysis.
ORA-1287: logfile specified is from a different database incarnation.
ORA-1289: attempt to add duplicate logfile.
ORA-1290: attempt to remove unlisted logfile.

START_LOGMNR Procedure

This procedure starts a LogMiner session.

Note:
This procedure fails if you did not specify a list of logfiles to be analyzed previously through the ADD_LOGFILE procedure.

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 20-3 START_LOGMNR Procedure Parameters

Parameter	Description
startScn	Only consider redo records with `SCN` greater than or equal to the `startSCN` specified. This fails if there is no logfile with an `SCN` range (i.e, the `LOW_SCN` and `NEXT_SCN` associated with the logfile as shown in `V$LOGMNR_LOGS` view) containing the `startScn`.
endScn	Only consider redo records with `SCN` less than or equal to the `endSCN` specified. This fails if there is no logfile with an `SCN` range (i.e, the `LOW_SCN` and `NEXT_SCN` associated with the logfile as shown in `V$LOGMNR_LOGS` view) containing the `endScn`.
startTime	Only consider redo records with timestamp greater than or equal to the `startTime` specified. This fails if there is no logfile with a time range (i.e, the `LOW_TIME` and `HIGH_TIME` associated with the logfile as shown in `V$LOGMNR_LOGS` view) containing the `startTime`. This parameter is ignored if `startScn` is specified.
endTime	Only consider redo records with timestamp less than or equal to the `endTime` specified. This fails if there is no logfile with a time range (i.e, the `LOW_TIME` and `HIGH_TIME` associated with the logfile as shown in `V$LOGMNR_LOGS` view) containing the `endTime`. This parameter is ignored if `endScn` is specified.
DictFileName	This flat file contains a snapshot of the database catalog. This must be specified if you expect to see reconstructed `SQL_REDO` and `SQL_UNDO` columns in `V$LOGMNR_CONTENTS`, as well as fully translated `SEG_NAME`, `SEG_OWNER`, `SEG_TYPE_NAME` and `TABLE_SPACE` columns. The fully qualified pathname for the dictionary file must be specified (This file must have been created previously through the `DBMS_LOGMNR_D`.`BUILD` procedure).
Options	`DBMS_LOGMNR`.`USE_COLMAP`: Use the column map specified in `logmnr`.`opt` file. This file must be in the same directory as the dictionary file specified by `DictFileName`. See "Constants for START_LOGMNR Options flag" and "Using the logmnr.opt Place Holder Column".

After executing the START_LOGMNR() procedure, you can obtain current information about the dictionary file by querying the V$LOGMNR_DICTIONARY view. For information about the LogMiner session, query the V$LOGMNR_PARAMETERS view.

Exceptions

The procedure fails with ORA-1280 if LogMiner encounters an internal error:
ORA-1281: endScn is less than startScn
ORA-1282: endDate is earlier than startDate
ORA-1283: Invalid options is specified
The procedure fails with an ORA-1293 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.
ORA-1294: Dictionary file specified is corrupt.
ORA-1295: Dictionary specified does not correspond to the same database that produced the logfiles being analyzed.
ORA-1301: DBMS_LOGMNR.USE_COLMAP was used without a dictionary file.
ORA-1302: logmnr.opt file has syntax error.
ORA-1303: schema specified in logmnr.opt file does not exist.
ORA-1304: table specified in logmnr.opt file does not exist.
ORA-1305: column specified in logmnr.opt file does not exist.

END_LOGMNR Procedure

This procedure finishes a session.

Syntax

DBMS_LOGMNR.END_LOGMNR;

Parameters

None.

Exception

ORA-1307: No logminer session is active. The END_LOGMNR procedure was called without adding any logfiles.

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
EXECUTE DBMS_LOGMNR.END_LOGMNR();