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.

Get deeper understanding about Logminer by reading this OTN article.

Mining for Clues

You must enable supplemental logging prior to generating log files that will be analyzed by LogMiner.
When you enable supplemental logging, additional information is recorded in the redo stream that is needed to make the information in the redo log files useful to you. Therefore, at the very least, you must enable minimal supplemental logging, as the following SQL statement shows:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

To determine whether supplemental logging is enabled, query the V$DATABASE view, as the following SQL statement shows:

SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

If the query returns a value of YES or IMPLICIT, minimal supplemental logging is enabled. See Supplemental Logging for complete information about supplemental logging.

Thanks