Re: using ifile in the tnsnames.ora file
- From: "Stefan Knecht" <knecht.stefan@xxxxxxxxx>
- To: Jacques.Kilchoer@xxxxxxxxx
- Date: Fri, 9 Feb 2007 10:05:27 +0100
I've had the same situation a little while ago. And after tons of tests and
a very long lasting service request with oracle - it basically comes down to
this:
I've created an enhancement request with Oracle Support, asking for this
functionality (because it wasn't documented). Oracle then told me that this
feature was already there. But, "2. I am not sure whether this can be
considered as a BUG for IFILE issue as this has been used since oracle 8i.
This is not a recommendation by oracle to use IFILE. " was the response.
Basically, if you're using IFILE in tnsnames.ora, you're entering uncharted
territory - and you won't get a lot of support if it hits the fan. And
there's several conditions, where it won't work - apart from the one you've
already found out, re database links.
In the end, it appears to me that the problem lies in the dynamic reloading
of the file. That mechanism seems to break once you've got an IFILE line in
your tnsnames.ora. I've tested this on many different versions of oracle --
and many of them behave differently. Most of the time though, you can
reproduce the "bug" like this:
- open sql*plus
- connect to a tns alias referenced in your tnsnames.ora (at the time of
sql*plus startup)
- while sql*plus is still running, change an entry in your tnsnames.ora, and
try to connect to the new service name
- you'll find that this works ONLY IF you dont have an IFILE entry in your
tnsnames.ora - because once you do, you're stuck with the version of the
tnsnames.ora you've had when you started sql*plus.
Therefore, I'm guessing, that when you bounce the database containing the db
links, you'll be able to connect to them - as the database will also re-read
the file only on startup, once you've added an IFILE to it.
Stefan
On 2/8/07, Jacques Kilchoer <Jacques.Kilchoer@xxxxxxxxx> wrote:
Do you use an IFILE parameter in a network configuration file (
tnsnames.ora, sqlnet.ora, other) to consolidate entries in a central
location? I guess it's an obsolete practice and I should stop.
I've been using IFILE parametes in the tnsnames.ora file for as long as
I've been using Oracle. Recently however, when creating database links, I
noticed that the database link was using an alias that is coming from an
"ifile" in the tnsnames.ora, even though SQL*Plus on the server could use
that entry. I've never had problems using an IFILE in the tnsnames.oraexcept
the one I recently encountered with database links.
Imagine my surprise when I saw this on Tom Kyte's website:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:489021635775
Quoter: "IFILE is not supported for tnsnames/listener/sqlnet/protocol.ora
files."
I checked the network administration / reference manuals for 8.0.6, 8.1.7,
9.0, 9.2, 10.1, the "ifile" parameter is never mentioned for the network
configuration files (sqlnet.ora, tnsnames.ora, listener.ora, cman.ora,
names.ora, ldap.ora) - but it is mentioned in the server reference manual
as a valid parameter for the init.ora file.
Is this a new limitation? For example I quickly found Metalink note
1017689.102 that tells me I can use ifile in the tnsnames.ora and
listener.ora files with an Oracle 8.0 listener.
I found a couple of recent notes that mentioned a tnsnames.ora containing
an ifile (Notes 397684.1 and 397567.1) but both of these notes had the
comment "This document is being delivered to you via Oracle Support's Rapid
Visibility (RaV) process, and therefore has not been subject to an
independent technical review."
Other related posts:
