PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B10802-01 |
|
|
View PDF |
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:
You must have the role, EXECUTE_CATALOG_ROLE
to use the DBMS_LOGMNR package.
Table 47-1 describes the constants for the ADD_LOGFILE
options flag in the DBMS_LOGMNR
package.
Table 47-2 describes the constants for the START_LOGMNR
options flag in the DBMS_LOGMNR
package.
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.
This procedure adds a file to an existing or newly created list of log files for LogMiner to process.
DBMS_LOGMNR.ADD_LOGFILE( LogFileName IN VARCHAR2, options IN BINARY_INTEGER default ADDFILE );
V$LOGMNR_CONTENTS
view, you must make a successful call to the DBMS_LOGMNR.START_LOGMNR
procedure (within the current LogMiner session).CONTINUOUS_MINE
option, the LogMiner session must be set up with a list of redo log files to be analyzed. Use the ADD_LOGFILE
procedure to specify the list of redo log files to analyze.CONTINUOUS_MINE
option and you want to analyze more than one redo log file, you must call the ADD_LOGFILE
procedure separately for each redo log file. The redo log files do not need to be registered in any particular order.RESETLOGS
SCN
as the first redo log file. (The database RESETLOGS
SCN
uniquely identifies each execution of an ALTER
DATABASE
OPEN
RESETLOGS
statement. When the online redo logs are reset, Oracle creates a new and unique incarnation of the database.)RESETLOGS SCN
) than that with which the current list of redo log files is associated, use the END_LOGMNR
procedure to end the current LogMiner session, and then build a new list using the ADD_LOGFILE
procedure.ORA-01284
: file <filename> cannot be opened.ORA-01287
: file <filename> is from a different database incarnation.ORA-01289
: cannot add duplicate log file <filename>.ORA-01290
: cannot remove unlisted log file <filename>.ORA-01324
: cannot add file <filename> due to DB_ID mismatch.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:
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
.
DBMS_LOGMNR.COLUMN_PRESENT( sql_redo_undo IN RAW, column_name IN VARCHAR2 default '') RETURN NUMBER;
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.
Return | Description |
---|---|
|
Specified column is not present in this row of |
|
Column is present in this row of |
COLUMN_PRESENT
function, you must have successfully started LogMiner.COLUMN_PRESENT
function must be invoked in the context of a select operation on the V$LOGMNR_CONTENTS
view.COLUMN_PRESENT
function does not support LONG
, LOB, ADT
, or COLLECTION
datatypes.sql_redo_undo
parameter depends on the operation performed and the data of interest:
UNDO_VALUE
.REDO_VALUE
.REDO_VALUE
(because the value of a column prior to an insert operation will always be null).UNDO_VALUE
(because the value of a column after a delete operation will always be null).Currently, a LogMiner dictionary is not associated with the LogMiner session. You must specify a LogMiner dictionary for the LogMiner session.
The value specified for the column_name
parameter was not a fully qualified column name.
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.
DBMS_LOGMNR.END_LOGMNR;
The END_LOGMNR
procedure was called without adding any log files or before the START_LOGMNR
procedure was called.
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.
DBMS_LOGMNR.MINE_VALUE( sql_redo_undo IN RAW, column_name IN VARCHAR2 default '') RETURN VARCHAR2;
MINE_VALUE
function, you must have successfully started LogMiner.MINE_VALUE
function must be invoked in the context of a select operation from the V$LOGMNR_CONTENTS
view.MINE_VALUE
function does not support LONG
, LOB, ADT
, or COLLECTION
datatypes.sql_redo_undo
parameter depends on the operation performed and the data of interest:
UNDO_VALUE
.REDO_VALUE
.REDO_VALUE
(because the value of a column prior to an insert operation will always be null).UNDO_VALUE
(because the value of a column after a delete operation will always be null).Currently, a LogMiner dictionary is not associated with the LogMiner session. You must specify a LogMiner dictionary for the LogMiner session.
The value specified for the column_name
parameter was not a fully qualified column name.
This procedure removes a redo log file from an existing list of redo log files for LogMiner to process.
Note: This procedure replaces the |
DBMS_LOGMNR.REMOVE_LOGFILE( LogFileName IN VARCHAR2);
Parameter | Description |
---|---|
|
Specifies the name of the redo log file to be removed from the list of redo log files to be analyzed during this session. |
V$LOGMNR_CONTENTS
view, you must make a successful call to to the DBMS_LOGMNR.START_LOGMNR
procedure (within the current LogMiner session).END_LOGMNR
procedure to end the current LogMiner session, and then build a new list using the ADD_LOGFILE
procedure.ADD_LOGFILE
procedure must match the database ID and RESETLOGS
SCN
of the removed redo log files. Therefore, to analyze the redo log files from a different database (or a database incarnation with a different database RESETLOGS
SCN
) than that with which the current list of redo log files is associated, use the END_LOGMNR
procedure to end the current LogMiner session, and then build a new list using the ADD_LOGFILE
procedure.This procedure starts LogMiner by loading the dictionary that LogMiner will use to translate internal schema object identifiers to names.
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 );
START_LOGMNR
procedure, you can query the following views:
V$LOGMNR_CONTENTS
- contains history of information in redo log filesV$LOGMNR_DICTIONARY
- contains current information about the LogMiner dictionary file extracted to a flat fileV$LOGMNR_PARAMETERS
- contains information about the LogMiner session(You can query the V$LOGMNR_LOGS
view after a redo log file list has been added to the list of files that LogMiner is to mine.)
DBMS_LOGMNR.START_LOGMNR
. You must specify all desired parameters and options (including SCN and time ranges) each time you call DBMS_LOGMNR.START_LOGMNR
CONTINUOUS_MINE
option directs LogMiner to automatically add redo log files, as needed, to find the data of interest. You need to specify only the first log to start mining, or just the starting SCN or date to indicate to LogMiner where to begin mining logs. Keep the following in mind when using the CONTINUOUS_MINE
option:
V$ARCHIVED_LOGS
view to determine which redo log file entries will be found by LogMiner.
Even if an entry is listed in the database control file (and the V$ARCHIVED_LOGS
view), the archived redo log file may not be accessible by LogMiner for various reasons. For example, the archived redo log file may have been deleted or moved from its location (maybe because of a backup operation to tape), or the directory where it resides may not be not available.
CONTINUOUS_MINE
option and an ending time or SCN that will occur in the future (or you do not specify an end time or SCN), a query of the V$LOGMNR_CONTENTS
view will not finish until the database has generated redo log files beyond the specified time or SCN. In this scenario, LogMiner will automatically add archived redo log files to the LogMiner redo log file list as they are generated. In addition, in this scenario only, LogMiner may automatically remove redo log files from the list to keep it at 50 processed redo files. This is to save PGA memory as LogMiner automatically adds redo log files to the list. If LogMiner did not perform automated removal, memory could eventually be exhausted.CONTINUOUS_MINE
option is not specified, it is possible that the database is writing to the online redo log file at the same time that LogMiner is reading the online redo log file. If a log switch occurs while LogMiner is reading an online redo log file, the database will overwrite what LogMiner is attempting to read. The data that LogMiner returns if the file it is trying to read gets overwritten by the database is unpredictable.startTime
nor a startScn
parameter, LogMiner will set the startScn
parameter to use the lowest SCN value from the redo log file that contains the oldest changes.DBMS_LOGMNR.START_LOGMNR
without the CONTINUOUS_MINE
option, and you specify:
0
for the startTime
or startScn
value, then the lowest SCN in the LogMiner redo log file list will be used as the startScn
startTime
or startScn
value, then an error is returned0
or a nonzero number for the endTime
or endScn
value, then the highest SCN in the LogMiner redo log file list will be used as the endScn
DBMS_LOGMNR.START_LOGMNR
with the CONTINUOUS_MINE
option, and you specify:
0
for the startTime
or startScn
value, then an error is returned.startTime
or startScn
value that is greater than any value in the database's archived redo log files, then LogMiner starts mining in the online redo log file. LogMiner will continue to process the online redo log file until it finds a change at, or beyond, the requested starting point before it returns rows from the V$LOGMNR_CONTENTS
view.endTime
or endScn
parameter value that indicates a time or SCN in the future, then LogMiner includes the online redo log files when it mines. When you query the V$LOGMNR_CONTENTS
view, rows will be returned from this view as changes are made to the database, and will not stop until LogMiner sees a change beyond the requested ending point.0
for the endTime
or endScn
parameter value, then LogMiner includes the online redo log files when it mines. When you query the V$LOGMNR_CONTENTS
view, rows will be returned from this view as changes are made to the database, and will not stop until you enter CTL+C or you terminate the PL/SQL cursor.ORA-01280
: fatal LogMiner error.
The procedure fails with this exception if LogMiner encounters an internal error.
ORA-01281
: SCN range specified is invalid.
The startScn
or endScn
parameter value is not a valid SCN, or endScn
is less than startScn
.
ORA-01282
: date range specified is invalid.
The value for the startTime
parameter was greater than the value specified for the endTime
parameter, or there was no redo log file that was compatible with the date range specified with the startTime
and endTime
parameters.
ORA-01283
: options parameter specified is invalid.ORA-01284
: file <filename> cannot be opened.
The LogMiner dictionary file specified in the DictFileName
parameter has a full path length greater than 256 characters, or the file cannot be opened.
ORA-01285
: error reading file <filename>.ORA-01291
: missing log file.
Redo log files that are needed to satisfy the user's requested SCN or time range are missing.
ORA-01292
: no log file has been specified for the current LogMiner session.ORA-01293
: mounted database required for specified LogMiner options.ORA-01294
: error occurred while processing information in dictionary file <filename>, possible corruption.ORA-01295
: DB_ID mismatch between dictionary <filename> and log files.
The specified LogMiner dictionary does not correspond to the database that produced the log files being analyzed.
ORA-01296
: character set mismatch between dictionary <filename> and log files.ORA-01297
: redo version mismatch between dictionary <filename> and log files.ORA-01299
: dictionary <filename> corresponds to a different database incarnation.ORA-01300
: writable database required for specified LogMiner options.