In a CDB, the ADD_LOGFILE procedure must be called from the root database. You must have the LOGMINING administrative privilege to use this procedure.

Syntax

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

Parameters

Exceptions

Usage Notes

• The DBMS_LOGMNR.ADD_LOGFILE call from a PDB connection is not supported for adhoc users and returns error 65040.

• Dumping Flat File dictionary and mining using Flat File dictionary are not supported from a PDB connection.

• Before querying the V$LOGMNR_CONTENTS view, you must make a successful call to the DBMS_LOGMNR.START_LOGMNR procedure (within the current LogMiner session).

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

• If 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.

• Both archived and online redo log files can be mined.

• After you have added the first redo log file to the list, each additional redo log file that you add to the list must be associated with the same database and database 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.)

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

• LogMiner matches redo log files by the log sequence number. Thus, two redo log files with different names but with the same log sequence number will return the ORA-01289 exception. For instance, the online counterpart of an archived redo log file has a different name from the archived redo log file, but attempting to register it with LogMiner after registering the archived counterpart will result in the ORA-01289 exception being returned.

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

Return Values

Table 100-7 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.

Exceptions

Usage Notes

• To use the COLUMN_PRESENT function, you must have successfully started LogMiner.

• The COLUMN_PRESENT function must be invoked in the context of a select operation on the V$LOGMNR_CONTENTS view.

• The COLUMN_PRESENT function does not support LONG, LOB, ADT, or COLLECTION datatypes.

• The value for the sql_redo_undo parameter depends on the operation performed and the data of interest:

  • If an update operation was performed and you want to know what the value was prior to the update operation, specify UNDO_VALUE.

  • If an update operation was performed and you want to know what the value is after the update operation, specify REDO_VALUE.

  • If an insert operation was performed, typically you would specify REDO_VALUE (because the value of a column prior to an insert operation will always be NULL).

  • If a delete operation was performed, typically you would specify UNDO_VALUE (because the value of a column after a delete operation will always be NULL).

Syntax

DBMS_LOGMNR.END_LOGMNR;

Exceptions

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

Return Values

Exceptions

Usage Notes

• To use the MINE_VALUE function, you must have successfully started LogMiner.

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

• The value for the sql_redo_undo parameter depends on the operation performed and the data of interest:

  • If an update operation was performed and you want to know what the value was prior to the update operation, specify UNDO_VALUE.

  • If an update operation was performed and you want to know what the value is after the update operation, specify REDO_VALUE.

  • If an insert operation was performed, typically you would specify REDO_VALUE (because the value of a column prior to an insert operation will always be null).

  • If a delete operation was performed, typically you would specify UNDO_VALUE (because the value of a column after a delete operation will always be null).

• If the DBMS_LOGMNR.MINE_VALUE function is used to get an NCHAR value that includes characters not found in the database character set, then those characters are returned as the replacement character (for example, an inverted question mark) of the database character set.

In a CDB, the REMOVE_LOGFILE procedure must be called from the root database. You must have the LOGMINING administrative privilege to use this procedure.

Syntax

DBMS_LOGMNR.REMOVE_LOGFILE ( 
   LogFileName     IN VARCHAR2);

Parameters

Exceptions

Usage Notes

• Before querying the V$LOGMNR_CONTENTS view, you must make a successful call to the DBMS_LOGMNR.START_LOGMNR procedure (within the current LogMiner session).

• You can use this procedure to remove a redo log file from the list of redo log files for LogMiner to process if you know that redo log file does not contain any data of interest.

• Multiple redo log files can be removed by calling this procedure repeatedly.

• The redo log files do not need to be removed in any particular order.

• To start a new list of redo log files for analysis, use the END_LOGMNR procedure to end the current LogMiner session, and then build a new list using the ADD_LOGFILE procedure.

• Even if you remove all redo log files from the list, any subsequent calls you make to the 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.

In a CDB, the START_LOGMNR procedure must be called from the root database. You must have the LOGMINING administrative privilege to use this 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 '31-dec-2110',
   DictFileName       IN VARCHAR2 default '',
   Options            IN BINARY_INTEGER default 0 );

Parameters

Exceptions

Usage Notes

• The DBMS_LOGMNR.ADD_LOGFILE call from a PDB connection is not supported for adhoc users and returns error 65040.

• Users can specify the SCN range or the time range. The required logfiles will be added programmatically.

• Dumping Flat File dictionary and mining using Flat File dictionary are not supported from a PDB connection.

• LogMiner can use a dictionary that you previously extracted to the redo log files or to a flat file, or you can specify that LogMiner use the online catalog if LogMiner is mining data from the source system.

• After executing the START_LOGMNR procedure, you can query the following views:

  • V$LOGMNR_CONTENTS - contains history of information in redo log files

  • V$LOGMNR_DICTIONARY - contains current information about the LogMiner dictionary file extracted to a flat file

  • V$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.)

• Parameters and options are not persistent across calls to 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

• Be aware that specifying redo log files using a timestamp is not precise.

• Keep the following in mind regarding starting and ending times or SCN ranges:

  • If you specify neither a 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.

  • If you specify both time and SCN values, LogMiner uses the SCN value or values and ignores the time values.

  • If you specify starting and ending time or SCN values and they are found in the LogMiner redo log file list, then LogMiner mines the logs indicated by those values.

  • If you specify starting and ending times or SCN values that are not in the LogMiner redo log file list, and you specify DBMS_LOGMNR.START_LOGMNR, 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

    • A nonzero number for the startTime or startScn value, then an error is returned

    • 0 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

  • If you specify starting and ending times or SCN values and they are not found in the LogMiner redo log file list, and you specify DBMS_LOGMNR.START_LOGMNR, and you specify:

    • 0 for the startTime or startScn value, then an error is returned.

    • A 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.

    • An 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.