TNSNAMES File.

[The Brit]

Hi Guys, I am having a terrible time with the TNSNAMES file on a new OpenVMS Blade cluster.
System is running 8.3-1H1 with Oracle 10G client. The TNSNAMES.ORA file contains two entries,

$ ty tnsnames.ora
DEV.TEMPO.COM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = TABDL385A-VIP.TEMPO.COM)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = TABDL385B-VIP.TEMPO.COM)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DEV.TEMPO.COM)
)
)
#
STG.TEMPO.COM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = TABDL385A-VIP.TEMPO.COM)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = TABDL385B-VIP.TEMPO.COM)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STG.TEMPO.COM)
)
)
#

If I run TNSPING on the services I get

$ tnsping dev.tempo.com
TNS Ping Utility for VMS: Version 10.2.0.2.0 - Production on 22-JAN-2009 14:18:56
Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
SYS$SYSDEVICE:[ORACLE10G.NETWORK.ADMIN]sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = TABDL385A-VIP.TEMPO.COM)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = TABDL385B-VIP.TEMPO.COM)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DEV.TEMPO.COM)))

OK (20 msec)
$
$
$ tnsping stg.tempo.com

TNS Ping Utility for VMS: Version 10.2.0.2.0 - Production on 22-JAN-2009 14:19:00

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
SYS$SYSDEVICE:[ORACLE10G.NETWORK.ADMIN]sqlnet.ora

TNS-03505: Failed to resolve name
$
$
So it succeeds on "dev", and fails on "stg".

However, if I reverse the order of the entries in the file, then it succeeds on the "stg" and fails on "dev".

I have tried deleting the file and recreating it from scratch, manually typing in each entry, to no avail.

Interestingly, if I remove the "#" sign from between the entries,and leave a blank line, then TNS ping concatenates the entries and fails on "TNS-12533: TNS:Illegal ADDRESS parameters"

Is this my problem? Should I be using some other separator?

Also, Oracle 10G is on an ODS5 disk, and I am using "Parse Style = Traditional". Could this have something to do with it.

I would really appreciate any information that you could provide. At my age I cant afford the hair loss.

Thanks

Dave.

[Mike Kier]

The example at < http://lbdwww.epfl.ch/f/teaching/courses/oracle9i/network.920/a96581/tnsnames.htm > (figure 7-2) seems to indicate using multiple (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=) from a single top level definition, rather than having multiple full definitions.

[John H. Reinhardt]

Since you have multiple addresses per database instance you may want to add the "ADDRESS_LIST" keyword. See the attached file.

Note the extra ")" at the end of the last address line:

".... (PORT = 1521)))"

[The Brit]

Thanks for your suggestions guys, unfortunately neither of them resolved the issue.

I dont think this has anything to do with the formatting of the entries. The problem is that

1. IT DOES NOT SEEM TO BE ABLE TO FIND AN INSTANCE NAME UNLESS IT IS THE FIRST LINE IN THE FILE.

and

2. IF THERE IS NOT A "#" SIGN AT THE END OF THE ENTRY, IT DOESN'T RECOGNIZE THE END OF THE ENTRY, (i.e. it simply continues into (concatenates) the next entry.)

Using a Working TNSNAMES file (albeit from another OpenVMS system, running different OS and Oracle versions), also fails to read any but the first entry, and then only if there are no comments or blank lines before the first entry). If there ARE comments or blanks before the first entry, it will not resolve even the first entry.

Disgarding the file completely and starting from scratch, fails to read any but the first entry.

One additional piece of information which might be relevent, the SQLNet.ORA file contains,

$ ty tns_admin:sqlnet.ora
#SQLNET.AUTHENTICATION_SERVICES=(NONE)
AUTOMATIC_IPC = OFF
TRACE_LEVEL_CLIENT = OFF
names.directory_path = (TNSNAMES)
names.default_domain = tempo.com
#name.default_zone = world
$

Retroactively, I went back and checked the situation on another blade (standalone node running same OS and Oracle version) and found out that the situation was the same. Only the first instance is being resolved.

This failure to parse the file seems to be specific to 10g Client installed on OpenVMS 8.3-1H1 (Itanium Blade bl860c).

Does anyone know if there is a parsing parameter that needs to be set, or if there is a patch that needs to be installed.

Any further suggestions would be appreciated.

(I am taking this also to Metalink)

Dave.

[Marc Van den Broeck]

Hi Dave,

where have you copied the tnsnames.ora file from?
If you copied from an older Oracle version, remember that the file format has changed in Oracle 10.

Rgds
Marc

[Duncan Morris · Accepted solution]

I don't have an itanium handy to play with, but have you checked the format of TNSNAMES.ORA?

The following extract come from the 10g R2 installation guide:-

If you have a previous release of Oracle software installed on this system, you may want to copy information from the Oracle Net Services TNSNAMES.ORA and LISTENER.ORA configuration files from the previous release to the corresponding files for the new release.

These files should have a record format of stream_lf.

If you copy a TNSNAMES.ORA or a LISTENER.ORA file from an earlier version, use the following command to convert the files:

$ CONVERT/FDL=SYS$INPUT filename
filename
RECORD
CARRIAGE_CONTROL carriage_return
FORMAT stream_LF ^Z

Duncan

[The Brit]

Hi Marc,
This might be my problem. Can you point me to any docs about how the format changed?

thanks

Dave.

[Hein van den Heuvel]

The file is STREAM_LF format I hope?
This is a (relatively) new Oracle requirement.

Easy to fix:

$ CONVERT/FDL="RECORD; FORMAT STREAM_LF;
CARRIAGE CARRIAGE" old new

I published a little script to test and generate the above lines, which you can then copy & paste to execute if desired:

http://forums11.itrc.hp.com/service/forums/questionanswer.do?threadId=1263875

Hein.

[The Brit]

Thanks again,

One last question, do I have to take precautions when adding addition entries to the file?? Or do I need to do this everytime I add? Or is this one time convert sufficient?

Dave.

[Duncan Morris]

Dave,

you would need to be careful every time.

Depending upon your personal choice of editor, the output from an edit is unlikely to retain the stream_lf format, and you would need to reconvert.

Try editing a stream_lf version of a file and then look at the file attributes of the new version.


Duncan

[Hein van den Heuvel]

It all depends on the editor you use.
Most OpenVMS editor will create a variable length record file, which will fail unless the output file is re-converted

VIM works, and can be handy in the parenthesis matching.

Also if you used say 'perl' to script an edit, that would work.

Hein.

[The Brit]

Hein,
Does this mean that I am ok if I edit with "$ edit/tpu", i.e. use EVE editor. Or do you recomment converting after every edit anyway.

Dave.

[Steven Schweda]

> you would need to be careful every time.

I'm not, and I have very little trouble
(except with new files).

> Try editing a stream_lf version of a file
> [...]

Yes. Let's do that. (Did you?)

1. Create a file using an editor:

alp $ edit /tpu orig.txt
[...]

2. Notice that it's not Stream_LF:

alp $ write sys$output f$file_attributes( "orig.txt", "rfm")
VAR

3. Convert it to Stream_LF (under a different
name):

alp $ write sys$output f$file_attributes( "orig.txt", "rfm")
VAR
alp $ convert orig.txt stmlf.txt /fdl = sys$input:
RECORD
FORMAT stream_lf
Exit <--- Ctrl/Z

4. Verify the format of the converted file:

alp $ write sys$output f$file_attributes( "stmlf.txt", "rfm")
STMLF

5. Edit the Stream-LF file, and save it under
yet another name (so it can't inherit any
attributes from a previous version):

alp $ edit /tpu stmlf.txt /output = stmlf_2.txt
[...]

6. See what happened:

alp $ write sys$output f$file_attributes( "stmlf_2.txt", "rfm")
STMLF

7. Act amazed.


That's on VMS V7.3-2 (Alpha), but find me a
system where it fails.


> Or do you recomment converting after every
> edit anyway.

I recommend running a quick test, and then
relaxing. DIRE /FULL is a quick (if noisy)
way to check a file's record format, too.

[Duncan Morris]

Dave,

EDIT/TPU should be fine.

I created a dummy file using an FDL to set the STREAM_LF format.

Record format: Stream_LF, maximum 0 bytes, longest 0 bytes
Record attributes: Carriage return carriage control

Then I used edit/tpu to modify the file, and checked the file attributes with DIR/FULL

Record format: Stream_LF, maximum 0 bytes, longest 37 bytes
Record attributes: Carriage return carriage control

After you do any work on your TNSNAMES.ORA file, just make sure that you check that the file still has the correct record format.

Duncan

[The Brit]

Thanks to you all for your help and comments on this problem.

Dave.