Re: ORA-12518, 04030 errors after 9i-10G upgrade.

From: GS <GS_at_GS.com>
Date: Wed, 11 Jun 2008 16:05:46 GMT
Message-ID: <uJS3k.857$sg6.139@edtnps91>


joel garry wrote:

> On Jun 10, 2:21 pm, GS <G..._at_GS.com> wrote:

>> Database is 10.2.0.4 on Windows 2003 (32 bit)server, upgraded via
>> exp/imp on new hardware. New server has 6GB ram.
>>
>> All went ok until users started connecting in 100+ numbers, database is
>> set up for dedicated server connections, this was never a problem in 9i.
>>
>> After the upgrade last friday, I had set up 10G with 1500MB for SGA and
>> 200 PGA size, monday once connections were up around 100+ mark
>> listener.log started showing out the tns and ora 12518 (TNS:listener
>> could not hand off client connection) errors, which also showed "32-bit
>> Windows Error: 233: Unknown error", nothing in alert log until some
>> time later when BEQ connections also would not work. Never saw this
>> happen on 9i,so I did some googling on this error and increased
>> processes to 300 from the default 150, and increased PGA to 300MB.
>>
>> This did not solve problems so bounced database and decreased SGA to
>> 1300MB, this worked for awhile, listener made about 2400 connections and
>> refused 0 until about 8:45 am this morning (20 hours later)started
>> getting the errors again, this time users were also getting ORA-04030
>> out of process memory errors. By this time I had opened up severity 2 SR
>> on metalink, the recommended adding the lines
>> INBOUND_CONNECT_TIMEOUT_<listener_name> = 0 to listner.ora and
>> SQLNET.INBOUND_CONNECT_TIMEOUT = 0 to sqlnet.ora on Oracles advice and
>> bounced database.
>>
>> This was ok for a few hours then just started to get more connection
>> errors, so bounced database again, dropped SGA to 1200MB and so far all
>> is good (fingers crossed)
>>
>> Havent heard boo from Oracle yet today, even though I updated the SR and
>> told them to get their database team on it, as currently it is with the
>> sqlnet guys and they aren't dealing with database parameters. Besides
>> the lines added to .ora files they have suggested I go to MTS, which is
>> doable, but why is this giving grief when the identical setup in 9i ran
>> without these problems?
>>
>> Server has 6Gb RAM with /PAE switch in boot.ini - but it still seems
>> that Oracle is having issues with OS resources.
>>
>> This is one of our bigger databases, but at 30Gb and an average of 100
>> connections it's relatively small database wise. I have tons of RAM
>> available and the windows pagefile is barely being touched, so stumped
>> why it's crapping out like this.
>>
>> Any other suggestions on what to try while I wait for my SR? So far
>> their advice has been of little help, as it was my idea to drop the SGA
>> size, and that seems to have had the most positive impact, but the
>> problem still persists..
> 
> I'm sure the analysts told you to look at Note:233869.1, 371983.1,
> 342080.1 and 223730.1.  10g works a little differently as far as some
> of the hidden parameters are concerned, but it's worth it to look at
> how much PGA is being used, are any threads going bonkers with memory,
> are sorts spilling over, is your app going bonkers with cursors, do
> you have automatic tuning turned on and is it shooting itself in the
> foot, are disconnects not releasing memory...
> 
> There's always linux <g,d&r>
> 
> jg
> --
> @home.com is bogus.
> Control-Alt-SCRAM http://www.washingtonpost.com/wp-dyn/content/article/2008/06/05/AR2008060501958_pf.html

Actually they didn't suggest those notes, but I have read the first one on my own, and will read the others as well. There was one alert about a large number of cursors being in use, and I haven't checked to see if auto-tuning is on (is it by default?)

This is the 2nd 10G upgrade I have done where the database has needed a LOT more attention and tweaking than it did in 9i just to get it stable, and this was touted as the "self managing database"?

On another note, it's been over 24 hours since the last update from Oracle on this severity 2 SR, sure glad I'm paying premium support.. Received on Wed Jun 11 2008 - 11:05:46 CDT

Original text of this message