2008 June « My confrontations with oracle

June 30, 2008

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

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

Few more scrolls down and i found

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)
      )
     )
)

Comments (3)

My confrontations with oracle

June 30, 2008

ORA-12541: TNS:no listener ??

Archives

Blog Stats

Meta

Recent Posts

Blogroll

My Visitors

June 2008
S	M	T	W	T	F	S
« May				Jul »
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30