What is my database called? (Part 1)

Naming oracle databases - Part 1
There are many names which can be considered the database name, depending on the point of view.

• DBID - system generated database identifier
  
• SID - System IDentifier
  
• DB_NAME - The real name of the database.
  
• GLOBAL_NAME - combination of DNAME and DBDOMAIN
  
• DB_UNIQUE_NAME - used in data guard to distinguish between copies of the same database
  
• SERVICE - the 'published' name under which end users would access a databas
  

DBID

This is not strictly a name, but is the one absolutely constant identfier for a database. It is generated when you create a database and if you are lucky you will never see it again. It becomes important if you have to restore a database on to a clean machine. You can find the DBID by querying V$DATABASE.

SID

This is the system - or instance - identifier. The instance is the set of processes which manage the database. The SID is used as the environment variable ORACLE_SID and is key to database admin tasks such as starting and stopping the database. You specify the SID when you create the instance.

DB_NAME

This IS the name of the database, but for most purposes is largely irrelevant. You never connect to a database, you connect to an instance or a service. You specify the db_name when you create the database by setting the DB_NAME initialisation parameter. The DB_NAME can be different from the instance name, but (except in the case of a RAC setup) there is no real reason to make it different.

SERVICE

This is where it starts to get complicated. The service is the public face of the database and is what clients connect to. The complexity arises because the service name can be defined in 3 different places - in the database; in the listener; and in the client.

In the database

Since Oracle8i, a running instance publishes its availability by registering with a listener. The SERVICE_NAMES initialisation parameter defines the name(s) to be registered. The default is the same as SID.

In the listener

You can override the self-registered name by added service entries to the listener configuration LISTENER.ORA.

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=BLOG)
(SID_NAME=ODBUAD1)
(ORACLE_HOME=/opt/oracle/10.2.0/db_1)))

This entry creates a "service" called BLOG, which uses the ODBUAD1 instance. The output from "lsnrctl status" shows this service and the self-registered service "ODDBUAD1"

C:\var\logs>LSNRCTL status

LSNRCTL for 32-bit Windows: Version 10.2.0.2.0 - Production on 23-NOV-2006 11:58:47

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mercury)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))
Services Summary...
Service "BLOG" has 1 instance(s).
Instance "ODBUAD1", status UNKNOWN, has 1 handler(s) for this service...
Service "ODBUAD1" has 1 instance(s).
Instance "ODBUAD1", status READY, has 1 handler(s) for this service...
The command completed successfully

In the client

The services a client can connect to are defined in TNSNAMES.ORA. A typical tnsnames entry looks like this

BLAG =
(DESCRIPTION =
(ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
)
(CONNECT_DATA =
  (SERVICE_NAME = BLOG)
)
)

This entry defines a "service" called BLAG, which connects to the service defined on the server as BLOG (which in turn uses the database ODBUAD1).

While it is possible to have several different names in several different places for the same database, and multiple names sometimes have their uses, for ease of management it is generally best to keep all the names the same. In a non-RAC environment the main use for different names is to isolate users from changes in the backend.