Re: ORA-12518, 04030 errors after 9i-10G upgrade.
- From: GS <GS@xxxxxx>
- Date: Wed, 11 Jun 2008 21:56:31 GMT
Mark D Powell wrote:
On Jun 10, 7:02 pm, joel garry <joel-ga...@xxxxxxxx> wrote:On Jun 10, 2:21 pm, GS <G...@xxxxxx> wrote:
Database is 10.2.0.4 on Windows 2003 (32 bit)server, upgraded viaI'm sure the analysts told you to look at Note:233869.1, 371983.1,
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..
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..
.
- References:
- ORA-12518, 04030 errors after 9i-10G upgrade.
- From: GS
- Re: ORA-12518, 04030 errors after 9i-10G upgrade.
- From: joel garry
- Re: ORA-12518, 04030 errors after 9i-10G upgrade.
- From: Mark D Powell
- ORA-12518, 04030 errors after 9i-10G upgrade.
- Prev by Date: Re: Crossing over from SQL Server
- Next by Date: Re: Crossing over from SQL Server
- Previous by thread: Re: ORA-12518, 04030 errors after 9i-10G upgrade.
- Next by thread: Re: ORA-12518, 04030 errors after 9i-10G upgrade.
- Index(es):
Relevant Pages
|
