Oracle 11g SQL语句查询alert.log 日志视图 V$DIAG_ALERT_EXT

WITH diag_alert_ext
       AS (SELECT                                           /*+ materialize */
                 ORIGINATING_TIMESTAMP, MESSAGE_TEXT
             FROM   v$diag_alert_ext
            WHERE   ORIGINATING_TIMESTAMP > SYSTIMESTAMP - INTERVAL '7' DAY)
  SELECT   ORIGINATING_TIMESTAMP, MESSAGE_TEXT
    FROM   diag_alert_ext
   WHERE      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%'
ORDER BY   originating_timestamp DESC

 

with diag_alert_ext as (select /*+ materialize */ * from v$diag_alert_ext where ORIGINATING_TIMESTAMP>systimestamp - INTERVAL '99' day)
select ORIGINATING_TIMESTAMP,message_text from diag_alert_ext
where ORIGINATING_TIMESTAMP>systimestamp- INTERVAL '99' day and message_text like '%ORA-%' and message_text not like '%result of ORA-609%' and message_text not like '%result of ORA-28%' and message_text not like '%(ORA-3136)%' and message_text not like '%ORA-01013:%';

 

SELECT   originating_timestamp, MESSAGE_TEXT
    FROM   V$DIAG_ALERT_EXT
   WHERE   originating_timestamp > SYSTIMESTAMP - INTERVAL '40' DAY
           AND MESSAGE_TEXT LIKE '%ORA-%'
ORDER BY   originating_timestamp DESC;

ORIGINATING_TIMESTAMP,MESSAGE_TEXT
2019-7-10 11:42:14.822 +08:00,WARNING: inbound connection timed out (ORA-3136)

 

select 
originating_timestamp,message_text
from 
   V$DIAG_ALERT_EXT
where 
   message_text like '%ORA-%'
order by 
   originating_timestamp desc;

ORIGINATING_TIMESTAMP,MESSAGE_TEXT
2019-7-10 11:42:14.822 +08:00,WARNING: inbound connection timed out (ORA-3136)

2019-5-10 10:25:58.829 +08:00,WARNING: inbound connection timed out (ORA-3136)

2019-4-26 10:35:08.775 +08:00,WARNING: inbound connection timed out (ORA-3136)

2019-4-18 9:53:28.763 +08:00,WARNING: inbound connection timed out (ORA-3136)

2019-4-11 9:28:30.136 +08:00,WARNING: inbound connection timed out (ORA-3136)

2019-4-3 9:22:07.615 +08:00,WARNING: inbound connection timed out (ORA-3136)

2019-4-1 14:09:57.577 +08:00,WARNING: inbound connection timed out (ORA-3136)

2019-3-26 13:36:50.498 +08:00,WARNING: inbound connection timed out (ORA-3136)

2019-3-21 11:17:04.002 +08:00,WARNING: inbound connection timed out (ORA-3136)

2019-3-19 13:42:49.146 +08:00,WARNING: inbound connection timed out (ORA-3136)

2019-3-15 21:52:46.431 +08:00,Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_14090922.trc  (incident=60505):
ORA-03137: TTC 协议内部错误: [3120] [] [] [] [] [] [] []

2019-3-15 21:51:50.951 +08:00,Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_23331108.trc  (incident=60482):
ORA-03137: TTC 协议内部错误: [3120] [] [] [] [] [] [] []

2019-3-15 21:51:02.329 +08:00,Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_14090916.trc  (incident=60489):
ORA-03137: TTC 协议内部错误: [3120] [] [] [] [] [] [] []

2019-3-15 21:49:56.948 +08:00,Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_33817218.trc  (incident=60481):
ORA-03137: TTC 协议内部错误: [3120] [] [] [] [] [] [] []

2019-3-15 21:49:04.371 +08:00,Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_33817212.trc  (incident=60473):
ORA-03137: TTC 协议内部错误: [3120] [] [] [] [] [] [] []

2019-3-15 21:48:08.472 +08:00,Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_36831584.trc  (incident=60465):
ORA-03137: TTC 协议内部错误: [3120] [] [] [] [] [] [] []

2019-3-15 21:47:04.910 +08:00,Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_32833840.trc  (incident=60457):
ORA-03137: TTC 协议内部错误: [3120] [] [] [] [] [] [] []

2019-3-15 21:46:06.507 +08:00,Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7406304.trc  (incident=60449):
ORA-03137: TTC 协议内部错误: [3120] [] [] [] [] [] [] []

2019-3-15 21:44:45.102 +08:00,Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_15925530.trc  (incident=60441):
ORA-03137: TTC 协议内部错误: [3120] [] [] [] [] [] [] []

2019-3-15 21:43:44.515 +08:00,Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7471870.trc  (incident=60322):
ORA-03137: TTC 协议内部错误: [3120] [] [] [] [] [] [] []

2019-3-15 21:42:49.687 +08:00,Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7471860.trc  (incident=60321):
ORA-03137: TTC 协议内部错误: [3120] [] [] [] [] [] [] []

2019-3-15 21:41:16.033 +08:00,Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7471852.trc  (incident=60257):
ORA-03137: TTC 协议内部错误: [3120] [] [] [] [] [] [] []

2019-3-15 20:45:18.869 +08:00,Errors in file /data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_24642122.trc  (incident=60281):
ORA-03137: TTC 协议内部错误: [3120] [] [] [] [] [] [] []

2019-3-15 20:33:40.393 +08:00,WARNING: inbound connection timed out (ORA-3136)

2019-3-4 17:15:00.846 +08:00,WARNING: inbound connection timed out (ORA-3136)

2019-2-26 18:09:56.670 +08:00,WARNING: inbound connection timed out (ORA-3136)

2019-2-19 19:55:50.818 +08:00,WARNING: inbound connection timed out (ORA-3136)

2019-2-18 9:32:23.786 +08:00,WARNING: inbound connection timed out (ORA-3136)

2019-1-5 12:21:33.441 +08:00,WARNING: inbound connection timed out (ORA-3136)