Home » RDBMS Server » Server Administration » Database connectivity check (Oracle 11gr2,Linux 6.4)
Database connectivity check [message #600789] Tue, 12 November 2013 10:04 Go to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Hi,

What is the first and foremost thing we need to verify in database end when developers ask us to check database connectivity ?

Note
Its a rac database with 4 node cluster and there are 5 databases located in shared storage.

Thank you
Re: Database connectivity check [message #600790 is a reply to message #600789] Tue, 12 November 2013 10:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ask developer for proof a problem exists for him.
What EXACTY is he doing & what actual & specific error does he get?

he is saying similar to below

my car won't go.
Tell me how to make my car go.
Re: Database connectivity check [message #600872 is a reply to message #600789] Wed, 13 November 2013 06:44 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Jack14 wrote on Tue, 12 November 2013 10:04
Hi,

What is the first and foremost thing we need to verify in database end when developers ask us to check database connectivity ?

Note
Its a rac database with 4 node cluster and there are 5 databases located in shared storage.

Thank you


The "first and foremost thing we need to verify" for ANY reported connectivity issues is the actual, full, and complete error message that the reporting user is receiving. Any other approach is simply playing a game of "guess what's in my pocket."

I always attack connectivity issues starting from the client end, not the database end.

see: http://edstevensdba.wordpress.com/2011/02/09/sqlnet_overview/ (Help! I can't connect to my database )



Re: Database connectivity check [message #600905 is a reply to message #600789] Wed, 13 November 2013 12:33 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Jack14 wrote on Tue, 12 November 2013 21:34

What is the first and foremost thing we need to verify in database end when developers ask us to check database connectivity ?


Just connect to your DB through SQL*Plus and confirm.
Re: Database connectivity check [message #600906 is a reply to message #600790] Wed, 13 November 2013 12:48 Go to previous messageGo to next message
tim2boles
Messages: 38
Registered: August 2008
Location: Clarksburg, WV
Member
Excellent answer. I kind of think of it in terms that I learned in school for "NEWS" reporting.

What, When, Where and How?

Where are you connecting from?
What are you using to connect?
When were you last able to connect?
How do you know there is an issue? (Send me a screen shot of your message).
Re: Database connectivity check [message #600920 is a reply to message #600906] Wed, 13 November 2013 20:46 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Hi All,

First of all i would like to thank everyone for responding my thread.

Recently, following error message was found in application server logs and they asked me to verify listener from database server.

"Connection could not be allocated because: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
;_RequestID=986d6629-82a1-4cd9-9121-1abfcbbe0c8e;|RAR5117 : Failed to obtain/create connection from connection pool [ OracleManualBankingXAPool ]. Reason : Connection could not be allocated because: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor"

I did findings and do not see any tns errors in the listener logs during the issue time and listener and services were also up.

I found the below entry been repeated in these databases alert logs during the issue time.

VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.3.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
  Time: 12-NOV-2013 10:00:00
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535


TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505

TNS-00505: Operation timed out
    nt secondary err code: 110
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.118.165.145)(PORT=13218))

Note:-
This error emerged in two of databases.

Kindly advice me how to proceed further to fix this issue.

Thank you
Re: Database connectivity check [message #600921 is a reply to message #600920] Wed, 13 November 2013 20:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>ORA-12505, TNS:listener does not currently know of SID given in connect descriptor"
I can 100% guarantee a request got to some listener which dutifully logged this error.
The application logs does not reveal to which server/listener the request was sent.
The listener log file will show SID was requested that the listener knew nothing about.
Re: Database connectivity check [message #600922 is a reply to message #600921] Wed, 13 November 2013 22:00 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Blackswan,

<The listener log file will show SID was requested that the listener knew nothing about.>

I checked listener log file for any ORA errors during the issue time and have found no ORA errors in it.

[Updated on: Wed, 13 November 2013 22:00]

Report message to a moderator

Re: Database connectivity check [message #600923 is a reply to message #600922] Wed, 13 November 2013 22:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
listener log file NEVER shows any "ORA-?????" string; EVER!
the listener log file will contain the status code of 12505
any listener with status code = 0 indicates success (no error)
any non-zero status code is an error
where the status code the rightmost value in each record in listener.log file.
Re: Database connectivity check [message #600926 is a reply to message #600923] Thu, 14 November 2013 00:32 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Blackswan,
I dont see any non zero value in status code in listener log file during the issue time.
I could only see status=0 in listener log file during issue time.
Kindly suggest.

Re: Database connectivity check [message #600929 is a reply to message #600926] Thu, 14 November 2013 02:14 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The most likely cause is that the client tried to connect to a different host that also had a listener running.

That would explain both the facts that the listener had no idea about the SID it was asked about, and on the other hand that you have no failed connection status in your listener log.

Re: Database connectivity check [message #601028 is a reply to message #600929] Thu, 14 November 2013 21:14 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Thanks Thomas.

Quote:
That would explain both the facts that the listener had no idea about the SID it was asked about, and on the other hand that you have no failed connection status in your listener log.

Does this mean there is no issue with listener ?

Re: Database connectivity check [message #601062 is a reply to message #601028] Fri, 15 November 2013 02:46 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
If Thomas is correct then yes, the problem is the client is trying to connect to the wrong host.
Re: Database connectivity check [message #601064 is a reply to message #600920] Fri, 15 November 2013 03:26 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Jack14 wrote on Thu, 14 November 2013 02:46
Hi All,

First of all i would like to thank everyone for responding my thread.

Recently, following error message was found in application server logs and they asked me to verify listener from database server.

[snip]

I would say that the heart of the problem is that there is no problem. If the only symptom is a message in a log, the source of which no-one understands, I would ignore it. Has any user complained? No.
Perhaps someone has too much time and not enough work. Or is trying to avoid real work by picking up trivia.
Re: Database connectivity check [message #601067 is a reply to message #601064] Fri, 15 November 2013 03:45 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Cookiemaster,
Quote:
If Thomas is correct then yes, the problem is the client is trying to connect to the wrong host.

Can you explain me how are you saying that client is trying to connect to the wrong host ?

Thanks John..

[Updated on: Fri, 15 November 2013 03:45]

Report message to a moderator

Re: Database connectivity check [message #601069 is a reply to message #601067] Fri, 15 November 2013 03:51 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'm just restating what Thomas said.
Re: Database connectivity check [message #601084 is a reply to message #601067] Fri, 15 November 2013 07:21 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Jack14 wrote on Fri, 15 November 2013 03:45
Cookiemaster,
Quote:
If Thomas is correct then yes, the problem is the client is trying to connect to the wrong host.

Can you explain me how are you saying that client is trying to connect to the wrong host ?

Thanks John..


see: http://edstevensdba.wordpress.com/2011/02/09/sqlnet_overview/ (Help! I can't connect to my database )
see: http://edstevensdba.wordpress.com/2011/02/16/sqlnet_client_cfg/ ( Help! I can't connect to my database (part duex) )
see: http://edstevensdba.wordpress.com/2011/02/26/ora-12154tns-03505/ (ora-12154 TNS:could not resolve the connect identifier specified)
see: http://edstevensdba.wordpress.com/2011/02/27/tnsping-101/ (tnsping-101 )
see: http://edstevensdba.wordpress.com/2011/03/19/ora-12514/ (ora-12514 Listener does not know of requested service )
see: http://edstevensdba.wordpress.com/2011/03/05/ora-12545/ (ora-12545 Connect failed because target host or object does not exist)
Re: Database connectivity check [message #601093 is a reply to message #601084] Fri, 15 November 2013 09:26 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Thanks for the links EdStevens Smile
Re: Database connectivity check [message #601719 is a reply to message #601067] Sun, 24 November 2013 19:55 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Jack14 wrote on Fri, 15 November 2013 03:45
Cookiemaster,
Quote:
If Thomas is correct then yes, the problem is the client is trying to connect to the wrong host.

Can you explain me how are you saying that client is trying to connect to the wrong host ?

Thanks John..


The reported message is the equivelent of placeing a phone call and asking the operator to connect you to "Fred", and the operator says "no one named Fred works here".

That could mean the client called the correct number (host) but mis-specified who he wanted to speak to.
Or it could mean that the client called the wrong number (host) in the first place.

Suppose you have two database servers, serverA and serverB. Both will have listeners, but each will obviously be serving different databsases. If your client specifies in his connect string to connect to host=serverA, but specifies a service name of a database on serverB .....
Re: Database connectivity check [message #605601 is a reply to message #601719] Sat, 11 January 2014 15:55 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I have removed plagiarism-related messages on www.dbametrix.net from this topic and put them into a separate topic (Plagiarism on www.dbametrix.net #2) which now resides in our Suggestion & Feedback forum. Frank Naude will read it, and take (or not) steps according to your findings. Please, don't continue plagiarism discussion here any more (but feel free to do so in a newly created topic).
Previous Topic: Table data changed
Next Topic: problem in db startup
Goto Forum:
  


Current Time: Thu Mar 28 07:28:45 CDT 2024