ORACLE 11G 告警日志查询视图v$diag_alert_ext显示多实例日志

为ORACLE11G 数据库监控做了一个告警日志查询的脚本：

select *
  from (WITH diag_alert_ext AS (SELECT ORIGINATING_TIMESTAMP,
                                       MESSAGE_TEXT,
                                       component_id,
                                       case
                                         when MESSAGE_TEXT LIKE '%Error%' OR
                                              MESSAGE_TEXT LIKE '%Fail%' OR
                                              MESSAGE_TEXT LIKE '%WARNING%' OR
                                              MESSAGE_TEXT LIKE '%Invalid%' OR
                                              MESSAGE_TEXT LIKE '%ORA-%' OR
                                              MESSAGE_TEXT LIKE
                                              '%Global Enqueue Services%' OR
                                              MESSAGE_TEXT LIKE '%dead%' then
                                          1
                                         else
                                          0
                                       end error_occured
                                  FROM v$diag_alert_ext
                                 WHERE ORIGINATING_TIMESTAMP >
                                       SYSTIMESTAMP - INTERVAL '1'
                                 DAY
                                   and component_id = 'rdbms')
         SELECT ORIGINATING_TIMESTAMP,
                
                MESSAGE_TEXT,
                lag(MESSAGE_TEXT, 3) over(order by ORIGINATING_TIMESTAMP desc),
                lag(MESSAGE_TEXT, 2) over(order by ORIGINATING_TIMESTAMP desc),
                lag(MESSAGE_TEXT, 1) over(order by ORIGINATING_TIMESTAMP desc),
                lead(MESSAGE_TEXT, 1) over(order by ORIGINATING_TIMESTAMP desc),
                lead(MESSAGE_TEXT, 2) over(order by ORIGINATING_TIMESTAMP desc),
                lead(MESSAGE_TEXT, 3) over(order by ORIGINATING_TIMESTAMP desc),
                component_id,
                error_occured
           FROM diag_alert_ext order by ORIGINATING_TIMESTAMP )
          where error_occured = 1

这个脚本可以查询Oracle alert log中一些报错信息，使用了窗口函数lag和lead,可以查看某条错误日志的，前3条信息和后3条信息。

在我对我的备库进行查询的时候发现了一个，奇怪的现象：我发现查出的日志不对，不是我连接到的数据库的日志。再仔细查看，原来查看的是另外一个实例的日志。

这里我交代一下背景，备库是两个生产库的备库，使用的是同一台机器，分别使用不同的实例。通过我进一步分析，发现 v$diag_alert_ext可以显示所有实例的相关日志。里面会记录日志文件名

那如果我要区分是什么数据库的日志呢，可以根据ADR_HOME字段，或者上面的Filename来区分