1

We have set up RAC database on server. We can connect to database from sqlplus on the server using SCAN address. However, if I try to connect to RAC using SQL developer from client machine using SCAN address-based TNS entry, it shows the error

12-APR-2016 22:33:07 * (CONNECT_DATA=(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=User))(SERVICE_NAME=racdb)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.227.110)(PORT=63694)) * establish * racdb * 12516
TNS-12516: TNS:listener could not find available handler with matching protocol stack

Client machine is behind the firewall. Here is the TNS entry.

RACDB =
  (DESCRIPTION =
    (LOAD_BALANCE = ON)
    (FAILOVER = ON)
    (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.149)(PORT = 1521))
            (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.150)(PORT = 1521))
            (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.151)(PORT = 1521))
        )
    (CONNECT_DATA =
      (SERVICE_NAME = racdb)
    )
  )

We cannot use DNS to resolve SCAN IPs from client side, so we used the IP address on the client machine. And IP addresses of SCAN are converted on client side using NAT. Below is their real addresses set on the server

# SCAN
#192.168.227.149 racdb-scan.esplink.mn racdb-scan
#192.168.227.150 racdb-scan.esplink.mn racdb-scan
#192.168.227.151 racdb-scan.esplink.mn racdb-scan

If we change the IP address of TNS entry to VIP addresses, the connection is successful and I can connect to RAC database.

Is it wrong to use SCAN addresses to connect to RAC?

1

Looks like the JDBC 11g NAT+RAC issue.

JDBC Connections Using SCAN Fail With ORA-12516/ORA-12520 (Doc ID 1555793.1)

JDBC thin 11g 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

The above note suggests upgrading the JDBC driver to 12c.

Whenever you connect through SCAN, your connection is redirected. Previous versions of the Oracle JDBC driver can not handle that properly.

If you download the latest SQL Developer, that comes with the 12.1.0.2 JDBC driver.

Another solution that I prefer is to use OCI with an Oracle Client instead of JDBC (this makes possible to cancel long-running queries in SQL Developer). After installing the Oracle Client, you can configure this in Tools / Preferences / Database / Advanced.

4
  • I used OCI in SQL Developer as you advised. The ORA-12516 error is gone now. However, when the SCAN redirects me to VIP address, the VIP address is not the hostname but the IP address (192.168.227.147) is returned. On the firewall, there is no rule to allow the client to access that IP address. From client 172.20.1.XX to the server 192.168.227.147 is not possible. I used the latest SQL Developer and the oracle instant client 12c. Is it possible to set that SCAN redirects to VIP hostname? – babuuz Apr 13 '16 at 6:20
  • @babuuz By default the clusterware registers the VIP addresses with IP address. You can override this behaviour by setting the local_listener parameter manually: ALTER SYSTEM SET local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip.domain.com)(PORT=1521))' SCOPE=BOTH SID='racdb1'; and ALTER SYSTEM SET local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip.domain.com)(PORT=1521))' SCOPE=BOTH SID='racdb2';. After this, you can check in the output of lsnrctl services listener_scan1 the addresses where your requests will be redirected. – Balazs Papp Apr 13 '16 at 7:45
  • Thank you very much. You saved me a lot of trouble. I set local listener as you said and specified VIP hostnames in hosts file. – babuuz Apr 13 '16 at 8:10
  • btw, SQLDev 4.1 ships with the 12c (12.1.0.2) JDBC driver... – thatjeffsmith Apr 13 '16 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.