tnsping on a non oracle related account !!!

[Henrique Silva_3]

Here is a good one, which I could not answer, since I have never encountered this problem before.

I am having problems with a apache web server account. The c program accesses the local DB fine from the command prompt, but not from the web cgi-bin program.

I think this is an environment problem on the web server, and the same program, when executed on another box, access this local DB fine.

Looking at the sqlnet.log file generated, we get this error :

Fatal NI connect error 12541, connecting to:
(DESCRIPTION=(CONNECT_DATA=(SID=*)(SERVICE_NAME=RIM.)(CID=(PROGRAM=)(HOST=HOSTHERE)(USER=webuser)))(ADDRESS=(PROTOCOL=TCP)(HOST=RIM)(PORT=1521)))

as you can see, SID is *, which is not correct. ANd service name is also trying to concatenate something to the correct SID name.

NOW, the interesting part, and I am sure I am about to learn something here.

When I login to this account, webuser, on the box that this works from, it has no oracle environment setup at all, but If I execute tnsping from that account, I get something like this :

tnsping SID_NAME

under the host I get the correct host name

if I try the same thing on the local machine, it does not revolve to the host name, instead it uses the SID_NAME as the hostname !!! ???

there is no sqlnet.ora file on these boxes ( not sure why )

So, my question here is !!!!

how does an account with no oracle environment resolves hostname on one box OK but not on the other ? I want to be able to explain to somebody, what is the process that tnsping uses when there are no oracle environments setup for a given account, which is what is happening here. I would like to KNOW as well :-))

Thanks,

Henrique Silva

[T G Manikandan]

Just check whether you have configured a sql net connection on the machine which is working
The apache should either use the sql net to connect to the database or the ODBC.

I am not sure about the configuration of the apache but would tell that the sql net should be configured.

Use Net8 configuration Assistant for the same.


Thanks

[Henrique Silva_3]

That is the thing. There is no sqlnet.ora file on that box where it is working.

when you do a tnsping from the webuser account, which has no ORACLE environment variables set, it resolves the hostname correctly !!!! Somehow !!!!

say, tnsping from host green.com I get :

$ /opt/oracle/product/8.1.7/bin/tnsping TEST

TNS Ping Utility for HPUX: Version 8.1.7.3.0 - Production on 26-NOV-2002 07:25:46

(c) Copyright 1997 Oracle Corporation. All rights reserved.

Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=GREEN.COM)(PORT=1521))
Message 3509 not found; No message file for product=network, facility=TNS


when you try the same thing from the non-working box, you get an ORA-03505 with not much info at first, and if you try to use the hostname as the instance name below, you get the host as the instance you are trying to tnsping :

$ /u00/app/oracle/product/8.1.7_64/bin/tnsping TEST

TNS Ping Utility for HPUX: Version 8.1.7.4.0 - Production on 26-NOV-2002 10:29:38

(c) Copyright 1997 Oracle Corporation. All rights reserved.

TNS-03505: Message 3505 not found; No message file for product=network, facility=TNS
$ /u00/app/oracle/product/8.1.7_64/bin/tnsping GREEN

TNS Ping Utility for HPUX: Version 8.1.7.4.0 - Production on 26-NOV-2002 10:30:00

(c) Copyright 1997 Oracle Corporation. All rights reserved.

Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=GREEN)(PORT=1521))
Message 3509 not found; No message file for product=network, facility=TNS

So, we found yesterday that in the resolv.conf, there were some domains that might explain why the SID name was appearing as RIM, however, what I need to understand is HOW A NON-ORACLE RELATED ACCOUNT ( webuser, which has no oracle variables ), and this is outside the box guys, do not think as it being apache here but just an account, say joeblow, how does it resolve SID and hostname when a tnsping is executed ? I could not explain it yesterday, and I sure would like to know.

My guess is that when you do a tnsping, it looks at your env and if it does not find anything there, it will use whatever is on the resolv.conf file in order to find out whether it will use hosts, NIS, or whatever, to at least find a system with the same name as the instance you are trying to tnsping !!!

But htis is just a guess, and I need to know exactly what happens, so I can make some sense out of this !!!

Thanks,

Henrique Silva

[Ian Dennison_1]

All the User needs to resolve the hostname is a tnsnames.ora, and a variables named TNS_ADMIN which points to the directory. And the executable of course.

a) Do you have a tnsnames.ora accessible by this User?
b) Do you have any other Oracle related variables in your shell?
c) What is the output of your 'env' command as the user?

Share and Enjoy! Ian

[Henrique Silva_3]

linked tnsnames.ora from /etc to the $ORACLE_HOME/network/admin directory, and listener.ora and it works now !!!!

Again, can somebody explain the process to me ? This user has NO ORACLE ENVIRONMENTS and the HOST was resolving the the proper ( local ) system. It did not find the instance, but resolved the host. I am still trying to understand how tnsping and ping relate here, and how the environment substitution is happening !!!

Thanks,

Henrique Silva

[Brian Crabtree · Accepted solution]

The search path for the SQLNET files (tnsnames,listener,etc) are as follows:

$TNS_ADMIN
/etc
$ORACLE_HOME/network/admin
~/.tnsnames.ora

It checks to see if the variable points to a directory, and then checks to see if files exist. The "Msg not found" messages mean that you do not have a valid $ORACLE_HOME variable set. The tnsping command will use DNS to define the host if it does not find the input variable in the tnsnames.ora file (hence you get a response from "GREEN" but not from "TEST").

Hope this helps.

Brian

[Wodisch]

Hi Henrique,

what about the "$TWOTASK" variable?
Even though it is quite an old-fashioned one, it still does work, AFAIK...
Its content would be the TNSNAME to be used.
And if the process using Net8 does a "cd" to the $ORACLE_HOME directory to be used, and has "." in its $PATH and maybe even its "$SHLIB_PATH", then that could be sufficient.

FWIW,
Wodisch

[Henrique Silva_3]

Thanks Brian.

This is really what I was looking for. An explanation of the places an account will look for, if it does not have any Oracle variables setup.

What I will do however, is make sure that this web account has ALL the oracle variables setup, so that it does not have any problems in the future.

Thanks everyone.

Henrique Silva

[Surya Narayana]

Did you check httpd.conf? Look for environment variables set with the SetEnv line in this file.
I personally like to set up any env variables that are required for the web server, in this file.
Surya.

[Henrique Silva_3]

Thanks, I will check this out !!!

the problem has been solved by adding the symbolic links from /etc to the *.ora files on ORACLE_HOME/network/admin, but this might have been the solution all along. The problem is ALWAYS been apache not being able to map the proper environments, and this might have been the answer.

thanks,

Henrique