What's In A Name - Naming and Idenifying Oracle Databases and Instances

Thursday Sep 4th 2003 by James Koopmann
Share:

Identification of the database you are currently working on can sometimes be somewhat confusing. James Koopmann examines the parameters that need to be set and at the same time explains ways of determining to which database and instance you are working and connected.

Identification of the database you are currently working on can sometimes be somewhat confusing. Take a look at the internal parameters that Oracle keeps and see if you can uniquely identify the database you are currently working on.

A big problem in the Oracle database world is the initialization and setup of naming conventions within the Oracle database. There are quite a few DBA(s) that approach this with a lackadaisical attitude. It is not until they find themselves in a distributed field of databases that they soon begin to loose their mind and wonder where and what database they are working on. Let's take a quick look at the handful of parameters that need to be set and at the same time take a look at how we can determine what database and instance we are working on and connected to.

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. You can look at this parameter by issuing the SQL in Listing 1.

Listing 1
Getting DB_NAME from V$PARAMETER

HCMC-SQL> select name , value from v$parameter where name = 'db_name';

NAME                 VALUE

-------------------- --------------------------------------------------

db_name              hcmc

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. Even if the database you are creating is not going to be a part of any network or distributed system, I would suggest that you set this parameter just for the sake of going through the thought process and understanding how things connect when networked. I personally always set up my net services for my local boxes that are not ever going to be connected to another database. You might ask why? Well I have never created a database that I 'did not want to be part of a distributed system after I played with it for a bit. After all, it will keep you in good practice of these configuration parameters. You can look at this parameter by issuing the SQL in Listing 2.

Listing 2
Getting DB_DOMAIN from V$PARAMETER

HCMC-SQL> select name , value from v$parameter where name = 'db_domain';

NAME                 VALUE

-------------------- --------------------------------------------------

db_domain den.dbdoctor.net

global_name

This little gem has always been at the top of my list for determining 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. The simple SQL in Listing 3 will show you how to get this value. If you are curious how you can set the SQL*PLUS prompt, just issue the SQL in Listing 4. While I 'would not quite recommend this for anything other than a single node instance on a database, it does work quite well if you have unique GLOBAL_NAME(s) within your organization.

Listing 3
SQL for global_name extraction

SQL> select * from global_name;

GLOBAL_NAME
---------------------------------------
HCMC.DEN.DBDOCTOR.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. In Listing 5, you can see a sequence of events where I first set GLOBAL_NAMES to TRUE and then created a database link. 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.





Listing 5
Use of GLOBAL_NAMES parameter




SQL> alter system set global_names=true;
System altered.
SQL> create database link alink connect to <user> identified by <pass> 
  1   using 'hcmc.den.dbdoctor.net';
Database link created.
SQL> select table_name from user_tables@alink;
select table_name from user_tables@alink
                                *

ERROR at line 1:
ORA-02085: database link ALINK.DEN.DBDOCTOR.NET connects to HCMC.DEN.DBDOCTOR.NET


SQL> alter system set global_names=false;
System altered.
SQL> select * from global_name@alink;
GLOBAL_NAME
---------------------------------------------------------
HCMC.DEN.DBDOCTOR.NET


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. The SQL is given in Listing 6.

Listing 6
SQL for extracting the INSTANCE_NAME

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


INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME
--------------- ---------------- ---------------------------------------
              1 hcmc1            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. Don't ask me how this number is generated, I wish I knew. All I can say is that it is supposed to be unique across databases. Well we all know that nothing in this world can be trusted to be absolutely unique across all 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. Listing 7 will give you the SQL to extract 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.

Listing 7
SQL for getting DBID

HCMC-SQL> select dbid,name from v$database;
      DBID NAME
---------- --------------------
3060884937 HCMC


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

   INST_ID       DBID NAME
---------- ---------- ---------
         1 3060884937 HCMC
         2 3060884937 HCMC

INST_ID

You should have noticed in Listing 6 and Listing 7 that when selecting from the V$ table there was no INST_ID. That is because the V$ tables act on the current instance connected to and the GV$ tables acts on all instances for the database as in a cluster environment. The other GV$ table to get some information from is the GV$INSTANCE table. By executing the SQL in Listing 8, you can get the INST_ID, INSTANCE_NUMBER, INSTANCE_NAME and HOST_NAME for determining what machine you are on.

Listing 8
SQL for extracting the HOST_NAME

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

INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME
--------------- ---------------- ---------------------------------------
              1 hcmc1            JKOOPMANN1

SQL> select inst_id,instance_number,instance_name,host_name from gv$instance INST_ID INSTANCE_NUMBER INSTANCE_NAME HOST_NAME ---------- --------------- ---------------- --------------------------------------- 1 1 hcmc1 JKOOPMANN1 2 2 hcmc2 JKOOPMANN2

When we as DBA(s) start working in a distributed environment where there are replicated environments, Real Application Clusters, standby databases, cloned databases and a variety of other configurations, it is imperative that we are able to distinguish the database instance that we are connected to and the database it is connected to. Go forth and name properly so that you won't get lost in the sea of confusion.

» See All Articles by Columnist James Koopmann

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved