Striking Gold with LogMiner - Part 1: Getting Started - Page 2

Starting, using, and ending a LogMiner session

We are now ready to mine for gold, well, SQL. Suppose Scott calls you and says he deleted rows from his emp table (where empno is greater than 7900). It was a mistake, and he needs the data restored to his table. The first step is to start LogMiner and populate v$logmnr_contents. This view or "table" is what you query against to extract the SQL_REDO and SQL_UNDO statements.

There are several options as to how you gather the contents. More than likely, you're going to know a time range as opposed to an SCN number, so knowing approximately when Scott deleted the rows is all we need from him (aside from the table name).

SQL> exec dbms_logmnr.start_logmnr( -
> dictfilename => 
  'c:\ora9i\admin\db00\file_dir\
    dictionary.ora', -
> starttime => 
  to_date('06-Jun-2004 17:30:00', 
  'DD-MON-YYYY HH24:MI:SS'), -
> endtime => 
  to_date('06-Jun-2004 17:35:00', 
  'DD-MON-YYYY HH24:MI:SS'));

PL/SQL procedure successfully completed.

Now we are ready to see what took place.

SQL> select sql_redo, sql_undo
  2  from v$logmnr_contents
  3  where username = 'SCOTT'
  4  and seg_name = 'EMP';

SQL_REDO
------------------------------------------------------------------------------------------
SQL_UNDO
------------------------------------------------------------------------------------------
delete from "SCOTT"."EMP" where "EMPNO" = '7902' and "ENAME" = 'FORD' and "JOB" = 'ANALYST
' and "MGR" = '7566' and "HIREDATE" = TO_DATE('03-DEC-81', 'DD-MON-RR') and "SAL" = '3000'
and "COMM" IS NULL and "DEPTNO" = '20' and ROWID = 'AAAHW7AABAAAMUiAAM';

insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") 
values ('7902','FORD','ANALYST','7566',TO_DATE('03-DEC-81', 'DD-MON-RR'),'3000',NULL,'20');
*******************************************************************************************
delete from "SCOTT"."EMP" where "EMPNO" = '7934' and "ENAME" = 'MILLER' and "JOB" = 'CLERK
' and "MGR" = '7782' and "HIREDATE" = TO_DATE('23-JAN-82', 'DD-MON-RR') and "SAL" = '1300'
and "COMM" IS NULL and "DEPTNO" = '10' and ROWID = 'AAAHW7AABAAAMUiAAN';

insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") 
values ('7934','MILLER','CLERK','7782',TO_DATE('23-JAN-82', 'DD-MON-RR'),'1300',NULL,'10');
********************************************************************************************
delete from "SCOTT"."EMP" where "EMPNO" = '7935' and "ENAME" = 'COLE' and "JOB" = 'LINDA'
and "MGR" = '7839' and "HIREDATE" = TO_DATE('01-MAY-04', 'DD-MON-RR') and "SAL" = '4000' 
and "COMM" IS NULL and "DEPTNO" = '30' and ROWID = 'AAAHW7AABAAAMUiAAO';

insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") 
values ('7935','COLE','LINDA','7839',TO_DATE('01-MAY-04', 'DD-MON-RR'),'4000',NULL,'30');

You can see how Oracle (shading and "****" lines were added for readability) took the "delete from emp where empno > 7900" statement and turned it into something more complex. It should be apparent that the SQL_UNDO statements are practically in a cut and paste state, ready for immediate use.

To end your LogMiner session, issue the following command:

SQL> exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

Suggestions for Oracle regarding LogMiner

If LogMiner is a utility, then include all of its information (background, steps, examples, etc.) in the Utilities guide instead of spreading the information out over several guides.

If LogMiner is a utility, get rid of the reliance on the DBMS_PACKAGE-NAME.OPTION syntax. Export and Import are good examples of easy to use (and format) utilities.

If the package is in place, wouldn't it be much simpler to be able to issue a query with the appropriate where clauses to extract the information of interest, instead of performing all of the setup in convoluted dbms_logmnr.whatever statements? Perhaps a simple version of LogMiner (with negligible impact on performance), specified with a dynamic parameter (simple_logminer=true), could be made part of a DBCA-created database.

In Closing

As you can see, the steps needed to setup LogMiner were not too painful, and the steps to obtain the SQL_REDO and SQL_UNDO statements were fairly straightforward. A new DBA could use this package to recover simple user errors and not need to access archived log files. So could a senior DBA, but use of this package is a good dividing line between quick and relatively small recoveries (suitable for a new DBA to be responsible for) and more complex recoveries (in the senior DBA's realm of duties).

» See All Articles by Columnist Steve Callan