RelationalDBDesign RelationalDBDesign

Backup Recovery   «Prev  Next»
Lesson 5LogMiner
ObjectiveDescribe the functionality of LogMiner.

Oracle LogMiner

The ability to easily and accurately determine and control what activity is taking place on computer systems is essential to the effective management of these systems. Resolving the reasons that cause key data to change is vital to the security and management of enterprise applications and systems. Oracle provides a powerful tool, LogMiner, enabling administrators to audit the use of the database and the mission critical data it manages. Not only does LogMiner provide extensive auditing capabilities, and the ability to undo erroneous changes to enterprise data, this tool also performs this task without introducing any of the overhead normally associated with auditing large systems.
Oracle redo log files contain a wealth of useful information about the activities and history of an Oracle database, but until Oracle there was no accurate or easy to use tool that could tap into this information. Log files contain all the data that is needed to perform database recovery. They also record every change made to data and metadata within the database.
LogMiner is a fully relational tool, which allows log files to be read, analyzed, and interpreted by the administrator using SQL. LogMiner can view any valid redo log file, online or archived, from Oracle8 forward. Analysis of the log files with LogMiner can be used to accomplish the following:
  1. Track specific sets of changes based on transaction, user, table, time, and so on. It is easy to determine who modified a database object and what the before and after data was. The ability to trace and audit database changes back to their source, and the ability to undo them, are valuable security and management tools.
  2. Pinpoint when an incorrect modification was introduced into the database. This can be used to perform logical recovery at the application level, instead of at the database level.
  3. Provide supplemental information for tuning and capacity planning. Administrators can also perform various forms of historical analysis to determine trends and data access patterns.
  4. Retrieve critical information for debugging complex applications.

LogMiner provides this functionality without imposing any data collection overhead on the system to be studied, because the Oracle server already maintains the redo log files for database recovery. In addition, LogMiner can be used as the underlying technology for a powerful set of management and analysis tools.
LogMiner does not require a mounted database to analyze redo log files. However, to translate the contents of the redo log files fully, LogMiner requires access to the dictionary of the database being analyzed. LogMiner uses the dictionary to translate internal object identifiers and data types to meaningful external object names and data formats. The log files to be analyzed are mapped to a dynamic performance view (V$ table). A PL/SQL package is provided to associate the log files with the V$ log table.
Oracle and LogMiner enable small and large enterprises to fully understand and control the activity that takes place within the database. LogMiner is a powerful tool that provides system managers, application developers, and database administrators visibility into database usage never before available. The Oracle log files contain a vast quantity of useful information accessible only from LogMiner. It also provides the infrastructure for tools that enable administrators and developers to easily access and use this information for comprehensive system management.
The following MouseOver explains the syntax and example for the 3 procedures within the DBMS_LOGMNR package.

This sub-program starts the process of reading the archive log files by opening it

Dbms Logminer log file

log_archive_dest - Quiz

Click the Quiz link below to check your knowledge of backup and recovery improvements.
log_archive_dest - Quiz
The next lesson explains how to create reports and lists about backups.