Multiple listeners for multiple SID in Oracle database

Posted 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
Post a Comment