Oracle SIDs Vs Oracle SERVICE NAMES

What is the difference between Oracle SIDs and Oracle SERVICE NAMES?

Oracle SID is the unique name that uniquely identifies your instance/database where as Service name is the TNS alias that you give when you remotely connect to your database and this Service name is recorded in Tnsnames.ora file on your clients and it can be the same as SID and you can also give it any other name you want.

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 you can use SERVICE_NAME parameter in tnsnames.ora otherwise - use SID in tnsnames.ora.

Also if you have OPS (RAC) you will have 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.acme.com, widgetsales.acme.com

You can also use service names to identify a single service that is available from two different databases through the use of replication.

In an Oracle Parallel Server environment, you must set this parameter for every instance.

In short: SID = the unique name of your DB, ServiceName = the alias used when connecting

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



A service name is more flexible than a SID would be.


A database can dynamically register with a listener using one or more service names. In fact, more than one database can register with a listener using the same service name (think about a clustered environment where you have multiple instances that all are the same database under the covers).

A database on the other hand has a single SID. And a single SID goes to a single database. It is a pure 1:1 relationship.

A service is a many to many relationship.


Service names are used with dynamic registration - the data registers with the listener after it starts up. Once it does that, you can connect.


With the SID - that is more like telling the listener "I want you to connect to this specific database, I know the 'address', here you go"

With the SERVICE - you are asking the listener to put you in touch with a database that can service your request, a database that registers using that service.

---

Service registration offers the following benefits:

Simplified configuration

Service registration reduces the need for the SID_LIST_listener_name parameter setting, which specifies information about the databases served by the listener, in the listener.ora file.


Note:

The SID_LIST_listener_name parameter is still required if you are using Oracle Enterprise Manager to manage the database.


Connect-time failover

Because the listener always knows the state of the instances, service registration facilitates automatic failover of the client connect request to a different instance if one instance is down.

In a static configuration model, a listener would start a dedicated server upon receiving a client request. The server would later find out that the instance is not up, causing an "Oracle not available" error message.

Connection load balancing

Service registration enables the listener to forward client connect requests to the least loaded instance and dispatcher or dedicated server. Service registration balances the load across the service handlers and nodes.