Reading alert log via SQL in Oracle 11g

X$DBGALERTEXT показывает последний log.xml

X$DIAG_ALERT_EXT показывает объединенное содержимое для файлов log_XX.xml, включая log.xml.

connect sys as sysdba

select * from X$DBGALERTEXT where rownum <= 20;


set linesize 254
col ORIGINATING_TIMESTAMP format a50
col MESSAGE_TEXT format a170

SELECT ORIGINATING_TIMESTAMP, message_text FROM X$DIAG_ALERT_EXT
where 1 = 1
  and originating_timestamp >= sysdate - 30
  and message_text like 'ALTER SYSTEM%'
  and message_text not like 'ALTER SYSTEM ARCHIVE LOG%'
;

Select ORA-XXXXX and TNS-XXXXX errors last 10 minutes:

set linesize 254
col ORIGINATING_TIMESTAMP format a50
col MESSAGE_TEXT format a170

SELECT inst_id, originating_timestamp, MESSAGE_TEXT
    FROM TABLE (
            gv$ (
               CURSOR (
                  SELECT inst_id, originating_timestamp, MESSAGE_TEXT
                    FROM v$diag_alert_ext
                   WHERE     originating_timestamp > (SYSDATE - 10/1440)
                         AND (MESSAGE_TEXT LIKE '%ORA-%' or MESSAGE_TEXT LIKE '%TNS-%')
                         AND MESSAGE_TEXT NOT LIKE '%TNS-12502%'
                      )
                )
               )
ORDER BY inst_id, originating_timestamp;