Skip Headers

PL/SQL Packages and Types Reference
10g Release 1 (10.1)

Part Number B10802-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

47
DBMS_LOGMNR

The DBMS_LOGMNR package contains procedures used to initialize the LogMiner tool and to begin and end a LogMiner session.

See Also:

Oracle Database Utilities for information about using LogMiner and DBMS_LOGMNR_D for information on the package subprograms that extract a LogMiner dictionary and re-create LogMiner tables in alternate tablespaces

This chapter contains the following topics:


Using DBMS_LOGMNR


Security Model

You must have the role, EXECUTE_CATALOG_ROLE to use the DBMS_LOGMNR package.


Constants

Table 47-1 describes the constants for the ADD_LOGFILE options flag in the DBMS_LOGMNR package.

Table 47-1  Constants for ADD_LOGFILE Options Flag
Constant Description

NEW

Implicitly calls the DBMS_LOGMNR.END_LOGMNR procedure to end the current LogMiner session and then creates a new session. The new session starts a new list of redo log files to be analyzed, beginning with the redo log file you specify.

ADDFILE

Adds the specified redo log file to the list of redo log files to be analyzed. Any attempt to add a duplicate file raises an exception (ORA-01289). This is the default if no options flag is specified.

Table 47-2 describes the constants for the START_LOGMNR options flag in the DBMS_LOGMNR package.

Table 47-2  Constants for START_LOGMNR Options Flag
Constant Description

COMMITTED_DATA_ONLY

If set, DML statements corresponding to committed transactions are returned. DML statements corresponding to a committed transaction are grouped together. Transactions are returned in their commit order. Transactions that are rolled back or in-progress are filtered out, as are internal redo records (those related to index operations, management, and so on).

If this option is not set, all rows for all transactions (committed, rolled back, and in-progress) are returned in the order in which they are found in the redo logs (in order of SCN values).

SKIP_CORRUPTION

Directs a select operation on the V$LOGMNR_CONTENTS view to skip any corruptions in the redo log file being analyzed and continue processing. This option works only when a block in the redo log file (and not the header of the redo log file) is corrupt. You should check the INFO column in the V$LOGMNR_CONTENTS view to determine the corrupt blocks skipped by LogMiner. When a corruption in the redo log file is skipped, the OPERATION column contains the value CORRUPTED_BLOCKS, and the STATUS column contains the value 1343.

DDL_DICT_TRACKING

If the LogMiner dictionary in use is a flat file or in the redo log files, LogMiner updates its internal dictionary if a DDL event occurs. This ensures that correct SQL_REDO and SQL_UNDO information is maintained for objects that are modified after the LogMiner internal dictionary is built. The database to which LogMiner is connected must be open.

This option cannot be used in conjunction with the DICT_FROM_ONLINE_CATALOG option and cannot be used when the LogMiner dictionary being used is one that was extracted to a flat file prior to Oracle9i.

DICT_FROM_ONLINE_CATALOG

Directs LogMiner to use the current online database dictionary rather than a LogMiner dictionary contained in a flat file or in the redo log files being analyzed.

This option cannot be used in conjunction with the DDL_DICT_TRACKING option. The database to which LogMiner is connected must be the same one that generated the redo log files.

Expect to see a value of 2 in the STATUS column of the V$LOGMNR_CONTENTS view if the table definition in the database does not match the table definition in the redo log file.

DICT_FROM_REDO_LOGS

If set, LogMiner expects to find a LogMiner dictionary in the redo log files that were specified. The redo log files are specified with the DBMS_LOGMNR.ADD_LOGFILE procedure or with the DBMS_LOGMNR.START_LOGMNR procedure with the CONTINUOUS_MINE option.

NO_SQL_DELIMITER

If set, the SQL delimiter (a semicolon) is not placed at the end of reconstructed SQL statements. This is helpful for applications that open a cursor and then execute the reconstructed statements.

NO_ROWID_IN_STMT

If set, the ROWID clause is not included in the reconstructed SQL statements. The redo log file may already contain logically unique identifiers for modified rows if supplemental logging is enabled.

When using this option, you must be sure that supplemental logging was enabled in the source database at the appropriate level and that no duplicate rows exist in the tables of interest. LogMiner does not make any guarantee regarding the uniqueness of logical row identifiers.

PRINT_PRETTY_SQL

If set, LogMiner formats the reconstructed SQL statements for ease of reading. These reconstructed SQL statements are not executable.

CONTINUOUS_MINE

Directs LogMiner to automatically add redo log files, as needed, to find the data of interest. You only need to specify the first log to start mining, or just the starting SCN or date to indicate to LogMiner where to begin mining logs. You are not required to specify any redo log files explicitly. LogMiner automatically adds and mines the (archived and online) redo log files for the data of interest. This option requires that LogMiner is connected to the same database instance that is generating the redo log files.


Operational Notes

A LogMiner session begins with a call to DBMS_LOGMNR.ADD_LOGFILE or DBMS_LOGMNR.START_LOGMNR (the former if you plan to specify log files explicitly; the latter if you plan to use continuous mining). The session ends with a call to DBMS_LOGMNR.END_LOGMNR. Within a LogMiner session, you can specify the redo log files to be analyzed and the SCN or time range of interest; then you can issue SQL SELECT statements against the V$LOGMNR_CONTENTS view to retrieve the data of interest.


Summary of DBMS_LOGMNR Subprograms

Table 47-3  DBMS_LOGMNR Package Subprograms
Subprogram Description

ADD_LOGFILE Procedure

Adds a redo log file to the existing or newly created list of redo log files for LogMiner to process, so that if a new list is created, this marks the beginning of a LogMiner session

COLUMN_PRESENT Function

You can call this function for any row returned from the V$LOGMNR_CONTENTS view to determine if undo or redo column values exist for the column specified by the column_name input parameter to this function

END_LOGMNR Procedure

Finishes a LogMiner session

MINE_VALUE Function

You can call this function for any row returned from the V$LOGMNR_CONTENTS view to retrieve the undo or redo column value of the column specified by the column_name input parameter to this function

REMOVE_LOGFILE Procedure

Removes a redo log file from the list of redo log files for LogMiner to process

START_LOGMNR Procedure

Initializes the LogMiner utility and starts LogMiner (unless the session was already started with a call to DBMS_LOGMNR.ADD_LOGFILE)


ADD_LOGFILE Procedure

This procedure adds a file to an existing or newly created list of log files for LogMiner to process.

Syntax

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

Parameters

Table 47-4  ADD_LOGFILE Procedure Parameters
Parameter Description

LogFileName

Specifies the name of the redo log file to add to the list of redo log files to be analyzed during this session.

options

Does one of the following:

  • Starts a new LogMiner session and a new list of redo log files for analysis (DBMS_LOGMNR.NEW)
  • Adds a file to an existing list of redo log files for analysis (DBMS_LOGMNR.ADDFILE)

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

Usage Notes

Exceptions


COLUMN_PRESENT Function

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

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

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 47-5  COLUMN_PRESENT Function Parameters
Parameter Description

sql_redo_undo

Specifies either the REDO_VALUE or the UNDO_VALUE column in the V$LOGMNR_CONTENTS view from which to extract data values. See the Usage Notes for more information.

column_name

Specifies the fully qualified name (schema.table.column) of the column for which this function will return information.

Return Values

Table 47-6 describes the return values for the COLUMN_PRESENT function. The COLUMN_PRESENT function returns 1 if the self-describing record (the first parameter) contains the column specified in the second parameter. This can be used to determine the meaning of NULL values returned by the DBMS_LOGMNR.MINE_VALUE function.

Table 47-6  Return Values for COLUMN_PRESENT Function
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.

Usage Notes

Exceptions


END_LOGMNR Procedure

This procedure finishes a LogMiner session. Because this procedure performs cleanup operations that may not otherwise be done, you must use it to properly end a LogMiner session. This procedure is called automatically when you log out of a database session or when you call DBMS_LOGMNR.ADD_LOGFILE and specify the NEW option.

Syntax

DBMS_LOGMNR.END_LOGMNR;

Exceptions


MINE_VALUE Function

This function facilitates queries based on a column's data value. This 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 47-7  MINE_VALUE Function Parameters
Parameter Description

sql_redo_undo

Specifies either the REDO_VALUE or the UNDO_VALUE column in the V$LOGMNR_CONTENTS view from which to extract data values. See the Usage Notes for more information.

column_name

Specifies the fully qualified name (schema.table.column) of the column for which this function will return information.

Return Values

Table 47-8  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. To distinguish between the two different null possibilities, use the DBMS_LOGMNR.COLUMN_PRESENT function.

NON-NULL

The column is contained within the self-describing record; the value is returned in string format.

Usage Notes

Exceptions


REMOVE_LOGFILE Procedure

This procedure removes a redo log file from an existing list of redo log files for LogMiner to process.


Note:

This procedure replaces the REMOVEFILE constant that was an option on the ADD_LOGFILE procedure prior to Oracle Database 10g.


Syntax

DBMS_LOGMNR.REMOVE_LOGFILE( 
   LogFileName     IN VARCHAR2);

Parameters

Table 47-9  REMOVE_LOGFILE Procedure Parameters
Parameter Description

LogFileName

Specifies the name of the redo log file to be removed from the list of redo log files to be analyzed during this session.

Usage Notes

Exceptions


START_LOGMNR Procedure

This procedure starts LogMiner by loading the dictionary that LogMiner will use to translate internal schema object identifiers to names.

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 '31-dec-2110',
   DictFileName       IN VARCHAR2 default '',
   Options            IN BINARY_INTEGER default 0 );

Parameters

Table 47-10  START_LOGMNR Procedure Parameters
Parameter Description

startScn

Directs LogMiner to return only redo records with an SCN greater than or equal to the startScn specified. This fails if there is no redo log file containing the specified startScn value. (You can query the FILENAME, LOW_SCN, and NEXT_SCN columns in the V$LOGMNR_LOGS view for each redo log file to determine the range of SCN values contained in each redo log file.)

endScn

Directs LogMiner to return only redo records with an SCN less than or equal to the endScn specified. If you specify an endScn value that is beyond the value in any redo log file, then LogMiner will use the greatest endScn value in the redo log file that contains the most recent changes. (You can query the FILENAME, LOW_SCN, and NEXT_SCN columns in the V$LOGMNR_LOGS view for each redo log file to determine the range of SCN values contained in each redo log file.)

startTime

Directs LogMiner to return only redo records with a timestamp greater than or equal to the startTime specified. This fails if there is no redo log file containing the specified startTime value. (You can query the FILENAME, LOW_TIME, and HIGH_TIME columns in the V$LOGMNR_LOGS view for each redo log file to determine the range of time covered in each redo log file.)

This parameter is ignored if startScn is specified. See the Usage Notes for additional information.

endTime

Directs LogMiner to return only redo records with a timestamp less than or equal to the endTime specified. If you specify an endTime value that is beyond the value in any redo log file, then LogMiner will use the greatest endTime in the redo log file that contains the most recent changes. You can query the FILENAME, LOW_TIME, and HIGH_TIME columns in the V$LOGMNR_LOGS view for each redo log file to determine the range of time covered in each redo log file.)

This parameter is ignored if endScn is specified. See the Usage Notes for additional information.

DictFileName

Specifies the flat file that contains the LogMiner dictionary. 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, TABLE_NAME, and TABLE_SPACE columns. The fully qualified path name for the LogMiner dictionary file must be specified. (This file must have been created previously through the DBMS_LOGMNR_D.BUILD procedure.)

You need to specify this parameter only if neither DICT_FROM_REDO_LOGS nor DICT_FROM_ONLINE_CATALOG is specified.

options

See Table 47-2, " Constants for START_LOGMNR Options Flag".

Usage Notes

Exceptions