DBMS_LOGMNR , 3 of 3

Summary of DBMS_LOGMNR Subprograms

Table 25-3 describes the procedures in the DBMS_LOGMNR supplied package.

Table 25-3 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 LogMiner utility.
END_LOGMNR Procedure	Finishes a LogMiner session.
MINE_VALUE Function	This function may be called for any row returned from `V$LOGMNR_CONTENTS` to retrieve the undo or redo column value of the column specified by the `column_name` input parameter to this function.
COLUMN_PRESENT Function	This function may be called for any row returned from `V$LOGMNR_CONTENTS` to determine if undo or redo column values exist for the column specified by the `column_name` input parameter to this function.

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 information about the redo logs to be analyzed. Use the ADD_LOGFILE procedure to specify the list of redo logs to analyze.

Note:

If you want to analyze more than one redo log, you must call the ADD_LOGFILE procedure separately for each redo log.

Syntax

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

Parameters

Table 25-4 describes the parameters for the ADD_LOGFILE procedure.

Table 25-4 ADD_LOGFILE Procedure Parameters

Parameter Description

Parameter	Description
`LogFileName`	Name of the redo log that must be added to the list of redo logs 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 redo log (`DBMS_LOGMNR.REMOVEFILE`) See Table 25-1, " Constants for ADD_LOGFILE Options Flag".

LogFileName

Name of the redo log that must be added to the list of redo logs 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 redo log (DBMS_LOGMNR.REMOVEFILE)

See Table 25-1, " Constants for ADD_LOGFILE Options Flag".

Exceptions

ORA-1284: Redo log file specified cannot be opened. Log file or the directory may be non-existent or inaccessible.
ORA-1285: Error reading the header of the redo log file.
ORA-1286: Redo log file specified is not from the database that produced other logfiles added for analysis.
ORA-1287: Redo log file specified is from a different database incarnation.
ORA-1289: Redo log file specified is a duplicate of a previously specified log file.
ORA-1290: Redo log file specified for removal is not a registered log file.
ORA-1337: Redo log file specified has a different compatibility version than the rest of the logfiles added.

START_LOGMNR Procedure

This procedure starts a LogMiner session.

Note:

This procedure fails if you did not previously use the ADD_LOGFILE procedure to specify a list of redo logs to be analyzed.

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 25-5 describes the parameters for the DBMS_LOGMNR.START_LOGMNR procedure.

Table 25-5 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 redo log with an `SCN` range (that is, the `LOW_SCN` and `NEXT_SCN` associated with the redo log 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 redo log with an `SCN` range (that is, the `LOW_SCN` and `NEXT_SCN` associated with the redo log 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 redo log with a time range (that is, the `LOW_TIME` and `HIGH_TIME` associated with the redo log 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 redo log with a time range (that is, the `LOW_TIME` and `HIGH_TIME` associated with the redo log 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. It is used to reconstruct `SQL_REDO` and `SQL_UNDO` columns in `V$LOGMNR_CONTENTS`, as well as to fully translate `SEG_NAME`, `SEG_OWNER`, `SEG_TYPE_NAME,` and `TABLE_SPACE` columns. The fully qualified path name for the dictionary file must be specified (This file must have been created previously through the `DBMS_LOGMNR_D`.`BUILD` procedure). You only need to specify this parameter if neither `DICT_FROM_REDO_LOGS` nor `DICT_FROM_ONLINE_CATALOG` is specified.
`Options`	See Table 25-2, " Constants for START_LOGMNR Options Flag".

After executing the START_LOGMNR procedure, you can make use of the following views:

V$LOGMNR_CONTENTS - contains history of information in redo logs
V$LOGMNR_DICTIONARY - contains current information about the dictionary file
V$LOGMNR_LOGS - contains information about the redo logs being analyzed
V$LOGMNR_PARAMETERS - contains information about the LogMiner session

Exceptions

ORA-1280: The procedure fails with this exception if LogMiner encounters an internal error
ORA-1281: endScn is less than startScn
ORA-1282: endDate is earlier than startDate
ORA-1283: Invalid option is specified
ORA-1292: No redo log file has been registered with LogMiner
ORA-1293: The procedure fails with this exception 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 log files being analyzed.
ORA-1296: Character set specified in the data dictionary does not match, and is incompatible with, that of the mining database.
ORA-1297: Redo version mismatch between the dictionary and the registered redo log files.
ORA-1299: The specified dictionary is from a different database incarnation.
ORA-1300: Enabled thread bit vector from the dictionary does not match the redo log file. Not all redo threads have been registered with LogMiner.

END_LOGMNR Procedure

This procedure finishes a LogMiner session. Because this procedure performs cleanup operations which may not otherwise be done, you must use it to properly end a LogMiner session.

Syntax

DBMS_LOGMNR.END_LOGMNR;

Parameters

None.

Exceptions

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

MINE_VALUE Function

The MINE_VALUE function takes two arguments. The first one specifies whether to mine the redo (REDO_VALUE) or undo (UNDO_VALUE) portion of the data. The second argument is a string that specifies the fully-qualified name of the column to be mined. The MINE_VALUE function always returns a string that can be converted back to the original datatype.

Syntax

dbms_logmnr.mine_value(
     sql_redo_undo      IN  RAW,
     column_name        IN  VARCHAR2 default '') RETURN VARCHAR2;

Parameters

Table 25-6 describes the parameters for the MINE_VALUE function.

Table 25-6 MINE_VALUE Function Parameters

Parameter	Description
`sql_redo_undo`	The column in `V$LOGMNR_CONTENTS` from which to extract data values. This parameter can be thought of as a self-describing record that contains values corresponding to several columns in a table.
`column_name`	Fully qualified name (`schema.table.column`) of the column for which this function will return information.

Returns

Table 25-7 describes the return values for the MINE_VALUE function.

Table 25-7 Return Values for MINE_VALUE Function

Return	Description
`NULL`	The column is not contained within the self-describing record or the column value is `NULL`.
`NON-NULL`	The column is contained within the self-describing record; the value is returned in string format.

Exceptions

ORA-1302: Specified table or column does not exist.

Usage Notes

To use the MINE_VALUE function, you must have successfully started a LogMiner session.
The MINE_VALUE function must be invoked in the context of a select operation from the V$LOGMNR_CONTENTS view.
The MINE_VALUE function does not support LONG, LOB, ADT, or COLLECTION datatypes.
When the column argument is of type DATE, the string that is returned is formatted in canonical form (DD-MON-YYYY HH24:MI:SS.SS) regardless of the date format of the current session.

COLUMN_PRESENT Function

This function is meant to be used in conjunction with the MINE_VALUE function.

If the MINE_VALUE function returns a NULL value, it can mean either:

The specified column is not present in the redo or undo portion of the data.
The specified column is present and has a null value.

To distinguish between these two cases, use the COLUMN_PRESENT function which returns a 1 if the column is present in the redo or undo portion of the data. Otherwise, it returns a 0.

Syntax

dbms_logmnr.column_present(
     sql_redo_undo      IN  RAW,
     column_name        IN  VARCHAR2 default '') RETURN NUMBER;

Parameters

Table 25-8 describes the parameters for the COLUMN_PRESENT function.

Table 25-8 COLUMN_PRESENT Function Parameters

Parameter	Description
`sql_redo_undo`	The column in `V$LOGMNR_CONTENTS` from which to extract data values. This parameter can be thought of as a self-describing record that contains values corresponding to several columns in a table.
`column_name`	Fully qualified name (`schema.table.column`) of the column for which this function will return information.

Returns

Table 25-9 describes the return values for the COLUMN_PRESENT function.

Table 25-9 Return Values for COLUMN_PRESENT Function

Return Description

Return	Description
0	Specified column is not present in this row of `V$LOGMNR_CONTENTS`.
1	Column is present in this row of `V$LOGMNR_CONTENTS`. Returns 1 if the self-describing record (the first parameter) contains the column specified in the second parameter. This can be used to distinguish between `NULL` returns from the `DBMS_LOGMNR.MINE_VALUE` function.

Specified column is not present in this row of V$LOGMNR_CONTENTS.

Column is present in this row of V$LOGMNR_CONTENTS.

Returns 1 if the self-describing record (the first parameter) contains the column specified in the second parameter. This can be used to distinguish between NULL returns from the DBMS_LOGMNR.MINE_VALUE function.

Exceptions

ORA-1302: Specified table or column does not exist.

Usage Notes

To use the COLUMN_PRESENT function, you must have successfully started a LogMiner session.
The COLUMN_PRESENT function must be invoked in the context of a select operation from the V$LOGMNR_CONTENTS view.
The COLUMN_PRESENT function does not support LONG, LOB, ADT, or COLLECTION datatypes.
When the column argument is of type DATE, the string that is returned is formatted in canonical form (DD-MON-YYYY HH24:MI:SS.SS) regardless of the date format of the current session.