HA RAC Testing
// You need to import the java.sql package to use JDBC
import java.sql.*;
class TestFailover
{
public static void main (String args [])
throws Exception
{
// Load the Oracle JDBC driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
// String url = "jdbc:oracle:thin:@DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=incq128ad.idc.oracle.com)(PORT = 1521))(ADDRESS=(PROTOCOL=TCP)(HOST=hatest-ias.idc.oracle.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ravi.idc.oracle.com)))";
String url = "jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)(FAILOVER=on)(ADDRESS=(PROTOCOL=tcp)(HOST=incq128ad)(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST=hatest-ias)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ravi.idc.oracle.com)(FAILOVER_MODE=(TYPE=session)(METHOD=basic)(RETRIES=20)(DELAY=15))))";
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
for (int i = 0 ; i < 100 ; i++) {
conn= DriverManager.getConnection (url, "scott", "tiger");
// Create a Statement
stmt = conn.createStatement ();
// Select the host name from the instance you are connected to
rset = stmt.executeQuery ("select INSTANCE_NAME from v$instance");
// Iterate through the result and print the host name of the DB instance
while (rset.next ())
System.out.println (rset.getString (1));
// Close the RseultSet
rset.close();
// Close the Statement
stmt.close();
// Close the connection
conn.close();
}
}
}
Select fail-over
----------------
testrac_s=
(DESCRIPTION=
(LOAD_BALANCE=on)
(FAILOVER=on)
(ADDRESS=(PROTOCOL=tcp)(HOST=incq128ad)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=hatest-ias)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=ravi.idc.oracle.com)
(FAILOVER_MODE=
(TYPE=select)
(METHOD=basic)
(RETRIES=20)
(DELAY=5))))
Copy the tnsnames.ora file to the other machine also.
Open a sql*plus connection
sqlplus scott/tiger@testrac_s
select INSTANCE_NAME from v$instance;
Note down the instance name , for example say Ravi1
now run the query , select * from dba_extents;
While the query is running , shutdown the ravi1 instance ,
you will see a pause for few seconds and after this the select command will start running again.
Session fail-over
-----------------
testrac_b=
(DESCRIPTION=
(LOAD_BALANCE=on)
(FAILOVER=on)
(ADDRESS=(PROTOCOL=tcp)(HOST=incq128ad)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=hatest-ias)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=ravi.idc.oracle.com)
(FAILOVER_MODE=
(TYPE=session)
(METHOD=basic)
(RETRIES=20)
(DELAY=15))))
Connect to
SQL> select INSTANCE_NAME from v$instance;
INSTANCE_NAME
----------------
ravi1
-- Now Shutdown Instance ravi1
SQL> /
select INSTANCE_NAME from v$instance
*
ERROR at line 1:
ORA-25408: can not safely replay call
SQL> /
INSTANCE_NAME
----------------
ravi2
testrac_b_p=
(DESCRIPTION=
(LOAD_BALANCE=on)
(FAILOVER=on)
(ADDRESS=(PROTOCOL=tcp)(HOST=incq128ad)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=hatest-ias)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=ravi.idc.oracle.com)
(FAILOVER_MODE=
(TYPE=session)
(METHOD=preconnect)
(RETRIES=20)
(DELAY=15))))
When you say preconnect , it opens the connection to the other database.
