Thursday, January 8, 2009

How to monitor alert log file in Oracle

We know in a UNIX system with tail -f we can monitor alert log file to see how alert log file is populated. For example the latest happenings in alert log file can be seen by,
$tail -f background_dump_dest location/alert_$ORACLE_SID.ora

But in windows there is not tail command. We can monitor the alert log within oracle itself regardless of platform. This can be archived by following steps.

1)Create an External Table to read the alert log.

column a_log new_value ALOG noprint
column value new_value bkgd_dmp noprint

select 'alert_'||instance_name||'.log' a_log
from v$instance;

select value
from v$parameter
where name = 'background_dump_dest';

create or replace directory data_dir as '&&bkgd_dmp';

CREATE TABLE alert_t
(
text_line varchar2(255)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY data_dir
ACCESS PARAMETERS
(
records delimited by newline
fields
REJECT ROWS WITH ALL NULL FIELDS
)
LOCATION
(
'&&ALOG'
)
)
REJECT LIMIT unlimited;


2)Query from the External Table.

For example, you can then query the last 21 lines of the alert log in this manner:

select text_line
from (
select rownum rn, text_line
from alert_t
)
where rn between (select max(rn) - 20 from (select rownum rn from
alert_t))
and (select max(rn) from (select rownum rn from alert_t));

No comments:

Post a Comment