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

矫情吗；* 2023-10-18 23:55 80阅读 0赞

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)