Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Connection problem through sqlplus

Re: Connection problem through sqlplus

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Tue, 15 Aug 2006 16:50:31 GMT
Message-ID: <J41tGI.2y9@igsrsparc2.er.usgs.gov>


Anoop wrote:

> DA Morgan wrote:
>> Anoop wrote:
>>> Terry Dykstra wrote:
>>>> "Anoop" <anoopkumarv_at_gmail.com> wrote in message
>>>> news:1155612727.627326.123500_at_b28g2000cwb.googlegroups.com...

>>>>> Hi All,
>>>>>
>>>>> Need some help in this baffling issue..
>>>>>
>>>>> I have 2 instances of oracle installed on my solaris box. The oracle
>>>>> versions are 10g and 9i.
>>>>>
>>>>> I hardly use the 10g and have 4 databases configured on 9i.
>>>>>
>>>>> I have written some shell scripts to connect (from the same box) to the
>>>>> 9i databases and they all make use of the sqlplus command like this:
>>>>>
>>>>> sqlplus <user>/<password>@<db>
>>>>>
>>>>> Of the 4, I can connect successfully to 3 of the databases using the
>>>>> above format in my shell scripts...
>>>>>
>>>>> But the 4th, I just dont seem to be able to connect and am not sure
>>>>> what the problem is..
>>>>> I am connecting from the server box itself, I mean the database is
>>>>> installed on the same box.
>>>>>
>>>>> If I use a complete qualifying command like this, I can connect
>>>>> perfectly fine:
>>>>>
>>>>> sqlplus
>>>>> 'rem@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=redbull)(PORT=1521)))(CONNECT_DATA=(SID=c1remdev)))'
>>>>>
>>>>> but, if I do the following I cannot connect:
>>>>>
>>>>> sqlplus rem/cap_at_c1remdev
>>>>>
>>>>> I get this error:
>>>>> ERROR:
>>>>> ORA-12154: TNS:could not resolve the connect identifier specified
>>>>>
>>>>> Also, the tnsping works for all 3 except the above database. Here is
>>>>> the output...
>>>>>
>>>>> [oracle_at_redbull ~]$ tnsping c1remdev
>>>>>
>>>>> TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on
>>>>> 14-AUG-2006 22:02:30
>>>>>
>>>>> Copyright (c) 1997, 2005, Oracle. All rights reserved.
>>>>>
>>>>> Used parameter files:
>>>>> /opt/app/oracle/product/10/network/admin/sqlnet.ora
>>>>>
>>>>> TNS-03505: Failed to resolve name
>>>>>
>>>>>
>>>>> I have also tried updating / fiddling with the tnsnames.ora and
>>>>> listener.ora files. Here are the contents (but to no avail):
>>>>>
>>>>>
>>>>> [oracle_at_redbull ~]$ more product/10/network/admin/listener.ora
>>>>> SID_LIST_LISTENER =
>>>>> (SID_LIST =
>>>>> (SID_DESC =
>>>>> (GLOBAL_DBNAME = fedex)
>>>>> (ORACLE_HOME = /opt/app/oracle/product/10)
>>>>> (SID_NAME = fedex)
>>>>> )
>>>>> (SID_DESC =
>>>>> (GLOBAL_DBNAME = captest)
>>>>> (ORACLE_HOME = /opt/app/oracle/product/10)
>>>>> (SID_NAME = captest)
>>>>> )
>>>>> (SID_DESC =
>>>>> (GLOBAL_DBNAME = c1rem)
>>>>> (ORACLE_HOME = /opt/app/oracle/product/9.2.0)
>>>>> (SID_NAME = c1rem)
>>>>> )
>>>>> (SID_DESC =
>>>>> (GLOBAL_DBNAME = c1apdev)
>>>>> (ORACLE_HOME = /opt/app/oracle/product/9.2.0)
>>>>> (SID_NAME = c1apdev)
>>>>> )
>>>>> (SID_DESC =
>>>>> (GLOBAL_DBNAME = c1remtst)
>>>>> (ORACLE_HOME = /opt/app/oracle/product/9.2.0)
>>>>> (SID_NAME = c1remtst)
>>>>> )
>>>>> (SID_DESC =
>>>>> (GLOBAL_DBNAME = c1aptst)
>>>>> (ORACLE_HOME = /opt/app/oracle/product/9.2.0)
>>>>> (SID_NAME = c1aptst)
>>>>> )
>>>>> (SID_DESC =
>>>>> (GLOBAL_DBNAME = c1remdev)
>>>>> (ORACLE_HOME = /opt/app/oracle/product/9.2.0)
>>>>> (SID_NAME = c1remdev)
>>>>> )
>>>>> )
>>>>>
>>>>> LISTENER =
>>>>> (DESCRIPTION =
>>>>> (ADDRESS = (PROTOCOL = TCP)(HOST = redbull)(PORT = 1521))
>>>>> )
>>>>>
>>>>> [oracle_at_redbull ~]$ more product/9.2.0/network/admin/tnsnames.ora
>>>>> c1aptst =
>>>>> (DESCRIPTION =
>>>>> (ADDRESS_LIST =
>>>>> (ADDRESS = (PROTOCOL = TCP)(HOST = redbull)(PORT = 1521))
>>>>> )
>>>>> (CONNECT_DATA =
>>>>> (SERVICE_NAME = c1aptst)
>>>>> )
>>>>> )
>>>>> c1apdev =
>>>>> (DESCRIPTION =
>>>>> (ADDRESS_LIST =
>>>>> (ADDRESS = (PROTOCOL = TCP)(HOST = redbull)(PORT = 1521))
>>>>> )
>>>>> (CONNECT_DATA =
>>>>> (SERVICE_NAME = c1apdev)
>>>>> )
>>>>> )
>>>>> c1remdev =
>>>>> (DESCRIPTION =
>>>>> (ADDRESS_LIST =
>>>>> (ADDRESS = (PROTOCOL = TCP)(HOST = redbull)(PORT = 1521))
>>>>> )
>>>>> (CONNECT_DATA =
>>>>> (SERVICE_NAME = c1remdev)
>>>>> )
>>>>> )
>>>>>
>>>>>
>>>>> Can somebody please point me as to where am I making the obvious
>>>>> mistake?
>>>>>
>>>>> I do not want to use the long version of sqlplus to connect (which
>>>>> always works) because I have a lot of shell scripts and I do not want
>>>>> to modify all of them for this sake.
>>>>>
>>>>> Thanks,
>>>>> Anoop
>>>>>
>>>> Your 9.2 tnsnames.ora has only 3 entries (whereas you are expecting 4), so
>>>> it appears that is the one being picked up by your script.
>>>>
>>>> --
>>>> Terry Dykstra
>>> Yes Terry,
>>>
>>> you are right - but the 9.2 is not even being referred to, I think as
>>> the 10g tnsnames.ora is taking precedence....
>>> I should have noticed that myself beforehand.. my bad!!
>>>
>>> Thanks,
>>> Anoop
>> When connecting to the 10g you need to have ORACLE_HOME pointing to the
>> 10g home. When connecting to 9i ... the 9i home. I think you may need to
>> take a look at the values of your environment variables.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan_at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org
> 
> What I see on our server is that we have the ORACLE_HOME pointing to
> the 10g...
> 
> Is that the reason why the 10g tnsnames.ora is taking precedence? I do
> not use 10g for any of my work... so should I reset my oracle_home
> everytime I need to connect to 9i dbs??
> 
> Thanks,
> Anoop
> 

If you have $ORACLE_HOME pointing to your 10g installation, then the default location for the tnsnames.ora file is $ORACLE_HOME/network/admin, meaning it is using your 10g tnsnames file. In your words....it is taking "precedence".

On my *nix servers, I use c-shell a lot. When I want to connect to a different database, I type "chsid orcl" where "orcl" is my new ORACLE_SID. The command "chsid" is not a *nix command nor an Oracle utility. Rather, I have defined this as an alias in my .cshrc file. This alias is defined as follows:

chsid setenv ORACLE_SID !*;source /usr/local/bin/coraenv;set prompt="myhost $ORACLE_SID% "

So the chsid alias does three things. First, it sets your ORACLE_SID environment variable to the given parameter. Next, it sources the /usr/local/bin/coraenv script. This script is supplied by Oracle and will set all of my Oracle-related environment variables to the correct locations for this instance. It reads the oratab file to know the correct ORACLE_HOME for this SID. Finally, the alias changes my prompt so that I know that the environment has changed.

The above does not work as well for other shells. If you are using bask or ksh, then do the following:

export ORACLE_SID=my-new-sid
. /usr/local/bin/oraenv

I'm not an expert in the other shells so I may have that wrong...I'm sure someone will correct me if I do.

Cheers,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown
Received on Tue Aug 15 2006 - 11:50:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US