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
0 Comments:
Post a Comment