Analyzing archive files using Oracle log miner

Where are the archive files located?

SELECT *
FROM v$parameter p
WHERE p.name LIKE '%log_archive_dest_%'
ORDER BY 1;

Find the rate of arc files generation per day/minute

I use two mwthods:

Using the command line:

[oracle@webDB arc_dir]$ cd /your/arc/files/dir/
[oracle@webDB arc_dir]$ # per day
[oracle@webDB arc_dir]$ ls -ltr *.arc | cut -c 40-47 | uniq -c | sort -n  | tail -15
      8  Apr  4
    241  Apr  6
    382  Apr  5
[oracle@webDB arc_dir]$ # per minute    (till the 52'th char)            
[oracle@webDB arc_dir]$ ls -ltr *.arc | cut -c 40-50 | uniq -c | sort -n  | tail -7
     16  Apr  5 04:
     17  Apr  5 17:
     19  Apr  5 19:
     20  Apr  6 03:
     24  Apr  5 16:
    137  Apr  5 00:
    139  Apr  6 00:

Here we see that we have a lot of writing activity on 00:00-01:00 on both: Apr 5’th and Apr 6’th BTW: if you don’t understand th command - take a look in the posts:

Using-the-cut-command
Perl-one-liners

using SQL:

  SELECT TO_CHAR (completion_time, 'mm-dd-HH24-Day') time_,
         TO_CHAR (completion_time, 'mm-dd') date_,
         TO_CHAR (completion_time, 'D-Day') day_,
         TO_CHAR (completion_time, 'HH24') hour_,
         COUNT (DISTINCT sequence#) archives,
         ROUND (  COUNT (DISTINCT sequence#)
                * (SELECT bytes / 1024 / 1024 / 1024 member_gb
                     FROM v$log
                    WHERE ROWNUM = 1))
             archive_gb
    FROM v$archived_log
   WHERE completion_time > SYSDATE - 21
GROUP BY TO_CHAR (completion_time, 'mm-dd-HH24-Day'),
         TO_CHAR (completion_time, 'mm-dd'),
         TO_CHAR (completion_time, 'D-Day'),
         TO_CHAR (completion_time, 'HH24')
 ORDER BY 1;

Analize the files using `DBMS_LOGMNR`

[oracle@webDB arc_dir]$ ls -ltr $PWD/arch_*.* |grep -P 'Apr  [56] 00:' |  cut -c 54-200 | perl -i -pe "s/^/$postfix/;"| perl -i -pe "s/$/$suffix/"
[oracle@webDB arc_dir]$ for i in `ls $PWD/*arc`; do  echo "exec DBMS_LOGMNR.ADD_LOGFILE('$i');"; done
exec DBMS_LOGMNR.ADD_LOGFILE('/oracle_dir/arc_dir/arch_1_859056_620585656.arc');
exec DBMS_LOGMNR.ADD_LOGFILE('/oracle_dir/arc_dir/arch_1_859057_620585656.arc');
exec DBMS_LOGMNR.ADD_LOGFILE('/oracle_dir/arc_dir/arch_1_859058_620585656.arc');
exec DBMS_LOGMNR.ADD_LOGFILE('/oracle_dir/arc_dir/arch_1_859059_620585656.arc');
exec DBMS_LOGMNR.ADD_LOGFILE('/oracle_dir/arc_dir/arch_1_859060_620585656.arc');
exec DBMS_LOGMNR.ADD_LOGFILE('/oracle_dir/arc_dir/arch_1_859061_620585656.arc');
exec DBMS_LOGMNR.ADD_LOGFILE('/oracle_dir/arc_dir/arch_1_859062_620585656.arc');
exec DBMS_LOGMNR.ADD_LOGFILE('/oracle_dir/arc_dir/arch_1_859063_620585656.arc');
exec DBMS_LOGMNR.ADD_LOGFILE('/oracle_dir/arc_dir/arch_1_859064_620585656.arc');
exec DBMS_LOGMNR.ADD_LOGFILE('/oracle_dir/arc_dir/arch_1_859065_620585656.arc');
...

Run the avove lines to add the log files

exec DBMS_LOGMNR.ADD_LOGFILE('/oracle_dir/arc_dir/arch_1_859056_620585656.arc');
exec DBMS_LOGMNR.ADD_LOGFILE('/oracle_dir/arc_dir/arch_1_859057_620585656.arc');
exec DBMS_LOGMNR.ADD_LOGFILE('/oracle_dir/arc_dir/arch_1_859058_620585656.arc');
exec DBMS_LOGMNR.ADD_LOGFILE('/oracle_dir/arc_dir/arch_1_859059_620585656.arc');
exec DBMS_LOGMNR.ADD_LOGFILE('/oracle_dir/arc_dir/arch_1_859060_620585656.arc');

Start oracle log miner

exec DBMS_LOGMNR.START_LOGMNR(options =>dbms_logmnr.dict_from_online_catalog);

End oracle log miner session

exec DBMS_LOGMNR.END_LOGMNR();

Start analizing…

Which actions are most common:

SELECT COUNT (*) n, lc.seg_owner, lc.table_name,  operation
FROM  v$logmnr_contents lc
GROUP BY lc.seg_owner, lc.table_name, operation
ORDER BY 1 DESC ;

Lets take a look at first 25 lines:

N          seg_owner  table_name                 operation
--------------------------------------------------------------
587146                                           INTERNAL
174896     USER1      CUTOMERS_TBL               UNSUPPORTED
132175     USER1      CUTOMERS_TBL               UPDATE
                                          START
                                          COMMIT
    USER2      REQUESTS_TBL               UNSUPPORTED
    AMSTERDAM  SESSIONS_TBL               UPDATE
     SYS        SUM$                       UNSUPPORTED
     SYS        SUM$                       UPDATE
     SYS        SNAP$                      UPDATE
     USER2      SMSES_TBL                  UNSUPPORTED
     USER2      MEET_SNP                   DELETE
     USER1      MEET_SNP                   INSERT
     SYS        SNAP_REFTIME$              UPDATE
     SYS        SUMDETAIL$                 UPDATE
     SYS        SEQ$                       UNSUPPORTED
     USER3      MEET_2_TBL                 INSERT
     SYS        WRH$_EVENT_HISTOGRAM       INSERT
     SYS        WRH$_SYSMETRIC_HISTORY     INSERT
      USER1      SESSION_STATUS_TBL         UPDATE
      SYS        MON_MODS$                  DELETE
      SYS        WRH$_SYSSTAT               INSERT
      SYS        WRH$_LATCH                 INSERT
      SYS        MON_MODS_ALL$              UNSUPPORTED
      SYS        JOB$                       UPDATE

Lets tke a look at the actions of CUTOMERS_TBL:

SELECT username,
       table_name, 
       to_char(timestamp,'mm/dd/yy hh24:mi:ss') timestamp,
	seg_type_name, seg_name, table_space, 
	session# SID, serial# ,operation
	--,substr(sql_redo,1,400) as sql_
FROM  v$logmnr_contents
WHERE table_name = 'CUTOMERS_TBL';

← Previous Post Next Post →