如何用java进行oracle TNSPING？

问题描述

我有数据库 TNSEntries 的详细信息。我需要用Java编写代码来检查 TNSPING 。反正有吗？

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

样品 TNSENtry ：

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

我使用jdbc oracle驱动程序使用用户名和密码进行了oracle连接。这个TNSPING对我来说是新的，它没有用户名和密码。你能帮助任何人帮忙在java中编写TNSPING吗？

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?

推荐答案

TNSPING只验证与Listener进程的网络连接，你不需要传递用户名和密码。

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

正如@ KonstantinV.Salikhov所说，您可以编写如下工具代码，它将验证：

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

.-与侦听器进程的网络连接（如果侦听器是UP或不像TNSPING那样）

.-数据库服务是否为UP（因为TNSPING将标识数据库服务名称）。

.- 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代码：

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");
                                     }
                     // e.printStackTrace();
    }
    finally {
      try {
        if (pconnection != null )pconnection .close();
      }
      catch(Exception e) {
        e.printStackTrace();
      }
    }
  }

}

您可以在另一个目录中使用自己的tnsnames.ora文件进行测试，例如在/ home / oracle / 2目录中：

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)
    )
  )

然后你调用它这样：

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

可能的输出是：

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

您可以通过查询dba_audit_trail视图来验证连接期间没有使用用户名，如下所示：

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

这篇关于如何用java进行oracle TNSPING？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！