|
|
IOTN :: Field Book :: ORACLE 
|
|
RAC jdbc connection test [ thin java test.java sample ]
|
|
최길호
[LIST]
|
2010-06-14 13:53:18, 조회 : 61,888 |
2019/10/19 gv$session 변경
2019/05/09 userenv('sessionid') , Integer.parseInt(args[6]) 추가
jdbc 로는 TAF 구성할수 없음
[ Exception in thread "main" java.sql.SQLException: 소켓에서 읽을 데이터가 없습니다 ] : java 연결 종료됨.
다시 실행하여 연결 재시도 : 다음 node로 바로 연결되면 정상.
FAILOVER=ON, (FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)) : 추가해도 Enable 되지 않음.
LOAD_BALANCE=OFF : ON 이면 node들중 골라서 들어감, OFF : 먼저 서술된 IP로 들어가고 연결 할수 없으면 다음 ip로 연결함.
추가
test.java
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
//// java test TIME node1-vip ndoe2-vip service_name user pwd 5[sec]
import java.sql.*;
class test {
public static void main (String args []) throws SQLException
{
try { Class.forName ("oracle.jdbc.driver.OracleDriver"); }
catch (ClassNotFoundException e) { e.printStackTrace(); }
System.out.println("Start");
while(true) {
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST="+args[1]+")(PORT=1531))(ADDRESS=(PROTOCOL=TCP)(HOST="+args[2]+")(PORT=1521)))(LOAD_BALANCE=OFF)(CONNECT_DATA=(SERVICE_NAME="+args[3]+")))",args[4],args[5]);
Statement stmt = conn.createStatement();
//ResultSet rset = stmt.executeQuery("select userenv('sessionid'),to_char(sysdate,'hh24:mi:ss'),HOST_NAME from v$instance");
// grant select on gv_$session to system;
ResultSet rset = stmt.executeQuery("select to_char(sysdate,'hh24:mi:ss'),INST_ID,count(1) from gv$session group by inst_id order by 2");
while (rset.next())
{
// System.out.println( args[0]+" "+rset.getString(1)+" "+rset.getString(2)+" "+rset.getString(3));
System.out.println( args[0]+" "+rset.getString(1)+" NODE_ID:"+rset.getString(2)+" SESSION COUNT:"+rset.getString(3));
}
try
{
Thread.sleep(Integer.parseInt(args[6]) * 1000);
stmt.close();
conn.close();
}
catch (InterruptedException e) {stmt.close();}
}
}
}
export CLASSPATH=.:$ORACLE_HOME/jdbc/lib/ojdbc14.jar
javac test.java
java test TIME node1-vip node2-vip2 DBSVR system pwd 5
test.java
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
import java.sql.*;
class test {
public static void main (String args []) throws SQLException
{
try { Class.forName ("oracle.jdbc.driver.OracleDriver"); }
catch (ClassNotFoundException e) { e.printStackTrace(); }
System.out.println("Start");
while(true) {
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.106)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.108)(PORT=1521)))(LOAD_BALANCE=OFF)(CONNECT_DATA=(SERVICE_NAME=data)))","system","pwd");
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select to_char(sysdate,'hh24:mi:ss'),HOST_NAME from v$instance");
while (rset.next())
{ System.out.println(" "+rset.getString(1)+' '+rset.getString(2)); }
try
{
Thread.sleep(5 * 1000);
}
catch (InterruptedException e) {stmt.close();} }
}
}
export CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc14.jar
export CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc5.jar
export CLASSPATH=.:$ORACLE_HOME/jdbc/lib/classes12.zip
export CLASSPATH=.:$JEUS_HOME/lib/datasource/ojdbc14.jar
export CLASSPATH=.:$ORACLE_HOME/oui/jlib/classes12.jar:$ORACLE_HOME/jdbc/lib/ojdbc5.jar
javac test.java
while true
do
java test
done
////////////////////////////////
16:28:12 cmsdb02
16:28:14 cmsdb02
16:28:17 cmsdb02
16:28:20 cmsdb02
16:28:22 cmsdb02
16:28:25 cmsdb02
16:28:28 cmsdb02
16:28:30 cmsdb02
16:28:33 cmsdb02
SQL Plus Test
ractest.sql
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
echo "set head off
select to_char(sysdate,'hh24:mi:ss'),HOST_NAME from v\$instance;
spool /dev/null
set ver on term off newpage 0 head off trimspool on
select * from dba_segments;
select * from dba_extents;
set ver off feedback off echo on term on head off echo off
select to_char(sysdate,'hh24:mi:ss')||' '||HOST_NAME " " from v\$instance;
exit" > ractest.sql
run.sh
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
while true
do
sqlplus system/pwd@tns @ractest.sql
done
tnsnames.ora
~~~~~~~~~~~~
DBSID =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip)(PORT = 1521))
(LOAD_BALANCE = OFF)
(FAILOVER = ON)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DBSID )
(failover_mode = (type=select)(method=basic))
)
) |
3.235.60.197
|
|
|
|
|
|
kill server process
Exception in thread "main" java.sql.SQLException: 소켓에서 읽을 데이터가 없습니다
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1123)
at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1075)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:480)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:804)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1049)
at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:845)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1154)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1313)
at test.main(test.java:21)
Exception in thread "main" java.sql.SQLException: IO 예외 상황: Connection reset
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:255)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:820)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1049)
at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:845)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1154)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1313)
at test.main(test.java:15) |
2010-07-23
15:47:57
|
|
|
|
|
-- 11gR2
import java.sql.*;
public class test {
public static void main(String[] s)throws Exception {
Class.forName("oracle.jdbc.OracleDriver");
/*String url="jdbc:oracle:thin:@(DESCRIPTION= (LOAD_BALANCE=on) (ADDRESS=(PROTOCOL=TCP)(HOST=orrcdbtsl01-vip.isc-seo.upenn.edu) (PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=orrcdbtsl02-vip.isc-seo.upenn.edu)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=askm1.seo.int)))";*/
String url="jdbc:oracle:thin:@(DESCRIPTION= (LOAD_BALANCE=on) (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.21)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=db)))";
for (int i=0; i<20; i++) {
try {
long x= System.currentTimeMillis () ;
Connection conn = DriverManager.getConnection(url,"system","pwd");
long y= System.currentTimeMillis ();
System.out.println("Connection Succesful "+conn);
System.out.println("Connection time is "+(y-x)/1000+" ms");
Statement stmt =conn.createStatement();
ResultSet res= stmt.executeQuery("select to_char(sysdate,'hh24:mi:ss'),HOST_NAME from v$instance");
while(res.next()) {
System.out.println(" "+res.getString(1)+' '+res.getString(2));
}
stmt.close();
conn.close();}
catch(Exception e) {
e.printStackTrace();}
}
}
}
export CLASSPATH=.:$ORACLE_HOME/oui/jlib/classes12.jar:$ORACLE_HOME/jdbc/lib/ojdbc5.jar
java -classpath .:$ORACLE_HOME/oui/jlib/classes12.jar:$ORACLE_HOME/jdbc/lib/ojdbc5.jar test
java test
Connection Succesful oracle.jdbc.driver.OracleConnection@7dad526a
Connection time is 0 ms
22:52:17 node1
Connection Succesful oracle.jdbc.driver.OracleConnection@7dad5132
Connection time is 0 ms
22:52:18 node1
Connection Succesful oracle.jdbc.driver.OracleConnection@7dad4ffa
Connection time is 0 ms
22:52:18 node1
http://learnwithme11g.wordpress.com/2011/02/18/jdbc-connection-issue-with-oracle-database-11gr2-rac-scan-listener/ |
2011-02-21
22:53:37
|
|
|
|
|
11g 이상 ucp 사용시 TAF 가능
https://docs.oracle.com/cd/E11882_01/java.112/e12265/toc.htm
$cat $ORACLE_HOME/ucp/Readme.txt
Oracle Universal Connection Pool
Release 11.2.0.1.0
Production README
=======================================================================
Contents Of This Release
------------------------
For all platforms:
[ORACLE_HOME]/ucp/lib contains:
- ucp.jar
Classes for use with JDK 5.0 and JDK 6. It contains the Universal
Connection Pool classes, as well as the built-in JDBC Pool Adapter
classes for standalone UCP / JDBC applications.
- ucpdemos.jar
Classes for use with JDK 5.0 and JDK 6. It contains the Universal
Connection Pool demos and code samples, sample logging configurations,
and a quick-start toolkit for Fast Connection Failover.
Javadoc / Documentation / Demo:
All of the above are also available for download on OTN.
Installation
------------
The Oracle Installer puts the Universal Connection Pool files in the
[ORACLE_HOME]/ucp directory.
Setting Up Your Environment
---------------------------
On Windows platforms:
- Add [ORACLE_HOME]\ucp\lib\ucp.jar to your CLASSPATH.
On all Unix platforms:
- Add [ORACLE_HOME]/ucp/lib/ucp.jar to your CLASSPATH.
Important Notes
---------------
* UCP works with older Oracle databases such as 11.1 and 10g databases,
as well as non-Oracle databases such as DB2, SQLServer. Please refer
to OTN for complete UCP certification information, including JDBC driver
and vendor database versions.
* UCP's connection affinity features (Web-session based affinity and
Transaction based affinity) are designed to work with Oracle Real
Application Clusters (RAC) 11.1 or above, since they are designed to
leverage features of the 11g RAC database. These UCP features may not
work as expected with older RAC versions.
* If you are using UCP's Connection Labeling feature, and enabled Fast
Connection Failover as well as RAC Load Balancing Advisory, note that
UCP's Runtime Connection Load-Balancing (RCLB) takes precedence over
connection labeling. In other words, in a RAC enabled environment,
UCP's RCLB would provide better performance characteristics than
generic Connection Labeling feature.
Known Problems/Limitations In This Release
------------------------------------------
* For applications using XA (global transactions) and Fast Connection
Failover (FCF), the method isValid() on oracle.ucp.jdbc.ValidConnection
may not be optimal in performance when returning from the method. This
could happen when using UCP's PoolXADataSource with FCF enabled.
This is expected, due to Oracle JDBC bug #7314006.
Workaround: This bug does not affect the correctness of the isValid API.
The application could choose to check for XAException.XAER_RMERR
inside the XAException catch-block instead, to determine a connection's
validity.
* For applications using the built-in pool adapter PoolDataSource and
PoolXADataSource, the pool allows dynamic setting of properties on
the specified connection factory, such as connect-URL, host, port,
etc., during run-time. However, other than connect-URL, the pool
does not refresh itself when setting other connection factory properties.
This applies to both standard connection factory properties (for
example, those on javax.sql.DataSource including user, password,
portNumber, databaseName, etc.), and customized properties specific
to a connection factory.
Workaround: Application should try to avoid dynamic setting of
important connection factory properties, after the pool has been
populated with connections. If this can not be avoided, application
can explicitly perform pool refresh after all dynamic property set. |
2017-05-15
11:28:44
|
|
|
Copyright 1999-2023 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
|
|
|
12/01 