Skip to Main Content
  • Questions
  • What's the use of "GLOBAL_DBNAME" in the listener.ora file

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: February 26, 2005 - 9:45 pm UTC

Last updated: May 05, 2005 - 2:05 pm UTC

Version: 9.2.0.1.0

Viewed 10K+ times! This question is

You Asked


In the listener.ora file, we can define the value of GLOBAL_DBNAME in SID_DESC section. for example:
(SID_DESC =
(GLOBAL_DBNAME = orachh)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = orachh)
)
I have found in many docs which said the value of GLOBAL_DBNAME must equal to the value of service_name.
During my experiments i conclude that we can assign a random value to GLOBAL_DBNAME such as "abcde" and in this situation the listener can still start successfully,it seems the global_name was only an identifier and has not any important function:
$lsnrctl status
Service "abcde" has 1 instance(s).
Instance "orachh",status UNKNOWN, has 1 handler(s) for this service..

So i want to know What's the use of Global_dbname and in which circumstance should we use it.
Thanks in advance.

and Tom said...

the global dbname in there will be the service name:

[ora9ir2@localhost admin]$ cat listener.ora
# LISTENER.ORA Network Configuration File: /home/ora9ir2/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/ora9ir2)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = WRONG)

(ORACLE_HOME = /home/ora9ir2)
(SID_NAME = ora9ir2)
)
)



when I ask that listener what services do you provide:

[ora9ir2@localhost admin]$ lsnrctl services

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 27-FEB-2005 09:08:05

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "WRONG" has 1 instance(s).
Instance "ora9ir2", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
[ora9ir2@localhost admin]$





[ora9ir2@localhost admin]$ sh -vx t.sh
sqlplus 'scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ora9ir2)))'
+ sqlplus 'scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ora9ir2)))'
SQL*Plus: Release 9.2.0.4.0 - Production on Sun Feb 27 09:10:04 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:
ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
descriptor


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
sqlplus 'scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=WRONG)))'
+ sqlplus 'scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=WRONG)))'

SQL*Plus: Release 9.2.0.4.0 - Production on Sun Feb 27 09:10:07 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL>


so, the global dbname is the service name -- it is just a way to keep it "all straight", in a multi-database environment, you might think about using the global dbnames (and using the right ones, not the wrong ones) -- as service names so you know what the service names are implicitly (global database names) and have a reasonable expectation of what database you are connecting to.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Service name and GLOBAL_DBNAME

Serge Shmygelsky, February 27, 2005 - 9:47 am UTC

Hi Tom,

I didn't get exactly your explanation. If we define GLOBAL_DBNAME to contain correct DB name, does it mean that we can put anything in ORACLE_SID and connection will be successful? What about SERVICE_NAME in that case?
Sorry for the questions that can seem stupid, but for me, these area has always been one of the most obscure sections in Oracle configuration.

Tom Kyte
February 27, 2005 - 10:01 am UTC

(SID_DESC =
(GLOBAL_DBNAME = WRONG)
(SERVICE_NAME = WRONGER)
(ORACLE_HOME = /home/ora9ir2)
(SID_NAME = INVALID)
)


[ora9ir2@localhost admin]$ lsnrctl services

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 27-FEB-2005 09:47:56

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "WRONG" has 1 instance(s).
Instance "INVALID", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
[ora9ir2@localhost admin]$


bascially, the global dbname is the service name, the "oracle sid" is the instance (a single service can point to many instances - RAC for example, you might have 4 nodes -- 4 instances -- the service would/could point to any of the 4 and load balance)


Given I put in an invalid SID to get to the instance, it'll never work:

sqlplus 'scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=WRONG)))'

SQL*Plus: Release 9.2.0.4.0 - Production on Sun Feb 27 09:49:28 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory


Enter user-name:



the service_name isn't really used in the listener.ora. You use global_dbname for static registration, you use service_name in the database init.ora to use dynamic service registration (no listener.ora setup, but no remote AS SYSDBA connections then either, you need a static entry to permit that)


I've been using dynamic registration -- so my listener.ora file has almost nothing in it at all (i don't do remote "as sysdba" connects to startup the database so I don't need the static entry)

ORA-12514

Sanjaya Balasuriya, May 05, 2005 - 6:31 am UTC

Hi Tom,

I have 9.2.0.6 database as "PANDA". And I have given "PANDA, CGEN, MC" as value for "service_names" parameter in the spfile.

Everything was fine upto now. I was able to connect to all PANDa, CGEN and MC without problem.

But now I can only connect to PANDA with is the database name. I can connect to the services.

Are these services not being registered ? Or some problem when the spfile is read ?
What can I do to fix this ?

Thanks.

Tom Kyte
May 05, 2005 - 7:35 am UTC

insufficient data here.

sysdba...

Jim, May 05, 2005 - 9:40 am UTC

You were saying above that to allow remote authentication as sysdba we need to put global_dbname in listener.ora....

So, in 10g if I use em dbcontrol to login as sys as sysdba, do I need the entry global_dbname as well? My understanding is that if I put global_dbname in listener.ora file, oracle cannot do dynamic registration? Is this right? Thanks.

Tom Kyte
May 05, 2005 - 9:48 am UTC

I diddn't say that.

you do not *need* the global_dbname.

remote authentication as sysdba...

Jim, May 05, 2005 - 1:35 pm UTC

Tom, from above,

<quote>but no remote AS SYSDBA
connections then either, you need a static entry to permit that<quote>

May be I am confused with this. Could you clarify your statement. Thanks.

Tom Kyte
May 05, 2005 - 2:05 pm UTC

you need a static entry? doens't have to have global_dname, just has to exist in the listener.ora.



JYOTI, August 20, 2006 - 7:43 am UTC

Its good as the concept is explained with examples

Explained very nicely

Wild_Wild, May 17, 2010 - 10:32 am UTC

Its been explained very nicely & easy to understand. Thanks

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.