aix 6.1 ORA-04030 問題

bash-3.2# lsattr -E -l sys0 -a maxuproc
maxuproc 128 Maximum number of PROCESSES allowed per user True
bash-3.2# chdev -l sys0 -a maxuproc=8192
sys0 changed
bash-3.2# lsattr -E -l sys0 -a maxuproc
maxuproc 8192 Maximum number of PROCESSES allowed per user True

執行完了，透過以下命令重啟listener
srvctl stop listener -n nodename
srvctl start listener -n nodename

相關命令：

ulimit -a

ulimit -Ha

[@more@]What does an ORA-4030 mean?
This error indicates that the oracle server process is unable to allocate more memory from the operating system.This memory consists of the PGA (Program Global Area) and its contents depend upon the server configuration.For dedicated server processes it contains the stack and the UGA (User Global Area) which holds user session data, cursor information and the sort area. In a multithreaded configuration (shared server), the UGA is allocated in the SGA (System Global Area) and will not be responsible for ORA-4030 errors.

The ORA-4030 thus indicates the process needs more memory (stack UGA or PGA) to perform its job.

What causes this error?

Since you run into this error, you can't allocate memory from the operating system. This could be caused by your process itself, like your process is just requesting too much memory, or some other reasons cause the operating system memory to be depleted, like a too big SGA or too many processes to be accomadated for the systems virtual memory (physical memory + swap space). Many operating systems impose limits on the amout of memory a single process can acquire to protect itself.

This leads to the following questions:

* Is there still sufficient memory available?

* Is_there_an_operating_system_limit_set?

* Is there an oracle limit set?

* Which process is requesting too much memory?

* How to collect information on what the process is actually doing?

Is there still sufficient memory available?

1. OpenVMS systems : show memory will give you information about physical memory and pagefile usage:

Physical Memory Usage (pages): Total Free In Use Modified
Main Memory (256.00Mb) 32768 24849 7500 419

.....

Paging File Usage (blocks): Free Reservable Total
DISK$BOBBIEAXPSYS:[SYS0.SYSEXE]SWAPFILE.SYS 30720 30720 39936
DISK$BOBBIEAXPSYS:[SYS0.SYSEXE]PAGEFILE.SYS 226160 201088 249984
DISK$BOBBIE_USER3:[SYS0.PAGEFILE]PAGEFILE.SYS 462224 405296 499968

As a general guideline, the sum of the free space in the pagefile(s) should not get lower than half the sum of the total space.
The swapfile(s) should almost be unused, free space should be about the same as the total space.

2. Windows systems: Check MEM Usage in the performance tab in Task Manager.

3. Unix systems: Every unix flavour usually has its own utilities to check for the global memory usage on the system like top, vmstat,... And memory management does work differently on each OS.

top usually displays physical memory and swapspace statistics.
swapon -s displays swapspace usage
vmstat displays free physical memory

Sample top output on Linux:

top - 10:17:09 up 1:27, 4 users, load average: 0.07, 0.12, 0.05
Tasks: 110 total, 4 running, 105 sleeping, 0 stopped, 1 zombie
Cpu(s): 0.3% user, 1.6% system, 0.0% nice, 98.0% idle
Mem: 1033012k total, 452520k used, 580492k free, 59440k buffers
Swap: 1052248k total, 0k used, 1052248k free, 169192k cached
.....
When sufficient memory is available, check for operating system enforced limits. When the memory has been depleted, we will have to find out where the memory has been used for.

Is there an operating system limit set?

When there still seems to be plenty of virtual memory left, we might not be allowed to use the amount of memory we are asking for. Check for limits imposed by the operating system.

1. OpenVMS systems: To check for the amount of physical memory you can use, check the working set quotas and the pagefile quota with the authorize utility. Refer to the reference for OpenVMS section on what quotas are used and how to modify them. Depending on what process and how it is started, the quotas used will not be those of the oracle account. Show process/id=/quota will show you how much quota is left for a process

UAF> show oracle7

Username: ORACLE7 Owner: Oracle7 DBA
Account: SUPPORT UIC: [200,2] ([SUPPORT,ORACLE7])
CLI: DCL Tables: DCLTABLES
Default: DISK$BOBBIE_USER1:[ORACLE7]
LGICMD: LOGIN
Flags:
Primary days: Mon Tue Wed Thu Fri
Secondary days: Sat Sun
No access restrictions
Expiration: (none) Pwdminimum: 6 Login Fails: 0
Pwdlifetime: (none) Pwdchange: 3-DEC-1997 15:38
Last Login: 27-MAY-2003 14:54 (interactive), 26-MAY-2003 16:15 (non-interactive)
Maxjobs: 0 Fillm: 1200 Bytlm: 180000
Maxacctjobs: 0 Shrfillm: 0 Pbytlm: 0
Maxdetach: 0 BIOlm: 500 JTquota: 8192
Prclm: 20 DIOlm: 500 WSdef: 2500
Prio: 4 ASTlm: 4000 WSquo: 4096
Queprio: 0 TQElm: 4000 WSextent: 30000
CPU: (none) Enqlm: 18000 Pgflquo: 750000
Authorized Privileges: .....

$ sho proc/id=20200139/quota

24-JUN-2003 12:30:54.39 User: ORACLE7 Process ID: 20200139
Node: BOBBIE Process name: "ORA_BOB901_PMON"

Process Quotas:
Account name: SUPPORT
CPU limit: Infinite Direct I/O limit: 100
Buffered I/O byte count quota: 9994816 Buffered I/O limit: 100
Timer queue entry quota: 99 Open file quota: 29997
Paging file quota: 145968 Subprocess quota: 10
Default page fault cluster: 64 AST quota: 496
Enqueue quota: 49995 Shared file limit: 0
Max detached processes: 0 Max active jobs: 0

2. Windows systems: On Microsoft windows operating systems, oracle processes are implemented as threads in 1 process. The addressable amount of memory is 2Gb (including stack, PGA, SGA). This limit can be increased to 3Gb or higher. See Document 46001.1"Oracle Database and the Windows NT memory architecture, Technical Bulletin" for more information. The total memory used by the oracle single process, excluding process stack and code, can be determined with this query:

select sum(bytes)/1024/1024 Mb from
(select bytes from v$sgastat
union
select value bytes from v$sesstat s,v$statname n
where

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/66634/viewspace-1059018/，如需轉載，請註明出處，否則將追究法律責任。