Re: oracle11 logs, the sequel.

From: Mladen Gogala <>
Date: 16 Jan 2008 10:26:40 GMT
Message-ID: <478ddbdf$0$1341$>

On Tue, 15 Jan 2008 10:09:52 -0800, joel garry wrote:

> Just thinking out loud:
> Could you use your favorite tool to parse it into single lines ending
> with the </msg>, tail some configurable number of lines, then parse back
> breaking lines on a space less than 78 or \>$?

Joel, here is the first cut:

#!/usr/bin/perl -w
use strict;

use XML::Parser;
use XML::Parser::EasyTree;
use Data::Dumper;

my $p=new XML::Parser(Style=>'EasyTree'); my $xml;
while (<>) {
	if (/^\s*<msg>/) {
    } else { $xml .= $_; }
	if (m|^\s*</msg>|) {
		my $tree=$p->parse($xml);
        my $msg=$tree->[0];
        my $msgtxt=$msg->{content}->[1]->{content}->[0]->{content};
        my $time=$msg->{attrib}->{time};
        my $type=$msg->{attrib}->{type};
        my $component=$msg->{attrib}->{comp_id};
		$time="$1 $2";
        print "COMPONENT:$component TYPE:$type TIME:$time MSG:$msgtxt\n";


This is the output it produces:

COMPONENT:rdbms TYPE:UNKNOWN TIME:2008-01-14 22:00:00 MSG:Setting Resource Manager plan SCHEDULER[0x2C09]:DEFAULT_MAINTENANCE_PLAN via scheduler window  

COMPONENT:rdbms TYPE:UNKNOWN TIME:2008-01-14 22:00:00 MSG:Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter  

COMPONENT:rdbms TYPE:UNKNOWN TIME:2008-01-14 22:00:11 MSG:Mon Jan 14 22:00:11 2008  

COMPONENT:rdbms TYPE:UNKNOWN TIME:2008-01-14 22:00:11 MSG:Logminer Bld: Lockdown Complete. DB_TXN_SCN is UnwindToSCN (LockdownSCN) is 4091395735  

COMPONENT:rdbms TYPE:UNKNOWN TIME:2008-01-14 22:01:40 MSG:Thread 1 advanced to log sequence 535  

COMPONENT:rdbms TYPE:UNKNOWN TIME:2008-01-14 22:01:40 MSG: Current log# 1 seq# 535 mem# 0: /oradata/test11/redo01.log  

COMPONENT:rdbms TYPE:UNKNOWN TIME:2008-01-15 02:00:00 MSG:Clearing Resource Manager plan via parameter  

It would be rather trivial to load this into a table and index it by time,type and component. Of course, you'd need modules XML::Parser and XML::Parser::EasyTree. I will need to test DBMS_SYSTEM.KSDWRT.

Mladen Gogala
Received on Wed Jan 16 2008 - 04:26:40 CST

Original text of this message