OraFAQ Forum: Server Administration » Database connectivity check

Home » RDBMS Server » Server Administration » Database connectivity check (Oracle 11gr2,Linux 6.4)

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

Database connectivity check [message #600789]

Tue, 12 November 2013 10:04

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

Report message to a moderator

Re: Database connectivity check [message #600790 is a reply to message #600789]

Tue, 12 November 2013 10:15

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.

Report message to a moderator

Re: Database connectivity check [message #600872 is a reply to message #600789]

Wed, 13 November 2013 06:44

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 )

Report message to a moderator

Re: Database connectivity check [message #600905 is a reply to message #600789]

Wed, 13 November 2013 12:33

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.

Report message to a moderator

Re: Database connectivity check [message #600906 is a reply to message #600790]

Wed, 13 November 2013 12:48

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).

Report message to a moderator

Re: Database connectivity check [message #600920 is a reply to message #600906]

Wed, 13 November 2013 20:46

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

Report message to a moderator

Re: Database connectivity check [message #600921 is a reply to message #600920]

Wed, 13 November 2013 20:55

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.

Report message to a moderator

Re: Database connectivity check [message #600922 is a reply to message #600921]

Wed, 13 November 2013 22:00

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

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.

Report message to a moderator

Re: Database connectivity check [message #600926 is a reply to message #600923]

Thu, 14 November 2013 00:32

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.

Report message to a moderator

Re: Database connectivity check [message #600929 is a reply to message #600926]

Thu, 14 November 2013 02:14

ThomasG
Messages: 3212
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.

Report message to a moderator

Re: Database connectivity check [message #601028 is a reply to message #600929]

Thu, 14 November 2013 21:14

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 ?

Report message to a moderator

Re: Database connectivity check [message #601062 is a reply to message #601028]

Fri, 15 November 2013 02:46

cookiemonster
Messages: 13959
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.

Report message to a moderator

Re: Database connectivity check [message #601064 is a reply to message #600920]

Fri, 15 November 2013 03:26

John Watson
Messages: 8960
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.

Report message to a moderator

Re: Database connectivity check [message #601067 is a reply to message #601064]

Fri, 15 November 2013 03:45

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

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

Senior Member

I'm just restating what Thomas said.

Report message to a moderator

Re: Database connectivity check [message #601084 is a reply to message #601067]

Fri, 15 November 2013 07:21

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)

Report message to a moderator

Re: Database connectivity check [message #601093 is a reply to message #601084]

Fri, 15 November 2013 09:26

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

Thanks for the links EdStevens Smile

Report message to a moderator

Re: Database connectivity check [message #601719 is a reply to message #601067]

Sun, 24 November 2013 19:55

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 .....

Report message to a moderator

Re: Database connectivity check [message #605601 is a reply to message #601719]

Sat, 11 January 2014 15:55

Littlefoot
Messages: 21823
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).

Report message to a moderator

Previous Topic:	Table data changed
Next Topic:	problem in db startup

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 28 21:55:27 CST 2024