I got this error trying to specify the tablespace for my text indexes. I was moving them from a default tablespace because they are taking up quite a lot of space.

Looking up oerr, you get:

Cause:
The user specified an invalid option like ASC, DESC, SORT or a parallel clause, partitioning clause or physical attributes clause.
Action:
Choose one of the valid clauses for creation of domain indexes.

You can' specify a tablespace as such in the create index statement. Something I would have found out had I been arsed to rtfm in the first place.

Anyway, as per Oracle note 150453.1 , you have to create a preference for the storage, and then use the parameter syntax when you create the index, as follows:

begin 
Ctx_Ddl.Drop_Preference ( 'eservice_text_storage' ); 
end; 
/
begin 
Ctx_Ddl.Create_Preference('eservice_text_storage', 'BASIC_STORAGE'); 
ctx_ddl.set_attribute('eservice_text_storage',
'I_TABLE_CLAUSE', 
'tablespace text_indexes'); 
ctx_ddl.set_attribute('eservice_text_storage', 
'K_TABLE_CLAUSE', 
'tablespace text_indexes'); 
ctx_ddl.set_attribute('eservice_text_storage', 
'R_TABLE_CLAUSE', 
'tablespace text_indexes storage (initial 1M) lob (data) store as (cache)'); 
ctx_ddl.set_attribute('eservice_text_storage', 
'N_TABLE_CLAUSE', 
'tablespace text_indexes'); 
ctx_ddl.set_attribute('eservice_text_storage', 
'I_INDEX_CLAUSE', 
'tablespace text_indexes compress 2'); 
ctx_ddl.set_attribute('eservice_text_storage', 
'P_TABLE_CLAUSE', 
'tablespace text_indexes'); 
end; 
/ 

drop index sc_sol_textidx_desc;
create index sc_sol_textidx_desc on owner.sc_solutions(description)
indextype is ctxsys.Context
parameters ('storage eservice_text_storage'); 
;

drop index sc_sol_textidx_dref;
create index sc_sol_textidx_dref on sc_solutions (document_ref)
indextype is ctxsys.context 
parameters ('storage eservice_text_storage LEXER ctxsys.mylex'); 

My procedure has a create database link in it:

v_exe := 'create database link temp_security_monitor '               ||
         'connect to '  p_username  ' identified by '  p_password    ||
         ' using '  ''''  p_tns_string  '''';          

if v_debug = TRUE
    then  dbms_output.put_line (v_exe);
end if;

execute immediate v_exe;

This was failing:

Unexpected error-1031 ORA-01031: insufficient privileges

SQL> conn <a href="mailto:sys@db">sys@db</a> as sysdba
Connected.
SQL> grant create database link to system;
Grant succeeded.

Then all was hunky-dory

The problem

I lost SQL connectivity to all databases between about 11.29 and 12.06 today (21st June).

According to the trace files whereas normally the Names servers are addressed as follows:

[000001 19-MAY-2005 14:11:16:517] NAMES.PREFERRED_SERVERS = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = vAlias1)(PORT = 101)) (ADDRESS = (PROTOCOL = TCP)(HOST = vAlias2)(PORT = 101)))

during this period they were addressed:

[000001 21-JUN-2005 11:43:34:114] NAMES.PREFERRED_SERVERS = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 46.2.3.44499)(PORT = 101)) (ADDRESS = (PROTOCOL = TCP)(HOST = 46.2.3.44599)(PORT = 101)))

The IP addresses in the incorrect address are the correct addresses (for dbs18 and dbs15) respectively, but with a ‘99’ appended. And ‘normally’ the trace file shows the alias anyway.

Empirix didn’t show any issues - it could have just been me, but I didn’t make any changes that might have caused, or fixed, it.

Oracles (correct) response

I don’t think this is a DNS issue. I would have to say that the sqlnet.ora has been changed. The beginning of the trace shows the sqlnet.ora after it has been read, but before it has been parsed at all. This shows the invalid value already there.

Your two traces differ in that the ‘incorrect’ one appears to be reading its sqlnet.ora from C:\Temp\sqlnet.ora

Presumably I was in c:temp when I was kicking off sql.

As I say it was a problem with the wetware

But on my PC its at:

C:\WINNT\system32\ODBCAD32.exe

On one of the users XP boxes it was

C:\WINDOWS\system32\ODBCAD32.exe

I had a query from a user about Text not retrieving the expected data for a given bit of Text

The Text field (‘Solution’) contains:

1. What type of OS can be used with the xxx?
2. What are the minimum requirements of a PC for use with xxx?
3. What versions of browser can be used?
4. What modem is recommended in PPP (Point-to-Point Protocol)?
Solution
1. Recommended Operating Systems:
· Windows 95/98
· Windows NT 4.0 with service pack 5 or later
· Windows 2000
Note:The Mac OS is not supported because, Mac version browsers do not have some key functions
(related to server push).
2. PC Requirements:
· Minimum requirements: 400MHz Pentium III with 64MB RAM
3. Recommended Browsers:
· Internet Explore 5.0 (or higher)
Note:The xxx does not require a special plug-in because the picture images use JPEG compression. However, some versions of Internet Explorer do not support 'Server Push' functions and the 'Active X' component will need to be installed . supplied with the xxx.
4. The xxx supports the following modems:
· 3Com 56k Faxmodem
· US Robotics Sportster Flash
· Ericsson K56 DTV
· Diamond Supra Express 56e PRO

The user found that:

• Search ‘What are the minimum requirements of a PC for use with’ finds document
• Search ‘What type of OS can be used with the’ finds document
• Search ‘What type os’ doesn’t
• Search ‘What type OS’ doesn’t
• Search ‘What os’ does
• Search ‘What type of OS can be used with the xxx-yyy’ doesn’t
• Search ‘What type of OS can be used with the xxx yyy’ does

As words are ored together these should all find the relevant document

Just going to paste my response in ‘as is’ for the time being:

First there’s a couple of points which I haven’t necessarily got chapter and verse on, but (currently) believe are true. These are related to:

• or-ing
• stopwords

Or-ing

To get the search terms to be ‘OR’-ed within the query I think you have to use the '' syntax. Spaces on there own don’t do it. The ‘$’ enables stemming, I think.

So running:

select document_id, SOLUTION from sc_solutions where contains(solution, ‘&1’) > 0 and document_id = 15526

SQL> / Enter value for 1: Flash Sportster old 4: contains(solution, ‘&1’) > 0 new 4: contains(solution, ‘Flash Sportster’) > 0

no rows selected

SQL> / Enter value for 1: FlashSportster old 4: contains(solution, ‘&1’) > 0 new 4: contains(solution, ‘FlashSportster’) > 0

15526

1. What type of OS can be used with the xxx?
2. What are the minimum requirements of a PC for use with xxx?
3. What versions of browser can be used?
4. What modem is recommended in

Note: not hugely relevant but I was tyring to prove this using ‘requirements minimum’. It appeared to be or-ing it in any case, but eventually noticed the text in green below. I think it was matching this.

Stoplist words

The way Oracle seems to process stopwords is, I reckon, just weird. If you have a stoplist in the search string in effect it turns it into a wild card word matching any other word (stopword or not).

So:

SQL> / Enter value for 1: requirements be what PC old 4: contains(solution, ‘&1’) > 0 new 4: contains(solution, ‘requirements be what PC’) > 0

15526

1. What type of OS can be used with the xxx?
2. What are the minimum requirements of a PC for use with xxx?
3. What versions of browser can be used?
4. What modem is recommended in

and also

SQL> / Enter value for 1: Mac is browsers old 4: contains(solution, ‘&1’) > 0 new 4: contains(solution, ‘Mac is browsers’) > 0

15526

1. What type of OS can be used with the xxx?
2. What are the minimum requirements of a PC for use with xxx?
3. What versions of browser can be used?
4. What modem is recommended in

[ its matching ‘Mac version browsers’ ]

But it doesn’t match if you leave the stop words out of the search:

SQL> / Enter value for 1: requirements PC old 4: contains(solution, ‘&1’) > 0 new 4: contains(solution, ‘requirements PC’) > 0

no rows selected

As I say this seems to me to be weird. But it seems to be as per the documentation. At:

http://www.stanford.edu/dept/itss/docs/oracle/9i/text.920 /a96517/query.htm (text manual)

It says

When you include a stopword within your query phrase, the stopword matches any word. For example, the query: ‘Jack was big’ matches phrases such as Jack is big and Jack grew big assuming was is a stopword.

Examples

So re: the examples below, currently I’d make the following assumptions.

Search ‘What are the minimum requirements of a PC for use with’ finds document

• as expected

Search ‘What type of OS can be used with the’ finds document

• as expected

Search ‘What type os’ doesn’t

As per the points above re ‘or-ing’ and stoplists, I believe you either need to explicitly ‘or’ it:

SQL> / Enter value for 1: whattypeos old 4: contains(solution, ‘&1’) > 0 new 4: contains(solution, ‘whattypeos’) > 0

15526

1. What type of OS can be used with the xxx?
2. What are the minimum requirements of a PC for use with xxx?
3. What versions of browser can be used?
4. What modem is recommended in

Or explicitly have a stopword in the place where the ‘of’ is. I used ‘what’ again here.

SQL> / Enter value for 1: what type what os old 4: contains(solution, ‘&1’) > 0 new 4: contains(solution, ‘what type what os’) > 0

15526

1. What type of OS can be used with the xxx?
2. What are the minimum requirements of a PC for use with xxx?
3. What versions of browser can be used?
4. What modem is recommended in

Search ‘What type OS’ doesn’t

• as above, I think

Search ‘What os’ does

• ‘What’ being a stopword is behaving like a wildcard. So it would also get a match with any other stopword I think. As per:

SQL> / Enter value for 1: mz os old 4: contains(solution, ‘&1’) > 0 new 4: contains(solution, ‘mz os’) > 0

15526

1. What type of OS can be used with the xxx?
2. What are the minimum requirements of a PC for use with xxx?
3. What versions of browser can be used?
4. What modem is recommended in

Note:‘mz’ is an Oracle default stopword, for some reason.

Started getting the error above this morning. The SQL server involved supports our Alteris installation. None of the users could get on either.

The main Microsoft reference is:
Microsoft doc

This covers a lot of ground, but something that seems/seemed to be significant is the (lack of) a SPN. SPN is a “service principle name”. My understanding is that it is a record in Active Directory which shows that resource X is up and running on server Y. It seems that this needs to be ther for the client to talk to the server.

The SPNs can be displayed by running setspn on the server. Setspn is in the Windows resource kit. I think there was a link from the doc above

> setspn -L bigserver

Registered ServicePrincipalNames for CN=BIGSERVER,OU=LONDON,OU=Servers,OU=SBE and SCE (UK),DC=eu,DC=mycompany,DC=com:
MSSQLSvc/bigserver.eu.mycompany.com:1433
HOST/bigserver
HOST/bigserver.eu.mycompany.com

When I ran the above for the server with the connectivity problem there was no entry for MSSQLSvc. I tried to add one as follows:

> setspn -A MSSQLSvc/otherserver.eu.mycompany.com:1433 otherserver

But this failed with:

Failed to assign SPN to account…

Instead out AD administrator used ADSI Edit (an AD GUI) to manually add the entry. This seems to have fixed it at a sqlserver level. Alteris users still can’t get in but I suspect this is because the Alteris application servers need to be bounced. This is happening as I type….

Why did the error suddenly occur ? The server was bounced for the first time in several weeks last night and the following hotfixes were applied:

KB899923
KB893066
KB893086
KB890859
KB893803
KB842773

Dunno which of the above if any made any difference

Well, it seems odd to me anyway….

If you set up a synonym pointing across a database link….

SQL> conn elvis@old_db
SQL> create synonym sale for sale@new_db
  2  /
Synonym created.

Other users cannot see it….

SQL> conn scott@old_db
Connected.
SQL> select count(*) from elvis.sale
  2  /
select count(*) from elvis.sale
                                *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from new_db

Unless you proceed it with the username (in bold below)

SQL> conn elvis/@old_db
Connected.
SQL> drop synonym sale;
Synonym dropped.
SQL> create synonym sale for <strong>elvis</strong>.sale@new_db
  2  /
Synonym created.

SQL> conn scott@old_db
Connected.
SQL> select count(*) from elvis.sale;

  COUNT(*)
----------
   3906455

SQL> 

Well, it seems odd to me anyway….

If you set up a synonym pointing across a database link….

SQL> conn elvis@old_db
SQL> create synonym sale for sale@new_db
  2  /
Synonym created.

Other users cannot see it….

SQL> conn scott@old_db
Connected.
SQL> select count(*) from elvis.sale
  2  /
select count(*) from elvis.sale
                                *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from new_db

Unless you proceed it with the username (in bold below)

SQL> conn elvis/@old_db
Connected.
SQL> drop synonym sale;
Synonym dropped.
SQL> create synonym sale for <strong>elvis</strong>.sale@new_db
  2  /
Synonym created.

SQL> conn scott@old_db
Connected.
SQL> select count(*) from elvis.sale;

  COUNT(*)
----------
   3906455

SQL> 

BJS is the scheduling tool we use on our Windows servers. Its produced by Camellia Software:

• Camellia website
  
  ….and it’s very good.
  
  I had a problem this morning though.
  
  

BJS jobs would go into ‘Awaiting Exec’ state for up to 45 minutes before running.

The Log file would show:


* BATCH JOB SERVER
* A Camellia Software Product - www.CamelliaSoftware.com
* Copyright 1994-1998
* Version 2.1 - A Build 529
* ***** J O B L O G

JOB NO. 0737


Job Name: BIG Backup PWHC Open
Username: xxx
Submitted by:

Server: bigserver
Source File: \bigserverD$BJSbig_backup_pwhc_open.bat
Output Directory: D:BJSBJSWORK
*
***

>
> 11/04/05 08:00:29 PM Job scheduled for execution
>
> 11/04/05 08:45:00 PM Job started execution
>

The reason for this was/is that there is a setting under Server-Administer-Change Service Parameters called ‘Maximum Batch Job Processes’. This limits the number of jobs you can run at any one time. It should be set to twice the number of jobs you might want to have running conccurrently.

There is also a setting against Job Class which does something similar - under Server-Administer-Job Class Maintenance

A case of RTFM….

BJS is the scheduling tool we use on our Windows servers. Its produced by Camellia Software:

• Camellia website
  
  ….and it’s very good.
  
  I had a problem this morning though.
  
  

BJS jobs would go into ‘Awaiting Exec’ state for up to 45 minutes before running.

The Log file would show:


* BATCH JOB SERVER
* A Camellia Software Product - www.CamelliaSoftware.com
* Copyright 1994-1998
* Version 2.1 - A Build 529
* ***** J O B L O G

JOB NO. 0737


Job Name: BIG Backup PWHC Open
Username: xxx
Submitted by:

Server: bigserver
Source File: \bigserverD$BJSbig_backup_pwhc_open.bat
Output Directory: D:BJSBJSWORK
*
***

>
> 11/04/05 08:00:29 PM Job scheduled for execution
>
> 11/04/05 08:45:00 PM Job started execution
>

The reason for this was/is that there is a setting under Server-Administer-Change Service Parameters called ‘Maximum Batch Job Processes’. This limits the number of jobs you can run at any one time. It should be set to twice the number of jobs you might want to have running conccurrently.

There is also a setting against Job Class which does something similar - under Server-Administer-Job Class Maintenance

A case of RTFM….

Going to try this out: http://download.microsoft.com/download/5/f/2/5f26ad97-ace1-4454-975c-bca19dbd61d1/sfu35intro.doc#_Toc59604538

SFU 3.5 includes Interix, a complete POSIX development environment tightly integrated with the Windows kernel. Both Korn and C shells are included, and the Bash shell is available as a free download. With more than 350 UNIX utilities and a single rooted file system, SFU provides a familiar environment for UNIX developers, users, and administrators.

Looking at Oracle’s External Tables feature, which ‘uses the ‘sql-loader’ engine.

Links include:

• http://www.quest-pipelines.com/newsletter-v6/0105_B.htm

• http://www.oracle.com/oramag/oracle/01-sep/o51o9i.html

Ran the following:

create or replace directory bjs_history as 'D:BJS_HISTORY';

drop table external_bjs_history;

CREATE TABLE external_bjs_history (bjs_sched_time varchar2(12),
bjs_job_no varchar2(8),
bjs_job_name VARCHAR2(40),
record_type varchar2(5),
job_started varchar2(17),
job_finished varchar2(17)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY bjs_history
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
badfile 'history.bad'
)
LOCATION ('history.dat')
)
REJECT LIMIT UNLIMITED;

select count(*) from external_bjs_history;

and got:

SQL> @c:bjs_owner

Directory created.
Table dropped.
Table created.

select count(*) from external_bjs_history
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "badfile": expecting one of: "exit, (, reject"
KUP-01007: at line 4 column 9
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

I took out the badfile clause and all OK:

Probably a rtfm type thing….

Or something…..

Blocking locks are easy enough to spot, through utllockt or v$session_waits or the goo-ey of your choice. The situation we had this morning seems to be that locks were being held which prevented a batch load from running. However, if you looked for them in a blocking locks type query you wouldn’t see diddley, ‘cos the batch load was bombing out straightaway. At least not unless you caught it at the right time

So, need to look at V$lock - in particular the ctime value. Found these:

0E3E08F0 0E3E09BC 20 TX 196617 877445 6 0 175785 0 1D95BCA4 1D95BCB8 20 TM 81940 0 2 0 175785 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST ——– ——– ———- – ———- ———- ———- ———- CTIME BLOCK

1D95B9D4 1D95B9E8 20 TM 81942 0 2 0 175785 0 1D95BC2C 1D95BC40 20 TM 81933 0 3 0 175785 0 1D95B4AC 1D95B4C0 20 TM 81938 0 2 0 175785 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST ——– ——– ———- – ———- ———- ———- ———- CTIME BLOCK

1D95B254 1D95B268 20 TM 81960 0 3 0 175785 0 1D95B614 1D95B628 20 TM 81952 0 3 0 175785 0 1D95B7F4 1D95B808 20 TM 81935 0 3 0 175785 0 Bounced the associated app, and all now ok

Note, 15 years on: I have have a clear idea of I was on about here, and I don’t do Oracle any more. I think I linked he ctime value to a ‘clock time’ and then tracked addr back to an application. I’m leaving it as published anyway a) just in case anybody else can decipher what I’m on about, and b) in case it becomes clear when I look at it next

This is a problem we had a month or so back. I’m going to cut and paste from my notes at the time…

We are trying to upgrade our warehouse from 8.1.7 to 9.2.0.4.

In testing the pre-production database, we are getting the error below from our systems integration tools (Crossworlds). This transfers data via jdbc to the warehouse. This works fine against the old 8i production database, but not against the 9i database.

ThreadPool Worker (#6775863)] [Type: Error] [MsgID: 90012] [Mesg: 
ParseException java.text.ParseException: Unparseable date: ""]

Used Metalink note Note 232357.1 to get the following versioning information:

Database Product Name is ... Oracle
Database Product Version is Oracle9i Enterprise Edition Release 9.2.0.5.0 - 

Pro duction
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 8.1.7.2.0
JDBC Driver Major Version is 8
JDBC Driver Minor Version is 1
=============

Following advice from Oracle, I copied cl* and nls* out of the 9i database $ORACLE_HOME/jdbc/lib to the same directory on the client.

This seemed to sort it out.

However there was an an associated problem which I’ll put into the next post….

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:

• Note 140571.1

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.

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:

• Note 140571.1

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.