Best practices for service name / aliases in tnsnames

Question

I have this idea that service names / aliases given to connections descriptors in a tnsnames.ora (not the one in the server that dblinks use, but the one for the public) file should in no way be related to the SID and much less the name of the server.

Suposse the name of the sever is "myserver" and the SID of the instance is "myinstance".

I think giving the alias "myinstance-at-myserver" to the connection string is not a good idea because you are somehow coupling something logical to something physical.

• Am I right or wrong and why ?

Is this the best practice?:

# one server-instance-named descriptor
MYINSTANCE-AT-MYSERVER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = myinstance)
    )
  )

Or is this ?

# several business-named descriptors pointing to the same listener
RRHH =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = myinstance)
    )
  )

FINANCE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = myinstance)
    )
  )

SALES =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = myinstance)
    )
  )

Answer 1

I certainly wouldn't want to include the server name in the TNS alias. It would seem highly probably that this would change over time as databases move from one server to another and as organizations move to things like RAC where there would be multiple servers.

Assuming that your service names are chosen meaningfully, I would expect that the service name would match the TNS alias since both are logical names for the same thing. That's not a hard and fast rule, of course. Some organizations may have reasons to have two separate logical names for a single service. For example, you may want a single TNS alias FINANCE that points to the FINUSA service for users in the US and the FINFRA service for users in France. But for the vast majority of situations, if the TNS alias would be FINANCE, the service name ought to be FINANCE as well.

Answer 2

What is the most costly thing for you? Prioritize.

Usually the most costly thing is confusion of users. Ordinary users very rarely use a TNS name. They usually use the data through a dedicated application, and know just the name of application, like CRM. Power users connect to TNS names (think SQLdeveloper), but they also connect to the ordinary applications. Why would you want application CRM use a TNS name of ORION? Power users see TNS name and all they care is TNS name; not SID, not hostname. They talk to other users in terms of TNS name (get that report from FINANCEDB John!). The state of minimal user confusion is when TNS name is the same as the name of application using it. Either exactly the same of with "db" appended. So TNS name is like CRM or CRMDB. I prefer the latter.

The next costly thing is changing anything inside tnsnames.ora. You don't know where it is distributed. You put the file in one's client machine or an application server, and voila three months later it magically appeared on dozen others. Can you prevent it? (No, you cannot.) So in practice this means once you give any user a TNS name, its definition is set in stone. So, don't put SID there, put a SERVICE_NAME. Don't put IP address there, put a hostname. And those two would stick to TNS name "forever", so there is no reason for them to be different than TNS name (Occam razor).

CRMDB=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = crmdb.mydomain.local)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = CRMDB)
    )
  )

The next thing in terms of cost is an OS (machine) and its IP address. You cannot afford a separate system for every TNS name. So crmdb.mydomain.local is not the only name for the IP address; the same IP address would have more names, like financedb.mydomain.local. Your OS admin would decide how to do this best, and how to determine the main hostname of the OS. They have the same problem with many other systems - multiple names referring to one OS - so they should have a solution at hand. The only people who are confused now are DBAs and OS admins, they see multiple hostnames leading to the same IP address. But users don't care about that and are not confused by that. (By the way this approach is coherent with SCAN. )

The next thing in terms of cost is either one of the two: an Oracle instance or "administrative cost of separating schemas out of instance". The tradeoff is for you to decide.

• You can put many SERVICE_NAMES onto one instance and it is quite a common practice. Caveat emptor, it can be quite difficult to separate them later to their own instances. It is up to you to keep developers at bay when they "conveniently" try to access a schema of one application from another. This is your administrative cost. In this case database name and instance name shouldn't be equal to any SERVICE_NAME. Set it somewhat similar to the main OS hostname to keep things simple.
• There are some SERVICE_NAMES which are delicate enough to have their own instance. Or maybe instances are just cheap for you in terms of labor and maintenance, and you prefer to have a separate instance than to carry the cost described above. In this variant, database name and instance name would be CRMDB (instance becoming something like CRMDB1 if you have RAC or DataGuard).

And SERVICE_NAME is basically free of charge. Every TNS name should have an equal SERVICE_NAME.