Solved

Ora 12541:TNS no listener error

Posted on 2009-04-08
16
1,026 Views
Last Modified: 2013-12-19
I am unable to login as the sysadmin of the oracle database.I am using SQLPLUS and I get the above error.I checked and edited my TNS file,Listener file and sqlnet.ora file and yet I seem to get the error.
This is my TNS entry

JLN.WORLD=

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = JACK)(PORT = 1527))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = JLN)

      (SID=JLN)

    )

  )
 

This is my listener entry

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = JACK)(PORT = 1527))

    )

  )
 
 

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = JLN)

      (ORACLE_HOME = C:\oracle\ora92)

      (PROGRAM = extproc)

    )

)
 

CONNECT_TIMEOUT_LISTENER = 10
 

STARTUP_WAIT_TIME_LISTENER = 0
 
 

TRACE_LEVEL_LISTENER = OFF
 

This is my SQLnet.ora entry
 

SQLNET.AUTHENTICATION_SERVICES = (NTS)

AUTOMATIC_IPC = ON

TRACE_LEVEL_CLIENT = 16

TRACE_LEVEL_CLIENT = OFF

NAMES.DEFAULT_DOMAIN = WORLD

NAME.DEFAULT_ZONE = WORLD

Open in new window

0
Comment
Question by:Jagdishl
  • 8
  • 6
16 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24099852
You don't need the listener to login.

sqlplus system/admin as sysdba
sqlplus / as sysdba


But, did you verify the listener is running?

> lsnrctl status

Try (as the oracle OS owner):

> lsnrctl start
0
 

Author Comment

by:Jagdishl
ID: 24099915


C:\Documents and Settings\Administrator>lsnrctl status

LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 08-APR-2009 11:44:
57
The listener is up and running.I am attaching the code for both the comands that I typed.
Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=JACK)(PORT=1527)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Produc
tion
Start Date                08-APR-2009 08:55:24
Uptime                    0 days 2 hr. 49 min. 33 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   C:\oracle\ora92\network\admin\listener.ora
Listener Log File         C:\oracle\ora92\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=JACK)(PORT=1527)))
Services Summary...
Service "JLN" has 1 instance(s).
  Instance "JLN", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
C:\Documents and Settings\Administrator>lsnrctl start

LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 08-APR-2009 11:48:
40

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Starting tnslsnr: please wait...

Service OracleOraHome92TNSListener already running.
TNS-12536: TNS:operation would block
 TNS-12560: TNS:protocol adapter error
  TNS-00506: Operation would block
   32-bit Windows Error: 997: Unknown error

C:\Documents and Settings\Administrator>


0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24099962
Your listener is running correctly.

Please try removing SID=JLN, it is not needed, see below, you should just need SERVICE_NAME

Also, please post the sqlplus command line you are running (change password for security).

JLN.WORLD=

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = JACK)(PORT = 1527))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = JLN)

#      (SID=JLN)  ## REMOVE NOT NEEDED

    )

  )

Open in new window

0
 

Author Comment

by:Jagdishl
ID: 24100084
I modifed the entry to remove the SID and tried again. I wasnt able to login.I got the same error.
C:\Documents and Settings\Administrator>sqlplus system/sysadmin@jack

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Apr 8 12:07:46 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

ERROR:
ORA-12541: TNS:no listener


Enter user-name:
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24100183
Don't use @jack, use @JLN

JACK is your hostname, not your Oracle service name. JLN is the Oracle TNS service, that is what you use.

Also, if you are local, you aren't required to use @syntax anyway, you can skip the listener altogether and connect direct.

set ORACLE_SID=JACK

sqlplus system/sysadmin

or one of these

sqlplus / as sysdba
sqlplus system/sysadmin as sysdba
0
 

Author Comment

by:Jagdishl
ID: 24100448
Thanks
I set the oracle SID as you had pointed out and tried the command in sqlplus window.I got this error below.

C:\Documents and Settings\Administrator>sqlplus system/sysadmin as sysdba
Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ]
where <option> ::= -H | -V | [ [-L] [-M <o>] [-R <n>] [-S] ]
      <logon>  ::= <username>[/<password>][@<connect_string>] | / | /NOLOG
      <start>  ::= @<URI>|<filename>[.<ext>] [<parameter> ...]
        "-H" displays the SQL*Plus version banner and usage syntax
        "-V" displays the SQL*Plus version banner
        "-L" attempts log on just once
        "-M <o>" uses HTML markup options <o>
        "-R <n>" uses restricted mode <n>
        "-S" uses silent mode

C:\Documents and Settings\Administrator>sqlplus / as sysdba
Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ]
where <option> ::= -H | -V | [ [-L] [-M <o>] [-R <n>] [-S] ]
      <logon>  ::= <username>[/<password>][@<connect_string>] | / | /NOLOG
      <start>  ::= @<URI>|<filename>[.<ext>] [<parameter> ...]
        "-H" displays the SQL*Plus version banner and usage syntax
        "-V" displays the SQL*Plus version banner
        "-L" attempts log on just once
        "-M <o>" uses HTML markup options <o>
        "-R <n>" uses restricted mode <n>
        "-S" uses silent mode
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24100487
Try enclosing in quotes. I don't have a 9i in front of me, I think the "as sysdba" syntax required quotes.

sqlplus "/ as sysdba"
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Jagdishl
ID: 24100578
I typed the right format from one of the forums and I got TNS protocol adapter error.(below)

C:\Documents and Settings\Administrator>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Apr 8 12:54:45 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:

0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24100679
Odd, do you have TWO_TASK set?

echo %TWO_TASK%

Oracle should not use TNS listener when connecting local without an @ALIAS unless you have some other configuration.

0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24100698
Also check if you have LOCAL set, that would also make the oracle client attempt a TNS listener connection instead of a direct.

echo %LOCAL%
0
 

Author Comment

by:Jagdishl
ID: 24100781
I am afraid that I didnt quite understand what the last 2 comments meant?
.Do you want me to type those commands in some script window?Also I am running this from the command prompt window of windows 2000.
0
 

Author Comment

by:Jagdishl
ID: 24101228
Ok
This is what I got when I typed the echo commands.Hope this helps

C:\Documents and Settings\Administrator>echo %TWO_TASK%
%TWO_TASK%

C:\Documents and Settings\Administrator>echo %LOCAL%
%LOCAL%

C:\Documents and Settings\Administrator>

0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24112757
jagdishl: Thats ok, that means the vars are not set, which is good.

So can you try this:

set ORACLE_SID=JLS
sqlplus /nolog
connect / as sysdba
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24112780
Backup your old listener.ora, please try this LISTENER.ORA, make sure to restart the listener after copying.

lsnrctl stop
lsnrctl start


# listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = C:\oracle\ora92)

      (PROGRAM = extproc)

    )

    (SID_DESC = (SID_NAME = JLN))

  )
 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = JACK)(PORT = 1527))

    )

  )

Open in new window

0
 

Accepted Solution

by:
ee_auto earned 0 total points
ID: 24333945
Question PAQ'd, 50 points not refunded, and stored in the solution database.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now