Oracle RAC DB Failover, JNDI remote lookup

rajldccbzt

前一段时间production的某一个RAC DB node挂掉了，但是不管是OC4J还是其他application 的JDBC Connection都没有failover到live的DB node上面去，于是各种google， 先写了个test 程序看看conncetion挂了能不能自动释放，这个就涉及到JNDI的RMI lookup， 程序如下：
package com.m.toplink.connection;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;
import javax.naming.Context;
import javax.naming.InitialContext;
import junit.framework.TestCase;
import oracle.jdbc.pool.OracleConnectionCacheManager;
import oracle.oc4j.sql.ManagedDataSource;
import org.junit.Test;
public class ConnectionPoolFailOverTest extends TestCase {
@Test
public void testGetConnection() throws Exception {
Hashtable<String,String> env1 = new Hashtable<String,String>();
env1.put("java.naming.factory.initial", "com.evermind.server.rmi.RMIInitialContextFactory");
env1.put("java.naming.provider.url", "opmn:ormi://localhost:6007/");
//注意默认是ormi://localhost:23791/
env1.put("java.naming.security.principal", "oc4jadmin");
env1.put("java.naming.security.credentials", "oc4jadmin");
Context context1 = new InitialContext(env1);
ManagedDataSource datasource = (ManagedDataSource) context1.lookup("jdbc/OracleAdminDS");
List<Connection> connections=new ArrayList<Connection>();
for (int i = 0; i < 30; i++) {
try{
Connection conn = datasource.getConnection();
connections.add(conn);
conn.setAutoCommit(false);
Statement stat= conn.createStatement();
ResultSet rs= stat.executeQuery("select instance_name from v$instance");
while(rs.next()){
System.out.println(i+"="+rs.getString(1));
}
Thread.sleep(10000);
ResultSet rs1= stat.executeQuery("select instance_name from v$instance");
rs1= stat.executeQuery("select instance_name from v$instance");
while(rs1.next()){
System.out.println(i+"in="+rs1.getString(1));
}
System.out.println(conn);
System.out.println(conn.isClosed());
Map status1= OracleConnectionCacheManager.getConnectionCacheManagerInstance().getStatistics("CS2AdminDSPool_default");
for(Object oneKey: status1.keySet()){
System.out.println(oneKey+"="+status1.get(oneKey));
}
if(i==3){
System.out.println(conn.isClosed());
String [] cacheNames= OracleConnectionCacheManager.getConnectionCacheManagerInstance().getCacheNameList();
int activeConn= OracleConnectionCacheManager.getConnectionCacheManagerInstance().getNumberOfActiveConnections("CS2AdminDSPool_default");
System.out.println("activeConn="+activeConn);
int avaliableConn= OracleConnectionCacheManager.getConnectionCacheManagerInstance().getNumberOfAvailableConnections("CS2AdminDSPool_default");
System.out.println("avaliableConn="+avaliableConn);
Map status= OracleConnectionCacheManager.getConnectionCacheManagerInstance().getStatistics("CS2AdminDSPool_default");
for(String cacheName: cacheNames){
System.out.println(cacheName);
}
for(Object oneKey: status.keySet()){
System.out.println(oneKey+"="+status.get(oneKey));
}
System.out.println(conn);
try{
Statement invalidStat= connections.get(0).createStatement();
ResultSet invalidRs= invalidStat.executeQuery("select instance_name from v$instance");
while(invalidRs.next()){
System.out.println(i+"invalid="+invalidRs.getString(1));
}
// OracleConnectionCacheManager.getConnectionCacheManagerInstance().
}catch(SQLException e){
System.out.println();
// OracleConnectionCacheManager.getConnectionCacheManagerInstance().getNumberOfActiveConnections();
//OracleConnectionCacheManager.getConnectionCacheManagerInstance().getCacheNameList();
if (OracleConnectionCacheManager.getConnectionCacheManagerInstance().isFatalConnectionError(e)) {
System.out.println("FCF execption1");
}
System.out.println("FCF execption");
e.printStackTrace();
}
}
// conn.commit();
//conn.close();
}catch(Exception e){
System.out.println("In refresh");
//   OracleConnectionCacheManager.getConnectionCacheManagerInstance().refreshCache("CS2AdminDSPool_default",  OracleConnectionCacheManager.REFRESH_INVALID_CONNECTIONS);
//   OracleConnectionCacheManager.getConnectionCacheManagerInstance().purgeCache("CS2AdminDSPool_default",  true);
System.out.println("Refreshed");
Thread.sleep(5000);
e.printStackTrace();
}
}
Thread.sleep(20000);
}
}

在test 环境下面的RAC DB有2个node，RAC 环境的ONE ONS（即某个RAC DB node挂了以后会自动广播一个event，让其他node都知道）之类的都设置好了，发现上面的代码确实没有释放挂掉的connection，之后找了好多资料，发现OC4J JNDI datasource的某个property （property-check-interval="60"
默认是15分钟check一次）没有设置，在data-sources.xml里面加了这个property以后，发现上面的代码FCF其实是OK的，apply上production,不过不能随便弄production,没办法verify.