Someone has deleted Mr. Ford
from Scott's EMP table, and everyone disavows any knowledge of the event.
However, you are very suspicious of one of your users, who has a tendency of
bending the truth at times. So, you decide to use LogMiner to track down the
guilty party.
First, you edit your parameter file entering
utl_fil_dir=C:\ORACLASS\LOGS.
LogMiner can now create an operating system file in this path.
SQL> SHOW USER
USER is "SYS"
SQL> SHOW PARAMETER utl
NAME
TYPE VALUE
------------ ------ ----------------
utl_file_dir string C:\ORACLASS\LOGS
Next, you execute the BUILD procedure in the
DBMS_LOGMNR_D package to create an operating sys- tem file MINER2.DAT. This
file will contain information taken from the specified redo log files.
SQL> CONNECT SYS/CHANGE_ON_INSTALL@DBA
Connected.
SQL> BEGIN
2
dbms_logmnr_d.build ( 'miner2.dat', 'C:\ORACLASS\LOGS' );
3
END;
4
/
PL/SQL procedure successfully completed.
Next, switch log files and identify the current log file.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> column member NEW_VALUE redo
SQL> SELECT member
2
FROM v$logfile —
IF REDO LOGS ARE MIRRORED,
ONLY WANT ONE(ROWNUM<2)
3
WHERE group# = ( SELECT
group# FROM v$log
WHERE status = 'CURRENT' )
4
AND ROWNUM < 2;
MEMBER
-----------------------------------
C:\ORACLASS\DATA\DISK3\REDO0201.LOG
Next, copy the above redo log member to C:\ORA-
CLASS\LOGS\redo.log.
SQL> host xcopy &redo C:\ORACLASS\LOGS\redo.log
Now, indicate to LogMiner that you
want information contained in the redo.log file by executing ADD_LOGFILE.
SQL> BEGIN
2
dbms_logmnr.add_logfile('C:\ ORACLASS\ LOGS\redo.log',
3
dbms_logmnr.NEW );
4 END;
5 /
PL/SQL procedure successfully completed.
Now, tell LogMiner to start. LogMiner takes the data in
the operating system file miner2.dat and loads it into the dynamic
performance views used by LogMiner. One of these views is
v$logmnr_contents.
SQL> BEGIN
2
dbms_logmnr.start_logmnr ( dictFileName =>
'c:\ORACLASS\LOGS\miner2.dat' );
4
END;
5
/
PL/SQL procedure successfully completed.
Now you are ready to write a query (Exhibit 8) using
v$logmnr_contents to identify the
villain in who deleted ford saga. The two columns of interest are SQL_REDO
and SQL_UNDO. The SQL_REDO column shows the SQL statement that changes data
in a table, and the SQL_UNDO column shows the SQL statement(s) to reverse or
undo the SQL statement that changed the table data. It is more than
painfully obvious that user LIAR_LIAR deleted Ford and also committed his
dastardly deed. Notice the undo of the DELETE command is an INSERT command.
SQL> columnsql_redo format a30 word_wrapped
SQL> columnsql_undo format a30 word_wrapped
SQL> columnusername format a12
SQL> SELECT scn,
username,
sql_redo,
sql_undo
FROM
V$LOGMNR_CONTENTS;
SCN
USERNAME SQL_REDO
SQL_UNDO
------ ---------
------------------------------ ------------------------
232009 LIAR_LIAR
set transaction read write;
232009 LIAR_LIAR
delete from SCOTT.EMP where
insert into
EMPNO = 7902 and ENAME =SCOTT .EMP(EMPNO,ENAME,JOB,MGR,
'ford' and JOB = 'ANALYST' and HIREDATE,SAL,COMM,DEPTNO)
MGR = 7566 and HIREDATE =
values
TO_DATE('03-DEC-2081
(7902,'ford','ANALYST',
00:00:00', 'DD-MON-YYYY
7566,TO_DATE('03-DEC-2081
HH24:MI:SS') and SAL = 3000
00:00:00','DD-MON-YYYY
and COMM IS NULL and DEPTNO =
HH24:MI:SS'),3000,NULL,
20 and ROWID =
20);
'AAAAsrAADAAAAF6AAM';
232010 LIAR_LIAR
commit;
Exhibit 8.Query
Using v$logmnr_contents to Identify the Villain
To view the INSERT required to place Mr. Ford back into
Scott's EMP table, write a query viewing the SQL_UNDO column of
v$logmnr_contents:
SQL> SELECT sql_undo
FROM
V$LOGMNR_CONTENTS;
insert into
SCOTT.EMP(EMPNO,ENAME,JOB,MGR,
HIREDATE,SAL,COMM,DEPTNO)
values
(7902,'ford','ANALYST',7566,TO
_DATE('03-DEC-2081
00:00:00',
'DD-MON-YYYY
HH24:MI:SS'),3000,NULL,20);
You can cut and paste the INSERT statement and exe- cute
it to place Ford back into Scott's EMP table.
SQL> insert into
SCOTT.EMP(EMPNO,ENAME,JOB,MGR,
HIREDATE,SAL,COMM,DEPTNO)
values
(7902,'ford','ANALYST',7566,
TO_DATE('03-DEC-2081 00:00:00','
DD-MON-YYYY
HH24:MI:SS'),3000,NULL,20);
1 row created.
SQL> COMMIT;
Commit complete.
Finally, end your LogMiner session.
SQL> BEGIN
SQL> dbms_logmnr.end_logmnr;
SQL> END;
SQL> /
PL/SQL procedure successfully completed.
BMC Corporation has had a product named LogMaster for many years. It is much
easier to use than Oracle's LogMiner. You merely enter commands at the
operating system prompt.
and include the URL for the page.
