Oracle … as usual

November 16, 2011

Read rdbms and listener log (xml) from SQL*Plus prompt

Filed under: Administration, Internals, Oracle — Laurent @ 6:13 PM

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
About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The WordPress Classic Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 207 other followers

%d bloggers like this: