Read rdbms and listener log.xml files with SQL

We recently upgraded our BI environment in 11gR2 (11.2.0.4). Yes, October 2014 and we upgraded in 11gR2 while the end of premium 11gR2 support in January 2015, fortunately first year of extended support is offered…

Processes initialization parameter has not been changed but we reach the limit (200) and I wanted to check how many connections were initiated over the window where we faced issues. I found the nice presentation of Tanel Poder located at Troubleshooting Complex Performance Issues – Part1 and found the information I wanted using the 10gR2 old fashion listener.log in old text format.

So, as suggested, using the always useful grep command:

server1{root}# grep '14-OCT-2014' listener.log | grep 'establish' | awk '{print $1 " " $2 }' | awk -F: '{print $1}' | sort | uniq -c
 107 14-OCT-2014 11
2995 14-OCT-2014 12
3245 14-OCT-2014 13
1461 14-OCT-2014 14
 207 14-OCT-2014 15
 216 14-OCT-2014 16
  71 14-OCT-2014 17
 319 14-OCT-2014 18
 157 14-OCT-2014 19
 242 14-OCT-2014 20
 111 14-OCT-2014 21
 148 14-OCT-2014 22
 115 14-OCT-2014 23

Focused between 13:00 and 13:59 with similar command:

server1{root}# grep '14-OCT-2014 13' listener.log | grep 'establish' | awk '{print $1 " " $2 }' | awk -F: '{print $1":"$2}' | sort | uniq -c
 119 14-OCT-2014 13:00
 128 14-OCT-2014 13:01
 111 14-OCT-2014 13:02
 125 14-OCT-2014 13:03
 122 14-OCT-2014 13:04
 116 14-OCT-2014 13:05
 126 14-OCT-2014 13:06
 138 14-OCT-2014 13:07
 114 14-OCT-2014 13:08
 125 14-OCT-2014 13:09
 139 14-OCT-2014 13:10
 141 14-OCT-2014 13:11
 134 14-OCT-2014 13:12
 134 14-OCT-2014 13:13
 129 14-OCT-2014 13:14
 136 14-OCT-2014 13:15
 152 14-OCT-2014 13:16
 153 14-OCT-2014 13:17
 146 14-OCT-2014 13:18
 134 14-OCT-2014 13:19
 134 14-OCT-2014 13:20
  91 14-OCT-2014 13:21
  86 14-OCT-2014 13:22
  65 14-OCT-2014 13:23
  56 14-OCT-2014 13:24
  57 14-OCT-2014 13:25
  39 14-OCT-2014 13:26
  41 14-OCT-2014 13:27
  14 14-OCT-2014 13:28
   1 14-OCT-2014 13:29

And saw that first 20 minutes have a taste of logon storm…

I had almost what I wanted but tried to go further using the log.xml XML file of listener (same as alert.log has been done at listener log file level). As a reminder the log.xml file is located at:

diagnostic_dest/diag/tnslsnr/server name/listener name/trace

I initially started to read documentation on how to fetch and read an XML file into database using the XML DB features but at the end told to myself: how is this possible that Oracle did not implement it by default. Finally found Tanel blog post Oracle 11g: Reading alert log via SQL and as usual the post is accessing information using X$ tables. But I think the method presented in this blog post Read rdbms and listener log (xml) from SQL*Plus prompt is more attractive (you can select V$ view with SQL Developer for example). What is also nice is the capability to access listener and database log.xml files in one central view.

Even if V$DIAG_ALERT_EXT is undocumented you can look at available views using something like:

SQL> SELECT view_name
FROM dba_views
WHERE owner='SYS'
AND view_name LIKE 'V_$DIAG%'
ORDER BY view_name;
 
VIEW_NAME
------------------------------
V_$DIAG_ADR_CONTROL
V_$DIAG_ADR_INVALIDATION
V_$DIAG_ALERT_EXT
V_$DIAG_AMS_XACTION
V_$DIAG_CRITICAL_ERROR
V_$DIAG_DDE_USER_ACTION
V_$DIAG_DDE_USER_ACTION_DEF
V_$DIAG_DDE_USR_ACT_PARAM
V_$DIAG_DDE_USR_ACT_PARAM_DEF
.
.
.

But when accessing the view I got the below error message:

select MESSAGE_TEXT from v$diag_alert_ext where COMPONENT_ID='tnslsnr';
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 8312
Session ID: 170 Serial number: 10843

And in alert log file saw:

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x2EA2550880008] [PC:0x400000001411B810, kgscDump()+2128] [flags: 0x0, count: 2]
Errors in file /ora_edwhubqa/dump/edwhubqa/diag/rdbms/edwhubqa/edwhubqa/trace/edwhubqa_ora_10322.trc  (incident=34028):
ORA-07445: exception encountered: core dump [kgscDump()+2128] [SIGSEGV] [ADDR:0x2EA2550880008] [PC:0x400000001411B810] [Address not mapped to object] []
ORA-07445: exception encountered: core dump [_memcpy()+7072] [SIGSEGV] [ADDR:0x9FFFFFFFEF401000] [PC:0xC0000000003B3B60] [Invalid Permissions for object] []
Incident details in: /ora_edwhubqa/dump/edwhubqa/diag/rdbms/edwhubqa/edwhubqa/incident/incdir_34028/edwhubqa_ora_10322_i34028.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

This is clearly documented as a bug in ORA-600 [1:dbktDefFileInit] ORA-07445 [dbgtrRecNewestGet()+8] ORA-48216 In Alert Log (Doc ID 1679553.1) in My Oracle Support web site, no patch available in 11.2.0.4… So a bit disappointed on this story but I created this blog post to find information again when I will be in 12c… 🙂

About Post Author

Share the knowledge!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>