Question: What is the difference between the SID and SERVICE_NAME in my tnsnames.ora file?  Why does Oracle have three parameters for the same thing,instance_name, service_name, db_name, and oracle_sid?

Answer: Part of the reason for this separation is in the evolution of Oracle, but we need to remember that Oracle specifies database locations in many files.  For TNS networking, the SERVICE_NAME and SERVICE_NAMES parameters were first introduced in Oracle 8i and supersede the older SID designation.

Difference between SID and SERVICE_NAME

Note the subtle difference between a SID and a SERVICE_NAME.  The SID is the same as the $ORACLE_SID environment variable in the sense that the SID is the system is for a unique instance on the server.
 
On the other hand, the SERVICE_NAME is used to register an instance with the listener.  In most all cases, Oracle recommends that the value of the service_name be the same as the SID.  However, a SERVICE_NAME  can point to more than one instance, and the DBA can gen-in additional SID's into a SERVICE_NAME .

Also see my TNS-12502 Tips.

MOSC Note 68632.1 verifies that the SERVICE_NAMES specifies one or more names for the database service to which this instance connects. You can also specify multiple service_name entries in order to distinguish among different uses of the same database.

Here is an example if two tnsnames.ora files, one using SID and the other using SERVICE_NAME:

db_name - This is created when you issue your "create database" statement and it can be seen in the v$database view.

SQL> CREATE DATABASE mysid DATAFILE SIZE 400M
2>   DEFAULT TEMPORARY TABLESPACE temp_ts 
3>   TEMPFILE SIZE 10M
4>   UNDO TABLESPACE undo_ts DATAFILE SIZE 10M;

select 
   name, 
   value 
from 
   v$parameter 
where 
   name = 'db_name';

NAME VALUE
------- ----------------------------------------------
db_name mysid

instance_name - In the init.ora file

init.ora
INSTANCE_NAME=mysid

select 
   instance_number,
   instance_name,
   host_name
from 
   v$instance

INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME
--------------- ---------------- ----------------------1               mysid            MYSERVER

service_name - The service_name is in the tnsnames.ora file to allow you to register an instance with the listener:

mysid=
  (DESCRIPTION =
    (ADDRESS = 
      (PROTOCOL = TCP)
      (HOST = myserver)
      (PORT = 1521))
   )
   (CONNECT_DATA =
      (SERVICE_NAME = mysid)
   )
)
 

sid_name - We find the sid_name values listed in the listener.ora file:

SID_LIST_LISTENER =
   (SID_LIST =
      (SID_DESC =
      (SID_NAME = mysid)
      (ORACLE_HOME = /u01/app/oracle)
       )
      (SID_DESC = 
      (GLOBAL_DBNAME = remote.dba.net)
      (ORACLE_HOME =/u01/app/oracle/product/10.1.2)
      (SID_NAME = mysid)
       )
   )
)
 
 

global_name - If you are using global_names (global_names = TRUE), the global_name is the combination of the db_name and db_domain:

select * 
from 
   global_name;

GLOBAL_NAME
---------------------------------------
MYSID.REMOTE.DBA.NET

 

For a complete description of these values, see Steve Karam's book "Easy Oracle Jumpstart":