Oracle – Best Practices for Service Name / Aliases in tnsnames

best practicesNetworkoracle

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)
    )
  )

Best Answer

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.

Related Question