Tag Archives: listener oracle database

Adding new listeners for Oracle Database

I got a request today, to make available the same Oracle Database instance on an another port besides the default one. One simple but not so elegant way to make this happen (if you have root access) is to redirect the traffic from one port to the one you already have setup using this command:

iptables -t nat -I PREROUTING --src 0/0 --dst 10.0.1.15 -p tcp --dport 1522 -j REDIRECT --to-ports 1521

The other way, the more elegant way, is to create another listener on the requested port and tell the database to register itself to that listener. Here are the steps you need to follow, after you have run “. oraenv” to setup your environment:

1. Create the new listener on the desired port:

srvctl add listener -l LISTENER2 -p TCP:1522 -o /u01/app/oracle/product/11.2.0.3/dbhome_1

-l = the listener name, LISTENER is most probably already taken
-p = the protocol and port to use
-o = Oracle Home

2. Start the listener:

srvctl start listener LISTENER2

3. Now you need to modify the local_listener parameter in the database. If you need to set multiple ports, then you will need to use the tnsname for that. If you try to input the string directly in the database you will receive an ORA-00972: identifier is too long. So add the following entry in the $ORACLE_HOME/network/admin/tnsnames.ora file:

ORCL_1521_1522 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.15)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.15)(PORT = 1522))
    )
  )

4. Configure the local_listener in the database:

[oracle@srv ~]$ sqlplus / as sysdba
SQL> ALTER SYSTEM SET local_listener = 'ORCL_1521_1522' SCOPE=MEMORY SID='ORCL11';

System altered.

5. Register the entries with the listener:

SQL> alter system register;

Now you should see when running ‘lsnrctl status LISTENER2’ you should see the new registered database in the listener.

As far as I remember the ‘lsnrct start’ command will only start the default listener, so don’t forget to start the second listener as well. If you have configuret the automatic db start on boot, then you will have to modify the startup scripts (/etc/init.d/dbora file fi you followed the Official Documentation) and add at startup and shutdown the appropriate commands.