ORA-12541: TNS:no listener ??

I created a new RAC database. It was on 10.2.0.3 and the database was in shared server mode and had 2 nodes. I created a service name ‘test’ and the listener and the instances were up and running on both the nodes.

I had the below in my tnsnames.ora on my PC.
rac_dev =
  (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST=3.208.169.202)(PORT= 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST=3.208.169.206)(PORT= 1521))
     (LOAD_BALANCE = no)
     (CONNECT_DATA =
        (SERVICE_NAME = test)
        (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
     )
  )

I could tnsping using the tnsalias.

C:\WINNT>tnsping rac_dev

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0
Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:D:\Oracle10gAS_Home\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST=3.208.169.202)(PORT= 1521)) (ADDRESS = (PROTOCOL = TCP)
(HOST=3.208.169.206)(PORT= 1521)) (LOAD_BALANCE = yes) (FAILOVER=ON) (CONNECT_DATA = (SERVICE_NAME = test)
 (SERVER = DEDICATED) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5))))
OK (40 msec)

C:\WINNT>
Then i tried to connect using sqlplus
 
C:\Documents and Settings\Administrator>sqlplus user/pwd@rac_dev
SQL*Plus: Release 10.1.0.5.0 – Production on 3 18:17:26 2015
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-12541: TNS:no listener

Oops!!.
I googled for “ORA-12541” and found many scenarios and i checked all.
But still it didn’t take me anywhere.

Then i traced my sql*plus client by adding the below to the sqlnet.ora file on my PC

TRACE_LEVEL_CLIENT = 16
TRACE_FILE_CLIENT = client
TRACE_DIRECTORY_CLIENT = <any valid DIRECTORY path>
TRACE_TIMESTAMP_CLIENT = ON

and tried sqlplus user/pwd@rac_de and got the tracefiles generated.vv

Halfway down inside the tracefiles i found

[07-11ŒŽ-2015 12:12:47:156] nspsend: packet dump
[07-11ŒŽ-2015 12:12:47:156] nspsend: 01 34 00 00 06 00 00 00  |.4……|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 00 00 28 44 45 53 43 52  |..(DESCR|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 49 50 54 49 4F 4E 3D 28  |IPTION=(|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 41 44 44 52 45 53 53 3D  |ADDRESS=|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 28 50 52 4F 54 4F 43 4F  |(PROTOCO|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 4C 3D 54 43 50 29 28 48  |L=TCP)(H|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 4F 53 54 3D 33 2E 32 30  |OST=3.20|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 38 2E 31 36 39 2E 32 30  |8.169.20|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 32 29 28 50 4F 52 54 3D  |2)(PORT=|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 31 35 32 31 29 29 28 4C  |1521))(L|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 4F 41 44 5F 42 41 4C 41  |OAD_BALA|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 4E 43 45 3D 6E 6F 29 28  |NCE=no)(|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 43 4F 4E 4E 45 43 54 5F  |CONNECT_|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 44 41 54 41 3D 28 53 45  |DATA=(SE|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 52 56 49 43 45 5F 4E 41  |RVICE_NA|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 4D 45 3D 64 61 79 62 72  |ME=rac_d|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 63 6F 6D 29 28 46 41 49  |ev)(FAI |
[07-11ŒŽ-2015 12:12:47:156] nspsend: 4C 4F 56 45 52 5F 4D 4F  |LOVER_MO|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 44 45 3D 28 54 59 50 45  |DE=(TYPE|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 3D 53 45 4C 45 43 54 29  |=SELECT)|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 28 4D 45 54 48 4F 44 3D  |(METHOD=|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 42 41 53 49 43 29 28 52  |BASIC)(R|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 45 54 52 49 45 53 3D 31  |ETRIES=1|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 38 30 29 28 44 45 4C 41  |80)(DELA|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 59 3D 35 29 29 28 43 49  |Y=5))(CI|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 44 3D 28 50 52 4F 47 52  |D=(PROGR|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 41 4D 3D 44 3A 5C 4F 72  |AM=D:\Or|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 61 63 6C 65 31 30 67 41  |acle10gA|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 53 5F 48 6F 6D 65 5C 62  |S_Home\b|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 69 6E 5C 73 71 6C 70 6C  |in\sqlpl|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 75 73 2E 65 78 65 29 28  |us.exe)(|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 48 4F 53 54 3D 4D 59 4A  |HOST=MYJ|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 50 53 57 4F 4C 49 43 4D  |PSWOLICM|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 47 54 30 31 29 28 55 53  |GT01)(US|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 45 52 3D 41 64 6D 69 6E  |ER=Admin|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 69 73 74 72 61 74 6F 72  |istrator|
[07-11ŒŽ-2015 12:12:47:156] nspsend: 29 29 29 29              |))))    |
[07-11ŒŽ-2015 12:12:47:156] nspsend: 308 bytes to transport
[07-11ŒŽ-2015 12:12:47:156] nspsend: normal exit

Few more scrolls down and i found

[07-11ŒŽ-2015 12:12:47:156] nsprecv: 00 42 00 00 05 00 00 00  |.B……|
[07-11ŒŽ-2015 12:12:47:156] nsprecv: 00 38 28 41 44 44 52 45  |.8(ADDRE|
[07-11ŒŽ-2015 12:12:47:156] nsprecv: 53 53 3D 28 50 52 4F 54  |SS=(PROT|
[07-11ŒŽ-2015 12:12:47:156] nsprecv: 4F 43 4F 4C 3D 74 63 70  |OCOL=tcp|
[07-11ŒŽ-2015 12:12:47:156] nsprecv: 29 28 48 4F 53 54 3D 33  |)(HOST=3|
[07-11ŒŽ-2015 12:12:47:156] nsprecv: 2E 32 30 38 2E 31 36 39  |.208.169|
[07-11ŒŽ-2015 12:12:47:156] nsprecv: 2E 32 30 32 29 28 50 4F  |.202)(PO|
[07-11ŒŽ-2015 12:12:47:156] nsprecv: 52 54 3D 33 32 38 38 36  |RT=32886|
[07-11ŒŽ-2015 12:12:47:156] nsprecv: 29 29                    |))      |
[07-11ŒŽ-2015 12:12:47:156] nsprecv: normal exit

But here the port is different. Its 32886.
Down again i found

[07-11ŒŽ-2015 12:12:47:156] nscall: connecting…
[07-11ŒŽ-2015 12:12:47:156] nsc2addr: entry
[07-11ŒŽ-2015 12:12:47:156] nsc2addr: (ADDRESS=(PROTOCOL=tcp)(HOST=3.208.169.202)(PORT=32886))
[07-11ŒŽ-2015 12:12:47:156] nttbnd2addr: entry
[07-11ŒŽ-2015 12:12:47:156] nttgetport: entry
[07-11ŒŽ-2015 12:12:47:156] nttgetport: port resolved to 32886
[07-11ŒŽ-2015 12:12:47:156] nttgetport: exit
[07-11ŒŽ-2015 12:12:47:156] nttbnd2addr: using host IP address: 3.208.169.202

and found that the client was trying to connect to port 32886 on 3.208.169.202 (when i had mentioned 1521 in the tnsentry).

But Why?
If the database is in SHARED MODE, all connections that does not have “SERVER=DEDICATED” in the tnsentry will try to make a shared connection.

In my case, the dispatchers were listening on ports between 32000 and 33000 and the firewall was not opened to these ports but only to 1521.

So, while the connection was initiated to port 1521, the listener when found my database in SHARED SERVER MODE and since i didnt have SERVER=DEDICATED in the tnsentry, it was redirected it to port 32886 to use the dispatchers and since the port 32886 was blocked by the firewall sql*plus client on my PC failed to establish the connection and resulted in “ORA-12541: TNS:no listener”

Then i changed my tnsentry to
rac_dev =
  (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST=3.208.169.202)(PORT= 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST=3.208.169.206)(PORT= 1521))
     (LOAD_BALANCE = yes) (FAILOVER=ON)
     (CONNECT_DATA =
     (SERVICE_NAME = test)
     (SERVER=DEDICATED)
        (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
     )
)

Advertisements

3 thoughts on “ORA-12541: TNS:no listener ??

  1. Pingback: Index « My confrontations with oracle

  2. I cant able to open sql*plus. It shows Ora-15421: TNS: no listener. I checked many forums. most says to run listener. How to run the listener. where i want to run. Please tell the procedure or code to run the listener.

    • Hello Boobesh,

      Listener process always run on the oracle DB server.
      Can you tell me from where you are getting the error ?
      From your PC or from the DB server ?

      If your Db server is on Windows, then Control Panel->Administrative Tools->Services-> Check for “Oracle….TNSListener”
      “Start” if it is not started.
      If your DB server is on nix(unix/linux) machine then, check whether listener is running or not:
      $lsnrctl status

      If it the above shows that it isnt running, start it using:
      $lsnrctl start

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s