Oracle RDBMS 11.1.0.6 maintains XML version of alert_$ORACLE_SID.log,
in addition to the ASCII one. Some of you might remember that log files
are inclined to grow. To prevent endless growth which would fill up my
disk space, I use logadm on Solaris and logrotate on Linux. I can do
that on the ASCII version only because it doesn't have any kind of an
internal structure. The award winning question is what to do about the
XML version? If I let logrotate cut it weekly, I will end up with a
useless XML file which I cannot validate or parse. It will require a
manual intervention to make it into a proper XML file again. On the other
hand, if I don't cut it with logrotate, it will grow like a sequoia in
a vase, with similar consequences.
The award winning question is what to do about that? How can I slice &
dice the log.xml file while preserving its usefulness? This file is
extremely useful because it contains the exact timestamp for every
message. An entry looks like this:

<msg time='2008-01-14T22:01:40.698+01:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
module='' pid='25495'>
<txt>Thread 1 advanced to log sequence 535
</txt>
</msg>


That provides, timestamp, component and message text, for every message.

--
Mladen Gogala
http://mgogala.freehostia.com

On Jan 15, 6:58*am, Mladen Gogala <mgog...@yahoo.com> wrote:
> Oracle RDBMS 11.1.0.6 maintains XML version of alert_$ORACLE_SID.log,
> in addition to the ASCII one. Some of you might remember that log files
> are inclined to grow. To prevent endless growth which would fill up my
> disk space, I use logadm on Solaris and logrotate on Linux. I can do
> that on the ASCII version only because it doesn't have any kind of an
> internal structure. The award winning question is what to do about the
> XML version? If I let logrotate cut it weekly, I will end up with a
> useless XML file which I cannot validate or parse. It will require a
> manual intervention to make it into a proper XML file again. On the other
> hand, if I don't cut it with logrotate, it will grow like a sequoia in
> a vase, with similar consequences.
> The award winning question is what to do about that? How can I slice &
> dice the log.xml file while preserving its usefulness? This file is
> extremely useful because it contains the exact timestamp for every
> message. An entry looks like this:
>
> <msg time='2008-01-14T22:01:40.698+01:00' org_id='oracle' comp_id='rdbms'
> *client_id='' type='UNKNOWN' level='16'
> *module='' pid='25495'>
> *<txt>Thread 1 advanced to log sequence 535
> *</txt>
> </msg>
>
> That provides, timestamp, component and message text, for every message.
>

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 \>$?

Or maybe easier, logrotate, then figure out where the first complete </
msg> is, head that to the archived file (if you are keeping one), and
tail everything after that for the new one.

I think the latter might be faster, I'm not sure about race issues
with oracle writing the logs.

jg
--
@home.com is bogus.
"My RSS Reader is Empty!" - Alex Gorbachev

On Jan 15, 1:09*pm, joel garry <joel-ga...@home.com> wrote:
> On Jan 15, 6:58*am, Mladen Gogala <mgog...@yahoo.com> wrote:
>
>
>
>
>
> > Oracle RDBMS 11.1.0.6 maintains XML version of alert_$ORACLE_SID.log,
> > in addition to the ASCII one. Some of you might remember that log files
> > are inclined to grow. To prevent endless growth which would fill up my
> > disk space, I use logadm on Solaris and logrotate on Linux. I can do
> > that on the ASCII version only because it doesn't have any kind of an
> > internal structure. The award winning question is what to do about the
> > XML version? If I let logrotate cut it weekly, I will end up with a
> > useless XML file which I cannot validate or parse. It will require a
> > manual intervention to make it into a proper XML file again. On the other
> > hand, if I don't cut it with logrotate, it will grow like a sequoia in
> > a vase, with similar consequences.
> > The award winning question is what to do about that? How can I slice &
> > dice the log.xml file while preserving its usefulness? This file is
> > extremely useful because it contains the exact timestamp for every
> > message. An entry looks like this:
>
> > <msg time='2008-01-14T22:01:40.698+01:00' org_id='oracle' comp_id='rdbms'
> > *client_id='' type='UNKNOWN' level='16'
> > *module='' pid='25495'>
> > *<txt>Thread 1 advanced to log sequence 535
> > *</txt>
> > </msg>
>
> > That provides, timestamp, component and message text, for every message.
>
> 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 \>$?
>
> Or maybe easier, logrotate, then figure out where the first complete </
> msg> is, head that to the archived file (if you are keeping one), and
> tail everything after that for the new one.
>
> I think the latter might be faster, I'm not sure about race issues
> with oracle writing the logs.
>
> jg
> --
> @home.com is bogus.
> "My RSS Reader is Empty!" - Alex Gorbachev- Hide quoted text -
>
> - Show quoted text -

Mladen, I consider your post of "Oracle RDBMS 11.1.0.6 maintains XML
version of alert_$ORACLE_SID.log, in addition to the ASCII one" to be
good news. I thought that the alert log was now XML. I do not have a
spare server capable of handling the load so I am stuck waiting for
the Express version so I can actually see 11g in action.

Your final solution on how to trim the XML version of the file is
bound to be of interest. We just trim ours manually once or twice a
year.

-- Mark D Powell --

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:umper;
$XML::Parser::Easytree::Noempty=1;
my $p=new XML::Parser(Style=>'EasyTree');
my $xml;
while (<>) {
if (/^\s*<msg>/) {
$xml=$;
} 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=~/(\d{4}-\d{2}-\d{2})T(\d{2}:\d{2}:\d{2})/;
$time="$1 $2";
print "COMPONENT:$component TYPE:$type TIME:$time MSG:$msgtxt\n";
$xml="";
}
}


This is the output it produces:

COMPONENT:rdbms TYPE:UNKNOWN TIME:2008-01-14 22:00:00 MSG:Setting
Resource Manager plan SCHEDULER[0x2C09]EFAULT_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
http://mgogala.freehostia.com

On Wed, 16 Jan 2008 10:26:40 +0000, Mladen Gogala wrote:


> 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:arser and
> XML:arser::EasyTree. I will need to test DBMS_SYSTEM.KSDWRT.
>

DBMS_SYSTEM.KSDWERT still has only 2 arguments. Here is the call to
KSDWRT:

SQL> exec dbms_system.ksdwrt(2,'This is a test!');

PL/SQL procedure successfully completed.

Result produced was:

<msg time='2008-01-16T13:14:04.248+01:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
module='sqlplus@nwxp-mgogala.ot.hr (TNS V1-V3)' pid='8026'>
<txt>This is a test!
</txt>
</msg>

Normal alert log contains the expected text:
Wed Jan 16 13:14:04 2008
This is a test!

Module information looks quite useful. The script I posted earlier
can be used to extract module information, too.


__
Mladen Gogala
http://mgogala.freehostia.com