Wednesday, December 5, 2012

Create a new listener with different port numbers (Non Default Port)



##########################
Create a new listener with different port numbers 
##########################

We are going to add a new listener named lsnr_10g_1533 Which is going to use port 1533

##########################
#  1) Check how Many Listeners are Running
##########################

+ASM @ my_server:/opt/oracle/product/10.2/db_a/network/admin
> ps -ef|grep tns
  oracle  4933     1   0   Jul 17 ?          16:23 /opt/oracle/product/10.2/db_a/bin/tnslsnr lsnr_10g -inherit
  oracle  4949     1   0   Jul 17 ?          33:49 /opt/oracle/product/10.2/db_a/bin/tnslsnr lsnr_10g_1532 -inherit
  oracle  4946     1   0   Jul 17 ?           6:17 /opt/oracle/product/10.2/db_a/bin/tnslsnr lsnr_10g_1531 -inherit
  oracle  4517  4062   0 23:27:17 pts/2       0:00 grep tns

##########################
#  2) Find the home where other Listeners are Running
##########################

Here Our listeners are running in ASM HOME

##########################
#  3) Edit Listener.ora
##########################

Add below entry in LISTENER.ORA file.

Here we are using a different Port number 1533. Default port is 1521. And also in SID_LIST_LSNR_10G_1533, we are specifying for which databases the listener has to listen.

LSNR_10G_1533 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = my_server.com)(PORT = 1533)(QUEUESIZE=99))
    )
  )

SID_LIST_LSNR_10G_1533 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mydb_1.com)
      (ORACLE_HOME = /opt/oracle/product/10.2/db_a)
      (SID_NAME = mydb_1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = mydb_2.com)
      (ORACLE_HOME = /opt/oracle/product/10.2/db_a)
      (SID_NAME = mydb_2)
    )
  )

##########################
## 4) Start the Listener
##########################

+ASM @ my_server:/opt/oracle/product/10.2/db_a/network/admin
> lsnrctl start LSNR_10G_1533

LSNRCTL for Solaris: Version 10.2.0.3.0 - Production on 05-DEC-2012 23:28:10

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

Starting /opt/oracle/product/10.2/db_a/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 10.2.0.3.0 - Production
System parameter file is /opt/oracle/product/10.2/db_a/network/admin/listener.ora
Log messages written to /opt/oracle/product/10.2/db_a/network/log/lsnr_10g_1533.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=my_server)(PORT=1533)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=my_server.com)(PORT=1533)(QUEUESIZE=99)))
STATUS of the LISTENER
------------------------
Alias                     LSNR_10G_1533
Version                   TNSLSNR for Solaris: Version 10.2.0.3.0 - Production
Start Date                05-DEC-2012 23:28:10
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/10.2/db_a/network/admin/listener.ora
Listener Log File         /opt/oracle/product/10.2/db_a/network/log/lsnr_10g_1533.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=my_server)(PORT=1533)))
Services Summary...
Service "mydb_2.com" has 1 instance(s).
  Instance "mydb_2", status UNKNOWN, has 1 handler(s) for this service...
Service "mydb_1.com" has 1 instance(s).
  Instance "mydb_1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

##########################
## 5) check Listener is running or not
##########################

+ASM @ my_server:/opt/oracle/product/10.2/db_a/network/admin
> ps -ef|grep tns
  oracle  4933     1   0   Jul 17 ?          16:23 /opt/oracle/product/10.2/db_a/bin/tnslsnr lsnr_10g -inherit
  oracle  4949     1   0   Jul 17 ?          33:49 /opt/oracle/product/10.2/db_a/bin/tnslsnr lsnr_10g_1532 -inherit
  oracle  4946     1   0   Jul 17 ?           6:17 /opt/oracle/product/10.2/db_a/bin/tnslsnr lsnr_10g_1531 -inherit
  oracle  4551     1   0 23:28:11 ?           0:00 /opt/oracle/product/10.2/db_a/bin/tnslsnr LSNR_10G_1533 -inherit

##########################
## 6) Test whether new listener working or not
##########################

Add below TNS entry in TNSNAMES.ora and try connecting the database with this newly created listener.

mydb_2_1533.com =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = my_server.com)(PORT = 1533))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydb_2.com)
    )
  )

##########################
## 7) Establish SQL session and check
##########################

+ASM @ my_server:/opt/oracle/product/10.2/db_a/network/admin
> sqlplus system/password@mydb_2_1533.com

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Dec 5 23:35:18 2012

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
mydb_2    READ WRITE

SQL> exit



2 comments:

Anonymous said...

Good Information Srinivasan.. Really helped

Srinivasan thirunavukkarasu said...

Thanks Dude. Appreciate your Feedback.