[转载]Oracle RAC客户端连接不稳定的解决方法

Oracle RAC安装完毕后，监听、tnsname.ora按照Oracle默认配置好后，在服务器端用sqlplus连接没有问题，但是用客户端连接，会出现有时候连不上的错误，解决方法：
1、修改监听文件，增加SID_LIST_LISTENER，两台机器都要修改，修改完毕后如下：
LISTENER_ORCL1 =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))
      )
    )
)

SID_LIST_LISTENER_ORCL1 =
(SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0)
      (SID_NAME = ora1)
    )
)
2、修改tnsname.ora，两台机器都要修改，例如第一台机器：
       用Oracle的netca配置的时候，只配置了一个监听LISTENERS_ORCL，手工增加另外两个监听。
LISTENER_ORCL2 =
(ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))
)

LISTENER_ORCL1 =
(ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))
)

LISTENERS_ORCL =
(ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))
)


ORCL1 =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
)


ORCL2 =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
)

ORCL =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))
      (LOAD_BALANCE = yes)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
)
3、修改pfile，将两个本地监听设置上。
可以先导出一份spfile，修改完毕后再导入pfile。增加以下三行：
*.local_listener='LISTENER_ORCL'
ora1.local_listener='LISTENER_ORCL1'
ora2.local_listener='LISTENER_ORCL2'

另外，在启动oralce时，出现ora-32006的错误，数据库倒是起来能用。到网上搜索一通，说是oracle10g以后将*.log_archive_start=TRUE 这个参数去掉了，把这行从spfile中删除了，再启动没有报错。是否真的不需要这个参数，还需要再确认。

4、oracle驱动
经测试发现，在oracle RAC环境下，如果down掉一个实例，应用出现以下错误：

Io 异常: Software caused connection abort: socket write error

org.apache.cocoon.ProcessingException: error on execute servlet:login: javax.servlet.ServletException: error on execute servlet:login

cause: java.sql.SQLException: Io 异常: Software caused connection abort: socket write error

经反复测试，确定原因是oracle的驱动ojdbc14.jar没有放到WEB-INF/lib下的原因。此启动在公共lib下，必须COPY到当前工程下，才能实现fail-over的自动切换.

5、测试jdbc连接的程序：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class RacTest {

       /**
        * 测试RAC
        *
        * @param args
        * @throws ClassNotFoundException
        */
       public static void main(String[] args) throws ClassNotFoundException {

              String url;
              Connection conn = null;
              Statement stmt = null;
              ResultSet rs = null;

              Properties conProps = new Properties();

              conProps.put("user", "sys");
              conProps.put("password", "XXXX");
              conProps.put("internal_logon", "sysdba");


              url = "jdbc:oracle:thin:@(description= (ADDRESS_LIST =";
              url += "(address=(protocol=tcp)(host=192.168.0.1)(port=1521))"; // 服务器IP
              url += "(address=(protocol=tcp)(host=192.168.0.2)(port=1521))";
              url += "(load_balance=yes))"; // 是否负载均衡
              url += "(connect_data =";
              url += "(server = dedicated)";// 专用服务器模式 可以去掉？
              url += "(service_name=orcl)"; // 服务名
              //url += "(failover_mode =";
              //url += "(type=session)";
              //url += "(method=preconnect)";
              //url += "(retries=5)";
              //url += "(delay=15))";
              url += " ))";

              /**
               * LOAD_BALANCE = yes表示程序走sword连接的时候，将在下面的地址里面随机的选择一个，达到接点间连接均匀
               * failover表示开启TAF(Transparent Application Failover)特性，其中TYPE =           * SESSION表示当一个连接好的会话 * 的实例发生故障，系统会自动将会话切换到其他可用的实例，前台应用无须再度发起连接，但会话正在执行的SQL 需要重新执行，METHOD = * BASIC表示初始连接就连接一个接点，他还有个选项是preconnect，表示初始连接所有的 * 接点，在failover的时候可以很快就切过去，这个基本是资源浪费 RETRIES:重试次数 DELAY:重试间的延迟(以秒为单位)
               * 如果指定了RETRIES，DELAY默认为1 如果指定了DELAY，RETRIES默认为5
               */

              // String sql = "select * from oa_sfm_fb_fwgl t where t.pk_id =
              // '17233'";
              String sql = "select * from v$instance";

              try {
                     Class.forName("oracle.jdbc.driver.OracleDriver");
                     conn = DriverManager.getConnection(url, conProps);
                     // conn = DriverManager.getConnection(url, username, password);
                     stmt = conn.createStatement();
                     rs = stmt.executeQuery(sql);
                     if (rs.next()) {
                            System.out.print(rs.getString("INSTANCE_NAME"));
                     }
                     rs.close();
                     rs = null;
                     stmt.close();
                     stmt = null;
                     conn.close();
                     conn = null;
              } catch (SQLException ex) {
                     ex.printStackTrace();
              } finally {

                     if (rs != null) {
                            try {
                                   rs.close();

                                   rs = null;
                            } catch (Exception ex) {
                                   ex.printStackTrace();
                            }
                     }
                     if (stmt != null) {
                            try {
                                   stmt.close();
                                   stmt = null;
                            } catch (Exception ex) {
                                   ex.printStackTrace();
                            }
                     }
                     if (conn != null) {
                            try {
                                   conn.close();
                                   conn = null;
                            } catch (SQLException ex) {
                                   ex.printStackTrace();
                            }
                     }

              }

       }

}

第一步就是：$ORACLE_HOME/network/admin/listerer.ora那个文件，用netca自动生成时只有LISTENER_ORCL1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))
)
)
)

转载 ： http://blog.vsharing.com/songtao1971/A730963.html