How to Add a Standalone Listener on Second IP to Grid

Suppose you have added a second IP on OS. You want a second listener to listen port 1521 of the new IP address for the second database. If the second database is a clustered database (RAC), I suggest that you to add a SCAN VIP on grid-level instead, it will add a SCAN listener automatically.

If the second database is a single-instance, you may create the second listener either on grid-level or database-level. In this case, I decide to add the listener on grid-level. Here I introduce how to add a separate listener with a separate IP address for serving the single-instance database.

First, please add an entry for the second listener in the configuration file listener.ora. In which, 10.0.0.201 is a separate IP address that I have already added it in eth0:18

[grid@primary01 ~]$ vi $ORACLE_HOME/network/admin/listener.ora
...
LISTENER2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.201)(PORT = 1521))
    )
  )

Then, we can start this listener. Since LISTENER2 is not a resource of RAC, you can't start it by srvctl, but you can use traditional lsnrctl to start it.

[grid@primary01 ~]$ lsnrctl start listener2

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on X

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

Starting /u01/app/12.1.0/grid/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/12.1.0/grid/network/admin/listener.ora
Log messages written to /u01/app/grid/diag/tnslsnr/primary01/listener2/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.201)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.201)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     listener2
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                X
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/primary01/listener2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.201)(PORT=1521)))
The listener supports no services
The command completed successfully

Back to the second database, we have to modify LOCAL_LISTENER parameter to register with the LISTENER2.

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

System altered.

SQL> show parameter local_listener;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (ADDRESS=(PROTOCOL=TCP)(HOST=1
                                                 0.0.0.201)(PORT=1521))
Check the status of LISTENER2.
[grid@primary01 ~]$ lsnrctl status listener2

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on X

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.201)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     listener2
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                X
Uptime                    0 days 0 hr. 3 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/primary01/listener2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.201)(PORT=1521)))
Services Summary...
Service "SMALLDB" has 1 instance(s).
  Instance "SMALLDB", status READY, has 1 handler(s) for this service...
Service "SMALLDBXDB" has 1 instance(s).
  Instance "SMALLDB", status READY, has 1 handler(s) for this service...
The command completed successfully

It's working now.

By the way, if you have SID_LIST_LISTENER in the configuration, the static listener will only stick with the default listener, not the additional listener. Which means, if you stop LISTENER2, the static listener will not be affected. But if you stop the default listener, it will also stop the static listener.