Oracle RAC DB Failover, JNDI remote lookup

前一段时间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.