Skip to Main Content
  • Questions

  • bringing up two listeners for two databases on the same machine

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Jayaraman.

Asked: February 18, 2001 - 12:23 am UTC

Last updated: May 21, 2003 - 9:59 am UTC

Version: 8.1.6.0.0

Viewed 1000+ times

You Asked

Hi Tom,
I had already sent you a question regarding this. But i had accidentally deleted the reply you had given me. Can you please tell me how i have to configure listener.ora and tnsnames.ora on the same server for two different databases. For example if we have one database/instance as 'production' and the other database/instance as 'test' how do we bring up the listeners for these two databases individually .
Thanks in advance
regards,
Ashok

and Tom said...

You just have multiple listener and sid_list_listener sections in your listener.ora:

LISTENER_database =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = slackdog)(PORT = 1521))
)
)
)

listener_extproc =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = slackdog)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = extproc))
)
)
)

SID_LIST_LISTENER_database =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = /d01/home/oracle8i)
(SID_NAME = test)
)
)


SID_LIST_LISTENER_extproc =
(SID_LIST =
(SID_DESC =
(SID_NAME = extproc)
(ORACLE_HOME = /d01/home/oracle8i)
(PROGRAM = /d01/home/oracle8i/bin/extproc)
)
)

Now, I can

$ lsnrctl start listener_database
$ su - safeuser
$ lsnrctl start listener_extproc

to have my database listener running as "oracle" and my extproc listener running as some "safe" user.




Rating

  (3 ratings)

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

Comments

Listener Behaviour

vicky, March 22, 2003 - 4:59 am UTC

i m New in DBA .. i m using Oracle 9.2.0 on Linux 7.3 Box
*********************************************

My database name = db1
sid = siddb1

we have these parameters Service_names = db1
db_domain = vsnl.com
instance_name = siddb1

i think is Normaly global Name is ==> db_name||'.'||db_domain ==> service_names

but we can change Oracle global name through " Alter system rename Global_name to db1.mtnl "
but it does not change the service name ....

is there any relation with actual global_name or service_names parameter .. and whenever i changed this parameter
runtime .. " Alter system set service_names = ABC " my database got crashed ... but not in 9.0.1 or 8.1.6
it done only in 9.2.0 on linux Box..

in static registration i tried to put in Listener.ora file .
. Sid_list_listener in sid_desc => Global_name = not_global_name << any String not actual global_name

but i can connect from client machine by providing service_name = not_global_name

y oracle allow this ..

and when i set my parameter instance_name = TEST and in listener.ora same SID_name = TEST
then i was unable to connect from client machine ..

Pls clarify this .. what is relationship between lister and instance_name,service_names and global_name


Thanx ..

Tom Kyte
March 22, 2003 - 9:42 am UTC 

global name is not related to service is not related to sid.


A global name is used to UNIQUELY identify a database in a federation of databases.  Using a global name, you can make it so the DBLINK from database with global name A to database with global name B will only work if the database link itself is named after "B".  This ensures when using dblinks, you KNOW what database you are connecting to.  global names are for distributed databases.


A sid or site identifier, is used in conjunction with the oracle_home in order to generate a unique key for a shared memory realm.  After that, it is meaningless really.  You can use an oracle_home+oracle_sid to connect to an oracle instance without using the network when you are on the machine that hosts the database server.


A service is a name given to a COLLECTION (and a collection can be of size 1) of instances.  You cannot to a SERVICE and using load balancing -- Oracle will pick an instance to connect you to.  In most cases, there is a single instance in the service and hence you always goto the same instance.  Using Oracle9i RAC in a cluster however, there maybe 2, 3, .... N instances in a service and Oracle will load balance connections over them.


Your database "did not get crashed" by simply renaming the global name.  Something might have happened but it wasn't the global name change.


[tkyte@tkyte-pc-isdn tkyte]$ plus

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Mar 22 09:40:45 2003

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


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

ops$tkyte@ORA920> alter database rename global_name to foobar;

Database altered.


So, not having any real information on what you mean by "crashed", I'll have to pass on that comment.


You would not use a global name to connect clients to the server, you use global names in a distributed database environment to enforce that a dblink must be named after the global name so you are ensured you know what database you are linking to.

A reader, March 26, 2003 - 3:47 am UTC

.. Sorry Sir .. i meant when i issued this command

'Alter system set service_names = foobar ;'

i received this message system altered ..

and with in 1 minute when i tried to select any table ..

tns . packet failure ..

i tried this for 3 or four time ... every time my database got crashed ...


And Sir u said that not use Database global name for connectivity from client side ... Listener in static registration Listener registerd with global_name

Sid_List_mylistener =
(sid_list=
(sid_desc = (Global_dbname=abc.vsnl.com)

and client sid Tnsnames.ora required this global_name

Connect_data = (Service_name=abc.vsnl.com))

.. if Global_dbname in listener.ora and service_name in Tnsnames.ora is not same i m unable to connect ...

pls explain ...


ok

liyirong, May 21, 2003 - 9:59 am UTC

ok
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.