DBA Tips Archive for Oracle
Oracle LogMiner Viewer and Flashback Transaction
by Jeff Hunter, Sr. Database Administrator
LogMiner is a built-in database tool introduced in Oracle8i that provides a relational interface for users to query redo records found in online and archived redo log files. LogMiner is most often used for auditing purposes, data analysis, or recovering data from a user error.
The LogMiner utility can be accessed through SQL statements (command-line) or through the Oracle LogMiner Viewer graphical user interface (GUI). Prior to Oracle Database 11g, Oracle LogMiner Viewer was a separate Java client application that provided a GUI to the the LogMiner utility. Starting with Oracle Database 11g, Oracle LogMiner Viewer has been incorporated into the Oracle Enterprise Manager web-based interface and also integrated with the new Flashback Transaction feature, making it simple to recover transactions that may have been modified by mistake. New to Oracle Database 11g, Flashback Transaction allows the changes made by a transaction to be undone, optionally including changes made by dependent transactions, while the database remains online.
This tutorial provides an example-based approach on how to use Oracle LogMiner Viewer and Flashback Transaction. The example will be based on Oracle Database 11g Release 2 (220.127.116.11.0) and Oracle Enterprise Manager Database Control running on the Linux platform.
Consult the following two guides for an overview of the LogMiner tool and a tutorial on how to use the LogMiner command-line interface.
The following are requirements for the source database that LogMiner will mine.
The source/mining database must be Oracle Database 11g or higher with Oracle Enterprise Manager Database Control or Grid Manager configured.
Archivelog mode must be enabled in order to generate usable redo log files.
A new role named LOGMNR_ADMIN will be created and assigned the appropriate privileges for LogMiner analysis. This role will be assigned to a new user named MINER that will be used to perform the LogMiner examples presented in this guide.
LogMiner is a redo-based application and as such, requires at least minimal supplemental logging be enabled on the source database. Oracle does not enable any supplemental logging by default. Additionally, supplemental logging for primary key columns must be enabled for Flashback Transaction to work.
After verifying supplemental logging is enabled, force a log switch in order for the new supplemental log configuration to begin archiving the additional column data to the redo logs.
Remember that supplemental logging must be enabled on the source database before generating redo log files that will be analyzed by LogMiner.
This section demonstrates how to use Oracle LogMiner Viewer to capture DDL and DML activity and how to use Flashback Transaction to backout a bad transaction (including dependent transactions).
In order to test the LogMiner and Flashback Transaction functionality, create a test environment in the source database. Use the following script to create a test table and perform a few transactions against it that will be analyzed by LogMiner.
Oracle LogMiner Viewer and Flashback Transaction will be used to backout the UPDATE (UPDATE...WHERE employee_id=1005) and DELETE (DELETE...WHERE employee_id=1001) statements from the script shown above. These two statements are part of TRANSACTION 1.
Before backing out the target transaction, Flashback Transaction will detect that there is a dependent transaction (DELETE...WHERE employee_id=1005) and prompt the user with a list of options on how to continue. Since the transaction we intend to backout (TRANSACTION 1) depends on the second transaction (TRANSACTION 2), we cannot backout the first transaction without first backing out the second transaction.
To access Oracle LogMiner Viewer, launch Oracle Enterprise Manager, select the Availability tab and click the View and Manage Transactions link in the Manage section.
Figure 1: Oracle LogMiner Viewer
From the LogMiner screen, filter the transactions that LogMiner will return by selecting the Time Range or SCN Range that is likely to pick up the recorded transactions for LogMiner to analyze.
Use the optional Query Filter parameters to further filter the number of transaction records for LogMiner to analyze. For this example, include the Table Name and DB User to specify which table and database user to which the transactions belong.
Use the Advanced Query section to enter any additional filter information which allows you to edit the actual WHERE clause. Click the Info Icon under Advanced Query for a description of columns in V$LOGMNR_CONTENTS that can be included in the WHERE clause. In this example, I will leave the Advanced Query section set to its default value click the [Continue] button.
Figure 2: Enter Search Criteria
Wait while the contents of the online and archived redo files are searched by Oracle LogMiner Viewer for transaction records matching the search criteria you entered.
Figure 3: Searching Online and Archived Redo Log Files
The resulting page shows the transaction records that match the search criteria you entered. The leftmost column shows the transaction identifier (XID), which is used by Oracle to uniquely identify a transaction. The Transaction Summary column shows how many UPDATE (upd), INSERT (ins), and DELETE (del) statements were performed for each transaction record.
Click on the Transaction ID associated with the UPDATE and DELETE statements being rolled back in this example (TRANSACTION 1). In this case it is Transaction ID 070010000A3A0000 and listed as "SCOTT.EMPLOYEES (1 upd, 1 del)".
Figure 4: LogMiner Results
This displays the Transaction Details page which shows the UPDATE and DELETE statements. Click the [Flashback Transaction] button.
Figure 5: Transaction Details
Confirm the Flashback Transaction operation request by clicking the [Yes] button.
Figure 6: Confirm Flashback Transaction Operation
The Flashback Transaction operation detected the dependent transaction (DELETE...WHERE employee_id=1005) from TRANSACTION 2. You need to decide how the Flashback Transaction operation should handle any dependent transactions.
The Flashback Transaction operation is currently using the NONCONFLICT_ONLY option. Click the [Change Recovery Option] button to change the recovery option settings.
Figure 7: Dependent Transactions Detected
Select the Cascade option to backout the target transaction and all dependent transactions.
Figure 8: Backout All Dependent Transactions (Cascade)
This brings you back to the Show Dependencies screen showing the Cascade option selected. Click on the target transaction and any dependent transactions to verify each statement will be executed [exec=yes].
After verifying the target and dependent transactions, click the [Next] button.
Figure 9: Confirm Cascade Option Selected
The Review screen provides you with the ability to show the Undo SQL script that will be used to backout the target (and dependency) transactions. In addition, you can provide a SQL statement that will allow you to view the effect of the changes prior to committing the Flashback Transaction.
Click the [Finish] button to commit the Flashback Transaction operation.
Figure 10: Review Flashback Transaction Details
After the Flashback Transaction operation is complete, click the [OK] button on the Results screen.
Figure 11: Flashback Transaction Results Screen
Clicking [OK] on the Results screen will return you to the Transaction Details screen.
Clicking [OK] again will return you to the LogMiner Results screen.
Finally, click the [Done] button to return the top level of the Availability tab screen.
Verify the Flashback Transaction successfully backed out the target (and any dependent) transactions.
Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.
Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at firstname.lastname@example.org.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.
Last modified on
Monday, 15-Oct-2012 17:18:51 EDT
Page Count: 7079