//
you're reading...
JDBC

Test JDBC Thin connection to Oracle RAC database (11gR2)

Test JDBC Thin connection to Oracle RAC database (11gR2)

Set the classpath

C:> set classpath="C:\Program Files\Java\jdk1.8.0_40\lib;C:\app\oracle\product\11.2.0\client_1\jdbc\lib\ojdbc6.jar;."

Oracle Client configuration


testdb=
  (DESCRIPTION=
    (FAILOVER=ON)(LOAD_BALANCE=ON)
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=testdb-cluster-scan.newzonetech.com)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=testdboltp.newzonetech.com)
    )
  )

testdb1=
  (DESCRIPTION=
    (FAILOVER=ON)(LOAD_BALANCE=ON)
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=testdbt01.newzonetech.com)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SID=testdb1)
    )
  )

Database Configuration

[oracle@testdbt01 ~]$ srvctl status database -d testdb
Instance testdb1 is running on node testdbt01
Instance testdb2 is running on node testdbt02

[oracle@testdbt01 ~]$ srvctl status service -d testdb
Service testdboltp.newzonetech.com is running on instance(s) testdb1,testdb2
[oracle@testdbt01 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 22-APR-2016 13:08:54
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                21-APR-2016 10:35:11
Uptime                    1 days 2 hr. 33 min. 43 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0.2/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/testdbt01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.208.4.21)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.208.4.22)(PORT=1521)))
Services Summary...
Service "+APX" has 1 instance(s).
  Instance "+APX1" status READY, has 1 handler(s) for this service...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1" status READY, has 1 handler(s) for this service...
Service "testdb.newzontech.com" has 1 instance(s).
  Instance "testdb1" status READY, has 1 handler(s) for this service...
Service "testdbXDB.newzontech.com" has 1 instance(s).
  Instance "testdb1" status READY, has 1 handler(s) for this service...
Service "testdboltp.newzontech.com" has 1 instance(s).
  Instance "testdb1" status READY, has 1 handler(s) for this service...
The command completed successfully

[oracle@testdbt01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 22 13:10:16 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> show parameter remote_listener;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_listener                      string      testdb-cluster-scan:1521
SQL>

JDBC Thin connection methods

For single instance Oracle database, the connection string need to be like:

jdbc:oracle:thin:[USER/PASSWORD]@[HOST][:PORT]:SID.

But for Oracle RAC, the connection string need to be:

jdbc:oracle:thin:[USER/PASSWORD]@//[HOST][:PORT]/SERVICE

or

jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=service_name)))

First, test the connection for single instance

JAVA Code to test the connection

import java.sql.*;

public class QueryTest01 {

    public static void main(String[] args) {
        if (args.length == 0) {
            throw new IllegalArgumentException("Invalided Parameter:java SelectTest03 job");
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            //load the database driver, in this case use 'Oracle'
            //new OracleDriver();
            Class.forName("oracle.jdbc.driver.OracleDriver");

            //Get the database connection
	    //Connection String for single instance database
	    //testdb1 is location instance
            String dbUrl = "jdbc:oracle:thin:@testdb01.newzonetech.com:1521:testdb1"

            String username = "davie";
            String password = "test";
            conn = DriverManager.getConnection(dbUrl, username, password);

            String sql = "select * from test where job=?";
            pstmt = conn.prepareStatement(sql);
            //In this case do not add a single quotation mark
            pstmt.setString(1, args[0]);
            rs = pstmt.executeQuery();
            //Get the result set
            while(rs.next()) {
                int empno = rs.getInt("empno");
                String ename = rs.getString("ename");
                System.out.println(empno + ", " + ename);
            }
        }catch(ClassNotFoundException e) {
            e.printStackTrace();
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            }catch(SQLException e){}
        }
    }
}

complie the java code under windows

C:> javac QueryTest01.java
C:> dir
22/04/2016  11:46 a.m.    DIR          .
19/04/2016  03:23 p.m.    DIR          ..
22/04/2016  11:46 a.m.             2,671 QueryTest01.class
22/04/2016  11:47 a.m.             2,703 QueryTest01.java
C:> java QueryTest01 01
10, Tester

works well!!!

Then, test the connection for using Oracle database service

Modify the JAVA code regarding the Oracle Database service

import java.sql.*;

public class QueryTest01 {

    public static void main(String[] args) {
        if (args.length == 0) {
            throw new IllegalArgumentException("Invalided Parameter:java SelectTest03");
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            //load the database driver, in this case use 'Oracle'
            //new OracleDriver();
            Class.forName("oracle.jdbc.driver.OracleDriver");

	   //Connection String by using Database Service
	   String dbUrl = "jdbc:oracle:thin:@//testdb-cluster-scan.newzonetech.com:1521/testdboltp.newzonetech.com";

            String username = "davie";
            String password = "test";
            conn = DriverManager.getConnection(dbUrl, username, password);

            String sql = "select * from test where job=?";
            pstmt = conn.prepareStatement(sql);
            //In this case do not add a single quotation mark
            pstmt.setString(1, args[0]);
            rs = pstmt.executeQuery();
            //Get the result set
            while(rs.next()) {
                int empno = rs.getInt("empno");
                String ename = rs.getString("ename");
                System.out.println(empno + ", " + ename);
            }
        }catch(ClassNotFoundException e) {
            e.printStackTrace();
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            }catch(SQLException e){}
        }
    }
}

re-complie the java code

C:> javac QueryTest01.java
C:> dir
22/04/2016  11:46 a.m.    DIR          .
19/04/2016  03:23 p.m.    DIR          ..
22/04/2016  11:56 a.m.             2,671 QueryTest01.class
22/04/2016  11:57 a.m.             2,703 QueryTest01.java
C:> java QueryTest01 01
10, Tester

works well again!!!

Finally, test the connection for using Oracle TNS

Modify the JAVA code regarding the Oracle TNS

import java.sql.*;

public class QueryTest01 {

    public static void main(String[] args) {
        if (args.length == 0) {
            throw new IllegalArgumentException("Invalided Parameter:java SelectTest03 job");
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            //load the database driver, in this case use 'Oracle'
            //new OracleDriver();
            Class.forName("oracle.jdbc.driver.OracleDriver");

	    //Connection String for Oracle RAC database
	    jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=testdbt01.newzontech.com) (PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=testdbt02.newzontech.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=testoltp.newzonetech.com)))

            String username = "davie";
            String password = "test";
            conn = DriverManager.getConnection(dbUrl, username, password);

            String sql = "select * from test where job=?";
            pstmt = conn.prepareStatement(sql);
            //In this case do not add a single quotation mark
            pstmt.setString(1, args[0]);
            rs = pstmt.executeQuery();
            //Get the result set
            while(rs.next()) {
                int empno = rs.getInt("empno");
                String ename = rs.getString("ename");
                System.out.println(empno + ", " + ename);
            }
        }catch(ClassNotFoundException e) {
            e.printStackTrace();
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {

            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            }catch(SQLException e){}
        }
    }
}

re-complie the java code

C:> javac QueryTest01.java
C:> dir
22/04/2016  11:46 a.m.    DIR          .
19/04/2016  03:23 p.m.    DIR          ..
22/04/2016  12:05 a.m.             2,671 QueryTest01.class
22/04/2016  12:06 a.m.             2,703 QueryTest01.java
C:> java QueryTest01 01
10, Tester

Works!!!

Advertisements

About daviewning

I am an Oracle DBA

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: