Recently I have searched a method to read and filter entries in alert log files (rdbms and listener.log).
A documented method consists in using adrci (ADR command interpreter) but I wanted an easier method, so I searched on the net and found this thread in Tanel Poder’s Blog (http://blog.tanelpoder.com/2009/03/21/oracle-11g-reading-alert-log-via-sql/).
This method seems to answer my questions but it only shows rdbms entries.
So I found another V$ table (undocumented) that resolves my problem : V$DIAG_ALERT_EXT.
This view is based on X$DIAG_ALERT_EXT and contains log entries about rdbms, tnslsnr etc. Now, I just have to write the code to exploit this.
1- First view for rdbms log entries:
create or replace view my_db_alert_log as
select ORIGINATING_TIMESTAMP,HOST_ID,HOST_ADDRESS,DETAILED_LOCATION,MODULE_ID,
CLIENT_ID,PROCESS_ID,USER_ID,MESSAGE_ID,MESSAGE_GROUP,MESSAGE_TEXT,PROBLEM_KEY,FILENAME
from V$DIAG_ALERT_EXT WHERE trim(COMPONENT_ID)='rdbms';
2- Another one for listener log entries:
create or replace view my_lsnr_alert_log as
select ORIGINATING_TIMESTAMP,HOST_ID,HOST_ADDRESS,DETAILED_LOCATION,MODULE_ID,
CLIENT_ID,PROCESS_ID,USER_ID,MESSAGE_ID,MESSAGE_GROUP,MESSAGE_TEXT,PROBLEM_KEY,FILENAME
from V$DIAG_ALERT_EXT WHERE trim(COMPONENT_ID)='tnslsnr';
Off course, you can add every column useful for you 😉
Now, you can query alert.log directly in SQL*Plus (for example, here’s my last hour rdbms alert log file entries):
SQL> select ORIGINATING_TIMESTAMP,DETAILED_LOCATION,MESSAGE_GROUP,MESSAGE_TEXT
2 from my_db_alert_log
3 where ORIGINATING_TIMESTAMP> systimestamp - INTERVAL '0 01:00:00.0' DAY TO SECOND(1)
4 order by 1
5 /
ORIGINATING_TIMESTAMP DETAILED_LOCATION MESSAGE_GROUP MESSAGE_TEXT
-------------------------------------- -------------------- ------------------------- --------------------------------------------------
16-NOV-11 05.33.03.090000000 PM +01:00 ALTER SYSTEM: Flushing buffer cache
16-NOV-11 05.57.16.259000000 PM +01:00 /u01/app/oracle/diag Errors in file /u01/app/oracle/diag/rdbms/db112/db
/rdbms/db112/db112/t 112/trace/db112_ora_6377.trc (incident=139371):
race/db112_ora_6377. ORA-00700: erreur logicielle interne, arguments :
trc [kgerev1], [600], [600], [700], [], [], [], [], []
, [], [], []
16-NOV-11 05.57.16.262000000 PM +01:00 Incident details in: /u01/app/oracle/diag/rdbms/db
112/db112/incident/incdir_139371/db112_ora_6377_i1
39371.trc
16-NOV-11 05.57.16.943000000 PM +01:00 /u01/app/oracle/diag Generic Internal Error Errors in file /u01/app/oracle/diag/rdbms/db112/db
/rdbms/db112/db112/t 112/trace/db112_ora_6377.trc (incident=139372):
race/db112_ora_6377. ORA-00600: code d'erreur interne, arguments : [],
trc [], [], [], [], [], [], [], [], [], [], []
16-NOV-11 05.57.16.946000000 PM +01:00 Incident details in: /u01/app/oracle/diag/rdbms/db
112/db112/incident/incdir_139372/db112_ora_6377_i1
39372.trc
16-NOV-11 05.57.17.278000000 PM +01:00 Dumping diagnostic data in directory=[cdmp_2011111
6175717], requested by (instance=1, osid=6377), su
mmary=[incident=139371].
16-NOV-11 05.57.17.544000000 PM +01:00 Use ADRCI or Support Workbench to package the inci
dent.
See Note 411.1 at My Oracle Support for error and
packaging details.
16-NOV-11 05.57.18.425000000 PM +01:00 Dumping diagnostic data in directory=[cdmp_2011111
6175718], requested by (instance=1, osid=6377), su
mmary=[incident=139372].
16-NOV-11 05.57.19.201000000 PM +01:00 ami_comp Sweep [inc][139372]: completed
16-NOV-11 05.57.19.220000000 PM +01:00 ami_comp Sweep [inc][139371]: completed
16-NOV-11 05.57.19.222000000 PM +01:00 ami_comp Sweep [inc2][139372]: completed
16-NOV-11 05.57.19.222000000 PM +01:00 ami_comp Sweep [inc2][139371]: completed
12 rows selected.
And for the listener.log
SQL> select ORIGINATING_TIMESTAMP,DETAILED_LOCATION,MESSAGE_GROUP,MESSAGE_TEXT
2 from my_lsnr_alert_log
3 where ORIGINATING_TIMESTAMP> systimestamp - INTERVAL '0 01:00:00.0' DAY TO SECOND(1)
4 order by 1
5 /
ORIGINATING_TIMESTAMP DETAILED_LOCATION MESSAGE_GROUP MESSAGE_TEXT
-------------------------------------- -------------------- ------------------------- --------------------------------------------------
16-NOV-11 05.11.24.147000000 PM +01:00 16-NOV-2011 17:11:24 * service_update * db112 * 0
16-NOV-11 05.11.54.266000000 PM +01:00 16-NOV-2011 17:11:54 * service_update * db112 * 0
16-NOV-11 05.12.24.385000000 PM +01:00 16-NOV-2011 17:12:24 * service_update * db112 * 0
16-NOV-11 05.12.54.487000000 PM +01:00 16-NOV-2011 17:12:54 * service_update * db112 * 0
16-NOV-11 05.13.24.573000000 PM +01:00 16-NOV-2011 17:13:24 * service_update * db112 * 0
16-NOV-11 05.13.54.818000000 PM +01:00 16-NOV-2011 17:13:54 * service_update * db112 * 0
16-NOV-11 05.14.25.011000000 PM +01:00 16-NOV-2011 17:14:25 * service_update * db112 * 0
16-NOV-11 05.14.28.013000000 PM +01:00 16-NOV-2011 17:14:28 * service_update * db112 * 0
16-NOV-11 05.14.55.085000000 PM +01:00 16-NOV-2011 17:14:55 * service_update * db112 * 0
16-NOV-11 05.15.28.207000000 PM +01:00 16-NOV-2011 17:15:28 * service_update * db112 * 0
16-NOV-11 05.15.46.267000000 PM +01:00 16-NOV-2011 17:15:46 * service_update * db112 * 0
16-NOV-11 05.15.52.297000000 PM +01:00 16-NOV-2011 17:15:52 * service_update * db112 * 0
Data ... as usual 
Reblogged this on MY DBA Notes.