If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Cannot connect to DB using sqlplus but can tnsping the database

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: May 2009
Location: Manila, Philippines
Posts: 16
Cannot connect to DB using sqlplus but can tnsping the database

hi guys,

Im just a starting DBA and I really need help with my problem. I have a windows server with an Oracle 10gR2 version DB. I have 10 DB in it with different and distinct SID. Before connecting to them I already set my oracle sid and oracle home. I even used the tnsping utility and it returned successful results however when I use SQLPLUS to connect to the database its returns an ORA-12560: Tns: protocol adapter error. However I've checked the tnsnames.ora and listener.ora and both file are correct. I tried connecting to other databases on the same server and they are just working fine. There are about 3-4 DB's I can't connect to. I dont know what to check anymore. Please help me! Thanks!
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,571
The bottom line is that when you are logged directly onto the DB server system at the OS level, then there is no need for SQL*Net to connect to any database.
Since you are reporting ORA-12560, you are (ab)using SQL*Net.
Code:
12560, 00000, "TNS:protocol adapter error"
// *Cause: A generic protocol adapter error occurred.
// *Action: Check addresses used for proper protocol specification. Before
// reporting this error, look at the error stack and check for lower level
// transport errors. For further details, turn on tracing and reexecute the
// operation. Turn off tracing when the operation is complete.
I suggest that you'd have have more success, from the command line & avoiding the Windoze GUI SQLPLUS interface.

Can you connect to Oracle from Command Window?
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Don't say, show. Don't promise, prove.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: May 2009
Location: Manila, Philippines
Posts: 16
I already tried connecting using the command line however it still returns the same error the got from sqlplus. Its kinda weird. Does the listener have to do something with this? I already tried restarting and it still gives the same error. I know you dont need 1 listener per DB but can I say that this is the fault of the listener?

Last edited by dadorador; 05-08-09 at 01:37.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: May 2009
Location: Manila, Philippines
Posts: 16
For example I have a database on the server with an SID of ETMLA220

on the command line:
-----------------------------------------------------------------------
c:\> tnsping ETMLA220

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

Used parameter files:
C:\oracle\product\10.2.0\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = localhost)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ETMLA220)))
OK (20 msec)
--------------------------------------------------------------------------


and I set the oracle_sid

c:\> set oracle_sid=ETMLA220

-------------------------------------------------------------------------
but when i ran the following command it returns the following results

c:\> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 8 14:01:08 2009

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

ERROR:
ORA-12560: TNSrotocol adapter error


Enter user-name:


I have verified the tnsnames.ora and it seem correct here is the entry

ETMLA220 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ETMLA220)
)
)

I dont know what to do anymore. Help anyone?
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: May 2009
Location: Manila, Philippines
Posts: 16
I aslo tried connecting like the one below and got the resulting error:

c:\> sqlplus system/manager@ETMLA220

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 8 14:18:39 2009

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Mar 2009
Posts: 14
first of all check whether you have added your windows user in ORA_DBA group on your machine
second.. stop / start listener..your second error is very generic and could be resolved just by start/stop the listener
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,571
>However I've checked the tnsnames.ora and listener.ora and both file are correct.
Post contents of both using <code tags> as documented in #1 STICKY Post at top of this forum.

>I tried connecting to other databases on the same server and they are just working fine.
Please provide 2 lists.
1) Names,alias,SID,ServiceName of database to which you can connect
2) Names,alias,SID,ServiceName of database to which you can NOT connect


Post results from following command:
SET
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Don't say, show. Don't promise, prove.

Last edited by anacedent; 05-08-09 at 12:12.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: May 2009
Location: Manila, Philippines
Posts: 16
to anadecent: I'll try to attend to your request to post what you need to see since its weekend I can't access the servers yet. Will get back at you asap. Thank you very much for helping me.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On