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

On Jun 11, 6:07 pm, GS <G..._at_GS.com> wrote:
> joel garry wrote:
> > On Jun 11, 9:05 am, GS <G..._at_GS.com> wrote:
> >> 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-SCRAMhttp://www.washingtonpost.com/wp-dyn/content/article/2008/06/05/AR200...
> >> 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
> > --
> > @home.com is bogus.
> >http://www.signonsandiego.com/uniontrib/20080611/news_1n11drone.html
>
> 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..- Hide quoted text -
>
> - Show quoted text -

Your reply to me indicated that you have automatic memory management in use. I do not believe you can use automatic memory management and AWE memory together. Without looking back I think the second Oracle note in the list contains this information.

Have you looked at ML Notes 46053.1 and 46001.1 ?

• If you have 3-4 GB memory, include the /3GB switch in the Windows startup.
• If you have 4-8GB memory, use /3GB and /PAE
• If you have 16 GB or more, use only /PAE, as /3GB will cripple memory over 8GB

Also see MS note
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/memory/base/physical_address_extension.asp

HTH -- Mark D Powell -- Received on Fri Jun 13 2008 - 10:21:50 CDT