Alert-Log per XMLQuery auslesen

Heute geht's nochmal um XQuery, um damit das Alert-Log, welches seit 11g auch im XML-Format vorliegt, in SQL verfügbar zu machen.

Dazu wird zunächst ein Directory angelegt, welches das Alert-Log im XML-Format enthält:

create directory alert_log as '/u01/app/oracle/diag/rdbms/ora112/ora112/alert/';

Leider ist das Alert-Log nicht well-formed, da es kein Root-Element besitzt; hier besteht also noch Handlungsbedarf.

Wenngleich man auch mit etwas PL/SQL ein Root-Element hinzufügen kann, habe ich mich hier dafür entschieden, zyklisch eine Kopie des Alert-Logs zu erstellen; diese Kopie wird dann mithilfe von Betriebssystem-Mitteln um ein Root-Element erweitert.

Unter Unix genügt es den Start- und End-Tag in jeweils einer Datei abzuspeichern, um dann eine Verkettung mit cat vorzunehmen.

touch start_tag
echo "" > start_tag
touch end_tag
echo "" > start_tag
cat start_tag log.xml end_tag > log_well_formed.xml

Jetzt kann man auf dieser Grundlage einen View erstellen, der mithilfe eines FLOWR-Ausdrucks, das Alert-Log relational aufbereitet:

create view v_alert_log as
(
select
 to_date(substr(x.log_time, 1, 19), 'YYYY-MM-DD"T"HH24:MI:SS') as log_time
 x.log_org_id,
 x.log_comp_id,
 x.log_msg_id,
 x.log_type,
 x.log_group,
 x.log_level,
 x.log_host_id,
 x.log_host_addr,
 x.log_pid,
 x.log_version,
 x.log_txt
from
 xmltable
 (
  'for $i in //msg return $i'
  passing 
   xmltype(bfilename('ALERT_LOG', 'log_well_formed.xml'), nls_charset_id('AL32UTF8'))
  columns
   "LOG_TIME" varchar2(255) path '/msg/@time',
   "LOG_ORG_ID" varchar2(255) path '/msg/@org_id',
   "LOG_COMP_ID" varchar2(255) path '/msg/@comp_id',
   "LOG_MSG_ID" varchar2(255) path '/msg/@msg_id',
   "LOG_TYPE" varchar2(255) path '/msg/@type',
   "LOG_GROUP" varchar2(255) path '/msg/@group',
   "LOG_LEVEL" integer path '/msg/@level',
   "LOG_HOST_ID" varchar2(255) path '/msg/@host_id',
   "LOG_HOST_ADDR" varchar2(255) path '/msg/@host_addr',
   "LOG_PID" integer path '/msg/@pid',
   "LOG_VERSION" varchar2(255) path '/msg/@version',
   "LOG_TXT" varchar2(4000) path '/msg/txt'
 ) x
);

Jede Änderung an der Quelle wird direkt durch den View reflektiert.

Eine mögliche Abfrage könnte zum Beispiel wie folgt aussehen:

select 
 * 
from 
 v_alert_log 
where 
 log_txt like '%ORA-%' and
 sysdate - log_time <= 3;