LogMiner Workshop

Step 0: LogMiner Tools Installation

Log in as sysdba,
1) create dbms_logmnr package to analyze log files;

SQL>@$ORACLE_HOME/rdbms/admin/dbmslm.sql

2) create dbms_logmnr_d package to create dictionary files.

SQL>@$ORACLE_HOME/rdbms/admin/dbmslmd.sql

Step 1: Edit Parameter File

If you startup database using pfile, edit pfile directly;

If you startup database using spfile,

SQL> create pfile from spfile;

Edit init<SID>.ora and put the following:

utl_file_dir=/u01/app/oracle/admin/<SID>/udump

Step 2: Verify utl parameter

SQL> connect / as sysdba
SQL> startup force % OR STARTUP
SQL> show parameter utl

Step 3: Turn on supplemental logging at the database level

In order to use DDL_DICT_TRACKING later, you need to turn on supplemental logging at the database level. For other features, such as logging chain rows, cluster table, it should also be enabled.

SQL> alter database add supplemental log data

Step 4: Create a log miner dictionary file

SQL> execute sys.dbms_logmnr_d.build(-
>Dictionary_filename=>’mylog_dict.dic’,-
>Dictionary_location=>’/u01/app/oracle/admin/<SID>/udump’;

PL/SQL procedure successfully completed.

Step 5: Get current logfile info and current time

SQL> alter system switch logfile;
SQL> select member from v$logfile, v$log
where v$logfile.group# = v$log.group#
and v$log.status = ‘CURRENT’;

SQL>select to_char(current_timestamp, ‘DD-MON-RR HH24:MI:SS’) “TIME” from dual; % Time_1

Step 6: Perform some user activity

SQL> connect hr/hr
SQL> create table emp30 as
select employee_id, last_name, salary
from employees
where department_id=30;
SQL> alter table emp30 add (new_salary number(8,2));
SQL> update emp30 set new_salary=salary*1.5;
SQL> rollback;
SQL> update emp30 set new_salary=salary*1.2;
SQL> commit;
SQL> drop table emp30;
SQL> select to_char(current_timestamp, ‘DD-MON-RR HH24:MI:SS’) “TIME” from dual; % TIME_2

Step 7: Create (and add) the list of logfile

SQL> execute dbms_logmnr.add_logfile(-
> logfilename=>’/u01/app/oracle/oradata/<SID>/logxx.rdo’,-
> options=>dbms_logmnr.new);

Step 8: Start Logminer

SQL> alter session set nls_date_format=’DD-MON-RR HH24:MI:SS’;
SQL> execute dbms_logmnr.start_logmnr(-
dictfilename=>’/u01/app/oracle/admin/<SID>/udump/mylog_dict.dic’,-
starttime=>’&starttime’,-
endtime=>’&endtime’,-
options=>dbms_logmnr.ddl_dic_tracking+dbms_logmnr.committed_data_only);

Enter value for starttime: TIME_1
Enter value for endtim: TIME_2

Step 9: Display the committed changes made by user HR on segment EMP30

SQL> select timestamp, username, operation, sql_redo, sql_undo
from v$logmnr_contents
where username=’HR’ and (seg_name=’EMP30′ or seg_name is null);

Step 10: End the Logminer session and turn off supplemental logging at the database level

SQL> execute dbms_logmnr.end_logmnr;
SQL> alter database drop supplemental log data

Notes:

If supplemental logging is not enabled at Step 3, there would be an ORACLE error info at Step 9:

ORA-01347: Supplemental log data no longer found

References:

1. [Chinese] http://www.oracle.com.cn/redirect.php?tid=2491&goto=lastpost
2. [Chinese] http://bbs.chinaunix.net/viewthread.php?tid=477403
3. [Chinese] http://blog.csdn.net/linhong34/archive/2007/08/23/1756776.aspx