TNSnames Error

We have set up a DDA instance with an external vendor to access their database outside our firewall.  They have provided the below tnsnames definition in order to connect to the external database:
The tns names entry is below:
NAMEABC=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ABC.XY.AB.ABC)(PORT = 1234))
(ADDRESS = (PROTOCOL = TCP)(HOST = 123.XY.AB.123)(PORT = 5678))
)
(SOURCE_ROUTE=YES)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NAMEABC)
)
)

They have opened the firewall to ABC.XY.AB.ABC:1234 but not 123.XY.AB.123:5678

I am not able to confirm with an unresponsive vendor and am wondering why there is no defintion for the SID and why they might have the tnsnames set up as seen above.  At the moment every time we try and connect we get the below and I believe it is due to thefact that there is no SID defined:
ERROR:
ORA-12154: TNS:could not resolve service name

When I try and bypass the tnsnames altogether I get below:
sqlplus user@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=ABC.XY.AB.ABC)(Por
t=1234)))))'
ERROR:
ORA-12560: TNS:protocol adapter error

What is the easiest way to get to the database and bypass the tnsnames file issue to test the direct conenction with the external database and confirm we can connect?

Thanks!
LVL 1
gNomeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
If you have Ora 10g or 11g, you can use EZconnect:

sqlplus user/password@server/instance

In your direct connect string above, you omitted a service name or SID. Usually the SID is easier to handle, as it is the instance name only, while the service name can contain domain parts. Service name is usually   instance.server.domain or instance.server.world. While inside of the domain, the name might be extended automatically, depending on the SQL*Net config files, but from outside you need to provide the fully qualified name.

If you can't use EZconnect, your string would more likely go that way:
sqlplus user@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=ABC.XY.AB.ABC)(Por
t=1234)))) (Connect_Data= (SID=NAMEABC)) )'
0

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
slightwv (䄆 Netminder) Commented:
>>why there is no defintion for the SID

SERVICE_NAME is the newer (past 8i) way to declare the instance.

>>and bypass the tnsnames file issue

Use some other names resolution like LDAP/Oracle names,??? but tnsnames.ora is the easiest.

What happens if you perform the following from a command prompt:
tnsping NAMEABC

Check your sqlnet.ora file for a DOMAIN entry.  If it exists, comment it out and try it.

To verify connectivity:  You can also try telnet'ing to ABC.XY.AB.ABC port 1234
then 123.XY.AB.123 port 5678


Even though they are non-responsive it appears they have a couple of listeners set up.  You will need a 'status' from them:  lsrnctl status
0
POracleCommented:
FYI:-First important thing is Don't Give your actual server name and connection information in your post. Just modify it with some other dummy name.

Coming back to you question, try this:
1) ping ABC.XY.AB.ABC
If you got successful on first step then
2) telnet ABC.XY.AB.ABC:1234
If you got successful on 2nd step then

Ask your vender to create service name NAMEABC and register it with listener on server side.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

gNomeAuthor Commented:
slightwv:
Unforunately I am limited to the access - can't ping, can't telnet due to a secure channel created to connect to the remote db from within 2 corporate firewalls.  

Nothing for DOMAIN in the sqlnet file

I have tried tnsping as seen below:
H:\>tnsping NAMEABC
TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 13-APR-20
10 11:37:58

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:
C:\oracle\ora92\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

I am trying to bypass the tnsnames file to see if I can confirm that I can connect from within my current domain to the remote database on another corp network by way of a secure channel.  I am using below but am not having any success because I beleive the SID is not registered:
sqlplus user@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=ABC.XY.AB.ABC)(Port=1234)))) (Connect_Data= (SID= NAMEABC )) )'

Supposedly, we have connected in the past with the bypass but the big question is how to bypass using the service name instead of SID in the above...  We were also told to include use_cman=true in the sqlnet.ora but maybe I am missing another paramater ...
0
slightwv (䄆 Netminder) Commented:
>>can't telnet due to a secure channel created to connect to the remote db from within 2 corporate firewalls

this might cause problems down the road depending on how they implemented things but that won't cause a tns-03505.

I never saw where you checked the sqlnet.ora file for a DOMAIN entry. If it exists, comment it out and retry the tnsping.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
There is a firewall in-between? Do we talk about a post-10 Ora? That one listens to a single port, but listener is acting as port mapper, opening another, dynamically determined port. Which would not pass thru the firewall, if it is not SQL*Net-aware.
0
slightwv (䄆 Netminder) Commented:
Olemo,
true but I've never seen a blocking firewall cause an tns-03505.  I've only seen them create a tns-12170 (timeout occured).
0
gNomeAuthor Commented:
My bad - DOMAIN was not in there.
How about basic tnsping:
H:\>tnsping ABC.XY.AB.ABC:1234

TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 13-APR-20
10 12:15:06

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:
C:\oracle\ora92\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

Is there a way to bypass tnsnames alltogether to do a basic tnsping on the external port and listener?
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You need to use the connect description for TNSPing:

tnsping server/instance
tnsping '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=ABC.XY.AB.ABC)(Port=1234))))  (Connect_Data= (SID= NAMEABC )) )'

But that would not change anything ...
0
slightwv (䄆 Netminder) Commented:
I responded to the 'bypass' question back up in http:#30562121.

Yes but...  tnsnames.ora is the easiest to use/configure.
0
gNomeAuthor Commented:
Informative but probably needed more details on dblink
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
May I ask how this is related to dblinks ("Informative but probably needed more details on dblink")? That never occured anywhere. We always talked about a direct connection, and that is ODBC/sqlplus or whatsoever.

If you have questions, post them, and we will try to explain! I don't mind the "B", but you will not get to know things if you do not ask ...
0
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.