0

Oracle allows a few different syntaxes for specifying where to find your database service. The one I've seen most commonly (in the few short years I've been developing software professionally) looks something like this:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=myservice)))

I don't think this syntax has any particular name, but it is what Oracle generates in its TNS files if you use graphical tools to modify it. But Oracle also allows the following much more compact syntax, at least with tools like SQL*Plus and application divers:

myserver:1521/myservice

This is known as Easy Connect, and I have read that it was introduced by Oracle 10g. That makes it around 10 years old. I'm not sure if this can be used in a TNS file, and as short and simple as it is, I don't see much need to put it in a TNS file. Which is the good thing about it in my opinion: you can just use it directly with your application instead of needing to maintain a TNS file.

However, from the work I've done and the clients I've worked with, its usage seems to be rather uncommon. The only reason I even know about it is because ESRI started recommending its usage for establishing connections in newer versions of ArcGIS. That leaves me wondering why it's uncommon, so is there some kind of downside to using Easy Connect that I'm missing?

(I'd appreciate if someone could add more tags. I couldn't think of/find any more that made sense.)

Improve this question

2 Answers 2

Reset to default
5

The only real downside is that there are a number of configuration options that you can put in a TNS alias that you can't use when you use the easy connect syntax. Easy connect is designed to simplify the syntax for simple connections. In order to do that, though, it loses the ability to create more complex TNS aliases that do things like load balancing/ failover or that request a dedicated server session. If you don't need any of that, there is no downside to using the simpler syntax. As soon as an organization has one use case that uses something that the easy connect syntax doesn't support, though, it either has to support a hybrid configuration where some things use easy connect and others use TNS aliases or it has to use TNS aliases everywhere.

Supporting hybrid configurations or changing how connections are handled can be somewhat painful depending on your perspective. It's relatively easy for the Oracle client to handle a bunch of different naming conventions. It's much more difficult for an organization to support.

It's relatively easy for organizations to come up with ways of distributing a single, shared tnsnames.ora file to everyone or to stand up a central LDAP server to host the organization's TNS information. If some applications use easy connect syntax and some organizations use TNS aliases, that can be problematic when an organization wants to do things like consolidate databases or move databases from one host to another. If an organization wants to add load balancing support, it can be problematic when some applications fail over automatically and some don't. Someone has to track which applications are which, admins have to treat applications differently rather than being able to count on organization conventions that are adhered to across the board. None of this is impossible, of course. But consistency across the environment makes DBAs and admins much more productive and allows teams to support many more applications/ databases/ whatever with any given level of staffing.

Improve this answer
3
  • It's my understanding that allowing a hybrid configuration is fairly simple. Am I wrong?
    – jpmc26
    Mar 28, 2014 at 17:57
  • 1
    @jpmc26 - It depends. If you deploy a bunch of applications that use easy connect syntax and then decide that you want to add load balancing later on, that means that you've got to go touch a bunch of applications. Large organizations generally come up with a standard tnsnames file (or store TNS aliases in LDAP) that gets pushed to everyone when configuration changes are made. Supporting both can create support issues when, for example, some applications fail over automatically and some don't and someone needs to keep track of which are which.
    – Justin Cave
    Mar 28, 2014 at 18:45
  • That makes sense. I think my problems stem from a calamity of errors. This question arose because I was hoping to use Easy Connect in our client's dev/testing environment, but it's not enabled. (I can't modify any Oracle client settings.) They're also using TNS files, and I think they are simply maintaining each machine manually since our testing database doesn't have an entry on a new machine. On top of that, the service name on our database has mysteriously changed a couple times, breaking existing connections. Getting it fixed is a bureaucratic nightmare just to test my code. Fun, no?
    – jpmc26
    Mar 28, 2014 at 19:00
2

What @Justin-Cave has stated pretty much covers it. One of the best reasons for using tnsnames or another resolution method is to provide a level of abstraction. Basically it stops applications from hard coding values that can be hard to maintain/change/find. If the connections are kept in a tnsnames file then the database can be moved, renamed etc and the application doesn't need to be touched, just repoint the tnsnames entry.

Improve this answer
2
  • I'm not quite sold on the level of abstraction. I've really only seen two situations when the connection information might be repeated on the same machine: in a development environment and with ArcGIS connecting to a RAC database (where a connection holds both this information and username/password). In dev, I've found it's better to avoid installing the client because of x86/x64 or version (across projects) conflicts. In ArcGIS, this would be an advantage, but changing databases is a big deal and would likely be some kind of migration. So it seems like a small one. Is my experience atypical?
    – jpmc26
    Mar 28, 2014 at 18:01
  • I am only basing this on personal experience We have performed mass migrations in one case and this allowed for a smooth transition. The applications essentially had to make no changes. Another occasion where this is has helped is where we have a database accessed by a lot of applications. When moving it to a new VM we didn't have to reconfigure all of the apps, just update the tnsnames.ora file. As for client issues, there is usually only an issue across versions, 9i Vs 10g and even then the vast majority of functionality is compatible across versions. Oracle support is more of an issue.
    – Sparkle
    Mar 30, 2014 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.