Multiple listeners for multiple SID in Oracle database

datePosted on 17:18, March 21st, 2008 by yk

Scenario: You have one Oracle database with 3 different SID for different purposes: DR (disaster recovery), UAT (User Acceptance Testing) and SIT (System Integration Testing). You are tasked to configure the Database to open one port for each of the SID to control access. Piece of cake, you will only need to edit two oracle configuration files to achieve it.

Technical details: The following configuration has been tested on Oracle 10GR2 that was installed on AIX 5.3ML6.

Assumptions: The three SID in the Oracle database are named DRDB, UATDB and SITDB. Host name (machine name) is ORACLEDB01 and Global Database name is ORACLEDB. We willl create 3 listeners with the following configuration:
Listener name: LISTENER
Port: 1521 (default listener port)
SID: DRDB
Listener name: LISTENER1
Port: 1522
SID: UATDB
Listener name: LISTENER2
Port: 1523
SID: SITDB

First of all, back up the two files in case we need to restore the original configuration.

The next step is to stop the running listener. Get into your oracle shell (for my case, I need to su - oracle) and stop the activated listener by typing lsnrctl stop.

Now, you will need to located two files with the name tnsnames.ora and listener.ora. If you are using unix like me, you can type in:
find / -name "*.ora"
In my case, the relevant files are located at:
/u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

Next, edit tnsnames.ora and it should look something like this:
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRDDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORACLEDB01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prddb)
)
)

UATDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORACLEDB01)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = uatdb)
)
)

SITDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORACLEDB01)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sitdb)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

The next step is to edit listener.ora and it should look something like this:
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = ORACLEDB01)(PORT = 1521))
)
)

SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=ORACLEDB)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME=uatdb)
)
)

LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORACLEDB01)(PORT = 1522))
)
)

SID_LIST_LISTENER2 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=ORACLEDB)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME=sitdb)
)
)

LISTENER2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORACLEDB01)(PORT = 1523))
)
)

After editing the files, your new listeners are ready for action. If everything is done correctly, you can start each listener on its own by typing:
lsnrctl start listener
lsnrctl start listener1
lsnrctl start listener2
You can stop them individually by typing:
lsnrctl stop listener
lsnrctl stop listener1
lsnrctl stop listener2

categoryPosted in Tips and Tricks | printPrint

2 Responses to “Multiple listeners for multiple SID in Oracle database”

  1. BrenBart on January 12th, 2009 at 11:03 pm

    I also have a 10gR2 database on AIX (5.2) and have tried this but it doesn’t work for me. Listener1 and Listener2 will start and I can TNSPING them from the client but when I try to connect from the application it throws a “ORA-12514: TNS:listener does not currently know of service” error. I’m wondering if there isn’t a paramter in the init.ora file that allows/disallows multiple sids? Any thoughts?

  2. Goge on July 14th, 2009 at 5:44 am

    What do i need to do to ‘open’ the new port? cause i run nmap from another computer and i cannot see that port. (Oracle 11G on RHL5).
    I tried to open by iptables, but this doesn’t work yet.

Leave a Reply

Name: (required)
Email: (required) (will not be published)
Website:
Spam protection:
Comment: