At the beginning I have to apologize everyone who was waiting on that post and I will try to keep posting more regular.
This time before I will present connection test results I need to clarity one thing.
Instance name in One Node RAC can change on each node. This is related to way how instance is started. In case of Omotion migration there is a situation when both instances are up and running so both instances have to have different name, ex.
- testone_1 is running on rac1
- testone_2 is running on rac2
What happen if one of servers with crash with running instance (ex. rac1 will crashed).
ClusterWare status is showing that last running instance was testone_1 and this instance will be migrated to other node in cluster. This mean that testone_1 instance will be started on rac2 server.
If we fix rac1 server and we want to failback our database using Omotion instance will be started on rac1 server but according to restriction described in previous point instance name has to be different and in that case instance name will be testone_2. So that mean that now we have instance:
ClusterWare status is showing that last running instance was testone_1 and this instance will be migrated to other node in cluster. This mean that testone_1 instance will be started on rac2 server.
If we fix rac1 server and we want to failback our database using Omotion instance will be started on rac1 server but according to restriction described in previous point instance name has to be different and in that case instance name will be testone_2. So that mean that now we have instance:
- testone_1 running on rac2
- testone_2 running on rac1
Of course after session migration only one instance will be running but still with that same name.
Where is a problem? You can’t hardcoded instance name and server name in any monitoring tool because instance name can be vary depend on fail over scenario. You should use raconestatus to figure out what is a current status of database.
Now is a time to make a last tests with Java and Fast Connection Failover client.
I have used following code based on Oracle Example with FCF functionality.
After compilation it is high time to start tests:
Case no 1– both servers are up, instance is running on rac1
I have started Oracle test program and run Omotion in rac1 forcing instance migration to rac2.
Below is output from test program:
Below is output from test program:
Url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=rac2-vip)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=serviceone)))
Instance name: testone_1
FCF Activ(cache): 1
FCF Avail(cache): 9
Url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=rac2-vip)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=serviceone)))
java.sql.SQLException: Closed Connection
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:207)
at oracle.jdbc.driver.OracleStatement.ensureOpen(OracleStatement.java:3512)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1244)
at FCFDemo2.main(FCFDemo2.java:62)
getErrorCode=17008
getSQLState=null
getMessage=Closed Connection
Url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=rac2-vip)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=serviceone)))
Instance name: testone_2
FCF Activ(cache): 1
FCF Avail(cache): 0
As excepted session has been failover from instance to another without any problems with one raised exception.
So let’s test it once again
Url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=rac2-vip)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=serviceone)))
Instance name: testone_2
FCF Activ(cache): 1
FCF Avail(cache): 9
Url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=rac2-vip)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=serviceone)))
java.sql.SQLException: Io exception: Software caused connection abort: recv failed
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:254)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:805)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1030)
at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:829)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1123)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1263)
at FCFDemo2.main(FCFDemo2.java:62)
getErrorCode=17002
getSQLState=null
getMessage=Io exception: Software caused connection abort: recv failed
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
The Connection descriptor used by the client was:
(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=rac2-vip)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=serviceone)))
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:260)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:386)
at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:413)
at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:164)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:34)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:752)
at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:296)
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:220)
at oracle.jdbc.pool.OracleConnectionPoolDataSource.getPhysicalConnection(OracleConnectionPoolDataSource.java:156)
at oracle.jdbc.pool.OracleConnectionPoolDataSource.getPooledConnection(OracleConnectionPoolDataSource.java:93)
at oracle.jdbc.pool.OracleImplicitConnectionCache.makeCacheConnection(OracleImplicitConnectionCache.java:1529)
at oracle.jdbc.pool.OracleImplicitConnectionCache.getCacheConnection(OracleImplicitConnectionCache.java:463)
at oracle.jdbc.pool.OracleImplicitConnectionCache.getConnection(OracleImplicitConnectionCache.java:332)
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:403)
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:188)
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:164)
at FCFDemo2.main(FCFDemo2.java:77)
C:\Downloads\fcf_stepbystep>rem Restore old var values
C:\Downloads\fcf_stepbystep>set ORACLE_HOME=C:\oracle\product\10.2.0\db_1
Opps what happen ? We lost a connection and program has been terminated.
Let’s take a look on raised exceptions – first one is related to session closure on active instance. In code there is catch section which catch exception and try to reopen connection. But in that case a new started instance has not been registered in listener yet and another exception came in. This one was not catch by any section and demo has been terminated.
This same behaviour appear in SQL*Plus tests – in my opinion information about instance availability is sending before service is registered in listener. Maybe this is only my VM installation issue but if my assumption about order of event is correct it can happen everyware.
Case no 2 session with transactions
Base on Oracle example code I have added transactions. Below is my version of code:
//
// last revised 01/06/2006 - Adding connection pool exposure
// revised 01/05/2006 - kpm use VIP IP addresses.
// revised 12/21/2005 - PC
// ...........
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.*;
import java.sql.Statement;
import java.util.Properties;
import java.util.*;
import oracle.jdbc.pool.OracleDataSource;
import oracle.jdbc.pool.*;
import oracle.jdbc.*;
public class FCFDemo2 {
public static void main(String[] args) throws InterruptedException {
try {
OracleDataSource ods = new OracleDataSource();
OracleConnectionCacheManager occm = null;
ods.setUser("system");
ods.setPassword("oracle");
String cacheName = "cache";
String dbURL="jdbc:oracle:thin:@"
+"(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)" +
"(ADDRESS=(PROTOCOL=TCP)" +
"(HOST=rac1-vip)(PORT=1521))" +
"(ADDRESS=(PROTOCOL=TCP)" +
"(HOST=rac2-vip)(PORT=1521)))" +
"(CONNECT_DATA=(SERVICE_NAME=serviceone)))";
System.out.println("Url=" + dbURL );
ods.setURL(dbURL);
System.out.println("Url=" + dbURL );
ods.setConnectionCachingEnabled(true);
Properties prop = new Properties();
prop.setProperty("MinLimit", "5");
prop.setProperty("MaxLimit", "40");
prop.setProperty("InitialLimit", "10");
prop.put (oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR,"" + (5000)); // 5000ms
ods.setConnectionCacheProperties(prop);
ods.setFastConnectionFailoverEnabled(true);
ods.setONSConfiguration("nodes=rac1-vip:6200,rac2-vip:6200");
ods.setConnectionCacheName(cacheName); // moved from above
occm = OracleConnectionCacheManager.getConnectionCacheManagerInstance();
Connection conn = ods.getConnection();
conn.setAutoCommit(false);
System.out.println(conn);
Statement stmt=conn.createStatement(); // fixed
ResultSet rs = null;
int stop = 0;
rs =stmt.executeQuery("truncate table marcin");
int i = 0;
int j=0;
String name = new String();
while (true){
try {
//System.out.println("Url=" + dbURL );
rs =stmt.executeQuery("select instance_name from v$instance");
while(rs.next()) {
name = rs.getString(1);
System.out.println("Instance name: " + name);
System.out.println("FCF Activ(" + cacheName + "): " +
occm.getNumberOfActiveConnections(cacheName));
System.out.println("FCF Avail(" + cacheName + "): " +
occm.getNumberOfAvailableConnections(cacheName)+"\n");
PreparedStatement pstmt = conn.prepareStatement ("insert into marcin values (?, ?)");
pstmt.setInt (1, i);
pstmt.setString (2, name);
pstmt.execute ();
System.out.println("inserted " + i +" \n");
Thread.sleep(1000);
conn.commit();
System.out.println("Commited " + i + " \n");
i++;
}
catch (OracleSQLException sqle) {
//sqle.printStackTrace();
System.out.println("getErrorCode=" + sqle.getErrorCode());
System.out.println("getSQLState=" + sqle.getSQLState());
System.out.println("getMessage=" + sqle.getMessage());
//Thread.sleep(1000);
System.out.println("Try number " + j + "\n");
j++;
try {
conn =ods.getConnection(); //Re-get the conn
stmt =conn.createStatement();
} catch (OracleSQLException sqle1) {
System.out.println("getErrorCode - reconnect =" + sqle.getErrorCode());
System.out.println("getSQLState - reconnect =" + sqle.getSQLState());
System.out.println("getMessage - reconnect=" + sqle.getMessage());
sqle1.printStackTrace();
}
}
Thread.sleep(1000);
System.out.println("New iteration \n");
}
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
Start inserting rows.
Instance name: testone_1
FCF Activ(cache): 1
FCF Avail(cache): 9
inserted 185
Commited 185
Instance name: testone_1
FCF Activ(cache): 1
FCF Avail(cache): 9
inserted 186
Commited 186
java.sql.SQLException: Closed Connection
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:207)
at oracle.jdbc.driver.OracleStatement.ensureOpen(OracleStatement.java:3512)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1244)
at FCFDemo2.main(FCFDemo2.java:70)
getErrorCode=17008
getSQLState=null
getMessage=Closed Connection
Try number 0
Instance name: testone_2
FCF Activ(cache): 1
FCF Avail(cache): 0
inserted 187
Commited 187
Instance name: testone_2
FCF Activ(cache): 1
FCF Avail(cache): 0
inserted 188
Commited 188
Instance name: testone_2
FCF Activ(cache): 1
FCF Avail(cache): 0
As you can see transaction got a exception but because there is a loop in the code transaction has been replay and successfully inserted in database.
SQL> select id,cos from system.marcin where id > 184 order by id;
ID INSTANCE_NAME
---------- ------------------------------
185 testone_1
186 testone_1
187 testone_2
188 testone_2
Any RAC aware software should work with RAC OneNode without any problem like with typical Oracle RAC installation.
This is last part about Oracle RAC OneNode and connection details but I’m sure not a last about RAC itself.
0 comments:
Post a Comment