首先是接口SQLnetDef，定义了参数
public interface SQLnetDef
{
public static final boolean DEBUG = false;
public static final boolean ASSERT = false;
public static final int NSPTCN = 1;
public static final int NSPTAC = 2;
public static final int NSPTAK = 3;
public static final int NSPTRF = 4;
public static final int NSPTRD = 5;
public static final int NSPTDA = 6;
public static final int NSPTNL = 7;
public static final int NSPTAB = 9;
public static final int NSPTRS = 11;
public static final int NSPTMK = 12;
public static final int NSPTAT = 13;
public static final int NSPTCNL = 14;
public static final int NSPTHI = 19;
public static final byte NSPHDLEN = 0;
public static final byte NSPHDPSM = 2;
public static final byte NSPHDTYP = 4;
public static final byte NSPHDFLGS = 5;
public static final byte NSPHDHSM = 6;
public static final byte NSPSIZHD = 8;
public static final byte NO_HEADER_FLAGS = 0;
public static final byte NSPCNVSN = 8;
public static final byte NSPCNLOV = 10;
public static final byte NSPCNOPT = 12;
public static final byte NSPCNSDU = 14;
public static final byte NSPCNTDU = 16;
public static final byte NSPCNNTC = 18;
public static final byte NSPCNTNA = 20;
public static final byte NSPCNONE = 22;
public static final byte NSPCNLEN = 24;
public static final byte NSPCNOFF = 26;
public static final byte NSPCNMXC = 28;
public static final byte NSPCNFL0 = 32;
public static final byte NSPCNFL1 = 33;
public static final byte NSPCNDAT = 34;
public static final int NSPMXCDATA = 230;
public static final int NSINAWANTED = 1;
public static final int NSINAINTCHG = 2;
public static final int NSINADISABLEFORCONNECTION = 4;
public static final int NSINANOSERVICES = 8;
public static final int NSINAREQUIRED = 16;
public static final int NSINAAUTHWANTED = 32;
public static final byte NSPACVSN = 8;
public static final byte NSPACOPT = 10;
public static final byte NSPACSDU = 12;
public static final byte NSPACTDU = 14;
public static final byte NSPACONE = 16;
public static final byte NSPACLEN = 18;
public static final byte NSPACOFF = 20;
public static final byte NSPACFL0 = 22;
public static final byte NSPACFL1 = 23;
public static final byte NSPRFURS = 8;
public static final byte NSPRFSRS = 9;
public static final byte NSPRFLEN = 10;
public static final byte NSPRFDAT = 12;
public static final byte NSPRDLEN = 8;
public static final byte NSPRDDAT = 10;
public static final int NSPDAFLG = 8;
public static final int NSPDADAT = 10;
public static final int NSPDAFZER = 0;
public static final int NSPDAFTKN = 1;
public static final int NSPDAFRCF = 2;
public static final int NSPDAFCFM = 4;
public static final int NSPDAFRSV = 8;
public static final int NSPDAFMOR = 32;
public static final int NSPDAFEOF = 64;
public static final int NSPDAFIMM = 128;
public static final int NSPDAFRTS = 256;
public static final int NSPDAFRNT = 512;
public static final int NSPMKTYP = 8;
public static final int NSPMKODT = 9;
public static final int NSPMKDAT = 10;
public static final int NSPMKTD0 = 0;
public static final int NSPMKTD1 = 1;
public static final byte NIQBMARK = 1;
public static final byte NIQRMARK = 2;
public static final byte NIQIMARK = 3;
public static final int NSPDFSDULN = 2048;
public static final int NSPMXSDULN = 32767;
public static final int NSPMNSDULN = 512;
public static final int NSPDFTDULN = 32767;
public static final int NSPMXTDULN = 32767;
public static final int NSPMNTDULN = 255;
public static final int NSPINSDULN = 255;
public static final String TCP_NODELAY_STR = "TCP.NODELAY";
public static final String TCP_CONNTIMEOUT_STR = "oracle.net.CONNECT_TIMEOUT";
public static final String TCP_READTIMEOUT_STR = "oracle.net.READ_TIMEOUT";
public static final int TCP_NODELAY_OFF = 0;
public static final int TCP_KEEPALIVE_OFF = 1;
public static final int TCP_CONNTIMEOUT_OFF = 2;
public static final int TCP_READTIMEOUT_OFF = 3;
public static final int ORACLE_NET_NTMINOPT = 0;
public static final int ORACLE_NET_READ_TIMEOUT = 1;
public static final int ORACLE_NET_NTMAXOPT = 10;
}
Session 类描述：
package oracle.net.ns;
import java.io.*;
import oracle.net.ano.Ano;
import oracle.net.nt.ConnOption;
import oracle.net.nt.NTAdapter;
// Referenced classes of package oracle.net.ns:
// NetException, SQLnetDef, NetInputStream, NetOutputStream,
// ClientProfile
public class SessionAtts
implements SQLnetDef
{
public SessionAtts(int i, int j)
{
sdu = i;
tdu = j;
}
public int getANOFlags()
{
int i = 1;
if(ano != null)
i = ano.getNAFlags();
return i;
}
public InputStream getInputStream()
{
return nsInputStream;
}
public NTAdapter getNTAdapter()
{
return nt;
}
public OutputStream getOutputStream()
{
return nsOutputStream;
}
public int getSDU()
{
return sdu;
}
public int getTDU()
{
return tdu;
}
public void print()
{
System.out.println("Session Attributes: ");
System.out.println("sdu : " + sdu);
System.out.println("tdu : " + tdu);
System.out.println("nt : " + nt);
System.out.println("ntInputStream : " + ntInputStream);
System.out.println("ntOutputStream : " + ntOutputStream);
System.out.println("nsInputStream : " + nsInputStream);
System.out.println("nsOutputStream : " + nsOutputStream);
System.out.println("profile : " + profile);
System.out.println("cOption : " + cOption);
System.out.println("onBreakReset : " + onBreakReset);
System.out.println("dataEOF : " + dataEOF);
System.out.println("connected : " + connected);
}
public void setSDU(int i)
{
if(i <= 0)
sdu = 2048;
else
if(i > 32767)
sdu = 32767;
else
if(i < 512)
sdu = 512;
else
sdu = i;
}
public void setTDU(int i)
{
if(i <= 0)
tdu = 32767;
else
if(i > 32767)
tdu = 32767;
else
if(i < 255)
tdu = 255;
else
tdu = i;
}
public void turnEncryptionOn(NetInputStream netinputstream, NetOutputStream netoutputstream)
throws NetException
{
if(netinputstream != null && netoutputstream != null)
{
nsInputStream = netinputstream;
nsOutputStream = netoutputstream;
} else
{
throw new NetException(300);
}
}
private int sdu;
private int tdu;
protected NTAdapter nt;
protected InputStream ntInputStream;
protected OutputStream ntOutputStream;
protected NetInputStream nsInputStream;
protected NetOutputStream nsOutputStream;
protected ConnOption cOption;
protected boolean dataEOF;
protected boolean connected;
public boolean onBreakReset;
public ClientProfile profile;
public Ano ano;
public boolean anoEnabled;
public boolean isEncryptionActive;
public boolean isChecksumActive;
public boolean areEncryptionAndChecksumActive;
}
基础Packet类型：
package oracle.net.ns;
import java.io.*;
import oracle.net.nl.RepConversion;
// Referenced classes of package oracle.net.ns:
// NetException, NetInputStream, SQLnetDef, SessionAtts
public class Packet
implements SQLnetDef
{
public Packet(Packet packet)
{
this(packet.sAtts);
length = packet.length;
type = packet.type;
flags = packet.flags;
dataLen = packet.dataLen;
dataOff = packet.dataOff;
buffer = packet.buffer;
}
public Packet(SessionAtts sessionatts)
{
header = new byte[8];
sAtts = sessionatts;
sdu = sessionatts.getSDU();
tdu = sessionatts.getTDU();
}
public Packet(SessionAtts sessionatts, int i)
{
this(sessionatts);
createBuffer(i);
}
public Packet(SessionAtts sessionatts, int i, int j, int k)
{
this(sessionatts);
createBuffer(i, j, k);
}
protected void createBuffer(int i)
{
buffer = new byte[i];
buffer[0] = (byte)(i / 256);
buffer[1] = (byte)(i % 256);
}
protected void createBuffer(int i, int j, int k)
{
buffer = new byte[i];
buffer[0] = (byte)(i / 256);
buffer[1] = (byte)(i % 256);
buffer[5] = (byte)k;
buffer[4] = (byte)j;
}
protected void dump(byte abyte0[], int i, int j)
{
int k = 0;
System.out.println("Packet dump");
System.out.println("buffer.length=" + abyte0.length);
System.out.println("offset =" + i);
System.out.println("len =" + j);
for(int l = i; l < j; l += 8 )
{
System.out.print("|");
for(int i1 = 0; i1 < 8 && k < j - 1; i1++)
{
k = l + i1;
RepConversion.printInHex(abyte0[k]);
System.out.print(" ");
}
System.out.println("|");
}
System.out.println("finish dump");
}
protected void extractData()
throws IOException, NetException
{
if(dataLen <= 0)
data = new String();
else
if(length > dataOff)
{
data = new String(buffer, 0, dataOff, dataLen);
} else
{
byte abyte0[] = new byte[dataLen];
if(sAtts.nsInputStream.read(abyte0) < 0)
throw new NetException(0);
data = new String(abyte0, 0);
}
}
protected String getData()
{
return data;
}
protected void receive()
throws IOException, NetException
{
int i;
for(i = 0; i < header.length;)
try
{
if((i += sAtts.ntInputStream.read(header, i, header.length - i)) <= 0)
throw new NetException(0);
}
catch(InterruptedIOException _ex)
{
throw new NetException(504);
}
length = header[0] & 0xff;
length <<= 8;
length |= header[1] & 0xff;
type = header[4];
flags = header[5];
if(type > 19)
throw new NetException(204);
if(length > 32767 || length > sdu)
throw new NetException(203);
if(length < 8 )
throw new NetException(207);
buffer[5] = (byte)flags;
buffer[4] = (byte)type;
while(i < length)
try
{
if((i += sAtts.ntInputStream.read(buffer, i, length - i)) <= 0)
throw new NetException(0);
}
catch(InterruptedIOException _ex) { }
}
protected void send()
throws IOException
{
synchronized(sAtts.ntOutputStream)
{
sAtts.ntOutputStream.write(buffer, 0, buffer.length);
}
}
private int buffer2send;
protected int sdu;
protected int tdu;
protected int length;
public int type;
protected int flags;
protected int dataLen;
protected int dataOff;
protected String data;
protected byte buffer[];
protected byte header[];
public SessionAtts sAtts;
}
Connect Packet 连接包描述：
package oracle.net.ns;
import java.io.IOException;
import java.io.PrintStream;
import oracle.net.nt.ConnOption;
// Referenced classes of package oracle.net.ns:
// Packet, NetOutputStream, SQLnetDef, SessionAtts
public class ConnectPacket extends Packet
implements SQLnetDef
{
public ConnectPacket(SessionAtts sessionatts)
{
super(sessionatts);
super.data = sessionatts.cOption.conn_data.toString();
super.dataLen = super.data != null ? super.data.length() : 0;
connDataOflow = super.dataLen > 230;
int i = connDataOflow ? 34 : 34 + super.dataLen;
createBuffer(i, 1, 0);
super.buffer[8] = 1;
super.buffer[9] = 52;
super.buffer[10] = 1;
super.buffer[11] = 44;
super.buffer[12] = 0;
super.buffer[13] = 0;
super.buffer[14] = (byte)(super.sdu / 256);
super.buffer[15] = (byte)(super.sdu % 256);
super.buffer[16] = (byte)(super.tdu / 256);
super.buffer[17] = (byte)(super.tdu % 256);
super.buffer[18] = 79;
super.buffer[19] = -104;
super.buffer[22] = 0;
super.buffer[23] = 1;
super.buffer[24] = (byte)(super.dataLen / 256);
super.buffer[25] = (byte)(super.dataLen % 256);
super.buffer[27] = 34;
if(!sessionatts.anoEnabled)
super.buffer[32] = super.buffer[33] = 4;
else
super.buffer[32] = super.buffer[33] = (byte)sessionatts.getANOFlags();
if(!connDataOflow && super.dataLen > 0)
super.data.getBytes(0, super.dataLen, super.buffer, 34);
}
protected void send()
throws IOException
{
super.send();
if(connDataOflow)
{
byte abyte0[] = new byte[super.dataLen];
super.data.getBytes(0, super.dataLen, abyte0, 0);
super.sAtts.nsOutputStream.write(abyte0);
super.sAtts.nsOutputStream.flush();
}
}
private boolean connDataOflow;
}
接受包类：
package oracle.net.ns;
import java.io.IOException;
import java.io.PrintStream;
// Referenced classes of package oracle.net.ns:
// Packet, NetException, SQLnetDef, SessionAtts
public class AcceptPacket extends Packet
implements SQLnetDef
{
public AcceptPacket(Packet packet)
throws IOException, NetException
{
super(packet);
version = super.buffer[8] & 0xff;
version <<= 8;
version |= super.buffer[9] & 0xff;
options = super.buffer[10] & 0xff;
options <<= 8;
options |= super.buffer[11] & 0xff;
sduSize = super.buffer[12] & 0xff;
sduSize <<= 8;
sduSize |= super.buffer[13] & 0xff;
tduSize = super.buffer[14] & 0xff;
tduSize <<= 8;
tduSize |= super.buffer[15] & 0xff;
myHWByteOrder = super.buffer[16] & 0xff;
myHWByteOrder <<= 8;
myHWByteOrder |= super.buffer[17] & 0xff;
super.dataLen = super.buffer[18] & 0xff;
super.dataLen <<= 8;
super.dataLen |= super.buffer[19] & 0xff;
super.dataOff = super.buffer[20] & 0xff;
super.dataOff <<= 8;
super.dataOff |= super.buffer[21] & 0xff;
flag0 = super.buffer[22];
flag1 = super.buffer[23];
extractData();
super.sAtts.setSDU(sduSize);
super.sAtts.setTDU(tduSize);
if(tduSize < sduSize)
super.sAtts.setSDU(tduSize);
}
protected int version;
protected int options;
protected int sduSize;
protected int tduSize;
protected int myHWByteOrder;
protected int flag0;
protected int flag1;
}
Data Packet 数据包类：
package oracle.net.ns;
import java.io.*;
// Referenced classes of package oracle.net.ns:
// Packet, NetException, SQLnetDef, SessionAtts
public class DataPacket extends Packet
implements SQLnetDef
{
public DataPacket(SessionAtts sessionatts)
{
this(sessionatts, sessionatts.getSDU());
}
public DataPacket(SessionAtts sessionatts, int i)
{
super(sessionatts, i, 6, 0);
isBufferFull = false;
isBufferEmpty = false;
availableBytesToSend = 0;
availableBytesToRead = 0;
initialize(i);
}
protected int getDataFromBuffer(byte abyte0[], int i, int j)
throws NetException
{
int k = super.length - pktOffset > j ? j : super.length - pktOffset;
if(k > 0)
{
System.arraycopy(super.buffer, pktOffset, abyte0, i, k);
pktOffset += k;
isBufferEmpty = pktOffset == super.length;
availableBytesToRead = (super.dataOff + super.dataLen) - pktOffset;
}
return k;
}
protected void initialize(int i)
{
super.dataOff = pktOffset = 10;
super.dataLen = i - super.dataOff;
dataFlags = 0;
}
protected int putDataInBuffer(byte abyte0[], int i, int j)
throws IOException
{
int k = super.buffer.length - pktOffset > j ? j : super.buffer.length - pktOffset;
if(k > 0)
{
System.arraycopy(abyte0, i, super.buffer, pktOffset, k);
pktOffset += k;
isBufferFull = pktOffset == super.buffer.length;
availableBytesToSend = super.dataOff >= pktOffset ? 0 : pktOffset - super.dataOff;
}
return k;
}
protected void receive()
throws IOException, NetException
{
super.receive();
super.dataOff = pktOffset = 10;
super.dataLen = super.length - super.dataOff;
dataFlags = super.buffer[8] & 0xff;
dataFlags <<= 8;
dataFlags |= super.buffer[9] & 0xff;
if((dataFlags & 0x40) != 0)
super.sAtts.dataEOF = true;
if(super.type == 6 && super.dataLen == 0)
super.type = 7;
}
protected void send()
throws IOException
{
send(0);
}
protected void send(int i)
throws IOException
{
super.buffer[8] = (byte)(i / 256);
super.buffer[9] = (byte)(i % 256);
setBufferLength(pktOffset);
synchronized(super.sAtts.ntOutputStream)
{
super.sAtts.ntOutputStream.write(super.buffer, 0, pktOffset);
}
pktOffset = 10;
availableBytesToSend = 0;
isBufferFull = false;
}
protected void setBufferLength(int i)
throws NetException
{
super.buffer[0] = (byte)(i / 256);
super.buffer[1] = (byte)(i % 256);
}
static final boolean DEBUG2 = false;
protected int pktOffset;
protected int dataFlags;
protected boolean isBufferFull;
protected boolean isBufferEmpty;
protected int availableBytesToSend;
protected int availableBytesToRead;
}
以上类描述可以通过反编译jdbc包获得，实际数据结构是通用的即在C或其他语言下也是类似结构，只是实现略有不同。
Applies to:
JDBC – Version: 10.2.0.1 to 11.1.0.7
This problem can occur on any platform.
Symptoms
A java client connected to a RAC Database using JDBC thin gives the following error intermittently when attempting to establish a connection:
java.sql.SQLException: Io exception: Socket read timed out: SQL State = null, Error Code = 17002
Cause
The java client sets oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR to allow quick Connect Time Failover to the next subsequent node in the address list when one node becomes unavailable.
The code looks like:
…
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Properties p = new Properties();
p.put(oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR,”3000″);
…
Under heavy load, the value set for oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR is too low to establish a connection with any of the nodes in the RAC Cluster, thereby giving rise to intermittent errors
Solution
Increase the value of oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR to a suitable higher value, which would allow connections to be established during periods of high connection bursts to the RAC Database.
Applies to:
JDBC – Version: 11.2.0.1 – Release: 11.2
Information in this document applies to any platform.
Goal
This note discusses how to trace what is sent and received between the Oracle JDBC Driver release 11.2 and Oracle database.
Solution
From release 11.2, the JDBC Thin Driver includes a network trace capability. This capability allows you to trace the network packets that the driver exchanges with the server.
To generate network level trace information, add the following line into your config file in addition
to the other configuration information:
oracle.net.ns.level = [OFF/SEVERE/WARNING/INFO/CONFIG/FINE/FINER/FINEST/ALL]
For example:
.level=OFF
#.level=SEVERE
handlers=java.util.logging.FileHandler
# example of a full pathname in Windows
java.util.logging.FileHandler.pattern=D:\\Networkpacket.log
# Predefined levels are: ALL, SEVERE, WARNING, INFO, CONFIG, FINE, FINER,
# FINEST, OFF
java.util.logging.FileHandler.limit = 500000000
java.util.logging.FileHandler.count = 1
java.util.logging.FileHandler.level =ALL
java.util.logging.FileHandler.formatter = java.util.logging.SimpleFormatter
# Following line enables the Network packet tracing
oracle.net.ns.level = ALL
#oracle.jdbc.level=SEVERE
JavaNet Logging Sample Code
1) Download the following sample code, change the connect string and compile the code. This code makes a simple SELECT query.
2) Run the sample code using the following Java options:\Logging.properties NTraceSample
java -Doracle.jdbc.Trace=true -Djava.util.logging.config.file=
3) The log file will be generated according to the value specified in the property
“java.util.logging.FileHandler.pattern”.
Sample Packet contents
From JDBC to Server
Jan 19, 2010 9:25:35 PM oracle.net.ns.Packet send
TRACE_20: Debug:
00 D1 00 00 01 00 00 00 |……..|
01 36 01 2C 0E 41 20 00 |.6.,.A..|
7F FF 4F 98 00 00 00 01 |..O…..|
00 97 00 3A 00 00 00 00 |…:….|
01 01 00 00 00 00 00 00 |……..|
00 00 00 00 00 00 00 00 |……..|
00 00 00 00 00 00 00 00 |……..|
00 00 28 44 45 53 43 52 |..(DESCR|
49 50 54 49 4F 4E 3D 28 |IPTION=(|
43 4F 4E 4E 45 43 54 5F |CONNECT_|
44 41 54 41 3D 28 53 49 |DATA=(SI|
44 3D 6F 72 63 6C 29 28 |D=orcl)(|
43 49 44 3D 28 50 52 4F |CID=(PRO|
47 52 41 4D 3D 4A 44 42 |GRAM=JDB|
43 20 54 68 69 6E 20 43 |C.Thin.C|
6C 69 65 6E 74 29 28 48 |lient)(H|
4F 53 54 3D 5F 5F 6A 64 |OST=__jd|
62 63 5F 5F 29 28 55 53 |bc__)(US|
45 52 3D 64 6A 73 61 76 |ER=djsav|
69 6F 29 29 29 28 41 44 |io)))(AD|
44 52 45 53 53 3D 28 50 |DRESS=(P|
52 4F 54 4F 43 4F 4C 3D |ROTOCOL=|
74 63 70 29 28 48 4F 53 |tcp)(HOS|
54 3D 6C 6F 63 61 6C 68 |T=localh|
6F 73 74 29 28 50 4F 52 |ost)(POR|
54 3D 31 35 32 31 29 29 |T=1521))|
29 |) |
From Server to Client
Jan 19, 2010 9:25:35 PM oracle.net.ns.Packet receive
TRACE_20: Debug: type=6, length=185, flags=0
00 B9 00 00 06 00 00 00 |……..|
00 00 01 06 00 49 42 4D |…..IBM|
50 43 2F 57 49 4E 5F 4E |PC/WIN_N|
54 2D 38 2E 31 2E 30 00 |T-8.1.0.|
B2 00 01 00 00 00 64 00 |……d.|
00 00 60 01 24 0F 05 0B |..`.$…|
0C 03 0C 0C 05 04 05 0D |……..|
06 09 07 08 05 05 05 05 |……..|
05 0F 05 05 05 05 05 0A |……..|
05 05 05 05 05 04 05 06 |……..|
07 08 08 23 47 23 23 08 |…#G##.|
11 23 08 11 41 B0 23 00 |.#..A.#.|
83 00 B2 07 D0 03 00 00 |……..|
00 00 00 00 00 00 00 00 |……..|
00 00 00 00 00 00 00 00 |……..|
00 00 00 00 00 00 00 00 |……..|
00 00 00 00 00 00 00 00 |……..|
00 00 00 25 06 01 01 01 |…%….|
0D 01 01 05 01 01 01 01 |……..|
01 01 01 7F FF 03 09 03 |……..|
03 01 00 7F 01 1F FF 01 |……..|
03 01 01 3F 01 01 05 00 |…?….|
01 07 02 01 00 01 18 00 |……..|
01 |. |
While using earlier versions of the JDBC Thin Driver than release 11.2, you must use the JNETtrace utility to trace the network packets.
Applies to:
JDBC – Version: 10.2.0.1 to 11.2.0.1 – Release: 10.2 to 11.2
Information in this document applies to any platform.
Symptoms
The following exception is generated when connecting with JDBC thin:
java.sql.SQLException: SO Exception was generated
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:197)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:525)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:413)
at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:508)
at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:203)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:33)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:510)
at java.sql.DriverManager.getConnection(DriverManager.java:525)
at java.sql.DriverManager.getConnection(DriverManager.java:171)
at testcase.EmptyTest.main(EmptyTest.java:41)
Caused by: oracle.net.ns.NetException: SO Exception was generated
at oracle.net.resolver.AddrResolution.resolveAddrTree(AddrResolution.java:616)
at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:410)
at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:630)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:310)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:966)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:292)
… 7 more
The Connection works fine when using jdbc:oci in the url, or when using SQL*Plus with the same or similar connect description.
When using javanet, to obtain a .trc file, the following error is seen:
Server to client: 2010-03-30 17:05:55.64
Packet size = 103
00 67 00 00 04 00 00 00 .g……
22 00 00 5B 28 44 45 53 “..[(DES
43 52 49 50 54 49 4F 4E CRIPTION
3D 28 54 4D 50 3D 29 28 =(TMP=)(
56 53 4E 4E 55 4D 3D 31 VSNNUM=1
38 36 36 34 36 37 38 34 86646784
29 28 45 52 52 3D 31 32 )(ERR=12
35 32 30 29 28 45 52 52 520)(ERR
4F 52 5F 53 54 41 43 4B OR_STACK
3D 28 45 52 52 4F 52 3D =(ERROR=
28 43 4F 44 45 3D 31 32 (CODE=12
35 32 30 29 28 45 4D 46 520)(EMF
49 3D 34 29 29 29 29 I=4))))
Changes
a recent adjustment was made to the tnsnames.ora file, or local_listener/remote_listener parameter.
toad has been used to adapt the files.
Cause
(CONNECT_DATA=) is included in the DESCRIPTION of the listener.
The following bug was filed for this matter, and is under investigation:
Bug 9536905: SO EXCEPTION WHEN INCLUDING CONNECT_DATA IN LOCAL_LISTENER
An example:
tnsnames.ora file of node2:
LISTENER_NODE2 =(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST =node2-vip.be.oracle.com)(PORT = 1521))(CONNECT_DATA=))
local_listener parameter of node2:
local_listener=’LISTENER_NODE2′
Solution
1. wait for Bug 9536905 to be analyzed/fixed
OR
2. use the following workaround:
remove the CONNECT_DATA from the tnsnames.ora, local_listener/remote_listener.
In the example above, change the tnsnames.ora file of node2 to:
LISTENER_NODE2 =(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST =node2-vip.be.oracle.com)(PORT = 1521)))
Applies to:
JDBC – Version: 10.2.0.1 to 11.1.0.7
Information in this document applies to any platform.
Goal
The Oracle JDBC thin driver uses a java based implementation of the SQLNET protocol (JavaNet layer).
Unlike the JDBC OCI driver, the SQLNET trace is not available with the JDBC thin driver at the client side. Therefore, to diagnose JDBC connection or other database related error conditions, only the SQLNET server side trace is available. As a result, it can be difficult to get a good understanding of the problem with only one end of the equation.
This article describes how to enable a similar sqlnet client trace with the JDBC thin driver. This can be done by using a new utility (packaged as a JAVA jnettrace.jar library), the JavaNet utility, which allows tracing of the JavaNet layer.
This utility, attached to the article, is available on OTN : http://www.oracle.com/technology/tech/java/sqlj_jdbc/index.html .
Solution
The JavaNet trace utility provides a way to perform non-intrusive JDBC tracing : it can be executed on a different machine than the one hosting the JDBC driver application. This feature might be interesting in production environments when enabling this trace.
JDK 5 (or greater) version must be used when executing the JavaNet utility.
The JavaNet utility will produce :
A. the following output at the start: at port no.
Listening on
Forwarding Port: 1521
B. Whenever a connection is being made from JDBC::
> Connected to:
C. and a client_.trc file for every connection being made from a JDBC client application (where
is a hashcode of the physical connection) . It will contain the JavaNet trace related to a JDBC thin
connection.
Case 1 : accessing a single instance database
1. Run the JavaNet utility :
java -jar jnettrace.jar
where:
: hostname (or address) where database server instance is running.
: listener port related to the database server
: listening port of the JavaNet utility. The JDBC client application will communicate with the JavaNet utility by establishing a connection to this port instead the database server listener port.
Example:
java -jar jnettrace.jar DBHost 1521 8090
2. Modify the JDBC connection URL of the JDBC application client:: :::
The new URL will contain the host name and port of the machine running JavaNet utility instead of the hostname and port related to the database server.
If the initial JDBC connection URL is:
jdbc:oracle:thin:@
Then change this URL as:
jdbc:oracle:thin:@
OR
If the inital JDBC connection URL is:: /:/
jdbc:oracle:thin:@//
Then change JDBC URL as:
jdbc:oracle:thin://
where:
: hostname where the JavaNet utility is running
: listening port of the JavaNet utility
Example:
If the initial JDBC connection URL is jdbc:oracle:thin:@DBhost:1521:DBSid
jdbc:oracle:thin:@Javanet_hostname:8090:DBSid
Case 2 : accessing a RAC Database
1. Run the jnettrace.jar utility:
For every RAC database instance, a related JavNet utility has to be started with a different JavNet port number:
java -jar jnettrace.jar
where
: hostname (or address) of the node where database server RAC instance is running.
: listener port related to the RAC instance
: listening port of the JavaNet utility
Example :
Having a 2 RAC database instance running on two nodes DBHOST1 & DBHOST2:
java -jar jnettrace.jar DBHost1 1521 8090
java -jar jnettrace.jar DBHost2 1521 8091
2. Modify the JDBC connection URL of the JDBC application client:
Similarly as for a Single Instance Database, the connection URL has to be changed so that the JDBC client application connects to one of the JavaNet utility previously started.
The change consists of replacing:
– the node instance hostname by the hostname related to host where the JavaNet utility is running.
– the node instance port by the port of the JavaNet utility launched.
Example :
The initial JDBC connection URL is :
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DBHost1)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=DBHost2)(PORT=1521))
(LOAD_BALANCE=yes)(CONNECT_DATA=(SERVER=DEDICATED)
(SERVICE_NAME=orcl)))
Then the new JDBC connection URL will be
jdbc:oracle:thin:@
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=JNetTraceServerName)(PORT=8090))
(ADDRESS=(PROTOCOL=TCP)(HOST=JNetTraceServerName)(PORT=8091))
(LOAD_BALANCE=yes)(CONNECT_DATA=(SERVER=DEDICATED)
(SERVICE_NAME=orcl)))
Hdr: 9536905 11.2.0.1 THIN 11.2.0.1 PRODID-972 PORTID-23
Abstract: SO EXCEPTION WHEN INCLUDING CONNECT_DATA IN LOCAL_LISTENER
*** 03/30/10 08:23 am ***
BUG TYPE CHOSEN
===============
Code
Problem Description
===================
In a RAC environment, when a listener has CONNECT_DATA included in the
local_listener, then jdbc thin connection might fail with:
java.sql.SQLException: SO Exception was generated(PhysicalConnection.java: (T4CConnection.java:203)
at
oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.ja
va:70)
at
oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:1
33)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:199)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:480)
at
oracle.jdbc.driver.PhysicalConnection.
508)
at oracle.jdbc.driver.T4CConnection.
at
oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtensio
n.java:33)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:185)
at testcase.EmptyTest.main(EmptyTest.java:41)
at
a:616)
at
ava:410)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:966)
… 7 more
Enabling javanet, shows the client tries to connect 2 times and receives
12520 with both tries.
When using jdbc:oci or sqlplus, the connection works fine.
Workaround
==========
exclude (CONNECT_DATA=) in the local_listener description
Generic/Port-Specific Findings
==============================
Rep? Platform Client Client Ver. RDBMS Ver.
—- ———— ——— ————- ————
Y JRE 5 thin 10.2.0.4 11.2.0.1
Y JRE 6 thin 11.1.0.7 11.2.0.1
Y JRE 6 thin 11.2.0.1 11.2.0.1
N JRE 5 oci 10.2.0.4 11.2.0.1
N JRE 6 oci 11.1.0.7 11.2.0.1
N JRE 6 oci 11.2.0.1 11.2.0.1
Hdr: 5712356 10.2.0.2 JDBC 10.2.0.2 PRODID-972 PORTID-23
Abstract: JDBC DRIVER HANGS AFTER FETCHING ABOUT 200 ROWS
PROBLEM:
1. Clear description of the problem encountered:
10.2 Jdbc thin and OCI driver HANG’s when selecting over different
tables.
With the table/view which OCI Works, THIN Fails and viceversa
The same code works fine when executing the java code on the same
machine as Database.
The HANG only seen when executing the code on different box than database
Jdbc driver and database are 10.2.0.2. And OS of both client and server is
Solaris(differen boxes)
When the HANG (both thin/oci) the same select works fine from sql*plus
2. Pertinent configuration information (MTS/OPS/distributed/etc)
N/A
3. Indication of the frequency and predictability of the problem
Consistently on Ct environment
4. Sequence of events leading to the problem
5. Technical impact on the customer. Include persistent after effects.
Ct’s production application cannot use Jdbc drivers
=========================
DIAGNOSTIC ANALYSIS:
1) Thin driver fails on below query
stmt.executeQuery (“SELECT ACC_NOM, PPA_AMB, MOD_NOM FROM
VW_USUA_ACCI_VIGE_INT WHERE USR_ID = 1001 ORDER BY PPA_AMB”);
VW_USUA_ACCI_VIGE_INT is a view built on join of 5 tables
2)64bit Oci driver fails on below query
preparedstatement = connection.prepareStatement(“SELECT DEF_COD,
DEF_TIP_OTV FROM API_VAL_DEFTAB_INT”);
resultset = preparedstatement.executeQuery();
API_VAL_DEFTAB_INT is view built on VAL_DEFTAB table. The select directly
from the table itself also HANG’s
And Hang is on oracle.jdbc.driver.T2CStatement.t2cFetch
Note:- HANG only occurs when using 64bit OCI/jdk , the same code works fine
when using 32bit Oci/Jdk.
3) 32bit Oci driver fails on below query
SELECT SEG_MODU.MOD_NOM, SEG_AGRU.AGR_ID, SEG_AGRU.AGR_TIP,
SEG_AGRU.AGR_VIEW, SEG_AGRU.AGR_OCU_MAX, SEG_AGRU.AGR_NUL,
SEG_AGRU.AGR_OCU_MIN FROM SICAS.SEG_MODU SEG_MODU, SICAS.SEG_AGRU SEG_AGRU
WHERE ( SEG_MODU.MOD_ID=SEG_AGRU.MOD_ID )
Hang is on oracle.jdbc.driver.T2CStatement.t2cFetch
=========================
WORKAROUND:
None
=========================
RELATED BUGS:
Came across bug3685750 , but specific to 10.1
=========================
REPRODUCIBILITY:
1. State if the problem is reproducible; indicate where and predictability
Cannot reproduce the problem inhouse with the testcase provided
2. List the versions in which the problem has reproduced
3. List any versions in which the problem has not reproduced
=========================
TESTCASE:
Cannot reproduce the problem inhouse with the testcase provided. The only
difference between ct and in house I can think of is the DB NLS parameters
Ct Database is Spanish WE8
NLS_LANGUAGE SPANISH
NLS_TERRITORY SPAIN
NLS_CHARACTERSET
WE8ISO8859P15
NLS_NCHAR_CHARACTERSET AL16UTF16
========================
STACK TRACE:
Thin Thread dump
————–
at
oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1099)
at
oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1070)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:478)
at
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at
oracle.jdbc.driver.T4CPreparedStatement.fetch(T4CPreparedStatement.java:1027)
at
tImpl.java:291)
– locked <0xf1f26a20> (a oracle.jdbc.driver.T4CConnection)
at
at prueba_bbdd.main(prueba_bbdd.java:16)
Oci Thread dump
————
at oracle.jdbc.driver.T2CStatement.t2cFetch(Native Method)
at
oracle.jdbc.driver.T2CPreparedStatement.fetch(T2CPreparedStatement.java:977)
at
tImpl.java:291)
– locked <0xf1f23100> (a oracle.jdbc.driver.T2CConnection)
at
at prueba_bbdd_sunz100.main(prueba_bbdd_sunz100.java:23)
=========================
24 HOUR CONTACT INFORMATION FOR P1 BUGS:
=========================
DIAL-IN INFORMATION:
=========================
IMPACT DATE: 12-Dec-2006
Note:- Noticed the HANG occurs with Thin driver when only selecting from view
which is built using 5 or more tables
The problem is jdbc thin, oci 32 bit and oci 64 bit all hang on different
queries after about 200 records or so.
The client and server are both Solaris but different machines. When the
program is run on the SAME machine as the d/b it does not hang.
The program is single-threaded so is not a deadlock scenario. Checking if we
have a stack from the hanging shadow process so we can confirm that both ends
are waiting on a read, ie each is waiting for the other. No.
As it is stuck in the net code, in what appears to be unmarshalling data, it
might relate to eg packet size. The only bug I’m aware of in this area is
bug 5279696. In unmarshalNBytes it can try to read from the wire even when
the amount requested to read is 0 bytes. However this would only affect the
thin driver. The fact that oci also stops plus it works when the d/b is
local makes me suspect this is probably not a JDBC issue. Is the customer
sure it isn’t a problem with their network? Have they used eg a n/w sniffer
to check that data is not being lost?
Checked what’s been uploaded – only 10046 traces and the java stack traces.
Looking at the trace for oci 32 bit, it has the select, fetches 1 row,
commits then starts to fetch in batches of 10. It fetches 11 batches then
you see the explain plan info. This is usually written once the last row has
been sent to the client. So the server apparently thinks it has sent all the
data to the client and is now waiting for the next instruction.
Checked the oci 64 bit trace and it’s the same, fetch 1 row, commit, fetch 22
batches of 10 then see the STAT.
Again similar in the thin trace.
We need to see a net trace of the program when it fails. It would help to
have both a client and server side trace so this will need to be from the oci
driver, 32 or 64 bit doesn’t matter, just let me know which it was.
*** 12/13/06 04:32 am ***
When you provide the net traces can you also tell me how many rows the
hanging query should have retrieved.
*** proved statement/prepStmt both HANG so please Ignore 1)***
1) Noticed that with Jdbc OCI driver (atleast while accessing VAL_DEFTAB
table) that the code on remote client works when using Statement object and
HANGS when using PreparedStatement .
2) The tables/views I tested on OWC that HANG mostly have about 400 rows
API_VAL_DEFTAB_INT ==> Has 452 rows
VW_USUA_ACCI_VIGE_INT ==> Has 12013 rows (But select should return only 395
rows due to where condition)
I haven’t tried running the testcase as the problem is not reproducible here.
At the moment getting the client and server net traces together and knowing
exactly how many rows should have been retrieved may point us in the right
direction.
Talking to my collegue in US BDE apparently they do have this working in
another client to remote server set up. If that is the case then also get
them to provide the 2 net traces from the same query run successfully there
so we can compare.
While waiting for the traces from the same run, looking at the uploaded
client one.
It seems to be slightly corrupted. Paging down, the info between the ||
characters is sometimes out of line. Not sure if this is signficant but it’s
not something I’ve seen before.
Can see the select sent with column list and one row coming back. Then see
10 rows coming back at a time. This happens 10 times though if this ties up
to the 10046 trace the server sent 11 lots implying the last packet was never
received, hence the “hang”.
So we do need the client and server net traces together.
The info above re it working with Statement but not PreparedStatement is very
odd because there are no binds involved so the underlying calls should be the
same. Please ask the customer to run using the same JDBC driver each time (I
assume we’re using oci 32 bit for these tests) and upload the client and
server net traces for the following:
a) when it fails using PreparedStatement
b) when it works using PreparedStatement (preferably with the client and
server on different machines, otherwise when they are on the same one)
c) when it works in the normally failing environment using Statement
Please don’t forget to tell me exactly how many rows the query should be
retrieving.
Looking at the net traces – as preparedstatement and statement give the same
behaviour I’m expecting to see 4, 2 from the failing setup, and 2 from the
working one.
There are 5 zip files, though it looks like the working and failing
comparisons are done with the 64 bit oci driver which is what I’m interested
in. This uses:
SELECT DEF_COD, DEF_TIP_OTV FROM API_VAL_DEFTAB_INT;
which in sqlplus returns 452 rows. Files for this are:
64bit_OCI_Hang.zip (with remote d/b):
th_dump_64bit_oci.txt – thread dump
udump_sicapre_ora_3499.trc – 10046 trace
client_hang.log_5403.trc – client net trace
traces_server_oci64.zip – this contains 12 trace files!!!
Why are there 12 trace files? There is only one connection in the program so
there should be only one server net trace. I need the one that corresponds
directly to the client trace. Trying to find it….
Checked all 12 traces and not one corresponds to the JDBC session. These all
seem to be from background processes, eg selecting from v$parameter,
v$instance etc.
Please ask the customer to run the test again with the 64 bit oci testcase
both in the working and failing cases and upload 4 traces only – the client
and server net trace in each case.
I suggest the customer turns on net tracing, starts the d/b etc and then
removes all existing net traces before running the program. If they add a
pause in the program after the connect they can check and see which server
side net trace it is they require by eg comparing the pid or looking for the
alter session to set the nls env to spanish.
ACTION PLAN
==========
Placed the trace files in “/upload/bug5712356/new_traces” directory
and New_Readme_traces.txt provides details about the traces
One more interesting findind the HANG’s occur when only executing the
programs from a particular Solaris box(z100).
The same code works fine when executed from the database(z99) as well as a
different Solaris box(z101)
All the working traces are from Solaris box(z101)
ACTION PLAN
===========
Also interestingly found that the HANG occurs when resultset.setFecthsize(10)
or unset i.e default value which is also 10
But the same code works when resultset.setFecthsize is set to any value other
than 10 i.e even works for 1.
The traces for working cases I uploaded are run with
resultset.setFecthsize(1)/resultset.setFecthsize(5)
Inorder to fix the Jdbc driver HANG ct has applied below solaris patches
Patch 118777-08 (SunOS 5.10: Sun GigaSwift Ethernet 1.0 driver patch)
&
Patch 122027-08 (SunOS 5.10: bge Driver Patch).