TNS Listener Configuration for Oracle

Today I will mention Oracle Listener and prepare for database.

Listener is provide connection between databases at different location.

Generally standard Listener port is 1521.

Scenario:

When Oracle installed Listener_name is Listener and port is 1521. If more databases are located on the same machine then using different listener and port more beneficial.

At this Scenario I’ll use Listener_name: My_Listener and port:1525

    1.   Preparation Database

At first we are going to look default configuration status when database is up.  For this we must create

init.ora from spfile (server parameter file or binary parameter

file)

SQL> create pfile='/tmp/test_pfile.ora' from spfile;

Open the “test_pfile” and save the following command

 *.local_listener='MY_LISTENER'

SHUTDOWN IMMEDIATE the database.

We have to make this test_pfile as Oracle Spfile in order to Up database.

SQL> create spfile from pfile='/tmp/test_pfile.ora';

   2.   Preparation LISTENER.ORA

Now we have to change listener.ora file.

When you open Listener.ora you will see the default setting change like following

MY_LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

(ADDRESS = (PROTOCOL = TCP)(HOST = machine_name)(PORT = 1525))

)

)

Here write the machine_name and change port number..

After the setting Listener would be changed like following.:

# listener.ora Network Configuration File:

/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = orcl)

(ORACLE_HOME = /app/oracle/product/11.2.0/db_1)

(PROGRAM = extproc)

)

)

MY _LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

(ADDRESS = (PROTOCOL = TCP)(HOST = machine_name)(PORT = 1525))

)

)

Save the file.

    3.   Preparation TNSNAMES.ORA

TNSNAMES.ORA file is communicated between databases and server

Change TNSNAMES.ORA file like following:

MY_LISTENER =

(ADDRESS=(PROTOCOL=tcp)(HOST=machine_name)(PORT=1525))

Save it

    4.   Now start up Database and TNS listener

IF your database up normally then your listener setting should be:

SQL> show parameter local_listener

NAME TYPE VALUE

‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐

local_listener string                 MY_LISTENER

Now let start new listener:

oracle@orcl~> lsnrctl start MY _LISTENER

     5.   Control

Lets look at new listener status that is working or not:

oracle@orcl~> lsnrctl status

At the result we shoud se MY_Listener as a new name of Listener.

Sometime the listener doesn’t starting as fast as because your changing take time to active new listener.

All you need Register your system:

Use this command.

SQL> alter system register;

For more information read Oracle doc.