Re: using ifile in the tnsnames.ora file

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_at_quest.com> 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."
>
>

--
http://www.freelists.org/webpage/oracle-l