In the course of trying to sort out the problem outlined in the previous post, I hit a problem trying to get the jdbc to talk to the 9i database at all.

This started occurring once I’d re-built the 9i database. [Re-building the 9i database seems like, and on the face of it was, an over-reaction to the problem - its a longish story but at the time we had a fairly short but imminent window of opportunity for doing re-builds, we were waiting for Oracle to get back to us, the NLS_CHARACTERSET setting was wrong, it was Friday afternoon, yada yada..]

Anyway, running with a test script derived from Metalink Note Note 232357.1, I got

C:temp>javac doselect.java

C:temp>java doselect
Jstring is ... jdbc:oracle:thin:@::
Exception in thread "main" java.sql.SQLException: Io exception: The Network Adap
ter could not establish the connection
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
at oracle.jdbc.ttc7.TTC7Protocol.handleIOException(TTC7Protocol.java)
at oracle.jdbc.ttc7.TTC7Protocol.logon(TTC7Protocol.java)
at oracle.jdbc.driver.OracleConnection.(OracleConnection.java)
at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.ja
va)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java)
at java.sql.DriverManager.getConnection(DriverManager.java:517)
at java.sql.DriverManager.getConnection(DriverManager.java:177)
at doselect.main(doselect.java:22)

Most of the literature, both Metalink and otherwise, quite sensibly directs you towards:

  • correcting your jdbc connection string
  • correcting your sqlnet set up
  • I checked and re-checked but no issues with either. I could connect using sqlplus with no problem.

The Oracle support guy set up a web conference so he could see what was going on.

I showed him the listener.ora.

He spots a problem in the entry for the database I’m trying to connect to.

‘Aha!’ he says, there’s your problem.

‘Aha!’ I say, no its not, I just changed that [from what the Oracle chap was recommending] half an hour ago to try it out.

Oracle support guy insists I change it and re-try. In truth a little bit irritated, I change it back, from my point of view in order to demonstrate that its not relevant to the issue.

So I re-start the listener and…..it works.

So I eat several helpings of humble pie, profusely thank the Oracle support guy and go and lie down in a darkened room.

I then, for some reason try the connection again and…..it fails.

Nobody has changed anything in the meantime.

On investigation, after a listener restart, connectivity is fine for maybe half a minute or so then starts failing.

The culprit seems to be the dynamic service registration feature. As it happens, I’ve picked up a slight mistrust of the feature - I can’t particularly remember why or from where.

Anyway, given that:

  • it takes maybe half a minute or so for the service to dynamically register with the listener, and
  • it takes maybe half a minute or so for connectivity to start failing

The first thing I try is diasabling the dynamic service registration, as follows:

ALTER SYSTEM SET LOCAL_LISTENER="(ADDRESS=(PROTOCOL=tcp)(host=hostname)(port=7))" SCOPE=SPFILE;

Turning the feature off is described here:

And…it works.

I leave the database and listener up all weekend and all is still OK.

My best guess is that this is a bug. The likelihood is that it only effects our very specific (and largely unsupported) set of circumstances. I should possibly pursue it with Oracle Support, but the support guy I’m talking to is reluctant to do so - I could escalate it but its probably not worth it. I’ve typed all the above up a) so I can keep it for my records b) in the hope that anyone Googling “Exception in thread “main” java.sql.SQLException: Io exception: The Network Adapter could not establish the connection” may find it. We wouldn’t have hit hit if I hadn’t rebuilt the database, but then we would have hit it if somebody had inadvertently turned on dynamic service registration in the future. It was a learning experience.