Re: Strange problem logging on...

From: Volker Hetzer <volker.hetzer_at_ieee.org>
Date: Tue, 13 May 2003 20:09:25 +0200
Message-ID: <b9rccl$7mn$1@dackel.pdb.sbs.de>

Hans Forbrich wrote:
> Volker Hetzer wrote:
>
>> Hans Forbrich wrote:
>>> Volker Hetzer wrote:
>>>
>> sqlplus kurs/kurs@$ORACLE_SID works too.
>> Only when sqlplus has to take the SID from the environment, it fails.
>>
>>> the simplest environment the target is defined in the tnsnames.ora
>>> on
>>> the client machine in the $ORACLE_HOME/network/admin directory.
>>>
>>> Therefore, how is your tnsnames.ora configured?
>>
>> # TNSNAMES.ORA Network Configuration File:
>> /opt/oracle/client/9.2.0.1.0/network/admin/tnsnames.ora # Generated
>> by Oracle configuration tools.
>> ECADTST.ECAD =
>> (DESCRIPTION =
>> (ADDRESS_LIST =
>> (ADDRESS = (PROTOCOL = TCP)(HOST = database1.ecad)(PORT = 1521))
>> )
>> (CONNECT_DATA =
>> (SERVICE_NAME = ECADTST.ecad)
>> )
>> )
>
> Looks reasonable - however tnsnames.ora is extremely (let's say
> rediculously) sensitive to formatting. The header indicating it is
> generated is comforting.
>
> This is a basic troubleshooting process - instead of going through
> the beginner level basics (and potentially inadvertently insulting
> you), please describe what you have done/not done and are capable of
> doing. For example - what about tnsping? what about your Oracle
> related environment variables?

I'll try my best :-)

I installed the client software.
When the net configuration assistant ran, it tested the connection successfully. However, I was not able to connect using sqlplus. I *was* able to tnsping the db.
Turned out, tnsping used the hostnames method to connect to the db.

Then I've used netmgr to make tnsnames the only supported way of accessing the database. here is my sqlnet.ora:
# SQLNET.ORA Network Configuration File: /opt/oracle/client/9.2.0.1.0/network/admin/sqlnet.ora # Generated by Oracle configuration tools. TRACE_DIRECTORY_CLIENT = /tmp
LOG_FILE_CLIENT = x
NAMES.DEFAULT_DOMAIN = ecad
LOG_DIRECTORY_CLIENT = /tmp/
TRACE_FILE_CLIENT = x.trace
TRACE_LEVEL_CLIENT = SUPPORT
NAMES.DIRECTORY_PATH= (TNSNAMES) My environment is:

CVSROOT=/opt/cvs/rcs/cvsroot/
DISPLAY=:0.0
HISTSIZE=1000
HOME=/opt/users_1/hetzer
HOSTNAME=lin20
HOSTTYPE=i386
INPUTRC=/etc/inputrc
LANG=en_US
LESSOPEN=|/usr/bin/lesspipe.sh %s
LOGNAME=hetzer
LS_COLORS=no=00:fi=00:di=01;34:ln=01;36:pi=40;33:so=01;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=01;32:*.cmd=01;32 :*.exe=01;32:*.com=01;32:*.btm=01;32:*.bat=01;32:*.sh=01;32:*.csh=01;32:*.tar=01;31:*.tgz=01;31:*.arj=01;31:*.taz=01;31:*.lzh=01;31: *.zip=01;31:*.z=01;31:*.Z=01;31:*.gz=01;31:*.bz2=01;31:*.bz=01;31:*.tz=01;31:*.rpm=01;31:*.cpio=01;31:*.jpg=01;35:*.gif=01;35:*.bmp= 01;35:*.xbm=01;35:*.xpm=01;35:*.png=01;35:*.tif=01;35: MACHTYPE=i386-redhat-linux-gnu
MAIL=/var/spool/mail/hetzer
OLDPWD=/opt/users_1/hetzer
ORACLE_HOME=/opt/oracle/client/9.2.0.1.0 ORACLE_SID=ECADTST.ECAD
OSTYPE=linux-gnu
PATH=/opt/Acrobat4/bin:/opt/users_1/hetzer/Linux/Browsers/N7.0:/opt/tcltk/8.3.3/bin:/opt/linux/netscape/6.1:/opt/users_1/hetzer/Linu

x/bin:/opt/users_1/hetzer/Linux/Pub/bin.HP-UX:/bin:/usr/bin:/usr/local/bin:/opt/Acrobat4/bin:/opt/users_1/hetzer/Linux/Browsers/N7.0
:/opt/tcltk/8.3.3/bin:/opt/linux/netscape/6.1:/opt/linux/netscape/6.1:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/opt/users_1/hetze
r/Linux/Pub/bin:/opt/users_1/hetzer/Linux/Pub/bin:/opt/oracle/client/9.2.0.1.0/bin
PS1=\u@\h:\!\$

PS2=>
PWD=/opt/oracle/client/setup
SHELL=/opt/gnu/bin/bash
SHLVL=2
TCLLIBPATH=/opt/tcltk/8.3.3/lib
TERM=xterm
USER=hetzer
_=/usr/bin/env
WINDOWID=6291470
XMODIFIERS=@im=none

What else?
I've tried to change ECADTST.ECAD = in the tnsnames.ora to ECADTST = and then connecting with sqlplus kurs/kurs_at_ECADTST, to no avail.

sqlplus kurs/kurs_at_ECADTST.ECAD still works and sqlplus kurs/kurs with ECADTST.ECAD as SID still doesn't.

Greetings!
Volker Received on Tue May 13 2003 - 13:09:25 CDT