CODEWITHSUNDEEP
javaoracle-database
Mohan
Mohan

Reputation: 71

How to do oracle TNSPING with java?

I have a details of Database TNSEntries. I need to code in Java to check the TNSPING. Is there anyway to do that?

Sample TNSENtry:

orcldb=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
(CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = myservice)
  (INSTANCE_NAME = myinstance)
)
)

I have done oracle connection with username and password using jdbc oracle driver. This TNSPING is new to me and it doesn't have username and password. could you please anyone help to code TNSPING in java?

Upvotes: 3

Views: 6164

Answers (1)

Daniel Vukasovich
Daniel Vukasovich

Reputation: 1742

TNSPING validates only network connection to Listener process, you don't need to pass username and password.

As @KonstantinV.Salikhov says, you may code a tool like the following, it will verify:

.- Network connection to listener process (if listener is UP or not like TNSPING would do)
.- Database service is UP or not (as TNSPING would identify database service name).

The tool will not use username/password as we only need the login failure event to validate that database is up and running.

JAVA CODE:

import java.sql.*;
import oracle.jdbc.pool.*;

public class ThinTnsnames {

    static String sql = null;

    public static void main(String[] args) {
        String entry_name = args[0];
        test(entry_name);
    }

    public static void test(String entry_name) {
        Connection pconnection = null;
        try {
            String l_url = "jdbc:oracle:thin:@" + entry_name;
            System.out.println( "Connection string = " + l_url );

            OracleDataSource ods = new OracleDataSource();
            ods.setURL(l_url);
            pconnection = ods.getConnection ();
        }
        catch(SQLException e) {
            int errorCode = e.getErrorCode() ;
            System.err.println("Error Code: " + errorCode) ;
            if ( errorCode == 12514 ) {
                System.err.println("Listener is UP but database is DOWN");
            }
            if ( errorCode == 17002 ) {
                System.err.println("Listener is DOWN");
            }
           if ( errorCode == 1017 ) {
                System.err.println("Listener is UP and database is UP");
            }
        }
        finally {
            try {
                if ( pconnection != null ) {
                    pconnection .close();
                }
            }
            catch(Exception e) {
                e.printStackTrace();
            }
        }
    }
}

You can use your own tnsnames.ora file in another directory to test it, for example in "/home/oracle/2" directory:

[oracle@ora12c 2]$ cat /home/oracle/2/tnsnames.ora

orcldb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora12c.node.com)(PORT = 15300))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db02.node.com)
      (INSTANCE_NAME = db02)
    )
  )

And then you invoke it this way:

export JAVA_HOME=/opt/java/jdk1.7.0_71
export PATH=$JAVA_HOME/bin:$PATH

export CLASSPATH=/home/oracle/2/ojdbc7.jar:.

java -Doracle.net.tns_admin=/home/oracle/2 ThinTnsnames orcldb

Possible outputs are:

Connection string = jdbc:oracle:thin:@orcldb
Error Code: 1017
Listener is UP and database is UP

Connection string = jdbc:oracle:thin:@orcldb
Error Code: 12514
Listener is UP but database is DOWN

Connection string = jdbc:oracle:thin:@orcldb
Error Code: 17002
Listener is DOWN

You can verify that no username is being used during connection by querying dba_audit_trail view as follows:

select username,action_name,returncode from dba_audit_trail
where action_name like 'LOG%' ;

USERNAME             ACTION_NAME                  RETURNCODE
-------------------- ---------------------------- ----------
                     LOGON                              1017

Upvotes: 4

Related Questions