Re: ORA-12518, 04030 errors after 9i-10G upgrade.
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 <joel-ga..._at_home.com> 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...- 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