Mining Redo Log Files


Just when you DBAs think things are a bit too quiet and harmonious, someone walks into your cubicle to announce that they've done a silly thing and need the DBA to come to their rescue. For example, a developer may have forgotten to place a WHERE clause on a DELETE command and thus deleted all the rows from a table rather than just a select few. Beginning with 8i, Oracle provides a set of packages to make such transaction recoveries possible. But to use the DBMS_LOGMINER packages without a GUI is a tedious and garish exercise at best. So again TOAD comes to the rescue with the DBMS_LOGMINER Interface screen shown in Figure 6.63 and accessible from the main menu at DBALog Miner.

Figure 6.63. TOAD DBMS_LOGMINER Interface screen ”step 1.

The DBMS_LOGMINER Interface screen offers a relatively straightforward, four-step process whereby DBAs can mine online redo logs to locate and undo transactions. Prior to invoking this screen, it is advisable to force a log switch via the Redo Log Manager screen such that you know exactly which redo log files to mine. In the example shown, the current online redo log was group 4. By forcing the log switch, the contents of group 4 were now available for mining. After making your log switch, you open up the DBMS_LOGMINER Interface screen.

Step 1 asks you to define where the log miner packages can query the contents of the data dictionary. With Oracle 9i you have two options: using the online data dictionary (fast and recommended) or using offline dictionaries created by Oracle. In Oracle 8i there is no online data dictionary option; you must create an offline data dictionary file for the packages to reference. When doing an offline data dictionary method, Oracle will need the ability to write to a server directory via the UTL_FILE_DIR INIT.ORA parameter. If this has not been set, you will have to set it and then restart your database. Be patient when creating an offline data dictionary file; this process may take a few minutes.

Step 2 permits you to specify which online redo log files to mine as shown in Figure 6.64. In this example, only the fourth redo log file was chosen because the transaction was recent and because online redo log file 4 had been the current group prior to the forced manual log switch. You could of course have chosen more than one online redo log file to mine. Note that the online redo log files are on the database server. So if the example had not been on a local database, you would have had to FTP copies of the files onto your PC. Fortunately, TOAD is intelligent enough to recognize that fact and would provide an FTP-style dialog.

Figure 6.64. TOAD DBMS_LOGMINER Interface screen ”step 2.

Step 3 permits you to filter what will be mined as shown in Figure 6.65. You can limit by the Oracle System Change Number (SCN) or by date and time. Plus there are several useful Oracle 9i options, including one to show committed data only. The goal here is to expedite the log file mining process and also shorten the obtained results displayed. A typical database may have redo log files 100 megabytes or more in size, so any filtering will be advantageous. For the example shown, because the developer just recently issued the DELETE command without the WHERE clause, you could probably restrict based on time with reasonable accuracy.

Figure 6.65. TOAD DBMS_LOGMINER Interface screen ”step 3.

Step 4's screen initially comes up empty. You must select what options you want to see displayed and then click the Execute button. The screen will then show the redo log file mining results as shown in Figure 6.66. Remember this step can take a while, especially for those 100-megabyte redo log files. And even with filtering, the information displayed can still be quite extensive . In the example shown in Figure 6.66, scrolling to the bottom reveals that the last action to occur was delete activity against the MOVIES_DEV.RENTALITEM table followed by a commit. But note that redo log information is recorded at the row level. So in fact, there are 10,000 such entries as the one shown. If the goal is to create a script that perform undo operations for the entire logical transaction, you need to click the Move SQL to SQL Edit Window button and customize the query to your needs as shown in Figure 6.67. Now you have all 10,000 insert commands necessary to re-create the rows mistakenly deleted. You could then execute that query as a script and copy the script output into another SQL Editor window in order to actually run all those commands.

Figure 6.66. TOAD DBMS_LOGMINER Interface screen ”step 4.

Figure 6.67. Building an UNDO script for a transaction.



TOAD Handbook
TOAD Handbook (2nd Edition)
ISBN: 0321649109
EAN: 2147483647
Year: 2003
Pages: 171

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net