oracle10g的12518和12560错误
发布网友
发布时间：2022-04-30 01:47
共1个回答
热心网友
时间：2022-04-13 19:06
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 <machine: oid.mohan.com, pid: 25908>
(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.
