Oracle - SID vs Service Name

Oracle SIDs vs Oracle Service Name?


SID = unique name of the instance/database (eg the oracle process running on the machine). Oracle considers the "Database" to the be files.
ServiceName = alias used when connecting. The main purpose of this is if you are running a cluster, the client can say "connect me to SALES.acme.com", the DBA can on the fly change the number of instances which are available to SALES.acme.com requests, or even move SALES.acme.com to a completely different database without the client needing to change any settings.

Service name is recorded in Tnsnames.ora file on the clients and it can be the same as SID and you can also give it any other name you want. ORACLE_SID is recorded in instance_name; this could be the same as the database name (init.ora for db_name parameter). oratab file gives the list of instances in the server.

SERVICE_NAME is the new feature from oracle 8i onwards in which database can register itself with listener. If database is registered with listener in this way then use SERVICE_NAME parameter in tnsnames.ora; otherwise - use SID in tnsnames.ora.

In Oracle Parallel Server (RAC), there would be different SERVICE_NAME for each instance.

SERVICE_NAMES specifies one or more names for the database service to which this instance connects. You can specify multiple services names in order to distinguish among different uses of the same database. For example:
SERVICE_NAMES = sales.foo.com, phonesales.foo.com

Service names also identify a single service that is available from two different databases through the use of replication. In an Oracle Parallel Server environment, parameter has to be used for every instance.

You might have have a staging database and a production database with the same SID but referenced with 2 different service names:

STAGE.WORLD =
 (DESCRIPTION =
   (ADDRESS =
      (PROTOCOL = TCP) 
      (PORT = 1521)
      (HOST = LITTLECOMPUTER.ACME.ORG)
   )
   (CONNECT_DATA = (SID = MYSID))
)
PROD.WORLD =
 (DESCRIPTION =
   (ADDRESS =
      (PROTOCOL = TCP) 
      (PORT = 1521)
      (HOST = BIGCOMPUTER.ACME.ORG)
   )
   (CONNECT_DATA = (SID = MYSID))

[Refer: http://www.sap-img.com/oracle-database/finding-oracle-sid-of-a-database.htm]