1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Same sql work differently in different sql plus

Discussion in 'SQL PL/SQL' started by jayakumar.u05, Dec 18, 2015.

  1. jayakumar.u05

    jayakumar.u05 Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Columbus
    I have a SQL against Oracle 11 g Database and I am trying to execute the same SQL from SQL clients having same versions from different machines. In some of them SQL execute successfully and in some other clients it throws end of communication channel error. There is no connectivity issue between client and server through
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    "There is no connectivity issue between client and server through"

    You have yet to prove that so you cannot simply assume it is true. Yes, there could be other causes for that error but until you investigate the versions throwing that error all possibilities are to be considered.

    Check the alert log on each of the 'failing' databases and see what Oracle reports.
     
  3. jayakumar.u05

    jayakumar.u05 Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Columbus
    I have 4 or 5 identical application servers all with same sql plus client versions. Same SQL get succeeded from 3 of them and fails from 2. So DBAs are not willing to accept it as Oracle issue . Same SQL succeeed from their side as well

    I have tried unlimited ping from the client machine which throws error to the DB server and I am getting replies back
     
  4. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    That you have multiple clients means you can have multiple problems. Like a bad TNS file, a bad SQLNet.ora file or a bad client load. Have you tried using SQL Developer or some other tool for the connection? There's also the O/S, its patchsets, Group Policy differences, security settings, the firewall(s), switches, routers, DNS tables, bad cables, bad cards....literally, the list is almost endless.

    CJ
     
  5. jayakumar.u05

    jayakumar.u05 Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Columbus
    You are right. atleast I compared TNS and SQLNet files and they are identical between the machines. Current problem is since I dont have a credential to run it using other tools, I am running directly from the application(Cognos) and it uses the sql client in the Cognos server and execute against the DB.
    Even if it works with other tools (sqldeveloper for eg: finally the Cognos application cant use those tools to run the SQL). Having said that we need to make sure it is running from SQL plus.
     
  6. jayakumar.u05

    jayakumar.u05 Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Columbus
    I see another difference , where most of the TNS entries connect via 1521, this one connects through 1500. Will it make any difference? But 1500 is common for this DB in all servers
     
  7. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Earlier you indicated the SQL works from three clients and fails from two. If all five clients are using port 1500, this is not a difference that could be causing the failure. If the two that are failing use 1500, and the other three do not, then certainly the port might be a factor.

    Will *any* SQL run from the two failing clients? If a simple query from a small table fails, then there is a severe communication problem from the client to the server. However, if some queries work and a specific query does not, then you are looking at a problem that is connected with some aspect of the failing query (run length, volume of data returned, etc.)
     
  8. jayakumar.u05

    jayakumar.u05 Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Columbus
    I have confirmed that they are all connecting via same port i.e - 1521. Small Queries come back successfully against the server from failing clients. However a specific query fails from these clients. Everywhere else this query is successful (From our individual machines, other application servers etc)
     
  9. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Well based on what you ave said -- I agree with your DBAs that it isn't an issue with the Oracle server. Since some queries are working from the clients, the basic client-server communication is in place. Most errors in this will simply mean that the clients cannot connect at all rather than that they connect but fail randomly... or in this case fail repeatedly on the same query.

    It may be that something is different in the Cognos configuration on those clients. I've used Cognos, but never done any installation/configuration on it (and so can't offer specific suggestions). Alternately, there may be something with the client machines themselves (less memory, slower processor, different network environment/performance) which causes the query to complete slower and therefore time out even though the Cognos installation matches the other working clients). Generally the only reliable way to diagnose is to locate and eliminate the differences until you find a change that makes these clients work. Alternately you can try to make one of the 'working' clients fail so that you know what to fix on the others.
     
  10. jayakumar.u05

    jayakumar.u05 Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Columbus
    Even though this is happening from Cognos, I have eliminated the idea of having issues with Cognos because now I am trying to run the SQL from SQL Plus installed in the Cognos server. So Issues with Cognos is eliminated.

    I have tried to copy the same TNS entries from the right client machines, kept only one TNS entry in the clients which has issues and none of them helped. At any point I was not able to reproduce the issue in either of the machines which is working currently.Strange thing is I am getting 'end of communication channel' error after exact time limits in each of the tries. Like you said it could be configuration differences. I am looping network team to see how far they can help.
     
  11. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Earlier in the thread you had indicated you were unable to use tools other than Cognos. Having SQL*Plus as an option gives more flexibility in addition to removing Cognos as a factor.

    You indicate "...I am getting 'end of communication channel' error after exact time limits...". First of all, I assume you are getting an ORA-3113 error. Confirming that is the error doesn't help much because 3113 is a catch-all error that just means the client lost contact with the server, but it would be nice to be certain what is being diagnosed. Second, I gather from your statement that the query is always failing after running for a set amount of time (i.e. it always dies 5 minutes and 32 seconds after starting execution). If so, it is almost certain you are looking at a defined time limit for execution. One thing I am curious about is whether the 'working' clients run longer than that. For example, if the 'failing' machines hit the ORA-3113 after five minutes, but the 'working' machines complete the query in 4 minutes and 45 seconds... they may simply not be hitting a globally defined time limit. By contrast, if the 'working' machines complete the query after six minutes, then they are not subject to the same time restriction as the clients that are failing.
     
  12. jayakumar.u05

    jayakumar.u05 Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Columbus
    Yes now I am trying directly from SQL Plus than Cognos.

    I am getting is ORA 3113 error. As per your suggestion I have tried to execute the queries from SQL plus clients from both machines.

    From the working machines I am getting results back in around 30 seconds (Tried multiple times)
    From the non-working machine I am getting error in around 20 seconds (tried multiple times).

    But how can we define time outs in client machines. At the same time there are other long running SQLs which connects to other Database servers are running fine from all of these machines.
     
  13. jayakumar.u05

    jayakumar.u05 Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Columbus
    Finally I have a resolution to this issue last week. I was working with network team and they have installed some sort of monitoring tool(Opnet Client in client machine and DB servers. They figured out that the traffic that was coming from those servers was being matched to a buffer overflow definition by the IPS’s . They have removed this and added the IPs for exception.