Start Free TrialLog in
Avatar of nohacks
nohacksFlag for United States of America

asked on 

Oracle - Can TNSping ok..can't connect with sqlPlus ...just stopped working

Hey Guys,

I have an Oracle Server running for the last year with little issues.

The lister just stopped working...

I can TNSPing ok...I just can't connect with toad or sqlplus anymore from another computer..

I can connect with sqlplus on the same computer...just not from any other computers.


=================================================================

C:\Documents and Settings\Phil>tnsping elvis 5

TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 17-JUL-2008 11:10:28

Copyright (c) 1997, 2003, Oracle.  All rights reserved.

Used parameter files:
C:\Oracle\product\10.1.0\Client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=7.7.1.1)(PORT=1521))(CONNECT_DATA=(SID=orcl)))
OK (70 msec)
OK (40 msec)
OK (50 msec)
OK (50 msec)
OK (40 msec)

C:\Documents and Settings\Phil>

==================================================

Listener status info

================================================

LSNRCTL>
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.1.0.4.0 - Production
Start Date                17-JUL-2008 10:45:26
Uptime                    0 days 0 hr. 29 min. 2 sec
Trace Level               off
Security                  ON: Password or Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\oracle\product\10.1.0\Db_1\network\admin\listener.ora
Listener Log File         D:\oracle\product\10.1.0\Db_1\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=elvis)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=elvis)(PORT=8080))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=elvis)(PORT=2100))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>
LSNRCTL>
LSNRCTL>
LSNRCTL>

==================================================


Listener.ora

# listener.ora Network Configuration File: D:\oracle\product\10.1.0\Db_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\product\10.1.0\Db_1)
      (PROGRAM = extproc)
    )
  )



LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = elvis)(PORT = 1521))
    )
  )

====================================================================

sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

====================================================================

tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = elvis)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )


=============================================================================

I am getting this error from  sqlnet.log

***********************************************************************
Fatal NI connect error 12638, connecting to:
 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

  VERSION INFORMATION:
      TNS for 32-bit Windows: Version 10.1.0.4.0 - Production
      Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 10.1.0.4.0 - Production
  Time: 17-JUL-2008 10:23:14
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12638
    TNS-12638: Credential retrieval failed
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0


Oracle Database
Avatar of undefined
Last Comment
nohacks
SOLUTION
Avatar of David VanZandt
David VanZandt
Flag of United States of America image
Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of nohacks
nohacks
Flag of United States of America image

ASKER

The host sever only has one copy of the tnsnames.ora and sqlnet.ora.

Oracle is the only thing on this box. I installed once and just use it as a sever.

============================

I updated my sqlnet.ora

# SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

===============================================

Now I get this error...


SQL*Plus: Release 10.1.0.2.0 - Production on Thu Jul 17 12:00:15 2008

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Enter password:
ERROR:
ORA-12505: TNS:listener does not currently know of SID given in connect
descriptor

======================================================

What did that sqlnet.auth do? Do I need that for security??


Phil
Avatar of johnsone
johnsone
Flag of United States of America image
What is the connect string you are using?

From the error in your original message (protocol is beq), that would tell me you are not using SQL Net and none of the files are coming into play at all.
Avatar of David VanZandt
David VanZandt
Flag of United States of America image
You are setting a value for ORACLE_SID, right?
Avatar of nohacks
nohacks
Flag of United States of America image

ASKER

What is the connect string you are using?...

This happens from sqlplus, toad and a JDBC call...

=========================================

You are setting a value for ORACLE_SID, right?

how do you set the value of the sid??

C:\Documents and Settings\Phil>
C:\Documents and Settings\Phil>sqlplus testUserLogin@elvis


Phil




Avatar of nohacks
nohacks
Flag of United States of America image

ASKER

I just recreated my listener.ora with Net Config Assistant.

This it the output...still does not work...

===========================================

# listener.ora Network Configuration File: D:\oracle\product\10.1.0\Db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

#--------------------------------------------

#----ADDED BY TNSLSNR 16-JUL-2008 22:28:07---

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\product\10.1.0\Db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = elvis)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )
Avatar of johnsone
johnsone
Flag of United States of America image
I don't believe that changing anything in the listener.ora, sqlnet.ora or tnsnames.ora will help you.  Based on your original post, you are not using any of those files to resolve the connection.  That is why I asked for the connect string.

The bequeath protocol and the local setting to yes, would imply that you are trying to connect to a database that is located on the machine you are connected to and there is no database specified in the connect string (i.e. connecting with user/pass not user/pass@db).  This type of connection relies on the environment (specifically ORACLE_SID) to determine how to connect to the database.
Avatar of David VanZandt
David VanZandt
Flag of United States of America image
So to answer your question, if ORCL is your database SID, then
> set ORACLE_SID=ORCL

If you need to see your environment settings, just type "set" at the command prompt.
Avatar of Shaju Kumbalath
Shaju Kumbalath
Flag of India image
try this

Modify ur listner.ora to


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\product\10.1.0\Db_1)
      (PROGRAM = extproc)
    )
  )
(SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME =  D:\oracle\product\10.1.0\Db_1)
      (SID_NAME = orcl)

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = elvis)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )
Avatar of schwertner
schwertner
Flag of Antarctica image
There is an error in the above script.

see

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\Ora10g)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = test)
      (ORACLE_HOME = D:\Ora10g)
    )
  )

Add the entry
    (SID_DESC =
      (SID_NAME = test)
      (ORACLE_HOME = D:\Ora10g)
    )
with regard to the particular Oracle Home and SID Name.

In your case this will be

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\product\10.1.0\Db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
       (GLOBAL_DBNAME = orcl)
       (ORACLE_HOME =  D:\oracle\product\10.1.0\Db_1)
       (SID_NAME = orcl)
    )
  )



LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = elvis)(PORT = 1521))
    )
  )

Restart the listener:
c:>lsnrctl stop
c:>lsnctl start

Check for fiorewalls and turn them OFF!
ASKER CERTIFIED SOLUTION
Avatar of vishal68
vishal68
Flag of India image
Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
SOLUTION
Avatar of schwertner
schwertner
Flag of Antarctica image
Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of nohacks
nohacks
Flag of United States of America image

ASKER

Vishal68...
I was incorrect...I did add remote user on this box then rebooted it. That is all...should not have effect Oracle...Remote user is and XP Professional Util..I did copy and erase the log file. Same issue...the log file was not that big anyway..
Thanks for your reply...I appreciate all ideals..

Schwertner...
Yes, this is 10g.
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.1.0.4.0 - Production

What do you mean first letter in the instance name prevents connections?? How do I fix that??

Phil
Avatar of schwertner
schwertner
Flag of Antarctica image
This is a terible bug of 10g R1. Some letters used as first on the SID name prevent the usage
of Oracle.
I recall it was 'S'.... Please try to migrate to 10g R2
Avatar of nohacks
nohacks
Flag of United States of America image

ASKER

Thanks guys,

It was a combinations of things...I upgraded oracle to R2 and change firewall security settings.

Works great again

Thanks
Phil

Oracle Database
Oracle Database

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts

  • "Invaluable tool for our organization"

    Josh Regalski

  • "Your help has saved me hundreds of hours of internet surfing."

    @fblack61

  • "Just a dang good service!"

    @golferski

  • "Worth every penny."

    Steve Hougom

  • "It’s been a life saver."

    Maria Halt

  • "Best support site I’ve seen!"

    Bob Schneider

  • "I would be lost without them."

    @fblack61

  • "Saved my job multiple times."

    Walt Forbes

  • "I love this site."

    Maria Duwe

  • "Friendly respectful help."

    Andrew Kowtalo

  • "Such top-notch experts."

    @magento

  • "The best money I have ever spent."

    @rwheeler23

  • "Beyond any comprehensible value"

    George Morris

  • "Invaluable tool for our organization"

    Josh Regalski

Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo
© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent