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

From: GS <GS_at_GS.com>
Date: Tue, 10 Jun 2008 21:21:13 GMT
Message-ID: <dfC3k.721$sg6.168@edtnps91>

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.. Received on Tue Jun 10 2008 - 16:21:13 CDT

Original text of this message