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

From: GS <>
Date: Wed, 11 Jun 2008 22:07:01 GMT
Message-ID: <90Y3k.901$sg6.726@edtnps91>

joel garry wrote:

> On Jun 11, 9:05 am, GS <> wrote:
>> joel garry wrote:
>>> On Jun 10, 2:21 pm, GS <> wrote:

>>>> Database is 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
>>> --
>>> is bogus.
>>> Control-Alt-SCRAM
>> 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?)
> Gawsh, I'd think they would have noticed how close the 2nd one was
> like Mark did.
> SGA_TARGET determines whether auto-tuning is on (see Note:257643.1).
> If I recall correctly, dbca asks for the target value, but I haven't
> done it enough to be sure.  Seems to be set in XE.  Docs say default
> is disabled.  Concept manual notes it will enlarge SGA_MAX_TARGET if
> it is larger.  I suspect that and the granule size issues might trip
> up some people (I know I keep forgetting about granules).
>> 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"?
> I was wondering mostly if you had set USE_INDIRECT_DATA_BUFFERS but
> I'm not on Windows so that seemed a reach of a guess, even for me-not-
> afraid-of-silly-guessing, so I just posted the notes, thinking if you
> are running up against a 4G limit or whatever, you'd say something
> after you looked.
>> 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..
> They pay people to answer calls from pissed-off managers who think
> pushing gets it done faster (and who may be right with sev 2).  But
> then again, for continuous work, you need to check that box that says
> you will be there too.
> jg
> --
> is bogus.

I am considering using the USE_INDIRECT_DATA_BUFFERS = TRUE if bumping the SGA and PGA combined to 1Gb doesn't work. If you read my reply to Mark you'll see that so far support has only dealt with sqlnet issues, because I logged the tar under connection issues I guess they want to rule everything sqlnet related out of the picture before they hand it over to database team. As far as I recall I always check the little box off saying I am available, but I'll check again. I can hold off the grumpy users for while longer, but with them taking so long to respond to my last updates saying their last suggestion did not work I am getting frustrated.. Received on Wed Jun 11 2008 - 17:07:01 CDT

Original text of this message