Home » RDBMS Server » Server Administration » Suddenly SQL server cannot connect to Oracle Server :(
Suddenly SQL server cannot connect to Oracle Server :( [message #311380] Fri, 04 April 2008 05:36 Go to next message
NickStan
Messages: 8
Registered: April 2008
Location: London, England
Junior Member
I have a Microsoft SQL Server (Microsoft Windows Server 2003 R2, SP2, Windows NT 5.2, SQL Server V8) and an Oracle Machine (Fedcore5, Oracle 10G, SP2 I think!).

The SQL Server is used to host our reporting portal (and data) and the Linux/Oracle Server is used to connect to our US machines via links and to pass the data back to the SQL Server.

Now for some reason (after a 1.5 years of no problems) I cannot execute an oracle stored proc (from the SQL server), I cannot connect to Oracle (via TOAD), and all of my DTS packages that pull data from Oracle to SQL are failing - such a nightmare!

I can connect to the Oracle Machine from my laptop. I can run TOAD without any problems and I can run DTS packages without any problems.

When I try to connect in sqlplus I get the error messages:
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: Oracle may not be functioning properly

When I try to execute an Oracle Stored Procedure from SQL, I get the following error:
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly

I have tried the following:
--Reinstalled Oracle on the SQL server
--Reinstalled TOAD on the SQL server
--Reinstalled ODBC Oracle Driver on the SQL server
--Copied the tnsnames.ora file from my laptop to the SQL server.
--I can ping the Oracle Server from the SQL Server.

I dont know what the problem can be.
As far as I know, nothing has changed on the SQL and Oracle Machines (apparently!)
I get the feeling that something is preventing the SQL machine from accessing the Oracle Machine.

But what?

Any help much appreciated.

Thanks
Re: Suddenly SQL server cannot connect to Oracle Server :( [message #311390 is a reply to message #311380] Fri, 04 April 2008 06:19 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What happened on the other side of the link(s)? Did IP addresses change and hosts file doesn't reflect that change? Could you check "something" in that direction?
Re: Suddenly SQL server cannot connect to Oracle Server :( [message #311397 is a reply to message #311380] Fri, 04 April 2008 06:42 Go to previous messageGo to next message
NickStan
Messages: 8
Registered: April 2008
Location: London, England
Junior Member
The IP-Address of the Oracle Server has remained unchanged for the past 1.5 years too.

Firewall is 'Off'.

When I type in SQLPlus on the SQL Server I get the error:

SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly

I renamed the tnsnames.ora file to tnsnames_back.ora, error message did not change.

Seems like Oracle does not even check the tnsnames.ora, it simply errors.
Re: Suddenly SQL server cannot connect to Oracle Server :( [message #311400 is a reply to message #311397] Fri, 04 April 2008 06:47 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
It might be a permission problem on some of the oracle binaries.

Can you check the permission on the executables and the DLLs?

And have you tried the usual "Reboot" - Solution yet?
Re: Suddenly SQL server cannot connect to Oracle Server :( [message #311402 is a reply to message #311380] Fri, 04 April 2008 06:56 Go to previous messageGo to next message
NickStan
Messages: 8
Registered: April 2008
Location: London, England
Junior Member
Yes, I have rebooted the Oracle Machine.

No I have not checked permissions of the executables and the DLLs. I have done a search on how to check this, how do I check these permissions?

Re: Suddenly SQL server cannot connect to Oracle Server :( [message #311419 is a reply to message #311402] Fri, 04 April 2008 07:59 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
I have done a search on how to check this, how do I check these permissions?


Talk to your administrator. I you have never checked permissions you should not fiddle with them without someone who knows that he is doing.

And I meant re-booting the WINDOWS box, not the Oracle box.
Re: Suddenly SQL server cannot connect to Oracle Server :( [message #311432 is a reply to message #311380] Fri, 04 April 2008 08:39 Go to previous messageGo to next message
NickStan
Messages: 8
Registered: April 2008
Location: London, England
Junior Member
Yes, I have rebooted the Windows Machine many times since the problem started.

I believe that I am actually the administrator of both machines.

Am I supposed to check the file permissions on the Oracle machine or the SQL machine?
Re: Suddenly SQL server cannot connect to Oracle Server :( [message #311452 is a reply to message #311432] Fri, 04 April 2008 09:28 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The SQL Machine

(If that is the machine, where the Oracle client fails to load with that error message)
Re: Suddenly SQL server cannot connect to Oracle Server :( [message #311462 is a reply to message #311380] Fri, 04 April 2008 10:06 Go to previous messageGo to next message
NickStan
Messages: 8
Registered: April 2008
Location: London, England
Junior Member
I have asked one of the tech guys to check the permissions on the SQL machine and they reported that there is no problems with that.

Any other suggessions?
Re: Suddenly SQL server cannot connect to Oracle Server :( [message #311469 is a reply to message #311462] Fri, 04 April 2008 10:26 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You could check all the other possible reasons returned by this.
Re: Suddenly SQL server cannot connect to Oracle Server :( [message #311477 is a reply to message #311380] Fri, 04 April 2008 10:56 Go to previous messageGo to next message
NickStan
Messages: 8
Registered: April 2008
Location: London, England
Junior Member
Thanks Thomas, but Google was the first place I went to, to try and solve this.

I came to this forum as a result of me trying so many things that did not work.

So basically I am screwed now, I cant solve this and I have users going crazy because the reports have been down for 2 days now.
Re: Suddenly SQL server cannot connect to Oracle Server :( [message #311509 is a reply to message #311477] Fri, 04 April 2008 12:09 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, the problem is that due to whatever reason the binaries and/or libaries on the client side can not be loaded correctly.

Which could have a lot of reasons, as the search turns up.

( File corruption, Access permission, Wrong Windows Group Policies, BUG #4684738 on MetaLink, "Create Global Objects" missing in Windows Security configuration, etc, etc, etc ... )

Have you searched the ENTIRE windows server for "sqlnet.log" files that could point to a more specific error, and have you looked at the Windows event logs?
Re: Suddenly SQL server cannot connect to Oracle Server :( [message #311515 is a reply to message #311380] Fri, 04 April 2008 12:21 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
What changed?
Software does not spontaneously break itself.
Re: Suddenly SQL server cannot connect to Oracle Server :( [message #311522 is a reply to message #311380] Fri, 04 April 2008 12:29 Go to previous messageGo to next message
NickStan
Messages: 8
Registered: April 2008
Location: London, England
Junior Member
I wish I knew what has changed, but I dont.

As I said these 2 servers have communicated happily for about 1.5 years with no problems.

There have been no windows updates or SQL server updates applied recently (2 both machines) that could have caused anything.

I can now connect in SQLPlus from the SQL server to the Oracle server (after inserting a new environment variable), however I still cannot run a stored procedure from a DTS (using SQLPlus)
Re: Suddenly SQL server cannot connect to Oracle Server :( [message #311530 is a reply to message #311380] Fri, 04 April 2008 12:40 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I still cannot run a stored procedure from a DTS
My car can not run.
Please tell me how to fix it.

Surely, some place an error message/code is be thrown.
It would help to know what the specific error is being reported.
Re: Suddenly SQL server cannot connect to Oracle Server :( [message #311538 is a reply to message #311380] Fri, 04 April 2008 12:51 Go to previous messageGo to next message
NickStan
Messages: 8
Registered: April 2008
Location: London, England
Junior Member
When I try to execute an Oracle Stored Procedure from SQL, I get the following error:
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly
Re: Suddenly SQL server cannot connect to Oracle Server :( [message #311540 is a reply to message #311380] Fri, 04 April 2008 12:59 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/search/SP2-1503

& what does GOOGLE have to say about this flavor of errors?
Previous Topic: ORACLE_BASE
Next Topic: unable to drop portal user
Goto Forum:
  


Current Time: Thu Dec 08 16:33:03 CST 2016

Total time taken to generate the page: 0.28142 seconds