Thursday, November 12, 2015

SCAN and REDIRECT with Oracle RAC

If you access a ORACLE RAC, be aware that you must open a firewall rule not only for the SCAN listener, but also to all the RAC instances. Connections are NOT routed via the SCAN listeners, they are merely client-side redirected.

Failure to do so will result in the connection fail with a Socket Timeout Exception. You will be puzzled because the telnet to the listener works... the problem is that your JDBC driver is trying to connect ALSO to the instance on a different IP (possibly also PORT...).... so you should test with telnet also the RAC instance!

import java.sql.DriverManager;
import java.sql.SQLException;

public class DBPing {
        public static void main(String[] args) throws ClassNotFoundException, SQLException {
                System.out.println("length " + args.length);
                String user = args[0];
                String password = args[1];
                String url = args[2];
                String now = new java.util.Date().toString();
                System.out.println(now + " user= " + user + " password=" + password + " url=" + url);
                java.sql.Connection conn = DriverManager.getConnection(url, user, password);

java -cp /acme/appsrv/bin/wl12.1a/oracle_common/modules/oracle.jdbc_12.1.0/ojdbc6.jar:/home/userpippo DBPing dbusername dbpassword "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhostname-scan)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=myservicename)))" &^

The program will wait for connection for a while, then timeout. If you netstat BEFORE the timeout:

netstat -an | grep 1522

you will discover which IP corresponds to the RAC INSTANCE:

tcp 0 1 SYN_SENT

What is ? It's NOT the dbhostname-scan IP, but rather the IP of a RAC instance.... the SCAN listener tells the Oracle JDBC driver to do a redirect to a new IP/PORT where the RAC instance is running.

What is SYN_SENT ? .... which means that the JDBC driver sent a SYN, but the server didn't reply (because the firewall blocked the communication).

Here some doc on SCAN.
See also Oracle Support note "Port 1521 Open on Firewall But Unable to Connect Due to Errors: ORA-12535,TNS-12203 (Doc ID 361284.1)"

No comments: