Da wir sowohl Windows als auch Linux-Umgebungen haben, nehme ich für die Installation gern ein "One size fits all"-SQL-Skript, weil es am billigsten ist und wenig Aufwand verursacht.
Ab der Oracle-Version 11g kann man für die Auswertung des Alert-Logs die auf der XML-Ausgabe des Alertlogs basierenden Objekte – die fixed table X$DIAG_ALERT_EXT und die View V_$DIAG_ALERT_EXT – verwenden. Ich fand aber die Performance nicht wirklich prickelnd, vor allem, wenn im entsprechenden Verzeichnis (<diagnostic_dest>/diag/rdbms/<dbname>/<instname>) mehrere XML-Logs zusammengekommen waren.
Die XML-basierten Alert-Logs kann man natürlich mit dem ADRCI regelmäßig aufräumen, was das Problem verringert, aber ich hatte den Verdacht, dass für das Parsen der Logfiles im XML-Format irgendwelche alten Utilities wie das XML Developer Kit (XDK) verwendet wurden.
Also habe ich mir einfach selber etwas mit Oracle-Bordmitteln zusammengebastelt, um einen schnellen Überblick zu bekommen. Für die detaillierte, mehrere Meldungen übergreifende Auswertung eignet sich die View V_$DIAG_ALERT_EXT besser.
Zuerst muss ich natürlich rausfinden, wo das Alert-Verzeichnis steckt, ein Directory mit diesem Pfad erstellen und die Rechte an meinen monitor-User vergeben.
In das Verzeichnis kommt dann eine Textdatei namens "liste.txt" mit einem einzigen Eintrag "log.xml". Log.xml ist der Name des aktuellen Alertlogs, das von Oracle historisiert wird, sobald es die Größe von 10 MB überschreitet. Die historisierten Logs heißen dann log_1.xml, log_2.xml etc.
Ein kleiner PL/SQL-Block erspart einem hier Arbeit.
-- als sys ausführen
set serveroutput on
DECLARE
v_path dba_directories.directory_path%TYPE;
v_stmt VARCHAR2(400);
v_handle UTL_FILE.FILE_TYPE;
BEGIN
SELECT value INTO v_Path
FROM v$diag_info WHERE name = 'Diag Alert' ;
v_stmt := q'[CREATE OR REPLACE DIRECTORY alert_dir AS ']'||v_path||q'[']';
EXECUTE IMMEDIATE v_stmt;
v_stmt := 'GRANT READ, WRITE ON DIRECTORY alert_dir TO monitor;
EXECUTE IMMEDIATE v_stmt;
v_handle := UTL_FILE.FOPEN('ALERT_DIR', 'liste.txt', 'w');
UTL_FILE.PUT_LINE(v_handle, 'log.xml');
UTL_FILE.FCLOSE(v_handle);
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.FCLOSE(v_handle);
DBMS_OUTPUT.PUT_LINE('Fehler:'||sqlerrm);
END;
Diese Vorarbeiten sind nötig, damit ich das Alert-Log als external table auswerten kann. Die Syntax zur Erstellung dieser Tabelle ist leider alles andere als intuitiv.
conn monitor/pwd
CREATE TABLE alert_ext (
filename VARCHAR2(10),
xml_file CLOB)
ORGANIZATION EXTERNAL (
DEFAULT DIRECTORY alert_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
NOBADFILE NOLOGFILE
FIELDS (filename CHAR(10))
COLUMN TRANSFORMS (xml_file FROM lobfile (filename)))
LOCATION ('liste.txt'));
Oracle findet in der Datei liste.txt den Eintrag log.xml und verwendet diesen als Inhalt der Spalte filename in der externen Tabelle. Über die Klausel COLUMN TRANSFORMS wird der aktuelle Inhalt des Alertlogs log.xml als CLOB beim Select ausgelesen.
SELECT * from alert_ext;<o:p></o:p>
FILENAME XML_FILE
----------- -------------------------------------------------------------------------------------
log.xml <msg time='2023-05-16T13:06:45.411-05:00' org_id='oracle' comp_id='VOS' msg_id='opistr_real:1285:1197768316' type='NOTIFICATION' group='VOS' level='16' host_id='DBSERVER1' host_addr='.....'
<txt>Checkpoint not complete
</txt>
</msg>
Jetzt noch ein paar Hilfstabellen, damit ich die Einträge länger vorhalten kann.
In der Tabelle ALERT_INT werden jeweils die interessanten Alert-Log-Inhalte der letzten Stunde gespeichert. Attribute wie comp_id und group, die nur bei unkritischen Einträgen andere Inhalte als rdbms und VOS (virtual operating system) haben, lasse ich hier weg. Mich interessieren fürs erste nur der Zeitstempel (time) und die eigentliche Message (txt).
CREATE TABLE alert_int
datum TIMESTAMP (9) WITH TIME ZONE,
message VARCHAR2 (2000));
Die Tabelle ALERT_INT_HIST enthält dann die ganze Historie
CREATE TABLE alert_int_hist AS SELECT * FROM alert_int WHERE 1 = 0;
CREATE INDEX alert_date_hist_idx ON alert_int_hist(datum);
Der erste Schritt ist der Insert aller bisherigen Einträge in die ALERT_INT_HIST.
Dummerweise stellt das Alert-Log-File eigentlich kein gültiges XML dar, weil es keinen Root-Knoten hat.
Das kann man aber reparieren, indem man einfach 2 Tags vorn und hinten ergänzt (in rot). Damit lässt sich der Inhalt des Files log.xml über den Konstruktoraufruf XMLType in auswertbares XML umwandeln.
Die Zeilenumbrüche ersetze ich dabei gleich durch Leerzeichen, damit ich die ganze Fehlermeldung bekomme.
Und dann kommt die recht performante XQuery-Syntax zum Einsatz, mit der die XML-Inhalte in relationale Form überführt werden.
Mit der Klausel XMLTable wird aus den Knoten im XML-Pfad /x/msg eine Tabelle, bei der man die Inhalte der XML-Attribute (gekennzeichnet durch @) in die Spalten einlesen kann, deren Namen nach der COLUMNS-Klausel angegeben wird (in blau).
Wichtig ist, dass man die Spaltenbreiten nicht zu knapp wählt, sonst werden die Inhalte einfach gekürzt, ohne dass man eine Fehlermeldung erhält.
Danach kann man die Messages je nach Bedarf filtern.
INSERT INTO alert_int_hist
SELECT *
FROM (SELECT TO_TIMESTAMP_TZ(datum, 'yyyy-mm-dd"T"hh24:mi:ss.ff3tzh:tzm') datum,
message
FROM (SELECT XMLType(REPLACE ('<x>'||xml_file||'</x>', CHR(10), ' ')) xml_file
FROM alert_ext),
XMLTable('/x/msg' PASSING xml_file
COLUMNS message VARCHAR2(2000) PATH 'txt',
datum VARCHAR2(50) PATH '@time'))
WHERE (message LIKE ‘%ORA-%’
OR message LIKE ‘Errors in file%’
OR message LIKE ‘%TNS-%’
OR message = ‘Checkpoint not complete’
OR message LIKE ‘%ORACLE instance%’
OR message LIKE 'Corrupt%')
AND message NOT LIKE ‘%ORA-06512%’
ORDER BY datum;
Jetzt fehlt noch eine Prozedur für die stündliche Befüllung der Tabelle alert_int und die Mail-Benachrichtigung.
Für das Verschicken von Mails muss ich als sys das Package UTL_MAIL einrichten, den Parameter smtp_out_server anpassen und dem User monitor die Rechte am Package und eine ACL-Freigabe einrichten.
@?/rdbms/admin/utlmail.sql
@?/rdbms/admin/prvtmail.plb
ALTER SYSTEM SET smtp_out_server = '<IP-Adresse des Mail-Servers';<o:p></o:p>
GRANT EXECUTE ON utl_mail TO monitor;
BEGIN
DBMS_NETWORK_ACL_ADMIN.append_host_ace (
host => '<IP-Adresse des Mail-Servers>',
lower_port => <SMTP-Port>,
upper_port => <SMTP-Port>,
ace => xs$ace_type(privilege_list => xs$name_list('connect'),
principal_name => 'MONITOR',
principal_type => xs_acl.ptype_db));
END;<
Ein Test der Mail-Funktion empfiehlt sich
DECLARE
v_global_name VARCHAR2(30);
BEGIN
SELECT global_name INTO v_global_name FROM global_name;
UTL_MAIL.SEND(
sender => 'monitoring',
recipients => '<interne Mail-Adresse>',
subject => 'Mail-Test: '||v_global_name,
message => 'Test Test Test',
mime_type => 'text/plain; charset=UTF-8');
END;
/
Damit kann ich die Prozedur erstellen
create or replace PROCEDURE prc_fill_alert_int
AS
l_msg VARCHAR2(2000);
l_crlf VARCHAR2(2) := CHR(10);
v_global_name VARCHAR2(30);
BEGIN
-- ältere Einträge historisieren und löschen
INSERT INTO alert_int_hist SELECT * FROM alert_int;
DELETE FROM alert_int;
COMMIT;
INSERT INTO alert_int
SELECT * FROM (SELECT TO_TIMESTAMP_TZ(datum,
'yyyy-mm-dd"T"hh24:mi:ss.ff3tzh:tzm') datum, message
FROM (SELECT XMLType(REPLACE('<x>'||xml_file||'</x>',
CHR(10), ' ')) xml_file
FROM alert_ext),
XMLTable('/x/msg' PASSING xml_file
COLUMNS message VARCHAR2(4000) PATH 'txt',
datum VARCHAR2(50) PATH '@time'))
WHERE datum > sysdate -1/24
AND ( message LIKE ‘%ORA-%’
OR message LIKE ‘Errors in file%’
OR message LIKE ‘%TNS-%’
OR message = ‘Checkpoint not complete’
OR message LIKE ‘%ORACLE instance%’
OR message LIKE 'Corrupt%')
AND message NOT LIKE ‘%ORA-06512%’
ORDER BY datum;
-- Mail, wenn Probleme gefunden werden
IF SQL%ROWCOUNT <> 0 THEN
l_msg := 'Eintraege im Alert-Log:'||l_crlf;
l_msg := l_msg||' '||l_crlf;
l_msg := l_msg||'DATUM FEHLER '||l_crlf;
l_msg := l_msg||'------------------- -----------------------------------------'||l_crlf;
FOR rec IN (SELECT TO_CHAR(datum, 'DD.MM.RR HH24:MI:SS') datum, message
FROM alert_int) LOOP
l_msg := l_msg||rec.datum||' '||rec.message||l_crlf;
END LOOP;
SELECT global_name INTO v_global_name FROM global_name;
UTL_MAIL.SEND(
sender => 'Monitor',
recipients => '<interne Mail-Adresse>',
subject => 'Alert-Log-Monitoring ||v_global_name',
message => l_msg,
mime_type => 'text/plain; charset=utf-8');
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
pkg_err.log_error (
p_errm => REGEXP_SUBSTR(SQLERRM, '.*$'),
p_proc => 'monitor.prc_fill_alert_int');
END;
und als letztes einen Scheduler-Job einrichten:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'MONITOR_ALERT_LOG',
job_type => 'STORED_PROCEDURE',
job_action => 'PRC_FILL_ALERT_INT',
start_date => TO_DATE('2023-05-16 20','YYYY-MM-DD HH24'),
repeat_interval => 'FREQ=HOURLY;INTERVAL=1',
enabled => TRUE);
END;
/
Das wars. Damit kann ich alles mit einem einzigen Skript betriebssystem-unabhängig in SQL*Plus auf dem Datenbankserver gleich nach der Installation einrichten.
Die View v$diag_alert_ext liefert zwar unter 19c deutlich schneller Ergebnisse zurück als früher, aber ich bleibe erstmal bei der manuellen Lösung. Ab 21c kann ich ja dann auf das attention.log umsteigen und mit JSON_TABLE arbeiten.
Elke Fritsch
DOAG Themenverantwortliche Tuning
