Further to the earlier post today regarding setting up client access to a 10g database via CMAN - which I have now resolved thanks to aussie_dba - I now have another hurdele to overcome, which I hope is simply due to my unfamiliarity with oracle networking.

The client PC has a tnsnames.ora containing a number of connections to the various databases on the server, which are of the form

<db> =
(DESCRIPTION =
(SOURCE_ROUTE=yes)
(ADDRESS = (PROTOCOL = TCP)(HOST = <server>)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = <server>)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = <dbname>)
)
)

which all work fine for ODBC or SQL Plus.

I am now trying to connect to one of the databases via an application's API which uses JDBC to connect, and I cannot get it to work. Investigating the structure of the connection string it appears by default to want something of the form

jdbc:oracle:thin@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx
.xxx.xxx)(PORT=xxxx)))(CONNECT_DATA=(SID=xxxxx)))


which is the "normal" structure of a tns connection entry. If I try simply replacing the (DESCRIPTION ...) section with the one from my tnsnames.ora I get a Java error saying the string is malformed.

Has anybody experienced this before, and if so, how did they work around it? Is it possible to define a local service on the client that I can connect to in the normal fashion, which then routes everything off to the remote server via connection manager?

regards

Richard

Richard,

I don't do CMAN, but it was my underdtanding that it bypassed each PC's tnsnames.ora, in favor of a central one.

Have you thought about dumping CMAN, and just using standard coinnectivity? The only downside is managing all of the tnsnames,ora files, one on each PC client. The docs have this example:

http://www.cs.umbc.edu/help/oracle8/networ...a67440/appb.htm

Oracle Connection Manager Configuration File (CMAN.ORA)

The Connection Manager configuration file (CMAN.ORA) contains the parameters that specify preferences for using Oracle Connection Manager. CMAN.ORA is located at $ORACLE_HOME/network/admin on UNIX and ORACLE_HOME\network\admin on Windows NT. For more information on each individual parameter, see "Oracle Connection Manager Parameters (CMAN.ORA)"

cman = (address = (protocol=tcp)(host=anyhost)(port=1630))
cman_admin = (address=(protocol=tcp)(host=anyhost)(port=1830))

cman_profile = (parameter_list=

(maximum_relays=512)
(log_level=1)
(tracing=yes)
(trace_directory=/oracle/network/trace)
(relay_statistics=yes)
(show_tns_info=yes)
(use_async_call=yes)
(authentication_level=0)

)
# the following specifies a rule for single access control #
cman_rules = (rule_list=

(rule=(src=spcstn)(dst=x)(srv=x)(act=accept))

)



Here is a method to test it:

http://www.praetoriate.com/teas_prae_util24.htm

That's not really it - it allows you set up additional listeners on specific ports, and then apply firewall-like filtering to connection requests so that for example you can restrict connection to certain databases to requests from a particulat set of IP addresses. This is precisely why I want to use it - so that my contractors can remotely access our development server, but I can ensure that they only see the databases on it that are relevant to their project.



Yes. Having spent several days getting it to work at all for ODBC and SQL/Plus, discovering this further gotcha is rather depressing. According to the Oracle docs - discovered since my original post on this topic - the syntax

String myURL =
"jdbc:oracle:thin:@(description=(address_list=
(address=(protocol=tcp)(port=1610)(host=webHost))
(address=(protocol=tcp)(port=1521)(host=oraHost)))
(connect_data=(INSTANCE_NAME=orcl))
(source_route=yes))";

should work. I have tried this, replacing port numbers and host names with mine, and I just get a Java stack dump beginning

java.sql.SQLException: Invalid Oracle URL specified
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:197)

I have examined my URL very carefully several times and I can't see anything wrong with it - but feel free to double check it!

jdbc:oracle:thin@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(PORT=1522)(H
OST=<host ip>))(ADDRESS=(PROTOCOL=TCP)(PORT=1521)(HOST=<host ip>)))(CONNECT_DATA=(INSTANCE_NAME=PMDB))(SOURCE_ROUTE=yes))

I can't believe it is case sensitive, but I'm reduced to trying that next...

Richard

"I'm reduced to trying that next..."

Could you log a service request on Metalink?

http://metalink.oracle.com

We're not on support, but I think I'm winning now:

The URL is not case sensitive, but I had missed the colon out in front of the @ sign. Changing that gave me this instead:

java.sql.SQLException: Io exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=169869568)(ERR=12504)(ERROR_STACK=(ERROR=(CODE
=12504)(EMFI=4))))
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:334)

After some Googling around, it looked as if the error was something to do with it not understanding what it was trying to connect to, so I changed INSTANCE_NAME in the URL (which looked wrong anyway, even though that's what it says in the Oracle docs for 10g) to SERVICE_NAME...

AND IT NOW WORKS - so I can go back to having a quiet Saturday evening at home, and put the laptop down!

Richard