Servicename or not?

Few weeks ago I had issues with naming conventions: In one of my projects a cloning software should be used. The installation went fine but the integration of the first database failed. The error message stated that the service is not available – but to my knowledge all entries are fine …

Servicename

What are the different choices to connect to a database? But thats already the first mistake. “Connect to a database” is wrong. We always connect to an instance and never to the database. And here is our first connection method: If we know the Oracle System Identifier (SID) we are able to connect with the instance just using this. It already works with Version 6 and its still available with Versoin 12. This method is being used when we execute the command “sqlplus / as sysdba”. Using the variable ORACLE_SID we are able to connect to instance with it’s SID.

But the usage of SID should be limited to exactly this one sample. Whenever we connect via the Oracle listener we should use a dedicated “Service” and not the SID!
Since Version 9 Services are avaible and with the creation of the database at minimum one service is present. The name of the service is identical with the database name (and not the instance name). But from my experience I would recommend that you never use this service. Why not? Because you cannot change it. One of the advantages in using services is that you are able to create your own ones. And with this you can “hide” the name of the database. The application which is using this serive does not know that it belongs to a specific database. If you have to move the application to another database you simple drop and recreate the service for that database without any impact to the application. For Real Application Clusters (RAC) databases you can add additional characteristics to the service like the failover method or the preferred and available nodes. And finally a service can be started and stopped for maintenance. In this case the administrator can connect remotely with “his” service while the application is no longer able to use “its” service.

But in my project I defined a dedicated service for the cloning software and still the connect to the database did not work – why?
There a several possibilities to connect to an instance from a client to the instance. Mainly Easy Connect and TNS-Alias. Easy Connect consists of the hostname, the port and the Service, like the following example:

SQL> sqlplus user/pwd@myhost:1521/myservice

This works fine for single instance databases but not for RAC or especially Dataguard. Oracle has a very complex syntax for the connection with specific needs like the following:

SQL> sqlplus user/pwd@(DESCRIPTION =
   (ADDRESS_LIST =     
      (ADDRESS = (PROTOCOL = TCP)(HOST = beatles)(PORT = 1662))     
	  (ADDRESS = (PROTOCOL = TCP)(HOST = stones)(PORT = 1662))     
   )     
   (CONNECT_DATA =       
      (SERVER = DEDICATED)       
	  (SERVICE_NAME = JOHANNES)     
   )   
)

This doesn’t loke very simple so it would be nice if we can reduce the section. You of course already know what I meant: the good old tnsnames.ora (also available via LDAP).
The tnsnmes.ora allows us to create an alias (e.g. JOHANNES) for the DESCRIPTION. And now I can connect to the instance using this alias. BTW: it’s not nessary that the alias has the same name as the service.

Conclusion

There are three different namings we can use to connect to an instance:

1. the SID: please use it only for local connects with sqlplus / as sysdba
2. the Servicenamen with Easy Connect: Useful for single instance connections
3. the TNS-Alias: Useful for more complex aliases like for RAC or Dataguard

Now my problem in the project has been solved: the cloning software asked for an TNS-Alias and not for the servicename.