ASP interacting with Oracle server problem

Here we go:

IIS 6.0, win2k3, using Classic ASP and the Microsoft client for Oracle
connecting to Oracle 9.2.0.7 (latest patch).

The problems started appearing 2 days after the Oracle server was upgraded.
2 of our sites with Huge, nested, multi-table queries started having problems
inserting and updating while failing with ORA-12152 and then ORA-03114 errors.

Has anyone seen this before, or have any idea how to proceed with debugging,
so I can know if IIS, the Code, the Driver, or the Oracle Server has a
problem?

Thanks in advance!

pj_servadmin

Re: ASP interacting with Oracle server problem

"pj_servadmin" wrote in message
news:B50F808D-B978-49C2-9894-FCA86E47F016@microsoft.com...
> Here we go:
>
> IIS 6.0, win2k3, using Classic ASP and the Microsoft client for Oracle
> connecting to Oracle 9.2.0.7 (latest patch).
>
> The problems started appearing 2 days after the Oracle server was
> upgraded.
> 2 of our sites with Huge, nested, multi-table queries started having
> problems
> inserting and updating while failing with ORA-12152 and then ORA-03114
> errors.
>
> Has anyone seen this before, or have any idea how to proceed with
> debugging,
> so I can know if IIS, the Code, the Driver, or the Oracle Server has a
> problem?
>
> Thanks in advance!
>
> pj_servadmin

First thing I would eliminate is make sure you are not using an ODBC
connection to the database. IME ODBC is the cause of many wierd problems.

--
John Blessing

http://www.LbeHelpdesk.com - Help Desk software priced to suit all
businesses
http://www.room-booking-software.com - Schedule rooms & equipment bookings
for your meeting/class over the web.
http://www.lbetoolbox.com - Remove Duplicates from MS Outlook
http://www.outlook-find-replace.com - Find & Replace in Emails, Contacts,
Appointments, Tasks and Notes
http://www.schedule-email.com - Schedule multiple individual
emails/newsletters

Re: ASP interacting with Oracle server problem

Please don't make us look up Oracle error codes in the future.

ORA-12152: TNS:unable to send break message
ORA-03114: not connected to Oracle

It sounds like a problem with the Oracle client software. Exactly what was
upgraded? Was it just the DBMS server or was matching Oracle client software
installed on the web server (they aren't on the same machine are they)?

In our experience, in order to get Oracle 9+ to work reliable, you have to
use the Oracle OLEDB Provider (not the MS OLEDB for Oracle driver) instead
of ODBC. I don't recall the errors we got when first testing with Oracle 9
using the Microsoft ODBC. I think they were different from what you are
getting. There were also some minor coding changes that had to be made for
OLEDB in our application but I think they were for compatibility with
Access, Oracle, and SQLServer.

--
--Mark Schupp


"pj_servadmin" wrote in message
news:B50F808D-B978-49C2-9894-FCA86E47F016@microsoft.com...
> Here we go:
>
> IIS 6.0, win2k3, using Classic ASP and the Microsoft client for Oracle
> connecting to Oracle 9.2.0.7 (latest patch).
>
> The problems started appearing 2 days after the Oracle server was
> upgraded.
> 2 of our sites with Huge, nested, multi-table queries started having
> problems
> inserting and updating while failing with ORA-12152 and then ORA-03114
> errors.
>
> Has anyone seen this before, or have any idea how to proceed with
> debugging,
> so I can know if IIS, the Code, the Driver, or the Oracle Server has a
> problem?
>
> Thanks in advance!
>
> pj_servadmin

Re: ASP interacting with Oracle server problem

John & Mark,

Thank you for your responses.

John, I will check out your angle first thing monday morning - thanks.

Mark, I am sorry I did not include the relevant Oracle error definitions,
I've had my head buried in this for way too long, I assumed, and you know
what they say...

The Oracle client software on the webserver was not upgraded (well it was,
but that was months ago), only the unix database server.

As I am really not a DB guy, would you have a sample connect string using
the Oracle OLEDB provider that I could provide the developers?

I do not know if this is relevant at this point or not, but the oracle
errors amount to what almost appears to be a network level failure to connect
to the Oracle DB, solvable only by a restart of the machine. In addition,
retrieving files from another machine via WFETCH.exe results in a winsock
error 0x2745 aka: 10053 (WSAECONNABORTED (10053)
• Translation: Software caused connection abort.
• Description: An established connection was stopped by the software in your
host computer, possibly because of a data transmission time-out or protocol
error.)


Thanks in advance,

pj_servadmin


"Mark Schupp" wrote:

> Please don't make us look up Oracle error codes in the future.
>
> ORA-12152: TNS:unable to send break message
> ORA-03114: not connected to Oracle
>
> It sounds like a problem with the Oracle client software. Exactly what was
> upgraded? Was it just the DBMS server or was matching Oracle client software
> installed on the web server (they aren't on the same machine are they)?
>
> In our experience, in order to get Oracle 9+ to work reliable, you have to
> use the Oracle OLEDB Provider (not the MS OLEDB for Oracle driver) instead
> of ODBC. I don't recall the errors we got when first testing with Oracle 9
> using the Microsoft ODBC. I think they were different from what you are
> getting. There were also some minor coding changes that had to be made for
> OLEDB in our application but I think they were for compatibility with
> Access, Oracle, and SQLServer.
>
> --
> --Mark Schupp
>
>
> "pj_servadmin" wrote in message
> news:B50F808D-B978-49C2-9894-FCA86E47F016@microsoft.com...
> > Here we go:
> >
> > IIS 6.0, win2k3, using Classic ASP and the Microsoft client for Oracle
> > connecting to Oracle 9.2.0.7 (latest patch).
> >
> > The problems started appearing 2 days after the Oracle server was
> > upgraded.
> > 2 of our sites with Huge, nested, multi-table queries started having
> > problems
> > inserting and updating while failing with ORA-12152 and then ORA-03114
> > errors.
> >
> > Has anyone seen this before, or have any idea how to proceed with
> > debugging,
> > so I can know if IIS, the Code, the Driver, or the Oracle Server has a
> > problem?
> >
> > Thanks in advance!
> >
> > pj_servadmin
>
>
>

Re: ASP interacting with Oracle server problem

We have narrowed the problem down to a few of our classic asp apps that
dynamically generate huge Oracle queries & those site's interactions with the
default IIS recycle/failure protection settings.

The plot thickens...I will be load testing against our test server to see if
I can trigger the same type of problems which culminate in network level
choke conditions.


"pj_servadmin" wrote:

> John & Mark,
>
> Thank you for your responses.
>
> John, I will check out your angle first thing monday morning - thanks.
>
> Mark, I am sorry I did not include the relevant Oracle error definitions,
> I've had my head buried in this for way too long, I assumed, and you know
> what they say...
>
> The Oracle client software on the webserver was not upgraded (well it was,
> but that was months ago), only the unix database server.
>
> As I am really not a DB guy, would you have a sample connect string using
> the Oracle OLEDB provider that I could provide the developers?
>
> I do not know if this is relevant at this point or not, but the oracle
> errors amount to what almost appears to be a network level failure to connect
> to the Oracle DB, solvable only by a restart of the machine. In addition,
> retrieving files from another machine via WFETCH.exe results in a winsock
> error 0x2745 aka: 10053 (WSAECONNABORTED (10053)
> • Translation: Software caused connection abort.
> • Description: An established connection was stopped by the software in your
> host computer, possibly because of a data transmission time-out or protocol
> error.)
>
>
> Thanks in advance,
>
> pj_servadmin
>
>
> "Mark Schupp" wrote:
>
> > Please don't make us look up Oracle error codes in the future.
> >
> > ORA-12152: TNS:unable to send break message
> > ORA-03114: not connected to Oracle
> >
> > It sounds like a problem with the Oracle client software. Exactly what was
> > upgraded? Was it just the DBMS server or was matching Oracle client software
> > installed on the web server (they aren't on the same machine are they)?
> >
> > In our experience, in order to get Oracle 9+ to work reliable, you have to
> > use the Oracle OLEDB Provider (not the MS OLEDB for Oracle driver) instead
> > of ODBC. I don't recall the errors we got when first testing with Oracle 9
> > using the Microsoft ODBC. I think they were different from what you are
> > getting. There were also some minor coding changes that had to be made for
> > OLEDB in our application but I think they were for compatibility with
> > Access, Oracle, and SQLServer.
> >
> > --
> > --Mark Schupp
> >
> >
> > "pj_servadmin" wrote in message
> > news:B50F808D-B978-49C2-9894-FCA86E47F016@microsoft.com...
> > > Here we go:
> > >
> > > IIS 6.0, win2k3, using Classic ASP and the Microsoft client for Oracle
> > > connecting to Oracle 9.2.0.7 (latest patch).
> > >
> > > The problems started appearing 2 days after the Oracle server was
> > > upgraded.
> > > 2 of our sites with Huge, nested, multi-table queries started having
> > > problems
> > > inserting and updating while failing with ORA-12152 and then ORA-03114
> > > errors.
> > >
> > > Has anyone seen this before, or have any idea how to proceed with
> > > debugging,
> > > so I can know if IIS, the Code, the Driver, or the Oracle Server has a
> > > problem?
> > >
> > > Thanks in advance!
> > >
> > > pj_servadmin
> >
> >
> >