本文共 4687 字,大约阅读时间需要 15 分钟。
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