Oracle Point, Oracle Life.

How to use LogMiner to locate Archive Logs Flow

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.

To get complete PDF article, see Case_LogMiner.pdf

Popularity: 2% [?]