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
Jstring is … jdbc:oracle:thin:@::
Exception in thread “main” java.sql.SQLException: Io exception: The Network Adap
ter could not establish the connection
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, theres 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 descibed here:
I leave the database and listener up all weekend an 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.