This discussion is archived
4 Replies Latest reply: Apr 26, 2013 9:46 AM by user783777 RSS

Resolving alias in connection pool url via tnsname.ora file

user783777 Newbie
Currently Being Moderated
Hi,

In one of the weblogic environment, a jdbc data source has a connection pool url as jdbc:oracle:thin:@adpodj1_dbs.oracleoutsourcing.com. The driver used is oracle.jdbc.xa.client.OracleXADataSource

I understand that the connection pool url is using an alias (+adpodj1_dbs.oracleoutsourcing.com+) which get resolved via tnsname.ora file. I want to be able to resolve the alias to get the host,port,sid/service_name for the connection pool url which would be available in the tnsname.ora

My query is how to identify the location of tnsname.ora file either from command line or using mbeans ? Can you please help me with this.

Thanks,
Saurabh
  • 1. Re: Resolving alias in connection pool url via tnsname.ora file
    Joe Weinstein Expert
    Currently Being Moderated
    Hi. That understanding is incorrect. When used in the OCI mode, the driver URL has 'oci' in it,
    and certain forms of the URL will have references to tnsnames.ora entries. The tnsnames.ora file
    is under the ORACLE_HOME.
    When the driver is used in the thin (all-java) mode, the typical URL specifies a machine, port,
    and DBMS name. In the case you show, the machine name is adpodj1_dbs.oracleoutsourcing.com.
    The driver expects the OS to find this machine via the DNS service when the driver opens a socket
    to that machine.
  • 2. Re: Resolving alias in connection pool url via tnsname.ora file
    user783777 Newbie
    Currently Being Moderated
    Hi. Thank you for the reply. I actually read about the TNSNames alias in the following document and thought that this works for both thin and OCI driver - http://docs.oracle.com/cd/B28359_01/java.111/b31224/urls.htm#BEIJFHHB (Table 8.3)

    The doc also mentions (http://docs.oracle.com/cd/B28359_01/java.111/b31224/urls.htm#BEIDIJCE) -

    The oracle.net.tns_admin system property must be set to the location of the tnsnames.ora file so that the JDBC Thin driver can locate the tnsnames.ora file. For example:

    System.setProperty("oracle.net.tns_admin", "c:\\Temp");
    String url = "jdbc:oracle:thin:@tns_entry";

    Can you please help me clarify how did you determine that the alias is not a TNSNames alias and is actually a machine name ?

    Appreciate your help on this.
  • 3. Re: Resolving alias in connection pool url via tnsname.ora file
    Joe Weinstein Expert
    Currently Being Moderated
    You'll just have to read the docs carefully. If you have 'thin' in the URL, there is a way to ask it
    to find and use a tnsnames.ora file, but the URL and properties will be specific to that. 99.99% of
    the time, you'll be seeing the no-tnsnames, direct to DBMS mode with the thin URL.

    If there's an @tns_entry in the URL, there will be no port or SID...
  • 4. Re: Resolving alias in connection pool url via tnsname.ora file
    user783777 Newbie
    Currently Being Moderated
    So a System.getProperty("oracle.net.tns_admin") would return the location of tnsnames.ora file. Then I can get the properties from the tnsnames.ora file corresponding to @tns_entry. Is this right ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points