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
