Home » RDBMS Server » Server Administration » Clients can't connect to database - ORA-12560 (Windows server 2003, oracle 10.2.1)
Clients can't connect to database - ORA-12560 [message #533187] Mon, 28 November 2011 12:36 Go to next message
markers
Messages: 13
Registered: November 2011
Location: Norway
Junior Member
Hi!

Since this is my first post on this forum, and probably is posting in a very wrong section, I will be brief. Like that will ever happen...

Bare in mind that I am a total moron when it comes to Oracle database...

Since 13.00 hours, my clients can't connect to the database, and hence cannot access their application.

They get a ORA-12560 error. protocol adapter.

What I have discovered and done so far.

1. I can tnsping dbname -> OK.

2. I have rebooted both application server and databaseserver multiple times

3. I haven't touched tnsnames.ora, sqlnet.ora and listener.ora and they look to be in perfect mint condition.

4. I have full access to access the database instance via web.

5. I can connect to the database instance via sql

6. I have restarted the oracle-services: oracleDBConsoledbname, OracleOraDb20g_home1TNSListener and OracleServicedbname

7. I have rebooted the client computers, and the file server.

8. I don't see any errors in event logs, both on file server, client computer and database-server.

9. When I press down the link Listener, I get Metric Collection Error, which means I might not access it?

10. The only alert I find is that filesystem D:\ only has 15.62% available space.

Problem is that I don't have a clue what to do... They have been using the database for a very long time without any trouble, and suddenly boom.

What do I do? How do I check things? Does anyone have any good advice, written in capital letters?

This is slightly urgent and critical. Hopefully I will have to have this up and running by morning, when everybody is coming back to work.

Thanks in advance for your patience reading and responding to this post.

Markers


[Updated on: Mon, 28 November 2011 12:38]

Report message to a moderator

Re: Clients can't connect to database - ORA-12560 [message #533193 is a reply to message #533187] Mon, 28 November 2011 12:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-12560: 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.

Re: Clients can't connect to database - ORA-12560 [message #533194 is a reply to message #533187] Mon, 28 November 2011 12:48 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Can you ping (not just tnsping) the database server from client's computer?
If so,
Repeat steps 1 and 5 from the client computer.
Step 1 checks for the availability of listener.
Step 2 checks for the availability of database.

If both fails, look into tnsnames.ora file in clients computer.

[Updated on: Mon, 28 November 2011 12:49]

Report message to a moderator

Re: Clients can't connect to database - ORA-12560 [message #533197 is a reply to message #533194] Mon, 28 November 2011 12:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
what changed since last time all was OK?

what clues exist near tail end of listener.log file?
what clues exist near tail end of alert_SID.log file?

on DB Server system issue following OS commands

lsnrctl status
lsnrctl service

COPY commands & results then PASTE all back here
Re: Clients can't connect to database - ORA-12560 [message #533201 is a reply to message #533197] Mon, 28 November 2011 13:38 Go to previous messageGo to next message
markers
Messages: 13
Registered: November 2011
Location: Norway
Junior Member
1. I can ping the database server from clients computer

2. I can tnsping the database instance(correct name?) from the client computer

3. when I do this sqlplus dbuser61@servername I get: password please. Then ORA-12560: TNS:protocol adapter error
When I check tnsnames.ora on server db I see the correct one. Can't find any on client computer. Maybe on file server?

4. I don't know what has changed. As a rule I never touch anything with Oracle, since my skills aren't really there. And the two in my office with access was both out of the office at the time.

status says something like this: I have changed the databasename..

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbname1.domainname.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 28-NOV-2011 19:58:50
Uptime 0 days 0 hr. 31 min. 21 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Listener Log File D:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbname1.domainame.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dbname1" has 1 instance(s).
Instance "dbname1", status READY, has 1 handler(s) for this service...
Service "dbname1XDB" has 1 instance(s).
Instance "dbname1", status READY, has 1 handler(s) for this service...
Service "dbname1_XPT" has 1 instance(s).
Instance "dbname1", status READY, has 1 handler(s) for this service...
The command completed successfully

Status running: I have also changed some names.


LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 28-NOV-2011 20:33:27

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=63asdbnamedb.domainname.com)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "dbname" has 1 instance(s).
Instance "dbname", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:146 refused:0 state:ready
LOCAL SERVER
Service "dbnameXDB" has 1 instance(s).
Instance "dbname", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER <machine: 63ASdbnameDB, pid: 2796>
(ADDRESS=(PROTOCOL=tcp)(HOST=63asdbnamedb.orkidenett.no)(PORT=3864))
Service "dbname_XPT" has 1 instance(s).
Instance "dbname", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:146 refused:0 state:ready
LOCAL SERVER
The command completed successfully

Sorry if I haven't been totally honest with you, and changed some names and so on...





[Updated on: Mon, 28 November 2011 13:39]

Report message to a moderator

Re: Clients can't connect to database - ORA-12560 [message #533202 is a reply to message #533201] Mon, 28 November 2011 13:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
look at the error stack and check for lower level transport errors.
For further details, turn on tracing and reexecute the operation.


Regards
Michel
Re: Clients can't connect to database - ORA-12560 [message #533203 is a reply to message #533201] Mon, 28 November 2011 13:42 Go to previous messageGo to next message
markers
Messages: 13
Registered: November 2011
Location: Norway
Junior Member
The listener.log is monster big, coming in a whopping 1,6GB
Can't open it, but transfer a copy to another server where I can hopefully view it from my computer.

[Updated on: Mon, 28 November 2011 13:43]

Report message to a moderator

Re: Clients can't connect to database - ORA-12560 [message #533204 is a reply to message #533202] Mon, 28 November 2011 13:45 Go to previous messageGo to next message
markers
Messages: 13
Registered: November 2011
Location: Norway
Junior Member
I have seen it posted, but it looks for me like you are speaking a whole other language I don't understand...

Sorry, don't know what you mean, and don't know how to respond to it...
Re: Clients can't connect to database - ORA-12560 [message #533205 is a reply to message #533204] Mon, 28 November 2011 13:49 Go to previous messageGo to next message
markers
Messages: 13
Registered: November 2011
Location: Norway
Junior Member
New information. I find a tnsnames.ora which is located on a totally different file server than I originally thought.

In that tnsnames.ora it points to the ip address to my db server
correct db instance.

in tnsnames.ora on my database server, it points to the hostname, and correct db instance

Can that be a clue? Remember that I am not very skilled in these kind of matters Smile

Don't have a clue why it should, since it's been working since the dawn of time (approx).

[Updated on: Mon, 28 November 2011 13:52]

Report message to a moderator

Re: Clients can't connect to database - ORA-12560 [message #533206 is a reply to message #533205] Mon, 28 November 2011 14:02 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
Hi, Markers. I'll try to help, but I'm in GMT+2 and won't be awake much longer. Can you describe the problem again? You say this
Quote:
my clients can't connect to the database, and hence cannot access their application.

They get a ORA-12560 error. protocol adapter.
but then you say this
Quote:
4. I have full access to access the database instance via web.

5. I can connect to the database instance via sql
When does the problem occur?
You also say this
Quote:
when I do this sqlplus dbuser61@servername I get: password please. Then ORA-12560: TNS:protocol adapter error
but "password please" is NOT an ORacle message.

Can you post (using COPY/PASTE, and [code] tags) what you actually see when you try to connect?
Re: Clients can't connect to database - ORA-12560 [message #533207 is a reply to message #533206] Mon, 28 November 2011 14:22 Go to previous messageGo to next message
markers
Messages: 13
Registered: November 2011
Location: Norway
Junior Member
Yes sir. I am on GMT +1 myself, so it's beginning to get late.

1. Full access on db instance via web is: on database-server via oracle management tools
2. when I do sqlplus system@servername I get: enter sqlplus release 9.2 and copyright. Beneath it is: Enter password.i enter password, and get an ORA-12154 error: TNS:could not resolve the connect identifier specified
Why can't I connect now, but not earlier? something written wrong.



Re: Clients can't connect to database - ORA-12560 [message #533208 is a reply to message #533207] Mon, 28 November 2011 14:32 Go to previous messageGo to next message
markers
Messages: 13
Registered: November 2011
Location: Norway
Junior Member
One question: In the listener.log file: What exactly am I looking for?
Re: Clients can't connect to database - ORA-12560 [message #533209 is a reply to message #533207] Mon, 28 November 2011 14:34 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
I did ask you to use copy/paste, with [code] tags, to show what actually happens. That would save time. But OK, in this case it is obvious: you do not have an appropriate entry in your tnsnames.ora file. This is nothing to do with any ora-12560 error. Look at this:
c:\users\john\home>sqlplus jon/jon@rubbish

SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 28 20:28:00 2011

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

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name: jon/jon@orcl

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

orcl> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

c:\users\john\home>tnsping orcl

TNS Ping Utility for 32-bit Windows: Version 11.2.0.3.0 - Production on 28-NOV-2011 20:28:52

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

Used parameter files:
c:\app\john\product\11.2.0\dbhome_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = jwdell.bplc.co.za)(PORT = 1521)) 
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (20 msec)

c:\users\john\home>


What the above tells me is the tnsnames.ora file in the directory c:\app\john\product\11.2.0\dbhome_1\network\admin does include an entry for the name orcl but does not include an entry for name rubbish.

I do not think this is in any way relevant to your problem. What are your users doing to get an ora-12560 error?
Re: Clients can't connect to database - ORA-12560 [message #533210 is a reply to message #533209] Mon, 28 November 2011 14:46 Go to previous messageGo to next message
markers
Messages: 13
Registered: November 2011
Location: Norway
Junior Member
The question is rather this: Why did this happen suddenly at around 1300 hours today? It has been working perfectly 24/7 since I started working here.

And how can I rectify this?

I should really have to assume that it is not tnsnames.ora file which suddenly stops using the correct data, should I?
Re: Clients can't connect to database - ORA-12560 [message #533211 is a reply to message #533210] Mon, 28 November 2011 14:48 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
I'm sorry, man, but without seeing the ora-12560, I can't make any suggestions. Good luck, and good night.
Re: Clients can't connect to database - ORA-12560 [message #533212 is a reply to message #533211] Mon, 28 November 2011 14:54 Go to previous messageGo to next message
markers
Messages: 13
Registered: November 2011
Location: Norway
Junior Member
do u want the print screen?
Re: Clients can't connect to database - ORA-12560 [message #533213 is a reply to message #533212] Mon, 28 November 2011 14:56 Go to previous messageGo to next message
markers
Messages: 13
Registered: November 2011
Location: Norway
Junior Member
I appreciate you all trying to help me. thanks alot!

I believe that I will be packing my shit together, and go home get some sleep. After all, it's way past bedtime at the moment.

Good night everyone.
Re: Clients can't connect to database - ORA-12560 [message #533214 is a reply to message #533213] Mon, 28 November 2011 15:03 Go to previous messageGo to next message
markers
Messages: 13
Registered: November 2011
Location: Norway
Junior Member
1. when I do this on the client computer: sqlplus system/peralt@serverip:1521/dbinstance I get an syntax error, and asked to input username / password. When I do that, I get ORA-12560
2. when I do this on the db server: sqlplus system/peralt@serverip:1521/dbinstance, I get the following:

ORA12154 TNS. beneath it is: Enter username: I enter the same username. Get enter password: enter password.
Get: Connected to Oracle Database 10G and a sweet SQL> sign beneath that again.

Please tell my what to do. I am lost in space on this one.

Where do I start looking?
Re: Clients can't connect to database - ORA-12560 [message #533215 is a reply to message #533214] Mon, 28 November 2011 15:37 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
markers wrote on Mon, 28 November 2011 16:03
1. when I do this on the client computer: sqlplus system/peralt@serverip:1521/dbinstance I get an syntax error, and asked to input username / password. When I do that, I get ORA-12560
2. when I do this on the db server: sqlplus system/peralt@serverip:1521/dbinstance, I get the following:

ORA12154 TNS. beneath it is: Enter username: I enter the same username. Get enter password: enter password.
Get: Connected to Oracle Database 10G and a sweet SQL> sign beneath that again.

Please tell my what to do. I am lost in space on this one.

Where do I start looking?


A few things (I'm a little lost with what you wrote in this message particularly. It's a big mess).

Why do you insist on NOT cutting and pasting your complete session? If you do not, I cannot trust anything you say or write (like you previously said that you were not completely honest with us).

For example, if you get a syntax error, you won't get a prompt for username and password. You say syntax error, but why don't you show us what really happened with what you typed, like John so perfectly did?

Note also that you should be using a username, password and connect string, not the syntax you are using.

Why don't you also cut and paste your tnsnames.ora file from your client and listener.ora from your server?

Also, you said you changed nothing, but it sounds like someone else did.

Re: Clients can't connect to database - ORA-12560 [message #533225 is a reply to message #533214] Mon, 28 November 2011 23:28 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
Good morning. Why do you refuse to show the errors? I've asked twice already to use copy/paste, and format it with [code] tags. This is my last attempt to help.
Quote:
1. when I do this on the client computer: sqlplus system/peralt@serverip:1521/dbinstance I get an syntax error, and asked to input username / password. When I do that, I get ORA-12560
This is because you are using 9i client (I did manage to get that from your earlier message, even though you didn;t copy/paste) and this is not valid syntax for a 9i client. It will never work, unless you install the 10g or 11g client.

Unless you show the ora-12560 error, I don't think anyone can help.



Re: Clients can't connect to database - ORA-12560 [message #533261 is a reply to message #533225] Tue, 29 November 2011 02:06 Go to previous messageGo to next message
markers
Messages: 13
Registered: November 2011
Location: Norway
Junior Member
I'll tell you what. The reason I don't copy and paste is that I am afraid that I will reveal too much on the Internet. It's almost like this is my job, and company secrets / network stuff / passwords / databasenames shouldn't be put out on the Internet.

Second of all, I am not proficient with Oracle-db and am not sure how much I will reveal if I copy and paste. Will I reveal to much? I rather not. Thanks for trying at least.

As of today, I have called in the experts.

I am really sorry I have wasted your time. Thanks for trying to help me.

Re: Clients can't connect to database - ORA-12560 [message #533276 is a reply to message #533261] Tue, 29 November 2011 03:22 Go to previous messageGo to next message
markers
Messages: 13
Registered: November 2011
Location: Norway
Junior Member
hmmmm. seems that not only have I wasted your time, but I have wasted my own as well. On mssql, the company has had 2 errors on mssql where in the inifile, the host info has suddenly disappeared.

On oracle, they have had none. Until we showed up. So we got a gratulation on being the first in our country where the host info in the ini-file has disappeared in mysterious circumstances.

Again, sorry for wasting your time. Thank you for enlightning me. I feel like have learned something at least.
Re: Clients can't connect to database - ORA-12560 [message #533356 is a reply to message #533276] Tue, 29 November 2011 10:20 Go to previous message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
Perhaps an account lockout (after x retries) or password expired.
You might shutdown the listener, rename/backup the huge listener.log and restart the listener. With a fresh file it is easier to spot a possible error.
Previous Topic: 9i Installation
Next Topic: upgrade oracle10g to 11gr2
Goto Forum:
  


Current Time: Thu Apr 25 07:21:33 CDT 2024