Monday 1 June 2009

Oracle RAC (Real Application Cluster)


Important Links:

  • Implicit Connection Cache



  • FCF 

Property fcf-cache file:


# properties required for test
username=scott
password=tiger
InitialLimit=10
MinLimit=10
MaxLimit=20
onsconfig=nodes=aulnx4-vip.au.oracle.com:6200,aulnx3-vip.au.oracle.com:6200
url=jdbc:oracle:thin:@(DESCRIPTION= \
    (LOAD_BALANCE=yes) \
       (ADDRESS=(PROTOCOL=TCP)(HOST=aulnx4-vip.au.oracle.com)(PORT=1521)) \
       (ADDRESS=(PROTOCOL=TCP)(HOST=aulnx3-vip.au.oracle.com)(PORT=1521)) \
       (CONNECT_DATA=(service_name=sales2)))

Java API to support Oracle RAC

public class FCFConnectionCacheExample
{
  private OracleDataSource ods = null;
  private OracleConnectionCacheManager occm = null;
  private Properties cacheProperties = null;
  
  public FCFConnectionCacheExample() throws SQLException
  {
    // create a cache manager
    occm = OracleConnectionCacheManager.getConnectionCacheManagerInstance();

    Properties props = loadProperties("fcfcache");
    
    cacheProperties = new java.util.Properties();
    cacheProperties.setProperty("InitialLimit", (String)props.get("InitialLimit"));
    cacheProperties.setProperty("MinLimit", (String)props.get("MinLimit"));
    cacheProperties.setProperty("MaxLimit", (String)props.get("MaxLimit"));
    
    ods = new OracleDataSource();
    ods.setUser((String)props.get("username"));
    ods.setPassword((String)props.get("password"));
    ods.setConnectionCachingEnabled(true);
    ods.setFastConnectionFailoverEnabled(true);
    ods.setConnectionCacheName("MyCache");
    ods.setONSConfiguration((String)props.get("onsconfig"));
    ods.setURL((String)props.get("url"));

    occm.createCache("MyCache", ods, cacheProperties);
  }

  private Properties loadProperties (String file)
  {
    Properties prop = new Properties();
    ResourceBundle bundle = ResourceBundle.getBundle(file);

    Enumeration enumlist = bundle.getKeys();
    String key = null;

    while (enumlist.hasMoreElements()) 
    {
       key = (String) enumlist.nextElement();
       prop.put(key, bundle.getObject(key));
    }
    
    return prop;
  }
  
  public void run() throws Exception
  {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;

    String sQuery = 
        "select sys_context('userenv', 'instance_name'), " + 
               "sys_context('userenv', 'server_host'), " + 
               "sys_context('userenv', 'service_name') " + 
        "from dual";
        
    try
    {
      conn = null;
      conn = ods.getConnection();
      stmt = conn.createStatement();
      rset = stmt.executeQuery(sQuery);
      rset.next();
      System.out.println("-----------");
      System.out.println("Instance -> " + rset.getString(1));
      System.out.println("Host -> " + rset.getString(2));
      System.out.println("Service -> "  + rset.getString(3));

      System.out.println("NumberOfAvailableConnections: " +
                          occm.getNumberOfAvailableConnections("MyCache"));
      System.out.println("NumberOfActiveConnections: " +
                          occm.getNumberOfActiveConnections("MyCache"));
      System.out.println("-----------");
    }
    catch (SQLException sqle)
    {
      while (sqle != null) 
      {
          System.out.println("SQL State: " + sqle.getSQLState());
          System.out.println("Vendor Specific code: " +
                             sqle.getErrorCode());
          Throwable te = sqle.getCause();
          while (te != null) {
              System.out.print("Throwable: " + te);
              te = te.getCause();
          }
          sqle.printStackTrace();
          sqle = sqle.getNextException();
      }
    }
    finally
    {
      try
      {
        rset.close();
        stmt.close();
        conn.close();      
      }
      catch (SQLException sqle2)
      {
         System.out.println("Error during close");
      }
    }

  }  

  
  public static void main(String[] args)
  {  
    System.out.println(">> PROGRAM using JDBC thin driver no oracle client required");
    System.out.println(">> ojdbc14.jar and ons.jar must be in the CLASSPATH");
    System.out.println(">> Press CNTRL C to exit running program\n");

    try
    {
      FCFConnectionCacheExample test = new FCFConnectionCacheExample();
      while (true)
      {
        test.run();
        Thread.currentThread().sleep(10000);
      }

    }
    catch (InterruptedException e)
    {
      System.out.println("PROGRAM Ended by user");
    }
    catch (Exception ex)
    {
      System.out.println("Error Occurred in MAIN");
      ex.printStackTrace();
    }
  }
}