98 DBMS_LOGMNR_D

The DBMS_LOGMNR_D package, one of a set of LogMiner packages, contains two subprograms: the BUILD procedure and the SET_TABLESPACE procedure.

  • The BUILD procedure extracts the LogMiner data dictionary to either the redo log files or to a flat file. This information is saved in preparation for future analysis of redo log files using the LogMiner tool.

  • The SET_TABLESPACE procedure re-creates all LogMiner tables in an alternate tablespace.

    The LogMiner data dictionary consists of the memory data structures and the database tables that are used to store and retrieve information about objects and their versions. It is referred to as the LogMiner dictionary throughout the LogMiner documentation.

This chapter contains the following topics:

See Also:

Oracle Database Utilities for information regarding LogMiner.

98.1 DBMS_LOGMNR_D Overview

LogMiner requires a dictionary to translate object IDs into object names when it returns redo data to you.

LogMiner gives you three options for supplying the dictionary:

  • Using the online catalog

  • Extracting a LogMiner dictionary to the redo log files

  • Extracting a LogMiner dictionary to a flat file

Use the BUILD procedure to extract the LogMiner dictionary to the redo log files or a flat file. If you want to specify the online catalog as the dictionary source, you do so when you start LogMiner with the DBMS_LOGMNR.START_LOGMNR package.

Use the SET_TABLESPACE procedure if you want LogMiner tables to use a tablespace other than the default SYSAUX tablespace.

See Also:

DBMS_LOGMNR for information on the package subprograms used in running a LogMiner session.

98.2 DBMS_LOGMNR_D Security Model

You must have the EXECUTE_CATALOG_ROLE role to use the DBMS_LOGMNR_D package.

98.3 Summary of DBMS_LOGMNR_D Subprograms

This table lists and briefly describes the DBMS_LOGMNR_D subprograms.

In a multitenant container database (CDB), some subprograms must be called from the root. There may be other differences as well. See the individual subprogram descriptions for details.

Table 98-1 DBMS_LOGMNR_D Package Subprograms

Subprogram Description

BUILD Procedure

Extracts the LogMiner dictionary to either a flat file or one or more redo log files

SET_TABLESPACE Procedure

Re-creates all LogMiner tables in an alternate tablespace

98.3.1 BUILD Procedure

This procedure extracts the LogMiner data dictionary to either the redo log files or to a flat file.

The following considerations apply to a multitenant container database (CDB) environment.

  • In a CDB environment, when you extract to the redo log files, the BUILD procedure must be called from the root database. The LogMiner data dictionary for the entire CDB is extracted to the redo log files.

  • In a CDB environment, when you extract to flat files, you can mine only one pluggable database (PDB) at a time. You must first explicitly build the flat file dictionary for that specific PDB by running the BUILD procedure from it. The LogMiner data dictionary for each PDB for which the BUILD procedure is run is extracted to a flat file.

  • You cannot add or remove PDBs from a CDB while this procedure is executing.

Syntax

DBMS_LOGMNR_D.BUILD (
     dictionary_filename  IN  VARCHAR2,
     dictionary_location  IN  VARCHAR2,
     options              IN  NUMBER);

Parameters

Table 98-2 BUILD Procedure Parameters

Parameter Description

dictionary_filename

Specifies the name of the LogMiner dictionary file.

dictionary_location

Specifies the directory object for the LogMiner dictionary file.

options

Specifies that the LogMiner dictionary is written to either a flat file (STORE_IN_FLAT_FILE) or the redo log files (STORE_IN_REDO_LOGS).

Exceptions

Table 98-3 BUILD Procedure Exceptions

Exception Description

ORA-01302

Dictionary build options are missing or incorrect.

This error is returned under the following conditions:

  • If the value of the OPTIONS parameter is not one of the supported values (STORE_IN_REDO_LOGS, STORE_IN_FLAT_FILE) or is not specified

  • If the STORE_IN_REDO_LOGS option is not specified and neither the dictionary_filename nor the dictionary_location parameter is specified

  • If the STORE_IN_REDO_LOGS option is specified and either the dictionary_filename or the dictionary_location parameter is specified

ORA-01308

Initialization parameter UTL_FILE_DIR is not set.

Note:

In prior releases, you used the UTL_FILE_DIR initialization parameter to specify a directory location. However, as of Oracle Database 18c, the UTL_FILE_DIR initialization parameter is desupported. It is still supported for backward compatibility, but Oracle recommends that you instead use directory objects.

ORA-01336

Specified dictionary file cannot be opened.

This error is returned under the following conditions:

  • The dictionary file is read-only.

ORA-01308

Dictionary directory is not set.

This error is returned under the following conditions:
  • The specified value for the dictionary_location is not a directory object.
  • The specified value for the dictionary_location is a directory object that is defined to be a file path that cannot be accessed

    .

Usage Notes

  • To extract the LogMiner dictionary to a flat file, you must supply a filename and location. The location must be specified as a database directory object. (See CREATE DIRECTORY in the Oracle Database SQL Language Reference.)

    To extract the LogMiner dictionary to the redo log files, specify only the STORE_IN_REDO_LOGS option. The size of the LogMiner dictionary may cause it to be contained in multiple redo log files.

    The combinations of parameters used result in the following behavior:

    • If you do not specify any parameters, an error is returned.

    • If you specify a filename and location, without any options, the LogMiner dictionary is extracted to a flat file with that name.

    • If you specify a filename and location, as well as the STORE_IN_FLAT_FILE option, the LogMiner dictionary is extracted to a flat file with the specified name.

    • If you do not specify a filename and location, but do specify the STORE_IN_REDO_LOGS option, the LogMiner dictionary is extracted to the redo log files.

    • If you specify a filename and location, as well as the STORE_IN_REDO_LOGS option, an error is returned.

    • If you do not specify a filename and location, but do specify the STORE_IN_FLAT_FILE option, an error is returned.

  • Ideally, the LogMiner dictionary file will be created after all database dictionary changes have been made and prior to the creation of any redo log files that are to be analyzed. As of Oracle9i release 1 (9.0.1), you can use LogMiner to dump the LogMiner dictionary to the redo log files or a flat file, perform DDL operations, and dynamically apply the DDL changes to the LogMiner dictionary.

  • The database must be open when you run the DBMS_LOGMNR_D.BUILD procedure.

  • When extracting a LogMiner dictionary to a flat file, the procedure queries the dictionary tables of the current database and creates a text-based file containing the contents of the tables. To extract a LogMiner dictionary to a flat file, the following conditions must be met:

    • You must specify a directory for use by the PL/SQL procedure. To do so, first create an Oracle directory object with a pathname that specifies where the dictionary file is to be generated. For example:

      create directory my_dictionary_dir as '/oracle/dictionary';

    Be aware that the DDL_DICT_TRACKING option to the DBMS_LOGMNR.START_LOGMNR procedure is not supported for flat file dictionaries created prior to Oracle9i. If you attempt to use the DDL_DICT_TRACKING option with a LogMiner database extracted to a flat file prior to Oracle9i, the ORA-01330 error (problem loading a required build table) is returned.

  • To extract a LogMiner dictionary file to the redo log files, the following conditions must be met:

    • The DBMS_LOGMNR_D.BUILD procedure must be run on a system that is running Oracle9i or later.

    • Archivelog mode must be enabled in order to generate usable redo log files.

    • The COMPATIBLE parameter in the initialization parameter file must be set to 9.2.0 or higher.

    • The database to which LogMiner is attached must be Oracle9i or later.

    In addition, supplemental logging (at least the minimum level) should be enabled to ensure that you can take advantage of all the features that LogMiner offers.

Examples

Example 1: Extracting the LogMiner Dictionary to a Flat File

The following example extracts the LogMiner dictionary file to a flat file named dictionary.ora in a specified path (/oracle/database).

SQL> create directory my_dictionary_dir as '/oracle/database';
SQL> EXECUTE dbms_logmnr_d.build(dictionary_location=>my_dictionary_dir, - 
                                 dictionary_filename=>'dictionary.ora', -
                                options => dbms_logmnr_d.store_in_flat_file);

Example 2: Extracting the LogMiner Dictionary to the Redo Log Files

The following example extracts the LogMiner dictionary to the redo log files.

SQL> EXECUTE dbms_logmnr_d.build( -
     options => dbms_logmnr_d.store_in_redo_logs);

98.3.2 SET_TABLESPACE Procedure

This procedure moves LogMiner tables from the default SYSAUX tablespace to an alternate tablespace.

By default, all LogMiner tables are created to use the SYSAUX tablespace. However, it may be desirable to have LogMiner tables use an alternate tablespace. Use this procedure to move LogMiner tables to this alternate tablespace

In a CDB, only the LogMiner metadata in the local container is moved to the requested tablespace.

Syntax

DBMS_LOGMNR_D.SET_TABLESPACE (
     new_tablespace        IN VARCHAR2);

Parameters

Table 98-4 SET_TABLESPACE Parameter

Parameter Description

new_tablespace

A string naming a preexisting tablespace. To move all LogMiner tables to employ this tablespace, supply this parameter.

Usage Notes

  • Users upgrading from earlier versions of Oracle Database may find LogMiner tables in the SYSTEM tablespace. Oracle encourages such users to consider using the SET_TABLESPACE procedure to move the tables to the SYSAUX tablespace once they are confident that they will not be downgrading to an earlier version of Oracle Database.

  • Users of this routine must supply an existing tablespace.

Example: Using the DBMS_LOGMNR_D.SET_TABLESPACE Procedure

The following example shows the creation of an alternate tablespace and execution of the DBMS_LOGMNR_D.SET_TABLESPACE procedure.

SQL> CREATE TABLESPACE  logmnrts$ datafile '/usr/oracle/dbs/logmnrts.f'
     SIZE 25 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

SQL> EXECUTE dbms_logmnr_d.set_tablespace('logmnrts$');