OraFAQ Forum: Client Tools » SQLDeveloper working, SQLPlus does not work

Home » Other » Client Tools » SQLDeveloper working, SQLPlus does not work (Oracle 11.2.0.4 )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQLDeveloper working, SQLPlus does not work [message #615838]

Mon, 09 June 2014 18:03

Andy804
Messages: 7
Registered: June 2014

Junior Member

I am able to tnsping to my remote databases, I am able to SQLDeveloper connect to the remote databases without any problem. I am running Windows 7.

When I try SQLPlus command line, I record my user id and password and get a ORA-12560 TNS: Protocol Adapter error.

TNSNAMES.ORA file seems to work fine for TNSping and SQLDeveloper (below is an example remote database that I connect to).
QA.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = TLG0004.MYKFT.net)(PORT = 1521))
)
(CONNECT_DATA =
(SID = q153i0)
)
)

SQLNet.ORA file

NAMES.DEFAULT_DOMAIN = WORLD
NAMES.DIRECTORY_PATH= (tnsnames, onames, host)

Path includes C:\oracle\product\11.2.0.4\client_1\bin
TNS_ADMIN=C:\oracle\product\11.2.0.4\client_1\network\admin

Any suggestions on what I should look at next? I saw people mention listener, but the Oracle database is not running on my computer, it is a remote server that I connect to.

Thanks in advance

Report message to a moderator

Re: SQLDeveloper working, SQLPlus does not work [message #615840 is a reply to message #615838]

Mon, 09 June 2014 18:13

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum!

I believe that default behavior for SQL Developer is to use JDBC Thin to connect to the database.
You can quickly & easily test this out by temporarily renaming tnsnames.ora file to a different name.
If SQL Developer can still successfully connect to the database, this proves it is NOT using tnsnames.ora to connect to DB.
ORA-12560 is a nasty catchall error on Windows.

Is OS of DB Server Windows or *NIX?

http://stackoverflow.com/questions/18305788/sqlnet-ora-sqlnet-authentication-services-nts

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

Report message to a moderator

Re: SQLDeveloper working, SQLPlus does not work [message #615842 is a reply to message #615838]

Mon, 09 June 2014 18:28

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Show us your complete attempt at tnsping. Show us your complete attempt at sqlplus. Complete means full command and full response

Report message to a moderator

Re: SQLDeveloper working, SQLPlus does not work [message #615843 is a reply to message #615840]

Mon, 09 June 2014 18:31

Andy804
Messages: 7
Registered: June 2014

Junior Member

Database Server is on Linux. I cannot cut/paste from SQLPlus command line for some reason, but the error message is the nasty cathchall error.

Are you indicating I need to add AUTHENTICATION_SERVICES= (NTS) in my SQLNet.ORA file?

Report message to a moderator

Re: SQLDeveloper working, SQLPlus does not work [message #615844 is a reply to message #615843]

Mon, 09 June 2014 18:37

Andy804
Messages: 7
Registered: June 2014

Junior Member

I have a word document with the screen shots from my tnsping and from SQLPlus, but I do not have the ability to paste the document contents into this message. I use Windows snipping tool to capture the image (probably in HTML).

Report message to a moderator

Re: SQLDeveloper working, SQLPlus does not work [message #615845 is a reply to message #615844]

Mon, 09 June 2014 18:55

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

Windows allows you to copy and paste text from the command prompt, no need for images.
Just right click in the command window to see the options.

Report message to a moderator

Re: SQLDeveloper working, SQLPlus does not work [message #615864 is a reply to message #615845]

Tue, 10 June 2014 06:48

Andy804
Messages: 7
Registered: June 2014

Junior Member

I see the Copy option from my CMD window, but it is greyed out, so I cannot copy/paste. I will type the results of my tnsping below.

C:\Users\KCAHG00>tnsping QA.World

TNS Ping Utility for 32 Bit Windows: 11.2.0.4.0 - Production on 10-JUN-2014 03:39:28

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

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

Used TNSNAMES adapter to resolve the alias
Attempting to connect (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST=TLG0004.MYKFT.NET) (PORT=1521))) (CONNECT_DATA = (SID=q153i0)))
OK (60msec)

Report message to a moderator

Re: SQLDeveloper working, SQLPlus does not work [message #615865 is a reply to message #615842]

Tue, 10 June 2014 06:54

Andy804
Messages: 7
Registered: June 2014

Junior Member

TNSping was posted in the previous post.

I will type in my SQLPlus attempt below.

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 10 06:47:09 2014

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

Enter user-name: KCAHG00
Enter password:
ERROR:
ORA-12560 TNS:protocol adapter error

Enter user-name:

Report message to a moderator

Re: SQLDeveloper working, SQLPlus does not work [message #615866 is a reply to message #615865]

Tue, 10 June 2014 07:12

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

To copy in cmd:
Right click
select mark
highlight the text you want to copy by holding down the left mouse button
Hit enter

That copies the text you want to the buffer, you can then paste it wherever.

Report message to a moderator

Re: SQLDeveloper working, SQLPlus does not work [message #615867 is a reply to message #615866]

Tue, 10 June 2014 07:17

Andy804
Messages: 7
Registered: June 2014

Junior Member

Thanks. I learn something new everyday.

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Users\KCAHG00>tnsping QA.World

TNS Ping Utility for 32-bit Windows: Version 11.2.0.4.0 - Production on 10-JUN-2
014 07:15:16

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

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

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = TLG0004.MYKFT.net)(PORT = 1521))) (CONNECT_DATA = (SID = q153i0)))
OK (120 msec)

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 10 07:16:24 2014

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

Enter user-name: kcahg00
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error

Enter user-name:

Report message to a moderator

Re: SQLDeveloper working, SQLPlus does not work [message #615868 is a reply to message #615866]

Tue, 10 June 2014 07:17

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Andy804, I think that your mistake is in not specifying the database name.

This is what you do:

M:\>sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 10 14:15:16 2014

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

Enter user-name: trafogled
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error

This is what you should do:

M:\>sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 10 14:15:33 2014

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

Enter user-name: trafogled@ora11                      ------->>> this! Specify database name after the username
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

In your case, that would be

M:\>sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 10 14:17:45 2014

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

Enter user-name: kcahg00@qa             --> this!
Enter password:

[Updated on: Tue, 10 June 2014 07:18]

Report message to a moderator