Oracle Utilities - Extending the alert.log Analysis

Author: Packt Publishing Published on: 4th Jan 2010

Extending the alert.log analysis with External Tables

Reading the alert.log from the database is a useful feature which can help you to find any outstanding error messages reported in this file.

Ads

Sample Code

create table ALERT_LOG(
text_line varchar2(512)
)
organization external (
type ORACLE_LOADER
default directory BACKGROUND_DUMP_DEST
access parameters(
records delimited by newline
nobadfile
nodiscardfile
nologfile
)
location( 'alert_beta.log')
)

Once the External Table has been created, the alert.log file can be queried just like any other regular table.

Sample Code

SQL> select text_line from alert_log
2 where text_line like 'ORA-%'
TEXT_LINE
-------------------------------------------------------------------------
-------
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/beta/redo01.log'
ORA-27037: unable to obtain file status
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/beta/redo02.log'
ORA-27037: unable to obtain file status
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oracle/oradata/beta/redo03.log'
ORA-27037: unable to obtain file status

Querying the alert.log file up to this phase is useful just to see the contents of the file and look for basic ORA-% strings. This could also be achieved by using the alert.log link in the Enterprise Manager (EM).

The alert.log file can be queried by means of the EM, but as this can only be viewed from the EM in an interactive mode, you can only rely on the preset alerts.

If further automatic work needs to be done, then it is useful to do some more work with the alert analysis tool. A temporary table can be used to store the contents of the ALERT_LOG table, along with an extra TIMESTAMP column, so it can be queried in detail in an EM-like manner.

Sample Code

create global temporary table TMP_ALERT_LOG (
LINE_NO NUMBER(6),
TIMESTAMP DATE,
TEXT_LINE VARCHAR2(512)
)
on commit preserve rows

A bit of PLSQL programming is necessary so the ALERT_LOG file can be modified and inserted into the TMP_ALERT_LOG, (enabling further queries can be done).

Sample Code

declare
cursor
alertLogCur is
select ROWNUM, TEXT_LINE
from ALERT_LOG
currentDate date
altertLogRec ALERT_LOG.TEXT_LINE%TYPE
testDay varchar2(10)
begin
currentDate := sysdate
for alertLogInst in alertLogCur loop
-- fetch row and determine if this is a date row
testDay := substr(alertLogInst.text_line, 1, 3)
if testDay = 'Sun' or
testDay = 'Mon' or
testDay = 'Tue' or
testDay = 'Wed' or
testDay = 'Thu' or
testDay = 'Fri' or
testDay = 'Sat'
then
-- if this is a date row, it sets the current logical record date
currentDate := to_date( alertlogInst.text_line, 'Dy Mon DD HH24:
MI:SS YYYY')
end if
insert into TMP_ALERT_LOG
values(
alertLogInst.rownum,
currentDate,
alertLogInst.text_line
)
end loop
end
/

As the contents of the alert.log end up in a temporary table, more than one DBA can query it at the same time, or restrict the DBA's accessibilities. There is no need to manage the purge and maintenance of the table after the session has ended, it can be indexed and there is little overhead by means of this procedure. More over, as this is a temporary object, minimum redo log information is generated.

Once the external ALERT_LOG and the temporary ALERT_LOG tables have been created, it is possible to perform, not only filters by date (provided by Enterprise Manager) but also any query against the alert.log file.

Sample Code