Suppose you are in an environment where you have many logons and look for a
solution to tune the logon time for the users. This can be in an OLTP system.
An option we are going to explore here is creating multiple listeners.
You first have to create new listener, which let's say will run on port 1523;
on top of the default one you already have running on 1521.
You can simply edit your listener.ora or create new listener with Net Configuration
Assistant.
You will also have to add new entry in your tnsnames.ora, either by editing the file or
with Net Manager.
A sample of the existing files will be:
listener.ora
ADR_BASE_LISTENER_1523 = D:\oci112
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
ADR_BASE_LISTENER = D:\oci112
LISTENER_1523 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1523))
)
)
tnsnames.ora
HOBER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hober)
)
)
HOBER1523 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hober)
)
)
Now let's check the status of the listeners:
LSNRCTL> status LISTENER_1523
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias LISTENER_1523
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 30-JAN-2012 23:37:39
Uptime 0 days 0 hr. 7 min. 49 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oci112\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File d:\oci112\diag\tnslsnr\emy-85f0b520004\listener_1523\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1523)))
Services Summary...
Service "HOBER" has 1 instance(s).
Instance "HOBER", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> status LISTENER
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 30-JAN-2012 23:02:35
Uptime 0 days 0 hr. 43 min. 7 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oci112\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File d:\oci112\diag\tnslsnr\emy-85f0b520004\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "hober" has 1 instance(s).
Instance "hober", status READY, has 1 handler(s) for this service...
Service "hoberXDB" has 1 instance(s).
Instance "hober", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>
The new listener has the UNKNOWN status as is defined statically, still, this will not prevent us to establish a new connection.
The one with status READY is the one which is registered automatically -
when DB is started, PMON automatically registers the DB to the listener.
It is doing this in the very first minute since db startup.
That is the reason you have to keep the correct order, first you start the listener and then the database.
Now, let's attempt to connect:
----------1
C:\>sqlplus sys@hober as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 30 23:41:50 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set linesize 300
SQL> set pagesize 200
SQL> select name, db_unique_name from v$database;
NAME DB_UNIQUE_NAME
--------- ------------------------------
HOBER hober
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> select to_char(sysdate, 'dd-mm-yyyy hh24:mi:ss') as "date" from dual;
date
-------------------
30-01-2012 23:44:05
SQL>
------------2
C:\>sqlplus sys@hober1523 as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 30 23:43:34 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name, db_unique_name from v$database;
NAME DB_UNIQUE_NAME
--------- ------------------------------
HOBER hober
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> select to_char(sysdate, 'dd-mm-yyyy hh24:mi:ss') as "date" from dual;
date
-------------------
30-01-2012 23:44:16
SQL>
The connection is succesfully established.
Now we want the instance to automatically register with listener.
We are doing this by altering the parameter LOCAL_LISTENER as it follows:
SQL> alter system set LOCAL_LISTENER="(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
2 (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1523)))" scope=BOTH;
System altered.
SQL> show parameter listener;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
listener_networks string
local_listener string (ADDRESS_LIST=(ADDRESS=(PROTOC
OL=TCP)(HOST=localhost)(PORT=1
521))
(ADDRESS=(PROTOCOL=TCP)(HOST
=localhost)(PORT=1523)))
remote_listener string
We restart the listeners and check their status:
---stopping stage
LSNRCTL> stop LISTENER
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
LSNRCTL> stop LISTENER_1523
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1523)))
The command completed successfully
---startup stage
LSNRCTL> start LISTENER
Starting tnslsnr: please wait...
TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
System parameter file is D:\oci112\product\11.2.0\dbhome_1\network\admin\listener.ora
Log messages written to d:\oci112\diag\tnslsnr\emy-85f0b520004\listener\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 31-JAN-2012 00:00:31
Uptime 0 days 0 hr. 0 min. 2 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oci112\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File d:\oci112\diag\tnslsnr\emy-85f0b520004\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> start LISTENER_1523
Starting tnslsnr: please wait...
TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
System parameter file is D:\oci112\product\11.2.0\dbhome_1\network\admin\listener.ora
Log messages written to d:\oci112\diag\tnslsnr\emy-85f0b520004\listener_1523\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1523)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias LISTENER_1523
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 31-JAN-2012 00:00:44
Uptime 0 days 0 hr. 0 min. 2 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oci112\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File d:\oci112\diag\tnslsnr\emy-85f0b520004\listener_1523\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1523)))
Services Summary...
Service "HOBER" has 1 instance(s).
Instance "HOBER", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> services LISTENER
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
And now the stats:
LSNRCTL> stat LISTENER
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 31-JAN-2012 00:00:31
Uptime 0 days 0 hr. 3 min. 23 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oci112\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File d:\oci112\diag\tnslsnr\emy-85f0b520004\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "hober" has 1 instance(s).
Instance "hober", status READY, has 1 handler(s) for this service...
Service "hoberXDB" has 1 instance(s).
Instance "hober", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> stat LISTENER_1523
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias LISTENER_1523
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 31-JAN-2012 00:00:44
Uptime 0 days 0 hr. 3 min. 21 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oci112\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File d:\oci112\diag\tnslsnr\emy-85f0b520004\listener_1523\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1523)))
Services Summary...
Service "HOBER" has 2 instance(s).
Instance "HOBER", status UNKNOWN, has 1 handler(s) for this service...
Instance "hober", status READY, has 1 handler(s) for this service...
Service "hoberXDB" has 1 instance(s).
Instance "hober", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>
We see that the instance appears READY in both the listener configurations, meaning it has
succesfully registered with the listener.
Now you can redirect your application to connect either to service name hober or hober1523, which in the end will connect to the same db instance.
No comments:
Post a Comment