On Premises and Oracle Autonomous Cloud Platform Services LogMiner Sessions are similar, but require different users.

In a traditional LogMiner session, and when you run LogMiner on CDB$ROOT, you run LogMiner by using a PL/SQL package that is owned by SYS. To use LogMiner, there are requirements for the user account that you use with LogMiner.

When you run LogMiner in an On-Premise Oracle Database, you can create one CDB$ROOT capture extract to capture data from multiple PDBs at the same time, or mine multiple individual PDB logs using Oracle GoldenGate, each capturing data from just one PDB. However for Oracle Autonomous Database Cloud Platform Services, where you do not have access to CDB$ROOT, you must use the per-PDB capture method. In this mode, you provision a local user with a predefined set of privileges to the source PDB whose logs you want to review. All LogMiner processing is restricted to this PDB only.

With On-Premise PDBs, you can start as many sessions as resources allow. But for Cloud configurations, while you can still start many concurrent sessions in CDB$ROOT, you can start only one session for each PDB using the LogMiner PL/SQL package.

To run LogMiner on CDB$ROOT, you use the PL/SQL package DBMS_LOGMNR.ADD_LOGFILE and add log files explicitly. Additionally, if you choose to extract a LogMiner dictionary rather than use the online catalog, then you can also use the DBMS_LOGMNR_D package.

To run LogMiner on individual PDBs, the procedures are slightly different. instead of using DBMS_LOGMNR.ADD_LOGFILE. you specify a period in which you want to review log files for the PDB. Specify the SCN value of the log that you want to query, with either startScn and, if you choose, endScn, or startTime, and if you choose, endTime. You then start LogMiner with DBMS_LOGMNR.START_LOGMNR. DBMS_LOGMNR.START_LOGMNR automatically adds the redo logs for you to analyze.

The DBMS_LOGMNR package contains the procedures used to initialize and run LogMiner, including interfaces to specify names of redo log files, filter criteria, and session characteristics. The DBMS_LOGMNR_D package queries the database dictionary tables of the current database to create a LogMiner dictionary file.

SQL> execute dbms_logmnr_d.build(option=>dbms_logmnr_d.STORE_IN_REDO_LOGS);

To be able to use LogMiner with redo log files, you must enable supplemental logging.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

To use LogMiner, you must select an option to supply LogMiner with a database dictionary.

Choose one of the following options:

• Specify use of the online catalog by using the DICT_FROM_ONLINE_CATALOG option when you start LogMiner.

• Extract the database dictionary information to the redo log files.

• Extract database dictionary information to a flat file.

You must specify the redo log files that you want to analyze with DBMS_LOGMNR_ADD_LOGFILE before starting LogMiner.

1. Use SQL*Plus to start an Oracle Database instance, with the database either mounted or unmounted. For example, enter the STARTUP statement at the SQL prompt:

   STARTUP
   

2. Create a list of redo log files. Specify the NEW option of the DBMS_LOGMNR.ADD_LOGFILE PL/SQL procedure to signal that this is the beginning of a new list. For example, enter the following to specify the /oracle/logs/log1.f redo log file:

   EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
      LOGFILENAME => '/oracle/logs/log1.f', -
      OPTIONS => DBMS_LOGMNR.NEW);
   

3. If desired, add more redo log files by specifying the ADDFILE option of the DBMS_LOGMNR.ADD_LOGFILE PL/SQL procedure. For example, enter the following to add the /oracle/logs/log2.f redo log file:

   EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
      LOGFILENAME => '/oracle/logs/log2.f', -
      OPTIONS => DBMS_LOGMNR.ADDFILE);
   

   The OPTIONS parameter is optional when you are adding additional redo log files. For example, you can simply enter the following:

   EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
      LOGFILENAME=>'/oracle/logs/log2.f');
   

4. If desired, remove redo log files by using the DBMS_LOGMNR.REMOVE_LOGFILE PL/SQL procedure. For example, enter the following to remove the /oracle/logs/log2.f redo log file:

   EXECUTE DBMS_LOGMNR.REMOVE_LOGFILE( -
      LOGFILENAME => '/oracle/logs/log2.f');

See how to start LogMiner, and what options you can use to analyze redo log files, filter criteria, and other session characteristics.

1. To start LogMiner, execute the DBMS_LOGMNR.START_LOGMNR procedure.

   Oracle recommends that you specify a LogMiner dictionary option. If you do not specify a dictionary option, then LogMiner cannot translate internal object identifiers and data types to object names and external data formats. As a result, LogMiner returns internal object IDs and present data as binary data. Additionally, you cannot use the MINE_VALUE and COLUMN_PRESENT functions without a dictionary.

   If you are specifying the name of a flat file LogMiner dictionary, then you must supply a fully qualified file name for the dictionary file. For example, to start LogMiner using /oracle/database/dictionary.ora, issue the following statement:

   EXECUTE DBMS_LOGMNR.START_LOGMNR( -
      DICTFILENAME =>'/oracle/database/dictionary.ora');
   

   If you are not specifying a flat file dictionary name, then use the OPTIONS parameter to specify either the DICT_FROM_REDO_LOGS or DICT_FROM_ONLINE_CATALOG option.

   If you specify DICT_FROM_REDO_LOGS, then LogMiner expects to find a dictionary in the redo log files that you specified with the DBMS_LOGMNR.ADD_LOGFILE procedure. To determine which redo log files contain a dictionary, look at the V$ARCHIVED_LOG view. To see an example of this task, refer to "Extracting a LogMiner Dictionary to the Redo Log Files."

   Note:

   If you add additional redo log files after LogMiner has been started, then you must restart LogMiner. LogMiner does not retain options included in the previous call to DBMS_LOGMNR.START_LOGMNR; you must respecify the options that you want to use. However, if you do not specify a dictionary in the current call to DBMS_LOGMNR.START_LOGMNR, then LogMiner does retain the dictionary specification from the previous call.

2. Optionally, you can filter or format your query, or use the OPTIONS parameter to specify additional characteristics of your LogMiner session. For example, you might decide to use the online catalog as your LogMiner dictionary and to have only committed transactions shown in the V$LOGMNR_CONTENTS view, as follows:

   EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => -
      DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
      DBMS_LOGMNR.COMMITTED_DATA_ONLY);
   

   You can execute the DBMS_LOGMNR.START_LOGMNR procedure multiple times, specifying different options each time. For example, if you did not obtain the desired results from a query of V$LOGMNR_CONTENTS, you can restart LogMiner with different options. Unless you need to respecify the LogMiner dictionary, you do not need to add redo log files if they were already added with a previous call to DBMS_LOGMNR.START_LOGMNR.

Ending the LogMiner session.

EXECUTE DBMS_LOGMNR.END_LOGMNR;