Backup Recovery   «Prev 

Using LogMiner

Oracle uses online redo log files to track every change that is made to user data and the data dictionary. The information stored in the redo log files is used to re-create the database, in part or in full, during recovery. To enable recovery of the database to a point in time after the database backup was made, you can maintain archived copies of the redo log files.
The LogMiner utility provides a vital view into the modifications that have occurred within your database. When you use LogMiner, you see both the changes that have been made (the SQL_redo column) and the SQL you can use to reverse those changes (the SQL_undo column). Thus, you can review the history of the database, without actually applying any redo logs, and obtain the code to reverse any problematic transactions. Using LogMiner, you can pinpoint the transaction under which corruption first occurred so that you can determine the correct point in time or System Change Number (SCN) to use as the endpoint for a database recovery.

DBMS LogMiner Logfile

DBMS LogMiner Logfile

Location 1 This sub-program starts the process of reading the archive log files by opening it.
Location 2 Name of the log file that must be added to the list of log files to be analyzed within a session.
Location 3 The following options are valid: a) Purges the existing list of log files and starts a new list (DBMS_LOGMNR.NEW), b) Adds a file to anexisting list (DBMS_LOGMNR.ADDFILE), and c) Removes the log file from the list of log files to be analyzed (DBMS_LOGMNR.REMOVEFILE)
Location 4This sub-program starts the process of reading the archive log files by opening it.
Location 5starts the process of reading log files.
Location 6ends the session of LogMiner.
Location 7This sub-program starts the process of reading the archive log files by opening it.
Location 8starts the process of reading log files.
Location 9ends the session of LogMiner.
Location 10starts the process of reading log files.
Location 11ends the session of LogMiner.

Oracle Backup and Recovery

Oracle LogMiner

Oracle LogMiner, which is part of Oracle Database, enables you to query 1)online and 2) archived redo log files through a SQL interface. Redo log files contain information about the history of activity on a database.

LogMiner Benefits

All changes made to user data or to the database dictionary are recorded in the Oracle redo log files so that database recovery operations can be performed. Because LogMiner provides a well-defined, easy-to-use, and comprehensive relational interface to redo log files, it can be used as a powerful data auditing tool, and also as a sophisticated data analysis tool. The following list describes some key capabilities of LogMiner:
  1. Pin-pointing when a logical corruption to a database, such as errors made at the application level, may have begun. These might include errors such as those where the wrong rows were deleted because of incorrect values in a WHERE clause, rows were updated with incorrect values, the wrong index was dropped, and so forth. For example, a user application could mistakenly update a database to give all employees 100 percent salary increases rather than 10 percent increases, or a database administrator (DBA) could accidently delete a critical system table. It is important to know exactly when an error was made so that you know when to initiate time-based or change-based recovery. This enables you to restore the database to the state it was in just before corruption.
  2. Determining what actions you would have to take to perform fine-grained recovery at the transaction level. If you fully understand and take into account existing dependencies, then it may be possible to perform a table-specific undo operation to return the table to its original state. This is achieved by applying tablespecific reconstructed SQL statements that LogMiner provides in the reverse order from which they were originally issued. Normally you would have to restore the table to its previous state, and then apply an archived redo log file to roll it forward.
  3. Performance tuning and capacity planning through trend analysis. You can determine which tables get the most updates and inserts. That information provides a historical perspective on disk access statistics, which can be used for tuning purposes.
  4. Performing postauditing. LogMiner can be used to track any data manipulation language (DML) and data definition language (DDL) statements executed on the database, the order in which they were executed, and who executed them. (However, to use LogMiner for such a purpose, you need to have an idea when the event occurred so that you can specify the appropriate logs for analysis; otherwise you might have to mine a large number of redo log files, which can take a long time. Consider using LogMiner as a complementary activity to auditing database use. See the Oracle Database Administrator's Guide for information about database auditing.)