Home » RDBMS Server » Server Administration » TNSping ok..can't connect with sqlPlus ...just stopped working (Oracle 10g)
icon2.gif  TNSping ok..can't connect with sqlPlus ...just stopped working [message #334662] Thu, 17 July 2008 10:49 Go to next message
nohacks
Messages: 10
Registered: July 2008
Junior Member
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
Re: TNSping ok..can't connect with sqlPlus ...just stopped working [message #334663 is a reply to message #334662] Thu, 17 July 2008 10:53 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

What changed?

What is timestamp on sqlnet.ora file?

I suggest doing the following:

#SQLNET.AUTHENTICATION_SERVICES= (NTS)
or alternatively remove the line entirely from sqlnet.ora

Re: TNSping ok..can't connect with sqlPlus ...just stopped working [message #334664 is a reply to message #334662] Thu, 17 July 2008 10:55 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator

You are pinging for tnsentry elvis , which seems to be working.
But, I do not see that in tnsnames.ora you have posted.
What am i missing?

comment the contents of sqlnet.ora and try again.
Re: TNSping ok..can't connect with sqlPlus ...just stopped working [message #334668 is a reply to message #334663] Thu, 17 July 2008 11:04 Go to previous messageGo to next message
nohacks
Messages: 10
Registered: July 2008
Junior Member
Thanks for the reply...

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

Re: TNSping ok..can't connect with sqlPlus ...just stopped working [message #334673 is a reply to message #334668] Thu, 17 July 2008 11:08 Go to previous message
nohacks
Messages: 10
Registered: July 2008
Junior Member
sorry I posted from wrong tnsnames.ora file...


==============================
elvis = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=7.7.1.1)(PORT=1521))(CONNECT_DATA=(SID=orcl)))

============================
This is from my local tnsNames file..

Thanks
Phil


Previous Topic: Merge database& issues
Next Topic: SQL TRACE file generated by imp
Goto Forum:
  


Current Time: Sat Dec 03 10:05:31 CST 2016

Total time taken to generate the page: 0.04596 seconds