Tuesday, January 31, 2012

Configure multiple Oracle listeners

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