How to use LogMiner to locate Archive Logs Flow


R. Wang



Keywords:  logminer, STATSPACK, Archive log files

Environment: Oracle 9iR2 and Solaris 5.9

Circumstance: Without introduction of new jobs, we monitored rapid disk occupancy in very short period. The circumstance we encountered is that a new archive log file (18MB) is created every minute. And, the disk occupancy is rapidly increased as shown below.

-rw-r-----   1 oracle   dba      18873856 Jul 24 20:38 PROD_0001_0000045102.arc
-rw-r-----   1 oracle   dba      18873856 Jul 24 20:39 PROD_0001_0000045103.arc
-rw-r-----   1 oracle   dba      18873856 Jul 24 20:40 PROD_0001_0000045104.arc
-rw-r-----   1 oracle   dba      18873856 Jul 24 20:41 PROD_0001_0000045105.arc
-rw-r-----   1 oracle   dba      18873856 Jul 24 20:41 PROD_0001_0000045106.arc
-rw-r-----   1 oracle   dba      18873856 Jul 24 20:43 PROD_0001_0000045107.arc
-rw-r-----   1 oracle   dba      18873856 Jul 24 20:43 PROD_0001_0000045108.arc
-rw-r-----   1 oracle   dba      18873856 Jul 24 20:45 PROD_0001_0000045109.arc
-rw-r-----   1 oracle   dba      18873856 Jul 24 20:45 PROD_0001_0000045110.arc
-rw-r-----   1 oracle   dba      18873856 Jul 24 20:46 PROD_0001_0000045111.arc
-rw-r-----   1 oracle   dba      18873856 Jul 24 20:47 PROD_0001_0000045112.arc
-rw-r-----   1 oracle   dba      18873856 Jul 24 20:49 PROD_0001_0000045113.arc
-rw-r-----   1 oracle   dba      18873856 Jul 24 20:50 PROD_0001_0000045114.arc
-rw-r-----   1 oracle   dba      18873856 Jul 24 20:51 PROD_0001_0000045115.arc
-rw-r-----   1 oracle   dba      18873856 Jul 24 20:52 PROD_0001_0000045116.arc
-rw-r-----   1 oracle   dba      18873856 Jul 24 20:53 PROD_0001_0000045117.arc
-rw-r-----   1 oracle   dba      18873856 Jul 24 20:54 PROD_0001_0000045118.arc
-rw-r-----   1 oracle   dba      18873856 Jul 24 20:56 PROD_0001_0000045119.arc
-rw-r-----   1 oracle   dba      18873856 Jul 24 20:56 PROD_0001_0000045120.arc
-rw-r-----   1 oracle   dba      18873856 Jul 24 20:57 PROD_0001_0000045121.arc
-rw-r-----   1 oracle   dba      18873856 Jul 24 20:58 PROD_0001_0000045122.arc
-rw-r-----   1 oracle   dba      18873856 Jul 24 20:59 PROD_0001_0000045123.arc
-rw-r-----   1 oracle   dba      18873856 Jul 24 21:00 PROD_0001_0000045124.arc

Steps of Diagnosis

1.Checking on Memory, CPU, I/O

With checking on memory, CPU and I/O, we failed to find any abnormal process that excessively consumes resource of database and OS.

Note: For reference on “Monitor Oracle Resouce Consumption in Unix”, please refer to the article in “oracle metalink” with Note: 148466.1.

2.Using Logminer to locate targeted archive log files

Then, we would like to look at what does the database do by examining archive log files created with Logminer.

Before start using Logminer, please make sure of that you

•Log in with sys account with sysdba role
•Never escape from the current session because the data viewing of logminer will not be available at other sessions.

The steps to use Logminer are depicted as following in details.

Continue...............

See complete article in PDF as attachment.