Two different listeners on the same Oracle database server

Sunday, March 22, 2009

Listener is process that establishes a communication with Oracle database/databases.

On most database servers you will have one (default) listener "LISTENER" per server which listens on 1521 port.

But what if I have one server machine with two Oracle databases and I want to add two different listeners for this databases?

I'll show in simple demo case how to accomplish that.



So I have:
(Oracle 11g database)
SID : test11
HOME : /app/oracle/product/11.1.0

(Oracle 10g database)
SID : test10
HOME : /app/oracle/product/10.2.0

with sqlnet.ora:

# sqlnet.ora Network Configuration File: /oracle/product/10.2.0/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


First, I will configure listener for 11g database:

LISTENER.ORA

# listener.ora Network Configuration File: /app/oracle/product/11.1.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER_1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /app/oracle/product/11.1.0)
(PROGRAM = extproc)
)
)


LISTENER_1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = test11))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.10.18)(PORT = 1521))
)
)


And add appropriate tnsnames.ora entry:

# tnsnames.ora Network Configuration File: /app/oracle/product/11.1.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TEST11 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.10.18)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test11)
)
)


In listener.ora settings I've added home path for listener process, set listener name into "SID_LIST_LISTENER_1" and added configuration for "LISTENER_1" listener. Two important settings here are host name which is in my case 10.2.10.18 and port which will LISTENER_1 use for listening - I'll leave default 1521.


I'll start LISTENER_1:
$ lsnrctl start LISTENER_1

$ lsnrctl service LISTENER_1

LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 21-MAR-2009 22:59:11

Copyright (c) 1991, 2008, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=test11)))
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
The command completed successfully


PMON dynamically registers a database service with the listener, but I will force registration of the database information to the listener with "alter system register" command.

$ sqlplus "/as sysdba"

SQL> alter system register;

System altered.


$ lsnrctl status LISTENER_1

LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 21-MAR-2009 23:01:53

Copyright (c) 1991, 2008, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_1
Version                   TNSLSNR for Linux: Version 11.1.0.7.0 - Production
Start Date                21-MAR-2009 22:58:54
Uptime                    0 days 0 hr. 2 min. 58 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/11.1.0/network/admin/listener.ora
Listener Log File         /app/oracle/diag/tnslsnr/dibidus2/listener_1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=test11)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.10.18)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "test11" has 1 instance(s).
Instance "test11", status READY, has 1 handler(s) for this service...
Service "test11XDB" has 1 instance(s).
Instance "test11", status READY, has 1 handler(s) for this service...
Service "test11_XPT" has 1 instance(s).
Instance "test11", status READY, has 1 handler(s) for this service...
The command completed successfully


To test it out:
$ tnsping test11

TNS Ping Utility for Linux: Version 11.1.0.7.0 - Production on 21-MAR-2009 23:03:19

Copyright (c) 1997, 2008, Oracle.  All rights reserved.

Used parameter files:
/app/oracle/product/11.1.0/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.10.18)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test11)))
OK (0 msec)


[oracle@dibidus2 admin]$ sqlplus system/qwertz@test11

SQL*Plus: Release 11.1.0.7.0 - Production on Sat Mar 21 23:03:54 2009

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>


It works :)


Now another listener to configure for 10g database.

listener.ora
# listener.ora Network Configuration File: /app/oracle/product/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER_2 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /app/oracle/product/10.2.0)
(PROGRAM = extproc)
)
)

LISTENER_2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = test11))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.10.18)(PORT = 1522))
)



tnsnames.ora

TEST10 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.10.18)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = test10)
)
)


For LISTENER_2 I have to change default listener port because 1521 port is already used by LISTENER_1 so I'll use port 1522.

By default PMON will register database service with the listener on port 1521 (in our case LISTENER_1) so I have to force PMON to use non-default port 1522.

I can acomplish that changing LOCAL_LISTENER parameter value.
From documentation: LOCAL_LISTENER specifies a network name that resolves to an address or address list of Oracle Net local listeners.

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.2.10.18)(PORT=1522))' scope=both;

System altered.


Now PMON will register database service with the listener on port 1522.


To test it out:

$ lsnrctl start LISTENER_2


$ sqlplus "/as sysdba"

SQL> alter system register;

System altered.


$ lsnrctl status LISTENER_2

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 21-MAR-2009 23:19:00

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.2.10.18)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_2
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                21-MAR-2009 23:18:06
Uptime                    0 days 0 hr. 0 min. 53 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/10.2.0/network/admin/listener.ora
Listener Log File         /app/oracle/product/10.2.0/network/log/listener_2.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.10.18)(PORT=1522)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "test10" has 1 instance(s).
Instance "test10", status READY, has 1 handler(s) for this service...
Service "test10XDB" has 1 instance(s).
Instance "test10", status READY, has 1 handler(s) for this service...
Service "test10_DGB" has 1 instance(s).
Instance "test10", status READY, has 1 handler(s) for this service...
Service "test10_XPT" has 1 instance(s).
Instance "test10", status READY, has 1 handler(s) for this service...
The command completed successfully




$ tnsping test10

TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 21-MAR-2009 23:19:26

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:
/app/oracle/product/10.2.0/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.10.18)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = test10)))
OK (0 msec)




$ sqlplus system/qwertz@test10

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 21 23:19:43 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>



It works :)


Listener processes on server machine:

$ ps -ef |grep tnslsnr
oracle 444 1 0 23:18 ? 00:00:00 /app/oracle/product/10.2.0/bin/tnslsnr LISTENER_2 -inherit
oracle 601 31512 0 23:20 pts/3 00:00:00 grep tnslsnr
oracle 31773 1 0 22:58 ? 00:00:00 /app/oracle/product/11.1.0/bin/tnslsnr LISTENER_1 -inherit


Share/Bookmark

0 comments:

Post a Comment