JDBC Connections Using SCAN Fail With ORA-12516/ORA-12520 (

Maclean Liu(刘相兵

APPLIES TO:

JDBC - Version 11.1.0.6 and later
Information in this document applies to any platform.
SYMPTOMS

JDBC thin connections using  Single Client Access Name (SCAN)  fail with errors:

ORA-12520: TNS:listener could not find available handler for requested type of server
ORA-12516: TNS : listener could not find instance with matching protocol stack

when client and database are on different networks and Network Address Translation (NAT) router is used for translation.
JDBC OCI and SQL*Plus connections work fine. JDBC connections using the HOST vip names work fine over the NAT router, only connections using SCAN fail. JDBC thin connections using SCAN without NAT router work without any problems



CHANGES

Network Devices like NAT, Load Balancers and usage of Domain Name Server (DNS) aliases for the SCAN name cause this failure.

CAUSE

JDBC connections that are redirected by network devices or use DNS aliases for the SCAN name in the connect string are unable to establish a connection with the SCAN listener. This is because JDBC is unable to indicate to the SCAN listener that it is a redirected connection, and the Host Name/ IP Address in the connect packet could be different from the actual end point ip address of the SCAN listener. For example, a JDBC connection could use ipaddr1 in the connect string and get redirected by a network device to the SCAN Listener which listens on ipaddr2 and the connection is refused since both the ip addresses do not match. SQL*Plus and OCI clients use a redirect count to inform the SCAN listener about a redirected connection, and hence the connections are not rejected.

The following bug has been logged for this issue:

Bug 11853850 - JDBC THIN CONNECTIONS OVER SCAN AND NAT FAIL WITH ORA-12516


JDBC 11g and below use a lower version of NS , which does not keep a redirect count.  JDBC 12C uses a later version of NS like OCI clients and will not encounter this problem.

SOLUTION

Upgrade to JDBC 12C

(OR)

Apply Patch 17284368 on JDBC 11.2.0.3




REFERENCES

BUG:11853850 - JDBC THIN CONNECTIONS OVER SCAN AND NAT FAIL WITH ORA-12516
BUG:17284368 - ORA-12516, TNS:LISTENER COULD NOT FIND AVAILABLE HANDLER WITH MATCHING PROTOCOL

whutabs

谢刘大分享！哈，原来SCAN 也有BUG，虽然我的11gRAC配置了SCAN，但我一直给应用推荐的是10g的VIP方式，嘿嘿