inherits the listeners environment
Kiro, July 30, 2003 - 9:36 am UTC
Hi Tom
You said:
When you connect "system/manager@tns" -- the dedicated server is spawned by the
listener, it inherits the listeners environment
I am a little bit confused about it. I am using locally installed sqlplus and have entry in registry "TNS_ADMIN" pointing to my local tnsnames.ora(c:\orant\tnsnames.ora). Listener in on another(remote) Win2000 box.
By my opinion every time when I have connected to "system/manager@tns" this local tnsnames.ora will be used instead of tnsnames.ora from listeners environment. Am I right or not?
Regard
Kiro
July 30, 2003 - 10:18 am UTC
you are wrong, the example above proves that.
when you connect via a listener, the environment comes from THAT LISTENER. if you use an 8i listener, you'll have an 8i environment;
when you connect direct, without the listener, the environment comes FROM YOU.
that was the entire point of the exercise above.
kiro, July 30, 2003 - 10:39 am UTC
Thanks Tom
inherits the listeners environment
Haratsaris, July 30, 2003 - 2:02 pm UTC
Tom,
I have a Oracle Applications 11.0.3 environment with 8.1.7 database that uses different TNS_ADMINS because I´ve got some special services like Report Agents and Transaction Managers (FNDFS,OEORPC) which belongs from a different Oracle Version (8.0.6). Could I just use one 8.1.7 listener for both services ( database and OAP Services) rather than another listener 8.0.6.
Thanks a lot !
July 30, 2003 - 2:24 pm UTC
yes, you should be able to as far as I know. I don't have an environment set up to test it out however. But you should be able to merge the two tnsnames into one and use just one. I don't know if APPS has any special requirements in this area.
Database Link and tnsnames.ora response
LHollister Sallwasser, July 30, 2003 - 5:02 pm UTC
Thank you for the explanation and the test case. This also clarified other tns issues I've experienced. We will configure as recommended, with the 9i listener serving the 8i databases, as soon as possible. One observation regarding the review posted by Haratsaris: given Tom's response regarding environments that are determined by the listener, you may want to keep the 8.0.6 listener working for the admin and middle tiers. There are library files that exist only in the old 8.0.6 home, and you may find that certain concurrent requests or external interfaces fail if these files can't be found by user apps. I run 11.5.8 ERP and CRM.
July 30, 2003 - 7:33 pm UTC
it'll set the oracle-home for you...
Interesting behavior...
Mark Wooldridge, July 30, 2003 - 10:15 pm UTC
As always, your knowledge of Oracle seems to be endless.
Sorry for asking a question in a review, but the a previous reviewers comment make me think. Would the automatic_ipc parameter affect what environment you would get? I could see where if set to yes and if on the same box the connection of system/manager@tns might get the local environment of the user connected to the database.
July 31, 2003 - 7:00 am UTC
automatic-ipc means you used the listener.
ipc is just interprocess communication, it doesn't mean you forked the process off and the only way to inherit an environment is to be the child process. the process would still be the child of the listener.
Database Link and tnsnames.ora response
Haratsaris, July 31, 2003 - 3:32 pm UTC
Ok, So if I use symbolic links from 8.1.7 to 8.0.6 libraries (8.1.7 missing libraries), 8.0.6 services should work with 8.1.7 listener, am I right ?
July 31, 2003 - 7:14 pm UTC
no no -- you just put the right oracle-home in the configuration files.
Excerllent! It solves a mystery in our replication environment.
A reader, October 14, 2003 - 6:08 pm UTC
Restart
Justin, March 03, 2005 - 4:39 am UTC
Hi,
I'had the same problem the original poster had:
One listener, for differeet db's, and the tns entry for the link was not in the tnsnames used by the listner.
Since these are all production databases.
What do I have to restart to reflect the changes?
Listner or the DB hosting the link or both?
Thanks for the perfect explanation for what seemed a mystery
Justin
March 03, 2005 - 7:52 am UTC
not sure what you are asking -- but if you were missing a tnsnames entry in the tnsnames.ora, you could just edit it, no start is needed for that.
Justin, March 03, 2005 - 6:10 am UTC
Sorry,
for the second part.
It'wasn't a restart thing.
We had put the tns entry in the listners tnsname, but that did not help.
Now, we found out
The listener is running under user1
but the instances were all started under user 2, where normally each instance should have been started with it's own user (that was a mistake in the shell script starting the instances).
Now the link worked, when we put the tns in the user2's tnsnames, not the listener's.
connection was always via user/pw@db
So my question is when is the listners tnsnames used, only when there doesnt exist one for the db?
What ist the order?
Regards,
Justin
PS: This textbox is really small even on a 17" normal screen.
March 03, 2005 - 7:54 am UTC
I like it small -- i want small, I want small, concise to the point....
but that aside, the "listeners" environment is used to find the tnsnames.ora file for database links always when you do "u/p@db" -- the TNS_ADMIN can be used to override any of the defaults.
If TNS_ADMIN is not defined.
Kashif, June 11, 2005 - 1:00 pm UTC
Hi Tom -
If the TNS_ADMIN environment variable is not defined on a Unix database server, what is the default search hierarchy when trying to resolve a service name defined in a database link? This is what I found on Metalink, Note 114085.1 (though I am unclear whether this hierarchy applies to both client connections and db link service name resolutions):
Sun Solaris running Oracle 8i or 9i
First: The oracle user's home directory is searched for a hidden '.tnsnames.ora'
Second: The value of the TNS_ADMIN environment variable.
Third: /var/opt/oracle
Fourth: $ORACLE_HOME/network/admin
Does this sound about right? Note that I am trying to figure out the search hiearchy for a db link's service name, not for a SQL Plus connection to the database. Hope this makes sense. Thanks.
Kashif
June 11, 2005 - 2:53 pm UTC
this is always talking about a client -- for the file is on the client and if you were on the server, we'd have already found it!
see
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:18064818873846 <code>
that is how I would check to see what it truly is -- what the client searches through to find the tnsnames.ora file. (truss on solaris is like strace)
for a dblink, the CLIENT is the DATABASE, so it'll be using the search path set up by the DATABASES environment. It'll go through the same search pattern as sqlplus does, assuming the environments are the same.
TNS_ADMIN typically "trumps" unless you have that hidden file in $HOME.
Ok...
A reader, June 11, 2005 - 7:38 pm UTC
Thanks Tom. So, to ensure that my database (Oracle) environment uses the TNS_ADMIN variable, I go in as the Oracle user and set the TNS_ADMIN variable, right? Thanks.
Kashif
June 11, 2005 - 8:18 pm UTC
before STARTING the database, yes.
Excellent explanation of what spawns dedicated server
Max, November 07, 2005 - 9:05 pm UTC
Hi Tom,
That is an absolutely clear explanation of how server process is started.
How does direct hand off fits the picture here? For example I - start sqlplus session on the same machine as database server and listener
- conn scott/tiger@mydatabase (this should connect sqlplus process to the listener)
- listener spawns a new dedicated server process and hands off (not redirect) the connection
Is this done on the TCP/IP level:
- request listener for TCP/IP connection at port 1521
- listener responds with ask and provides new random generated port (say 2343)
- client process connects to the listener through 2343
- listener passes port 2343 to dedicated server
Or there is other mechanism involved in direct
hand off?
Looking forward to you reply
November 08, 2005 - 9:48 pm UTC
it would be that the listener creates new socket on wildcard port, listener forks off dedicated server, listener closes that socket but tells client "if you connect to 2343...." and client connects to dedicated server.
Thank you!
Bob, October 17, 2006 - 2:44 pm UTC
2 hours of db link debugging and I fixed it it 2 minutes after reading your first two paragraphs.