Re: Alias for database connection via jdbc

From: Gunawan Yuwono <gunawan.yuwono_at_gmail.com>
Date: Wed, 26 Sep 2012 10:05:24 -0500
Message-ID: <CAPJkHXERTnY2zko17_bcmJe-1UrhQBw8=7FSu8zDi51-OfoOrw_at_mail.gmail.com>

Here is an example of setting service_names parameter in the database: - service_names = 'JOHNTEST'

The listener should now listen to the JOHNTEST service.

Use the following JDBC connection string (Note there's a slight different in the JDBC connection string when using SERVICE_NAME):

jdbc:oracle:thin:_at_//<hostname>:<port>/JOHNTEST

Or, you can spell out the connection string just like in the tnsnames.ora entry, something like this:

jdbc:oracle:thin:_at_
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=<port>))(CONNECT_DATA=(SERVICE_NAME=JOHNTEST)))

Hope that helps.

Thanks
Gunawan Yuwono
Oracle DBA
Kansas City

On Tue, Sep 25, 2012 at 2:39 PM, Patterson, Joel <Joel.Patterson_at_crowley.com
> wrote:

> I don't know java, but say for example your 'name' (of the collection of
> information used to connect to ORCL) could be JOHNTEST, and your URL
> possible (for thin client); jdbc:oracle:thin:_at_<hostname>:<port#>/<db
> name or service name> -- as an example, but I don't know if your even using
> thin client.
>
> As for tnsnames.ora, this is for software that does not connect directly
> to the database, but uses this file to obtain its information in order to
> connect to the database.
>
> So for example if you have connection information in tnsnames.ora that
> goes to ORCL, and you can use Sqlplus to succesffuly connect, and it works,
> then you could copy that entry and change the leftmost entry to JOHNTEST:
>
> ie
> ORCL =
> (DESCRIPTION =
> Etc
> )
>
> JOHNTEST =
> (DESCRIPTION =
> Etc
> )
>
> This leftmost entry beginning the connection information can be considered
> a 'connection string' and is arbitrary, and can be called anything --
> normally the same as a database name or ORACLE_SID. The software looks
> through the file and stops at the first entry that matches the 'connection
> string'.
>
> So in tnsnames.ora the entry is just a 'name' you use to identify what
> connection information you need. You should then be able to use sqlplus
> and connect to your same database using either ORCL or JOHNTEST. Ie
> username, password, connection string -- and both entries in tnsnames.ora
> will connect the same connection information needed to connect to ORCL.
>
> Java connects directly, so this information has to be part of the
> connection information that is used to connect.
>
> The Error ORA-12505 means that the listener is not listening for a
> JOHNTEST SID (database), which is why Gunawan is suggesting you try to
> configure the listener to do that...
>
> But if the database is actually SID ORCL, then the connect string should
> use ORCL... but you might be able to 'save' this information giving that a
> name JOHNTEST -- or otherwise you might need to pursue Gunawan's idea and
> create a service etc.
>
>
> Joel Patterson
> Database Administrator
> 904 727-2546
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Gunawan Yuwono
> Sent: Tuesday, September 25, 2012 1:38 PM
> To: JDunn_at_sefas.com
> Cc: oracle-l_at_freelists.org
> Subject: Re: Alias for database connection via jdbc
>
> John,
> Try using service. Either set service_names parameter, or use srvctl add
> service command if you are using ASM (Grid Infrastructire).
> Thanks,
> Gunawan Yuwono
> On Mon, Sep 24, 2012 at 7:26 AM, John Dunn <JDunn_at_sefas.com> wrote:
> > My oracle database name(SID) is ORCL
> >
> > However in order to make it easier to support a customer I would like
> > to be able to refer to the same database in my JDBC string as e.g.
> > JOHNTEST rather than ORCL
> >
> > What changes do I need to make to listener config files to enable me
> > to do this? I tried adding JOHNTEST to tnsnames.ora on the Oracle
> > server but I get a message ORA-12505, TNS:listener does not currently
> > know of SID given in connect descriptor
> >
> > Oracle is 11G on linux.
> >
> > Any help appreciated.
> >
> >
> >
> >
> >
> >
> >
> >
> > John
> >
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> Only the wisest and the stupidest of men never change - Confucius
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Only the wisest and the stupidest of men never change - Confucius


--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 26 2012 - 10:05:24 CDT