?
Solved

tnsnames.ora

Posted on 2000-02-10
7
Medium Priority
?
2,471 Views
Last Modified: 2008-02-01
XXXXXXX.WORLD =
    (DESCRIPTION =
        (ADDRESS =
        (PROTOCOL = TCP)
        (HOST =      xxxxxxx.xxxx.xx.com)(PORT = 1527)
        )
        (CONNECT_DATA = (SID = xxxxxx)
        )
    )


When the SQL Server client attempts to connect to the Oracle database I receive an error message stating that there is "no listener".  I believe the problem is that Oracle is listening on port 1527 but the client is calling using port 1521 even though the tnsnames.ora file has port = 1527.

When I use the tnsnames.ora file within my company's network the 1527 port works fine.  When I use the tnsnames.ora configuration file from outside my company's network it fails because when the connection traverses the firewall somehow port 1521 is used.  My understanding is that port 1521 is the Oracle default/standard.

My question is, what can I do to make the client call the server using port 1527 which is what the server is listening on?

0
Comment
Question by:Melvin
7 Comments
 
LVL 3

Accepted Solution

by:
Ron Warshawsky earned 800 total points
ID: 2509149

Melvin,

1. Verify you don't have 2 tnsnames.ora file in the system. Rename all, but one in $ORAHOME/net8 directory.

2. Try to use TNSPING <service_name> to verify connectivity.

3. If tnsping works but connect failes, verify with network people, if any IP conversion is done on Firewall. ORACLE Tcp/ip does not supports IP/port conversions. If this is the case, than Consider using ORAPIPE to connect.


  Regards,
     Rwarsh

P.S. If this will not work, feel free to reject my answer and see, if others can help.

 
0
 
LVL 3

Expert Comment

by:bkowalski
ID: 2509614
Please provide the Oracle error code that you get.  Your assumption about port numbers may or may not be correct.
0
 
LVL 1

Expert Comment

by:aperdon
ID: 2509693
verify these settings

ORACLE_SID=...
ORACLE_HOME=...
TNS_ADMIN=...

especially the last one. this one has to point to your tnsnames.ora
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 4

Expert Comment

by:sudhi022299
ID: 2509797
There was similar Q like this in the PAQ section. You might want to look into it too.
But some general things to consider as pointed out by the previous comments.
1. Once you dial into your company's network, first try to use the ping x.x.x.x command and check whether its successfull. If the command is return "request time out" message then the problem would be with the network.
2. Verify that the tnsnames.ora file has the proper service name settings. SID,PORT,HOST ( according to your comment this should have proper values )
3. I assume you must be using your comp or laptop to log on and running a version or Windows OS. if this is the case, then there cannot be two tnsnames.ora file in the same directory.
4. Run regedit and check your TNS_ADMIN is set to the proper directory ( typically it would be ORACLE_HOME\Net80\Admin directory)
5. Run the tnsping <servicename> to check whether its successfull.
6. When you get the message "no listener", the first thing you would check is whether the listener is running at the time you are trying to connect.

Hope this leads somwhere near the problem.

Regards,
Sudhi.
0
 
LVL 4

Expert Comment

by:sudhi022299
ID: 2509804
You might also try changing the HOST parameter to the ip address of the machine and test it.

Regards,
Sudhi.
0
 

Author Comment

by:Melvin
ID: 2518461
I have what I want and all your comments led to the resolution of my problem.  After reading all that each of you wrote I began looking at the tnsname.ora file.  What I found was that the file wasn't in the "path".  After including the directory orant\net80\admin in the path, everything worked fine.

Is there some way for you guys to share the points?

0
 
LVL 4

Expert Comment

by:sudhi022299
ID: 2518938
If you want to give some points to others, you have to create another Question with the title "points for <name>" and then accept the comment from them.

Glad that your problem got solved.

Regards,
Sudhi.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question