Solved

Ora 12541:TNS no listener error

Posted on 2009-04-08
16
1,029 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
What is the version of ojdbc6.jar 2 39
Determine Who is Runnig my Bash Shell Script 4 62
Read only access to a Procedure in oracle? 4 48
MULTIPLE DATE QUERY 15 49
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

910 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

21 Experts available now in Live!

Get 1:1 Help Now