12.7 Oracle LogMiner

---

Oracle's LogMiner is a tool to basically mine the redo log files. Through this mining operation, several purposes could be accomplished. For example, if a user accidentally deletes rows from the database, instead of retrieving the rows from a backup system (if the archive log files or the redo log files are easily available) through this mining operation, the rows could be easily retrieved. LogMiner provides both the redo and undo functionality, that is, when the DML statements are retrieved by mining the redo logs files, the statements retrieved could be utilized to perform a redo operation or an undo operation. It can be used to provide a historical view of the database without the need for point-in-time recovery.

Like most of the Oracle interfaces, LogMiner can be implemented and used either through the command line interface or using the GUI interface provided as part of the OEM product.

Figure 12.5 illustrates the various LogMiner components and how they interact with the other background processes of Oracle. The LogMiner uses the various external sources such as the online catalog, dynamic views, PL/SQL packages, and dictionary flat file to complete the required mining operation against the online redo log files or the archive redo log files.

Figure 12.5: LogMiner components.

LogMiner components

The LogMiner process is composed of the following components:

• PL/SQL packages

• Online redo log files

• Archive redo log files

• Dynamic views

• Online catalog

• Data dictionary

PL/SQL packages  Since LogMiner comes bundled with the RDBMS, the required LogMiner PL/SQL packages are installed when the database is created. To enable LogMiner, the following two PL/SQL scripts need to execute as user sys:

• $ORACLE_HOME/rdbms/admin/dbmslmd.sql

• $ORACLE_HOME/rdbms/admin/dbmslm.sql

These scripts contain all the packages required for the various steps of the LogMiner process.

Online redo log files  Most of the information required with respect to the past activity on the database is contained in the redo log files. If the activity had happened before, and a couple of redo log files switches have occurred, the information would be contained in the archive redo log files.

Archive redo log files  If the information expected is not contained in the online redo log files, the next alternative is to look for the information in the archive redo log files. It could be that the activity had happened a while ago and the archive redo log files that contain this information have been transferred to a permanent backup medium and the files deleted from disk. In this case, to retrieve the specific data, the archive log files for the period of time should be restored before performing this activity.

Dynamic views  With the default database installation, the following dynamic views are also created. These views are used to access the information that LogMiner retrieves from the redo log files. The following dynamic views are populated when a LogMiner session is started:

V$LOGMNR_CONTENTS V$LOGMNR_DICTIONARY V$LOGMNR_LOGS V$LOGMNR_PARAMETERS

Online catalog  With this option, LogMiner uses the database's internal data dictionary as the LogMiner dictionary. To use the internal online dictionary, the database should be mounted and open. It is also required that the redo log files belong to the same database that the online dictionary belongs to.

Dictionary in a flat file  LogMiner could use a dictionary extracted to a text file. Since there is no direct access to the online dictionary, the database could be mounted or unmounted.

Using LogMiner

LogMiner can be used to:

• Audit past database activity, such as to determine the types and number of DML operations against a specific table and by a specific user.

• Track down corruption activity in the redo log files, find when this corruption occurred, and who caused this corruption.

• Pinpoint where to perform object-level recovery by SCN, either by using the SCN or by using date and time of transaction.

• Gather information about resource utilization and historical perfor mance.

• Diagnose and debug problems within an application.

Mining the log files

LogMiner functions can be performed either by using the command line option or by using the GUI interface provided with the OEM product.

Command line interface  The following are the steps to mine a redo log file.

1. LogMiner is started using the DBMS_LOGMNR.START_LOGMNR procedure. While executing this procedure, information such as the type of dictionary is specified. For example:

   1. When using the online catalog,

      EXECUTE DBMS_LOGMNR_START_LOGMNR (options=>DBMS_LOGMNR_D.DICT_FROM_ONLINE_CATALOG); 

   2. When using the dictionary flat file,

      EXECUTE DBMS_LOGMNR_START_LOGMNR (DICTFILENAME=> 'opt/Oracle/logmnr/ dictionary.ora');

2. Analyze the output using the Oracle-provided dynamic views. The V$LOGMNR_CONTENTS query is queried. This view provides the insight into the redo log files. The query executed against this view reads the redo log files and constructs one or more at a time in the V$LOGMNR_CONTENTS view. Every time the query is issued, LogMiner reads the entire redo log files again and returns the results to the view. This continues until all the data filtering criteria have been met.

For example, to mine the redo log files and retrieve data from the salary table where salary for a specific value or higher was inserted the following is performed.

SELECT SQL_REDO,  SQL_UNDO       SQL_REDO SQL_UNDO FROM V$LOGMNR_CONTENTS           DELETE FROM USER_PROFILE WHERE WHERE USERNAME = 'OLTP_USER_P'   USPRL_ID= 498458 AND ROWID = AND TABLENAME = 'USER_PROFILE';  'AAABgPAAFAAACtEAAE';   INSERT INTO USER_PROFILE         INSERT INTO USER_PROFILE (USPRL_ID, USER_NAME, EMAIL,     (USPRL_ID, USER_NAME, EMAIL, ADDRESS) VALUES (498458,         ADDRESS) VALUES (498458, 'MVALLATH', 'murali.vallath@     'MVALLATH', 'murali.vallath@ summersky.biz','53494,           summersky.biz', '53494, RAC drive, Big City,';           RAC drive, Big City,'; 2 rows selected                  DELETE FROM USER_PROFILE                                  WHERE USPRL_ID=498458 AND                                  ROWID= 'AAABgPAAFAAACtEAAE';

Graphical user interface  Oracle provides the LogMiner options to view the OEM product. The tool provides all functionalities that are available through the command line interface. However, OEM predefines most of the underlying scripts and provides immediate mining opportunities.

Accessing the redo log files from the OEM product requires that the user performing the mining activity has SYSDBA privileges.

After the tool has connected to the database (in a RAC environment, the tool is connected directly to the database and not to the instance), mining opportunities are provided to all redo log files that belong to all instances participating in the clustered configuration.

Figure 12.6 is a snapshot view of the create query interface screen provided for LogMiner from the OEM product. The figure illustrates how selecting from the drop-down list of criteria (for example, user, owner, table name, etc.) helps in construction of the query.

Figure 12.6: LogMiner OEM create query interface.

Once the user has created the required query condition and clicks on the ''execute'' button (if only the default selection criteria are required), the data from the current redo log files is filtered and displayed in a neat presentable format.

If additional conditions are to be specified, the tabs in the create query interface can be utilized to select the redo log files, and to specify the display criteria and LogMiner options such as formatting of output. For example, under this last tab option, OEM provides some additional options such as skipping redo log file corruptions, querying only committed transactions, etc. These options help filter good and clean data.

Figure 12.7 is a snapshot of the OEM LogMiner options interface. Through this interface, the selection is further trimmed down to provide a closer filtered data set from the online redo log files. Once the conditions and options have been selected and the ''execute'' button is clicked, OEM will retrieve all queries from the redo log files that meet the selection criteria and display them on the interface.

Figure 12.7: LogMiner OEM options interface.

LogMining a RAC environment

Each instance in a two or more instance configuration such as RAC has its own set of online redo log files and a set of archive redo log files. Since LogMiner is used to mine the redo log files and the archive redo log files, separate requests against each of the instances need to be performed to retrieve all the rows.

---