Home » Infrastructure » Windows » ORA-04030 error in Windows 2003!
ORA-04030 error in Windows 2003! [message #287925] Fri, 14 December 2007 01:20 Go to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Hi all!

Today, my application has not worked still now, the error encountered was ORA-04030

And, following the Oracle error data:

ORA-04030: out of process memory when trying to allocate string bytes (string,string)
    Cause: Operating system process private memory has been exhausted
    Action: none


Database 10gR2
Windows 2003.

The current SGA is 1512M within 2003 Server (32bit). I know that I can not increase the SGA_TARGET bigger than 3GB, before it's against the OS rule.

Have you got any idea?

Thank for responding!
Re: ORA-04030 error in Windows 2003! [message #287932 is a reply to message #287925] Fri, 14 December 2007 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The only solutions are to increase memory or decrease the number of users or change the application in order to use less memory.

Regards
Michel
Re: ORA-04030 error in Windows 2003! [message #287957 is a reply to message #287932] Fri, 14 December 2007 02:25 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you for your respond!

At the day before the HP's support team delivered the server to our company, I wished many many time that "Give me the 64bit OS, not 32, guys". But unfortunately, every thing decided by boss, I am only DBA staff.

As the Windows documentation, the OS manages memory by segment address, not flat, and I can not increase the SGA bigger than 3GB which may be set to 5GB in Solaris 10 or Linux that use Huge Page . Ohh, what a pity!

About the number users, our system is for telecommunication service, and I can not increase. Every one who take responsibility need to select, insert, delete or any thing else with their working. The Oracle Database was setup by me with 5 shared_server, 15 dispatcher, using connection pooling...
SQL> show parameter shared_server

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers                   integer     5
shared_server_sessions               integer
shared_servers                       integer     1
SQL> show parameter dispatcher

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dispatchers                          string      (protocol=TCP)(disp=15)
max_dispatchers                      integer     20
SQL> show parameter sessions

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
logmnr_max_persistent_sessions       integer     1
sessions                             integer     226
shared_server_sessions               integer
SQL> show parameter process

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     10
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
job_queue_processes                  integer     20
log_archive_max_processes            integer     2
processes                            integer     200


Portion of STATSPACK
                           Total ----------------- % of Waits ------------------
Event                      Waits  <1ms  <2ms  <4ms  <8ms <16ms <32ms  <=1s   >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
SQL*Net message from clien 6003   85.9   2.5   3.4   4.0    .8   1.5   1.3    .6
SQL*Net message to client  6015  100.0
SQL*Net more data from cli  677  100.0
Streams AQ: waiting for me   90                                          100.0
Streams AQ: waiting for ti  148   10.1        24.3                     6.1  59.5
class slave wait              2                                            100.0
dispatcher timer             16K  72.1   4.5   5.0   3.8   3.5   1.9   5.5   3.7
jobq slave wait             140                                            100.0
pipe get                     12   50.0   8.3   8.3        16.7   8.3   8.3
pmon timer                  206   22.3          .5    .5               1.5  75.2
rdbms ipc message          2591   12.7   2.6   1.5   3.4   5.1   5.2  31.0  38.4
smon timer                   32   18.8         9.4   9.4  12.5        28.1  21.9
virtual circuit status     9121   76.2   3.5   5.1   5.1   3.8    .7   4.1   1.5
wait for unread message on   34                                      100.0


Am I wrong or short of experience?

Michel


Change the application in order to use less memory.



I really do not understand what you mean. Need I tune SQL? Need I track the application which is OAS serves web server? Or any thing else?

Thank you very much!

[Updated on: Fri, 14 December 2007 02:32]

Report message to a moderator

Re: ORA-04030 error in Windows 2003! [message #287961 is a reply to message #287957] Fri, 14 December 2007 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The error comes from a lack of memory.
A client program ask a server process to do some work.
In order to execute it, this server process tries to allocate memory and fails.
So globally you have to ask for less memory (if you can't increase it).
To do so you only have 2 solutions:
- less processes asking the same memory
- each process ask for less memory

The first one is achieved decreasing the number of server processes but as you only have 5 shared servers (and assuming you don't have dedicated sessions) then you can't decrease this.
So rest the second one. If your application uses PL/SQL tables then limit the size of them. This is waht I meant with "Change the application in order to use less memory". (This is just an example)

Another way is to limit memory used by processes other than Oracle ones. Do you have several Oracle instances or an application server or other applications on the same server? If yes, investigate to move them.

Regards
Michel
Re: ORA-04030 error in Windows 2003! [message #287972 is a reply to message #287961] Fri, 14 December 2007 03:11 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you, Michel, just I dream about the travel to France, see you, and say that 'you're great teacher'!

I am sorry about below post, I wrote wrong.
Quote:


I can not increase...



that must is : I can not decrease.

What's the reason? Because, this is the application which have been deployed over VN's post office. In which county, or the other cities in VN, the local post office manage their employee. All of them may connect to the main system in Ha noi, retrieving data, making any statement they can do. I do not know exactly how many people who connect with sound privilege, just I as a DBA, not manager.

You're right, the problem most occur corresponding to the application (Java) with many package in some module calling to Database through dblink, some of them are inside Database, others are not.

I feel happier when I see

Quote:


The first one is achieved decreasing the number of server processes but as you only have 5 shared servers



Thank you very much!
Re: ORA-04030 error in Windows 2003! [message #288146 is a reply to message #287972] Sat, 15 December 2007 01:45 Go to previous message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you, Michel!

I have seen the error which was the problem occurred to Database.
It seems naturally to the OS allocation memory, but not, it's to be my colleague, he did not know about my Database configuration, and..he just changed it to mode dedicate by editing tnsname.ora file.


Previous Topic: Upgrade from Windows XP to Windows 2003 server
Next Topic: Create a Batch File[merged topic]
Goto Forum:
  


Current Time: Thu Mar 28 06:40:23 CDT 2024