Home » RDBMS Server » Server Administration » ORA-27300, ORA-27301, ORA-27302 Oracle Errors (10.2.0.1.0)
| ORA-27300, ORA-27301, ORA-27302 Oracle Errors [message #281977] |
Tue, 20 November 2007 04:06  |
amit_p_dixit
Messages: 31
Registered: November 2006
Location: Mumbai
|
Member |
 
|
|
Dear Sir,
(OLD SERVER)
I am experiencing ORA-12518 error in my environment. I am running Oracle 10.2.0.1.0 on Win 2003 Server with 2Gb RAM. The database is in dedicated server mode. My Sessions vary between 225 to 350. The error occurs intermittently. It occurs when sessions were 296 and sometimes at 260. My session limit is 885. It seems it hasn't anything to do with session limit.
At this time I see the following error in my alert log.
##################### Alert Log #####################
Errors in file e:\oracle\product\10.2.0\admin\alpsdb\bdump\alpsdb_psp0_4476.trc:
ORA-27300: OS system dependent operation:spcdr:9261:4200 failed with status: 997
ORA-27301: OS failure message: Overlapped I/O operation is in progress.
ORA-27302: failure occurred at: skgpspawn
Tue Nov 20 09:10:36 2007
Process J000 died, see its trace file
Tue Nov 20 09:10:36 2007
kkjcre1p: unable to spawn jobq slave process
Tue Nov 20 09:10:36 2007
Errors in file e:\oracle\product\10.2.0\admin\alpsdb\bdump\alpsdb_cjq0_3396.trc:
Tue Nov 20 09:16:34 2007
Errors in file e:\oracle\product\10.2.0\admin\alpsdb\udump\alpsdb_ora_2804.trc:
ORA-00600: internal error code, arguments: [psi.c: psitrp3: leak], [], [], [], [], [], [], []
ORA-03135: connection lost contact
t####################################################################
Once I get this error, the new user connecting to the application get ORA-12518 (TNS protocol Adapter error). But the old users still are connected.
Gradually the old users also start getting disconnected. And I have to restart the database. The database again works fine till the next time it gets this error in alert log (approx after 8 to 10 hrs).
Initialization Parameters are as follows:-
SGA :- 600 Mb.
PGA :- 200 Mb
Sessions :- 885
(NEW SERVER)
As I was getting this message so I configured a new server with 8 Gb RAM, and highest systems configuration with SAN storage With every table space having enough space. I applied the patch and upgraded it to 10.2.0.3.0. By using import/export I moved the database from old server to the new server. And set the following parameters:
SGA:- 1500Mb
PGA:- 300Mb
Sessions:- 885.
The same error was occurring only after 160 sessions got connected, and I have to restart the database after every 3 hrs. When I see v$session, about 145 sessions are in inactive status and 15 are in active status. I was also getting Ora-04030 along with Ora-27300 as follows in the alert log.
############################## Alert Log ########################
Mon Nov 19 13:00:21 2007
Errors in file d:\oracle\product\10.2.0\admin\alpsdb\bdump\alpsdb_q001_3088.trc:
ORA-04030: out of process memory when trying to allocate 123404 bytes (QERHJ hash-joi,kllcqas:kllsltba)
Mon Nov 19 13:00:52 2007
Process startup failed, error stack:
Mon Nov 19 13:00:52 2007
Errors in file d:\oracle\product\10.2.0\admin\alpsdb\bdump\alpsdb_psp0_3796.trc:
ORA-27300: OS system dependent operation:CreateThread failed with status: 8
ORA-27301: OS failure message: Not enough storage is available to process this command.
ORA-27302: failure occurred at: ssthrddcr
#################################################################
So I increased the PGA from 300Mb to 700 Mb. This solved the Ora-04030 error but still was getting ora-27300.
As my production was getting hampered I moved again to the old server.
I am attaching init file, listner.log, sqlnet.log, trace files, alert.log. (OLD and NEW)
Please help me in this matter. Thanks in advance.
Regards,
Amit Dixit
Oracle DBA
If any solution pls mail me on amitdixitp@gmail.com
-
Attachment: Servers.zip
(Size: 270.32KB, Downloaded 1885 times)
|
|
|
|
|
|
|
|
|
|
 Re: ORA-27300, ORA-27301, ORA-27302 Oracle Errors [message #422747 is a reply to message #281977] |
Fri, 18 September 2009 10:52  |
PhilipInNYS
Messages: 2
Registered: September 2009
Location: Upstate New York
|
Junior Member |
|
|
I just had one of these errors on my system; it IS an Oracle issue. Or more accurately, a memory parameters issue.
I'm on 10G R2, 10.2.0.3. It resides on a Windows Server 2003, Standard Edition system with 4GB of Ram, only 2GB of which can be accessed by a single process (oracle.exe).
I had set the SGA to 1400MB, and the PGA to 500MB, with a total SGA+PGA+etc (etc being the 40MB of memory that Oracle also sets aside, according to the Oracle installer) equal to 1940MB. I didn't think that was cutting it too close; it seemed safe enough.
I got the error and looked it up on Metalink, which reported that this means there wasn't enough memory available for the Oracle instance ("storage" might not be the best wording here). So Oracle asked Windows for some memory, Windows denied it, and this cryptic error was the result.
I decided that when my SGA was being fully used, I must have been getting too close to the limit Windows Server would allow for the oracle process, resulting in the O/S error. I decided that the 40MB figure must be incorrect; I guessed it was actually more like 148MB, which would have just pushed me over Windows' artificial limit.
I reduced my SGA by 100MB, to 1300MB for a total SGA+PGA+etc of 1840MB (or 1948MB if my guess above is correct). This seems to have worked. The error hasn't returned.
I forgot to mention earlier: I'm using Shared Server, with a minimum of 10 and a maximum of 20 shared servers, which means 100 to 200 sessions normally (it's about 10 sessions per shared server, although I've heard you can squeeze up to 20 on). Memory for shared server sessions is divided between the SGA and PGA.
P.S: UPDATE: Sorry -- I forgot to post the Metalink doc ID. It was: Doc ID: 466370.1
One more thing: If the guy's new server is still running Windows Server 2003, he should make sure he's not using Standard Edition. What we discovered was that SE is hard-wired to only let you use 4GB of Ram, with only 2GB available to a given process. It's a licensing issue; Microsoft only lets you access more than 4GB if you buy a higher edition of Windows. The next up level lets you use boot.ini flags to up your memory, and the Enterprise level lets you go all the way up to 128GB (I think). There's a 64-bit Windows available also, but I've heard it isn't well supported. I'm stuck with S.E. myself...
[Updated on: Fri, 18 September 2009 15:39] Report message to a moderator |
|
|
|
| Re: ORA-27300, ORA-27301, ORA-27302 Oracle Errors [message #422979 is a reply to message #422747] |
Mon, 21 September 2009 10:15 |
PhilipInNYS
Messages: 2
Registered: September 2009
Location: Upstate New York
|
Junior Member |
|
|
Oh, one final thing: I set my max sessions (sessions init parameter) to 500. Shared servers generally support 10 sessions per, and up to around 20, right? So by my estimation, if I have a min of 10 shared servers and 20 max, that could be up to 400 sessions. I set it to 500 to provide a little wiggle room (not sure if shared servers can support more than 20 sessions in a pinch...).
I notice the original guy had it set to 885, which seems a little high to me, but maybe that's because I'm in a relatively small environment (small gov't agency with a website that gets hammered a few times a year but is otherwise quiet).
The settings I described seem to be holding up really well; I haven't seen any ORA errors since.
|
|
|
|
Goto Forum:
Current Time: Sun Dec 01 17:04:49 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
