Naming and Identifying Oracle Databases and Instances

Identification of the database you are currently working on can sometimes be somewhat confusing.

DB_NAME

The DB_NAME parameter is the value of the database and is the name used when creating the database. It is specified within the INIT.ORA parameter file or in the CREATE DATABASE command. This is one of those parameters that is optional but is always best to set it. This is especially true for the standby database where it should match the production database.

Getting DB_NAME from V$PARAMETER

SQL> select name , value from v$parameter where name = ‘db_name’;
NAME VALUE
——————– ————————————————–
db_name abc

DB_DOMAIN

DB_DOMAIN is the value of the domain to which the database belongs. It is the location of the database within the network hierarchy.

Getting DB_DOMAIN from V$PARAMETER

SQL> select name , value from v$parameter where name = ‘db_domain’;
NAME VALUE
——————– ————————————————–
db_domain abc.tellme.net

global_name

This determes what database I am connected to. This is the parameter that everyone use to use to query and set the prompt for in SQL*PLUS to give a unique identity to the SQL*PLUS session currently active. This parameter is a combination of the DB_NAME parameter and the DB_DOMAIN parameter.

SQL> select * from global_name;
GLOBAL_NAME
—————————————
abc.red.tellme.NET

Listing 4
Setting the SQL*PLUS prompt

column gname new_value dname noprint
select substr(global_name,1,instr(global_name,’.’)-1) gname from global_name;
define prmpt=’&&dname’
set sqlprompt “&&prmpt-SQL> ”

GLOBAL_NAMES

While this variable does not directly store a value that you can identify your database with, it does dictate how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are linking. The interesting thing is that Oracle lets you create a database link that goes against the GLOBAL_NAMES parameter setting. It ‘is not until you actually try to use it that you get an ORA-02085 error. After setting GLOBAL_NAMES to FALSE, I am able to use the database link.

INSTANCE_NAME

The INSTANCE_NAME is the name given to the set of processes that are running on the database server. This parameter is set in the INIT.ORA and can be seen by querying the V$INSTANCE table.

SQL for extracting the INSTANCE_NAME

SQL> select instance_number,instance_name,host_name from v$instance

INSTANCE_NUMBER INSTANCE_NAME HOST_NAME
————— —————- —————————————
1 abc JKOOPMANN1

SERVICE_NAMES

This parameter will default to the GLOBAL_NAME. Remember the GLOBAL_NAME is a combination of DB_NAME and DB_DOMAIN. This parameter can take on multiple names that are comma separated. This allows you to give different service names to the same instance or a single service name for multiple instances that access the same database, as in Oracle’s real application cluster environment. The point here is that the service name will not give you a unique name for the instance to which you are connected.

DBID

While DBID is not a “name” of a database, it does have the quality of uniquely identifying a database be a unique number. The DBID number is generated at database creation. it is supposed to be unique across databases. I know of one instance where if you were to clone a database, the DBID will be the same for the cloned database as the original. At least Oracle has given us a work-around in the form of a utility to change the DBID. There are two tables you can get this information from, the V$DATABASE and the GV$DATABASE. If you are not aware of the ‘GV’ tables, they are GLOBAL across clustered instances.

SQL> select dbid,name from v$database;
DBID NAME
———- ——————–
3060884937 ABC

SQL> select inst_id,dbid,name from gv$database;

INST_ID DBID NAME
———- ———- ———
1 3060884937 abc
2 3060884937 abc

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: