(ADDRESS =
        (PROTOCOL = TCP)
        (HOST = = 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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ron WarshawskyCommented:


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.


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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Please provide the Oracle error code that you get.  Your assumption about port numbers may or may not be correct.
verify these settings


especially the last one. this one has to point to your tnsnames.ora
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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.

You might also try changing the HOST parameter to the ip address of the machine and test it.

MelvinAuthor Commented:
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?

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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.