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
» 