example of using ORACLE logminer - dbms_logmnr

1. Create DICTIONARY file on the SOURCE database using
   the following command:
   
      begin
              dbms_logmnr_d.build( dictionary_filename =>'dictionary.ora',
                                   dictionary_location => '/usr/tmp');
      end;
      /

2. MAKE SURE THAT "utl_file" path can access
   the ARCHIVE LOG files to analyze, in this case I soft
   linked the "/usr/tmp/logmine" with this directory since
   "/usr/tmp" was listed in "utl_file" path:

      ln -s /u01/app/oracle/admin/scripts/logmine /usr/tmp/logmine


3. Now lets logmine some logs shall we?

PWD=/u01/app/oracle/admin/scripts/logmine

-rw-r--r--   1 oracle   dba      52429312 Oct  9 11:49 XYLP84670.arc
-rw-r--r--   1 oracle   dba      52429312 Oct  9 11:49 XYLP84671.arc
-rw-r--r--   1 oracle   dba      52429312 Oct  9 11:49 XYLP84672.arc
-rw-r--r--   1 oracle   dba      52429312 Oct  9 11:49 XYLP84673.arc
-rw-r--r--   1 oracle   dba      52429312 Oct  9 11:49 XYLP84674.arc
-rw-r--r--   1 oracle   dba      52429312 Oct  9 11:49 XYLP84675.arc
-rw-r--r--   1 oracle   dba      52429312 Oct  9 11:49 XYLP84676.arc
-rw-r--r--   1 oracle   dba      52429312 Oct  9 11:49 XYLP84677.arc
-rw-r--r--   1 oracle   dba      52429312 Oct  9 11:49 XYLP84678.arc
-rw-r--r--   1 oracle   dba      52429312 Oct  9 11:50 XYLP84679.arc


## login to sqlplus as sys
##

set echo on
set feed on

-- add a first file to the logminer list
begin
   dbms_logmnr.add_logfile( logfilename=> '/usr/tmp/logmine/PLYX84670.arc',
                            options=> dbms_logmnr.new);
end;
/

-- now add the rest of the files
begin
   dbms_logmnr.add_logfile( logfilename=> '/usr/tmp/logmine/PLYX84670.arc', options=> dbms_logmnr.addfile);
   dbms_logmnr.add_logfile( logfilename=> '/usr/tmp/logmine/PLYX84671.arc', options=> dbms_logmnr.addfile);
   dbms_logmnr.add_logfile( logfilename=> '/usr/tmp/logmine/PLYX84672.arc', options=> dbms_logmnr.addfile);
   dbms_logmnr.add_logfile( logfilename=> '/usr/tmp/logmine/PLYX84673.arc', options=> dbms_logmnr.addfile);
   dbms_logmnr.add_logfile( logfilename=> '/usr/tmp/logmine/PLYX84674.arc', options=> dbms_logmnr.addfile);
   dbms_logmnr.add_logfile( logfilename=> '/usr/tmp/logmine/PLYX84675.arc', options=> dbms_logmnr.addfile);
   dbms_logmnr.add_logfile( logfilename=> '/usr/tmp/logmine/PLYX84676.arc', options=> dbms_logmnr.addfile);
   dbms_logmnr.add_logfile( logfilename=> '/usr/tmp/logmine/PLYX84677.arc', options=> dbms_logmnr.addfile);
   dbms_logmnr.add_logfile( logfilename=> '/usr/tmp/logmine/PLYX84678.arc', options=> dbms_logmnr.addfile);
   dbms_logmnr.add_logfile( logfilename=> '/usr/tmp/logmine/PLYX84679.arc', options=> dbms_logmnr.addfile);
end;
/


-- execute logminer
begin
   dbms_logmnr.start_logmnr( dictfilename=>'/usr/tmp/dictionary.ora');
end;
/

-- here are couple of variation of the above command
--
-- ANALYZE using TIME
-- ==================
-- begin
--      dbms_logmnr.start_logmnr( DICTFILENAME=> '/oracle/dictionary.ora',
--                                STARTTIME=> to_date('01-Jan-1998 08:30:00', 'DD-MON-YYYY HH:MI:SS'),
--                                ENDTIME=> to_date('01-Jan-1998 08:45:00', 'DD-MON-YYYY HH:MI:SS'));
-- end;
-- /
--
-- ANALYZE using SCN
-- =================
-- begin
--      dbms_logmnr.start_logmnr( dictfilename=> '/oracle/dictionary.ora',
--                                STARTSCN=> 100,
--                                ENDSCN=> 150);
-- end;
-- /


-- finally run a report
-- NOTE:
--      Here I just wanted to find the most active segments for a specific time
--      frame you'd have to adjust this sql to meet your own needs.
   

set echo off
set time on
set timing on

set lines 132
set trims on

select username, seg_owner, seg_name, seg_type, count(*) 
from v$logmnr_contents
group by username, seg_owner, seg_name, seg_type
having count(*) > 1000
order by 5
/

USERNAME  SEG_OWNER  SEG_NAME                           SEG_TYPE   COUNT(*)
--------- ---------- -------------------------------- ---------- ----------
          APPLSYS    WF_ITEM_ACTIVITY_STATUSES_H_N1            1       1145
          APPLSYS    WF_ITEM_ACTIVITY_STATUSES_H               2       1158
APPS      APPLSYS    WF_ITEM_ACTIVITY_STATUSES_N3              1       1166
          APPLSYS    WF_ITEM_ACTIVITY_STATUSES_PK              1       1166
SYS       SYS        HIST_HEAD$                                2       1224
          SYS        SOURCE$                                   2       1626
APPS      APPLSYS    WF_ITEM_ACTIVITY_STATUSES                 2       2120
APPS      QA         QA_RESULTS_INTERFACE                      2       2217
          APPLSYS    WF_ITEM_ACTIVITY_STATUSES_N3              1       2327
APPS      APPLSYS    WF_ITEM_ACTIVITY_STATUSES_N1              1       3050
SYS                                                            0       3997

USERNAME  SEG_OWNER  SEG_NAME                           SEG_TYPE   COUNT(*)
--------- ---------- -------------------------------- ---------- ----------
          APPLSYS    WF_ITEM_ACTIVITY_STATUSES                 2       4076
APPS                                                           0       4654
          APPLSYS    WF_ITEM_ACTIVITY_STATUSES_N1              1       5825
                                                               0      25871
          DW         DW_ALL_PORCV_DETAIL                       2     153989
          DW         DW_ALL_PO_DETAIL                          2    1393313

17 rows selected.

Elapsed: 00:14:20.56


-- end logminer
--

begin                          
        dbms_logmnr.end_logmnr;
end;
/

spool off