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

From: GS <>
Date: Wed, 11 Jun 2008 21:56:31 GMT
Message-ID: <jSX3k.900$sg6.743@edtnps91>

Mark D Powell wrote:

> On Jun 10, 7:02 pm, 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 Hide quoted text -
>> - Show quoted text -
> For anyone with metalink access who hits the same problem the second
> note Joel identified appears to be a nice match to the OP problem
> report.
> Diagnosing and Resolving ORA-4030 errors
> 10gR2 Dedicated Connections Intermittently Fail with TNS-12518
> How to use Very Large Memory, higher than 4Gb on Windows 2003 32 bit
> Automatic PGA Memory Managment in 9i and 10g
> If you do not have metalink you can pretty much figure out the first
> and forth note contents via referencing the standard documentation for
> managing the SGA, shared pool, and buffer cache.
> For the second and third note reference your Windows specific
> documentation plus there have been several posts on this topic in
> various online forumns.
> HTH -- Mark D Powell --

I read the 2nd note, and can count out #1 solution because the /PAE switch is already enabled on this box. I am looking at #2, but reading more on the possible effects of using the USE_INDIRECT_DATA_BUFFERS parameter, because to use that one needs to replace db_cache_size with db_block_buffers (both are at 0 currently), and I also believe that some fiddling with the AWE settings in the registry also need to be tweaked.

I have kept bumping the sga_target and sga_max_size back to the point that I am now at 600M and 700M for these settings. In another note I read on metalink it says if the AWE-<something> setting is not set in the registry it will default to 1GB, so I am thinking perhaps this is related as my combined SGA and PGA were previously over 1GB? The more I read about how to get around windows 32 bit memory limitations the more confused I get..

Oracle support has not issued any advice on database settings, so I have been doing these on my own as users are getting owly with the connection problems happening every 6 hours or so when connections are around the 100+ mark. Support wants to rule out any sqlnet issues before they hand it to database team, but their suggestions so far have been fruitless, although the last one (sqlnet.expire_time=10) I just implemented on last bounce is unknown at this point.. Received on Wed Jun 11 2008 - 16:56:31 CDT

Original text of this message