如何进行Oracle10GRAC监听当掉问题的处理

这篇文章将为大家详细讲解有关如何进行Oracle 10G RAC监听当掉问题的处理，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

创新互联2013年至今，先为古冶等服务建站，古冶等地企业，进行企业商务咨询服务。为古冶企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。

系统日志、告警日志、Clusterware日志都正常，唯一不正常的就是监听日志，监听器访问非常的频繁每1秒都有3到4个连接
，一直持续不断，最后有如下报错：
TNS-12518: TNS:listener could not hand off client connection
 TNS-12547: TNS:lost contact
  TNS-12560: TNS:protocol adapter error
   TNS-00517: Lost contact
    Linux Error: 32: Broken pipe
12-OCT-2010 08:57:03 * service_update * psrdb1 * 0
12-OCT-2010 08:57:03 * service_update * psrdb1 * 0

根据Linux Error: 32: Broken pipe查找了METALINK ID 550859.1。
发现如下文档：

In this Document
  Purpose
  Last Review Date
  Instructions for the Reader
  Troubleshooting Details
     Section I: Steps to approach ORA-12518/TNS-12518 Error: 
     Section II: Commonly Known Errors: 
     Error: 32: Broken pipe 
     Error: 11: Resource temporarily unavailable 
     Error: 12: Not enough space 
     Error: Connection Pooling limit reached 
     Error: 2: No such file or directory 
     Section III: Errors Specific to Windows
     Error: 233: Unknown error
     Error: 54: Unknown error 

Applies to:

Oracle Net Services - Version: 9.2.0.1.0 to 10.2.0.1.0 - Release: 9.2 to 10.2
Information in this document applies to any platform.

Purpose

This article discusses about how to approach ORA-12518/TNS-12518 and troubleshoot it for the resolution.

Last Review Date

January 29, 2008

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

Troubleshooting Details

Section I: Steps to approach ORA-12518/TNS-12518 Error: 

ORA-12518/TNS-12518 indicates a problem while listener hands off the client connection to the server process or dispatcher process.TNS-12518 is logged in the listener log. The client might receive ORA-12518 or some other disconnection errors like ORA-12537. Once TNS-12518 is noted in the listener log, follow the below steps to approach and resolve this error. 

Let us have a small discussion about how actually database connections are made:

In Dedicated mode, database client contacts listener and supplies the SERVICE NAME of the database. Then listener spawns a dedicated server process and hands off the client connection to this dedicated server process. TNS-12518 indicates a problem while handing off the client connection to the server process.

In Shared Server mode, database client contacts listener and supplies the SERVICE NAME of the database. Then listener hands off the client connection to one of the dispatcher configured for that service. TNS-12518 indicates a problem while handing off the client connection to the dispatcher server process.

Though this error is logged in the listener log, the listener is just the messenger, ORA-12518/TNS-12518 is mostly related to RDBMS and OS resources. 



Step 1. Is listener version compatible to the database

Check if you are using compatible listener version for your database version. 

If the database is 8i then use 8i or 9i listeners only. 10g listeners are not compatible to work with 8i databases. 
For 9i databases, 9i or 10g listeners can be used. 
For 10g databases, only 10g listeners can be used. 

The general rule is that use the higher version of the listener when there is a version mismatch between database and the listener. 




Step 2. Gather more information from listener log

The first place you would look for the TNS-12518 error is the listener log. Usually the listener log would be located under $ORACLE_HOME/network/log directory. You can use 'lsnrctl status' command output to look for the location of the listener log file.

$lsnrctl status 
- - - 
- - - 
Listener Parameter File /ora10g/home_ora10g/network/admin/listener.ora 
Listener Log File /ora10g/home_ora10g/network/log/listener.log 
- - - 
- - - 

listener log gives the complete error stack and the database service name to which the client tried to connect to. 

19-SEP-2007 13:55:34 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=test.oracle.com)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.3)(PORT=36030)) * establish * test.oracle.com * 12518
TNS-12518: TNS:listener could not hand off client connection 
 TNS-12547: TNS:lost contact 
  TNS-12560: TNS:protocol adapter error 
   TNS-00517: Lost contact 
    Linux Error: 32: Broken pipe

In the above example, listener log shows the complete error stack, the bottom error being 32 is the OS error. It also shows that the jdbc client from IP 10.10.10.3 has tried to connect to the database service 'test.oracle.com' and failed with the error 12518.

Look for the lowest error in the stack. That is the error we have to concentrate on and try to resolve it. In the above example, the lower error is 'Linux Error: 32: Broken pipe'.




Step 3. Is service handlers in blocked state

Check if the handlers are in blocked state. Check the output of the 'lsnrctl services'. Examine the status information under the database service name. From the listener log you would know which database service was affected by the 12518 error, now with the output of the 'lsnrctl services' under that service name gather more information.

Service "test.oracle.com" has 1 instance(s). 
  Instance "db10g", status READY, has 2 handler(s) for this service... 
    Handler(s): 
      "DEDICATED" established:9 refused:0 state:ready 
         LOCAL SERVER 
      "D000" established:10 refused:0 current:0 max:972 state:ready 
         DISPATCHER  
         (ADDRESS=(PROTOCOL=tcp)(HOST=oid.mohan.com)(PORT=33487))

The highlighted state should be in 'ready' state for the connection to be successful. If the state is in 'blocked' then the connection are not possible. The state of a handler could be in blocked state in the following scenario: 
i. The database parameter processes reached its value. 
ii. The database is in the process of startup or shutting down. 

In shared server mode, the number of dispatchers should be set according to the load that you expect. 'lsnrctl services' output shows the maximum number(max:997) of connections that the dispatcher would accept and the number connections refused (refused:0) by this dispatcher. If any connections refused by the dispatcher, then consider increasing the number of dispatchers. 

If you are using PFILE edit init.ora and increase the dispatchers parameter. If you are using SPFILE you can dynamically increase the dispatchers parameter by the 'alter system set' command. 




Step 4. Is local BEQ connection successful

Check if local BEQ connection to the database works fine. It also verifies if the database is up and in good condition to accept the connection. If the database is down or in a hung state then a connection request to the database by the listener will not be possible. 

Connect to the database server via telnet or ssh and check if a local bequeath SQL*Plus connection works. In other words, issue:
sqlplus username/password [Enter]

This connection bypasses the listener and directly connects to the database via the BEQ (bequeath) protocol. If this fails, then the TNS-12518 listener error is simply a result of the database issue. 

One such error is:
ORA-12560: TNS:protocol adapter error

A possible cause for this error on Microsoft Windows servers, is that the Windows Database Service has not yet been created (common when creating a "standby" instance).
Resolution for this would be to create the Windows Service first by using the "oradim" command (see the Database Admin guide for details on oradim and service creation).





Step 5. Is number of processes reached its limit

If local BEQ is successful, check the below query 

SQL> select * from v$resource_limit; 

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU 
-------------------- ------------------- --------------- ---------- ---------- 
processes 249 250 250 250 
sessions 54 82 280 280 
- - - - - -
- - - - - -

Verify if the processes or sessions reached its limit value. If these database parameters reached its limit value, then consider increasing it accordingly. 

In the above example, the processes parameter has been set to 250. It's MAX_UTILIZATION has reached the limit value of 250, so the processes parameter should be increased further to accomodate the number of incoming connections. 

Edit the init.ora and set the processes parameter to a higher value. By default, if you just increase the processes it is enough, the sessions value would automatically be increased. 

Step 6. Is OS kernal parameters configured for optimum

Database is operated by a single user, normally it would be 'oracle' user. At the Operating System level, there is a limit for the number of process spawned by a user. And also there is a limit for the total number of process running on the entire OS. 

The Oracle Database and the newly spawned processes would be owned by the 'oracle' user. And so make sure that these values are set accordingly.On Unix these values are configured through the configurable OS kernal parameters and is specific to Operating Systems. You will have check your corresponding OS documentation for your OS. 

For example for HP-UX the configurable kernal parameters are, 

maxuprc  Maximum number of processes for each non-root user 
nproc      Maximum number of processes on the system 






Step 7. Does alert log have any errors

Look in alert log and look for any errors related to memory or process during the time the error TNS-12518 occurred in the listener log. If the alert log has any memory related errors, there there is a potential memory resource issue at the OS level. 

OS memory issues can be addressed by the below: 
i.   Make sure that the OS has been configured with the enough Swap memory. In case of Windows it is called as Virtual Memory. 
ii.  Reduce the size of SGA, thus the newly spawned server process will have some more system memory available. 
iii. Reduce the PGA size, so that the newly spawned server process would occupy less memory. 
iv. If you are in DEDICATED mode, try switching to MTS mode. 

To address memory issue for 32-bit Windows: Refer Note 371983.1

If there are any memory or process related error in the alert log during the time the TNS-12518 is logged in the listener log, then those errors in the alert log should be focused on and should be solved at first. Because, the errors in the alert log is the base error for the TNS-12518 in the listener log. 

However, the errors in the alert logs are not being discussed in this article, they are out of the scope of this article.

Step 8. If using a statically defined SID_DESC in the listener.ora file for your sid, ensure that it is configured properly. 

A common mistake is to include a (PROGRAM=EXTPROC) parameter:

  (SID_LIST= 
    (SID_DESC = 
      (GLOBAL_DBNAME = ORCL.oracle.com) 
      (SID_NAME = ORCL) 
      (PROGRAM=extproc) 
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)))

This misconfiguration occurs when the PLSExtproc SID_DESC is copied, pasted and edited in the listener.ora file.  The inclusion of the PROGRAM line will cause an ORA-12518 to be returned to the client.  Here's the corrected SID_DESC for our example SID:

  (SID_LIST= 
     (SID_DESC = 
     (GLOBAL_DBNAME = ORCL.oracle.com) 
     (SID_NAME = ORCL) 
     (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)))______________________________________________________________________________________________________________________________________
======================================================================================================================== 

Section II: Commonly Known Errors:

This section lists some of the known and reported errors. It is also recommended that you refer the Section I above for a generic troubleshooting approach to the error TNS-12518.

Below shows example error stack that can be found in the listener log. The last line in the error stack shows the actual operating system name. Depending on the OS, only the operating system name would be different. For example, if you encounter error 32: Broken pipe, according to the OS, the last line in the error stack would be different only by the OS name, as shown below.

Solaris Error: 32: Broken pipe 
HPUX Error: 32: Broken pipe 
Linux Error: 32: Broken pipe

_______________________________________________________________________________________________________________________________________ 

Error: 32: Broken pipe

Error stack in listener log: 

TNS-12518: TNS:listener could not hand off client connection 
 TNS-12547: TNS:lost contact 
   TNS-12560: TNS:protocol adapter error 
     TNS-00517: Lost contact 
       IBM/AIX RISC System/6000 Error: 32: Broken pipe 


Cause: 
The error 32 indicates the communication has been broken while the listener is trying to hand off the client connection to the server process or dispatcher process. 

Action: 
1.  One of reason would be processes parameter being low, and can be verified by the v$resource_limit view. 
2. In Shared Server mode, check the 'lsnrctl services' output and see if the dispatcher has refused any connections, if so, then consider increasing the number of dispatchers. 
3. Check the alert log for any possible errors. 
4. Memory resource is also another cause for this issue. Check the swap, memory usage of the OS. 

______________________________________________________________________________________________________________________________________

Error: 11: Resource temporarily unavailable

Error stack in listener log: 
TNS-12518: TNS:listener could not hand off client connection 
 TNS-12549: TNS:operating system resource quota exceeded 
  TNS-12560: TNS:protocol adapter error 
   TNS-00519: Operating system resource quota exceeded 
    IBM/AIX RISC System/6000 Error: 11: Resource temporarily unavailable 


Cause: 
As the error indicates operating system resource has exceeded. 

Action: 
1. Increase the appropriate OS kernal parameters for 'maximum number of processes allowed per user'. 
For example for HP-UX the parameters are maxuprc and nproc. 


______________________________________________________________________________________________________________________________________

Error: 12: Not enough space

Error stack in listener log: 
 TNS-12518: TNS:listener could not hand off client connection 
   TNS-12549: TNS:operating system resource quota exceeded 
     TNS-12560: TNS:protocol adapter error 
       TNS-00519: Operating system resource quota exceeded 
         IBM/AIX RISC System/6000 Error: 12: Not enough space 


Cause: 
This is a memory related issue. The error indicates that there is not enough memory available to spawn and hand off the client connections. 
Typical problems are: 
- Out of system memory / swap 
- Out of process slots in the process table 
- Streams resources depleted 
- Out of File Handles 
- sga memory usage 


Action: 
1. Check in the alert log for any possible memory related error. 
2. Increase swap/Virtual memory if possible the available memory. 
3. SGA and PGA can be reduced to address the memory consumption. 
4. MTS mode can be used to reduce the amount of process and memory consumption. 

________________________________________________________________________________________________________________________________________

Error: Connection Pooling limit reached

Error stack in listener log: 
 TNS-12518 TNS:listener could not hand off client connection 
  TNS-12564 TNS:connection refused 
   TNS-12602 TNS: Connection Pooling limit reached 


Action: 
1. Try increasing initial number of dispatcher. 

_______________________________________________________________________________________________________________________________________ 

Error: 2: No such file or directory 

Error stack in listener log: 
 TNS-12518: TNS:listener could not hand off client connection 
  TNS-12560: TNS:proto adapter error 
   TNS-00530: Proto adapter error 
    32-bit Windows Error: 2: No such file or directory 


Error Description: 
ERROR_FILE_NOT_FOUND 
2 
The system cannot find the file specified. 

Cause: 
This indicates the database service is not actually available 


Ation: 
1.Verify if the inteneded database really up and accepting local BEQ connections. 

________________________________________________________________________________________________________________________________________ 
========================================================================================================================= 

Section III: Errors Specific to Windows

It is also recommended that you refer the Section I above for a generic troubleshooting approach to the error TNS-12518.

This section briefly describes about the errors that are encountered on Windows Operating System. TNS-12518 most commonly occurs on 32-bit OS due to its memory constraint, however TNS-12518 can occur on 64-bit OS as well. See Note 873752.1 for more information on Windows memory addressing and the 3GB switch.

_______________________________________________________________________________________________________________________________________  

Error: 233: Unknown error

Error stack in listener log: 
 TNS-12518: TNS:listener could not hand off client connection 
  TNS-12560: TNS:protocol adapter error 
   TNS-00530: Protocol adapter error 
    32-bit Windows Error: 233: Unknown error 


Error Description: 
ERROR_PIPE_NOT_CONNECTED 
233 
No process is on the other end of the pipe. 

Cause: 
The communication has been broken while the listener is trying to hand off the client connection to the server process or dispatcher process. 


Action: 
Refer Note 371983.1

_______________________________________________________________________________________________________________________________________

Error: 54: Unknown error

Error stack in listener log: 
 TNS-12518: TNS:listener could not hand off client connection 
  TNS-12571: TNS:packet writer failure 
   TNS-12560: TNS:protocol adapter error 
    TNS-00530: Protocol adapter error 
      32-bit Windows Error: 54: Unknown error 


Error Description: 
ERROR_NETWORK_BUSY 
54 
0x36 
The network is busy. 


Cause: 
This indicates a bottleneck at the network layer(TCP/IP). 


Action: 
1.Try increasing dispatchers and shared servers.

通过查看发现资源受系统或Oracle的限制等其他情况并不存在。可能是由于Step 8. If using a statically defined SID_DESC in the listener.ora file for your sid, ensure that it is configured properly. 所造成的。现在修改了listener.ora注释了 (PROGRAM=extproc) 这行。网上google一下      (PROGRAM=extproc) 是一个服务器漏洞，本来就应该处理掉，以前不知道，以后别忘记。哈哈
处理此问题记录一下，以便备查。

NND下午又报新的错误了。监听报错日志如下：
TNS-12518: TNS:listener could not hand off client connection
 TNS-12571: TNS:packet writer failure
  TNS-12560: TNS:protocol adapter error
   TNS-00530: Protocol adapter error
    Linux Error: 104: Connection reset by peer

系统日志报错：
Oct 12 14:19:44 zpdb2 kernel: tnslsnr[3554]: segfault at 0000000000000018 rip 0000003bbf070485 rsp 00007fffcef86c30 error 4

根据Linux Error: 104: Connection reset by peer
报错查询到了METALINK文章:[ID 549932.1]

Applies to:

Oracle Net Services - Version: 10.2.0.3.0 to 11.1.0.6.0
Generic UNIX

Symptoms

• There may be heavy load on the CPU shooting up to 100%. 

• The number of sessions in the database is well below the upper or maximum limit defined in the parameter  file. 

• The listener crashes suddenly during  this heavy CPU load generating the core.

• (Optional) Listener.Ora has SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF. 

Listener Log: 
------------- 

..... 
..... 
19-NOV-2007 13:40:49 * (CONNECT_DATA=(SID=ORAC)(CID=(PROGRAM=C:\pegasos\te\usys\bin\uniface.exe)(HOST=TERVI-NB179)(USER=kjokioja))) * (ADDRESS= (PROTOCOL=tcp)(HOST=10.12.152.5)(PORT=1670)) * establish * ORAC * 12518 
TNS-12518: TNS:listener could not hand off client connection 
TNS-12571: TNS:packet writer failure 
TNS-12560: TNS:protocol adapter error 
TNS-00530: Protocol adapter error 
Linux Error: 104: Connection reset by peer 
19-NOV-2007 13:40:49 * (CONNECT_DATA=(SID=ORAC)(CID=(PROGRAM=C:\pegaos\te\usys\bin\uniface.exe)(HOST=TERVI-0184A)(USER=paitasal))) * (ADDRESS=(PROTO COL=tcp)(HOST=10.12.176.136)(PORT=1574)) * establish * ORAC * 12518 
TNS-12518: TNS:listener could not hand off client connection 
TNS-12547: TNS:lost contact 
TNS-12560: TNS:protocol adapter error 
TNS-00517: Lost contact 
Linux Error: 32: Broken pipe 
......... 
........                                                                                                            

The Operating system log (/var/log/messages) may show the following :

tnslsnr[5841]: segfault at 0000000000000018 rip 0000003eab66854d rsp 0000007fbfff9230 error 4 
tnslsnr[6469]: segfault at 0000000000000018 rip 0000003eab66854d rsp 0000007fbfff9420 error 4 
tnslsnr[7375]: segfault at 0000000000000018 rip 0000003eab668bb3 rsp 0000007fbfff9c70 error 4 

Gdb on the core file generated shows the following stack :

#gdb  /home/oracle/db_1/bi/tnslsnr core.7375   Core was generated  by `/opt/oracle/ora102/bin/tnslsnr LISTENER -inherit'.  
    Program terminated with  signal 11, Segmentation fault.  
    Reading symbols from /opt/oracle/ora102/lib/libclntsh.so.10.1...(no debugging symbols found)...done. 
    ........ 
    ......... 
        (gdb) bt 
    #0  0x00000032b7468bb3 in _int_free () from  /lib64/tls/libc.so.6 
    #1  0x00000032b74691f6 in free () from /lib64/tls/libc.so.6 
    #2  0x000000000040f01f in nsglhe () 
    #3  0x00000000004114e9 in nsglma () 
    #4  0x00000000004061cb in main () 
    (gdb) where 
    #0  0x00000032b7468bb3 in _int_free () from /lib64/tls/libc.so.6 
    #1  0x00000032b74691f6 in free () from /lib64/tls/libc.so.6 
    #2  0x000000000040f01f  in nsglhe () 
    #3  0x00000000004114e9 in nsglma () 
    #4  0x00000000004061cb in main () 
                                 

The core indicates that the program terminated with signal 11, Segmentation fault . 
SIGSEGV is reported for improper memory handling .The default action for a program upon receiving 
SIGSEGV is abnormal termination. This action will end the process.

The virtual memory stats (vmstat) output may show huge paging/swapping activity .

For Example: 
------------- 

$vmstat 5 7 The si and so column under the swap section suggests the amount of memory swapped in from disk (/s)and amount of memory swapped to disk (/s) respectively. 

    procs                      memory    swap          io     system         cpu  
  r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us  sy  id  
  2  0  0  27124   3940   1160  74528   0 1304  956  5360  272   191  51  21  98  
  3  0  0  27080   3756   1180  72580   0 260  2552   388  218   429  90  10  89  
 14  2  1  26808   5096   1188  69868  84 1108 2016  9064  490   567  59  22  96  
  4  0  0  25548   5912   1192  73032   0   0   436     0  478   736  50  50   0  
  5  0  0  25548   3940   1192  73548   0   0  1560     0  301   385  93   7   0  
  1  1  1  25548   3336   1192  71800   8 176  2848   432  258   147  91   9  88  
  1  0  0  25544   4124   1200  70480 116  60   836    60  171   200  97   3  92 

Note: You may also use the top command to check the system memory usage.

Cause

Extensive paging/swapping activity is a clear  indication that the sytem is running out of the physical memory.

Solution

Solution : 

1. Increase the physical memory of the system. 

                             OR 

2. Apply the Patch 6139856 for unpublished Bug 6139856 if available for your platform.

                             OR

3. Configure Hugepages on the OS. Ref : Note 361323.1

A Note on HugePages:

Hugepages is a mechanism that allows the Linux kernel to utilise the multiple page size capabilities of modern hardware architectures.  Its important to know that utilising a huge amount of physical memory with the default page size consumes the TLB and adds processing overhead. The Linux kernel is able to set aside a portion of physical memory to be able be addressed using a larger page size. Since the page size is higher, there will be less overhead managing the pages with the TLB. In the Linux 2.6 series of kernels, hugepages is enabled using the CONFIG_HUGETLB_PAGE feature when the kernel is built. Systems with large amount of memory can be configured to utilise the memory more efficiently by setting aside a portion dedicated for hugepages. The actual size of the page is dependent on the system architecture. A typical x86 system will have a Huge Page Size of 2048 kBytes. The huge page size may be found by looking at the /proc/meminfo : 

# cat /proc/meminfo |grep Hugepagesize Hugepagesize: 2048 kB 

For more on configuring Hugepages please refer Note 361323.1

References

BUG:6752308 - LISTENER DIED BY SEGFAULT AFTER TNS ERROR
NOTE:361323.1 - HugePages on Linux: What It Is... and What It Is Not...


根据文档建议做了如下调整：
1.启用HugePage设置

2.应用补丁
系统出现的问题跟文档描述的BUG一致，Linux环境，版本10.2.0.4.0，故给集群2个节点都打上了补丁：p6139856_10204_Linux-x86-64.zip

3.启动共享服务器模式
系统连接频繁，使用常用的独占模式，服务器进程创建销毁过于频繁，报的错都是跟创建或访问服务器进程有关，根据文档的建议使用共享模式来把服务器进程固定下来，避免这种情况的发生。
参数
dispatchers=10
max_dispatchers=40
max_shared_servers=200
shared_servers=20
shared_server_sessions=200
circuits=500

调整后，根据查看调度器的状态等情况，感觉还不错，应该不会再报了吧。

p6139856_10204_Linux-x86-64.zip补丁可以在这里下载p6139856_10204_Linux-x86-64.zip。

最后还得补充一句，在RAC环境中，监听日志文件过大可能也是导致监听器自己当掉的原因之一(有一次监听器当的时候监听日志达到了4G)。

关于如何进行Oracle 10G RAC监听当掉问题的处理就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。