Registering with Multiple listeners.

A client attempted to sort out the networking to allow two databases to register on different listeners.

Firstly, we required a listener on 1521 and another on 1522. These were set up as listener1 and listener respectively. Here’s the listener.ora file we used.

LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/app/ora_home)
(PROGRAM = extproc)
)
)
SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/app/ora_home)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 1522))
)
)

Here’s the current database setup whereby I found it registered on the wrong endpoint.

SQL> show parameter local
NAME                                 TYPE        VALUE
———————————— ———– ——————————
local_listener                       string      LISTENER1
log_archive_local_first              boolean     TRUE

The endpoint appeared only at the one ip address (listener on 1522, but not in listener1 on 1521). Easiest way to complete this is to change the parameter in the database to point to the end points.

SQL> alter system set local_listener = ‘(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 1522)))’;
System altered.
SQL> alter system register;
System altered.

Now we will be registered on both databases.

-bash-3.00$ lsnrctl status listener
LSNRCTL for Solaris: Version 10.2.0.3.0 – Production on 11-MAY-2011 20:00:51
Copyright (c) 1991, 2006, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.1)(PORT=1522)))
STATUS of the LISTENER
————————
Alias                     listener
Version                   TNSLSNR for Solaris: Version 10.2.0.3.0 – Production
Start Date                11-MAY-2011 17:02:26
Uptime                    0 days 2 hr. 58 min. 25 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/app/ora_home/network/admin/listener.ora
Listener Log File         /opt/oracle/app/ora_home/network/log/listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.1)(PORT=1522)))
Services Summary…
Service “MYDB1″ has 1 instance(s).
Instance “MYDB1″, status READY, has 1 handler(s) for this service…
Service “MYDB1XDB” has 1 instance(s).
Instance “MYDB1″, status READY, has 1 handler(s) for this service…
Service “MYDB1_XPT” has 1 instance(s).
Instance “MYDB1″, status READY, has 1 handler(s) for this service…
Service “MYDB2″ has 1 instance(s).
Instance “MYDB2″, status READY, has 1 handler(s) for this service…
Service “MYDB2XDB” has 1 instance(s).
Instance “MYDB2″, status READY, has 1 handler(s) for this service…
Service “MYDB2_XPT” has 1 instance(s).
Instance “MYDB2″, status READY, has 1 handler(s) for this service…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
-bash-3.00$ lsnrctl status listener1
LSNRCTL for Solaris: Version 10.2.0.3.0 – Production on 11-MAY-2011 20:00:59
Copyright (c) 1991, 2006, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.1)(PORT=1521)))
STATUS of the LISTENER
————————
Alias                     listener1
Version                   TNSLSNR for Solaris: Version 10.2.0.3.0 – Production
Start Date                11-MAY-2011 17:02:28
Uptime                    0 days 2 hr. 58 min. 30 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/app/ora_home/network/admin/listener.ora
Listener Log File         /opt/oracle/app/ora_home/network/log/listener1.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.1)(PORT=1521)))
Services Summary…
Service “MYDB1″ has 1 instance(s).
Instance “MYDB1″, status READY, has 1 handler(s) for this service…
Service “MYDB1XDB” has 1 instance(s).
Instance “MYDB1″, status READY, has 1 handler(s) for this service…
Service “MYDB1_XPT” has 1 instance(s).
Instance “MYDB1″, status READY, has 1 handler(s) for this service…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

You can set this in the tnsnames.ora to register with multiple end points too. Here I’ll create a new tnsnames entry to point to the two endpoints.

LISTENERB = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 1522)))
SQL> alter system set local_listener = ‘LISTENERB’;
System altered.

Now I stopped and started the database, and both listeners. So, to test, we’ll run the following:

-bash-3.00$ lsnrctl status listener
LSNRCTL for Solaris: Version 10.2.0.3.0 – Production on 11-MAY-2011 20:04:58
Copyright (c) 1991, 2006, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.1)(PORT=1522)))
STATUS of the LISTENER
————————
Alias                     listener
Version                   TNSLSNR for Solaris: Version 10.2.0.3.0 – Production
Start Date                11-MAY-2011 20:04:42
Uptime                    0 days 0 hr. 0 min. 16 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/app/ora_home/network/admin/listener.ora
Listener Log File         /opt/oracle/app/ora_home/network/log/listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.1)(PORT=1522)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
-bash-3.00$ lsnrctl status listener1
LSNRCTL for Solaris: Version 10.2.0.3.0 – Production on 11-MAY-2011 20:05:02
Copyright (c) 1991, 2006, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.1)(PORT=1521)))
STATUS of the LISTENER
————————
Alias                     listener1
Version                   TNSLSNR for Solaris: Version 10.2.0.3.0 – Production
Start Date                11-MAY-2011 20:04:54
Uptime                    0 days 0 hr. 0 min. 7 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/app/ora_home/network/admin/listener.ora
Listener Log File         /opt/oracle/app/ora_home/network/log/listener1.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.1)(PORT=1521)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

And now to register the database.

SQL> alter system register;
System altered.
-bash-3.00$ lsnrctl status listener
LSNRCTL for Solaris: Version 10.2.0.3.0 – Production on 11-MAY-2011 20:05:07
Copyright (c) 1991, 2006, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.1)(PORT=1522)))
STATUS of the LISTENER
————————
Alias                     listener
Version                   TNSLSNR for Solaris: Version 10.2.0.3.0 – Production
Start Date                11-MAY-2011 20:04:42
Uptime                    0 days 0 hr. 0 min. 25 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/app/ora_home/network/admin/listener.ora
Listener Log File         /opt/oracle/app/ora_home/network/log/listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.1)(PORT=1522)))
Services Summary…
Service “MYDB1″ has 1 instance(s).
Instance “MYDB1″, status READY, has 1 handler(s) for this service…
Service “MYDB1XDB” has 1 instance(s).
Instance “MYDB1″, status READY, has 1 handler(s) for this service…
Service “MYDB1_XPT” has 1 instance(s).
Instance “MYDB1″, status READY, has 1 handler(s) for this service…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
-bash-3.00$ lsnrctl status listener1
LSNRCTL for Solaris: Version 10.2.0.3.0 – Production on 11-MAY-2011 20:05:09
Copyright (c) 1991, 2006, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.1)(PORT=1521)))
STATUS of the LISTENER
————————
Alias                     listener1
Version                   TNSLSNR for Solaris: Version 10.2.0.3.0 – Production
Start Date                11-MAY-2011 20:04:54
Uptime                    0 days 0 hr. 0 min. 14 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/app/ora_home/network/admin/listener.ora
Listener Log File         /opt/oracle/app/ora_home/network/log/listener1.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.1)(PORT=1521)))
Services Summary…
Service “MYDB1″ has 1 instance(s).
Instance “MYDB1″, status READY, has 1 handler(s) for this service…
Service “MYDB1XDB” has 1 instance(s).
Instance “MYDB1″, status READY, has 1 handler(s) for this service…
Service “MYDB1_XPT” has 1 instance(s).
Instance “MYDB1″, status READY, has 1 handler(s) for this service…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
-bash-3.00$

After a short while, the second database registering on the listener called “listener” will appear.

-bash-3.00$ lsnrctl status listener
LSNRCTL for Solaris: Version 10.2.0.3.0 – Production on 11-MAY-2011 20:06:21
Copyright (c) 1991, 2006, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.1)(PORT=1522)))
STATUS of the LISTENER
————————
Alias                     listener
Version                   TNSLSNR for Solaris: Version 10.2.0.3.0 – Production
Start Date                11-MAY-2011 20:04:42
Uptime                    0 days 0 hr. 1 min. 39 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/app/ora_home/network/admin/listener.ora
Listener Log File         /opt/oracle/app/ora_home/network/log/listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.1.1)(PORT=1522)))
Services Summary…
Service “MYDB1″ has 1 instance(s).
Instance “MYDB1″, status READY, has 1 handler(s) for this service…
Service “MYDB1XDB” has 1 instance(s).
Instance “MYDB1″, status READY, has 1 handler(s) for this service…
Service “MYDB1_XPT” has 1 instance(s).
Instance “MYDB1″, status READY, has 1 handler(s) for this service…
Service “MYDB2″ has 1 instance(s).
Instance “MYDB2″, status READY, has 1 handler(s) for this service…
Service “MYDB2XDB” has 1 instance(s).
Instance “MYDB2″, status READY, has 1 handler(s) for this service…
Service “MYDB2_XPT” has 1 instance(s).
Instance “MYDB2″, status READY, has 1 handler(s) for this service…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

So remember there are a number of ways to set this up, some are easier to understand than others.

This entry was posted in Configuration., Networking and tagged , , , , . Bookmark the permalink.

One Response to Registering with Multiple listeners.

  1. Sean Robinson says:

    Thank you so much! You won’t believe how many websites have scripts and commands that don’t work. Everything here was spot on.

Comments are closed.