To account for the environment, is the real-time production database host network. HP-UX 11.31+oracle 10.2.0.4.0 RAC cluster.
Two machines outdoor rac,essdb3 and ESSDB4 two machines.
Physical memory 128g,sga allocated 64G,PGA 5G. Morning found ESSDB4 host glance run only 6G of free memory.
Use the following command for a simple analysis:
Sql> select distinct SID from V$mystat; Gets the SID that calculates how large the PGA is occupied by an idle process.
Sql>select b.pga_used_mem Pga_used_mem, B.pga_alloc_mem pga_alloc_mem,b.pga_freeable_mem PGA_FREEABLE_MEM,b.PGA_ Max_mem Pga_max_mem from V$session A, v$process b where a.paddr=b.addr and a.sid = ' &sid '
Enter the SID number you just got. Figure out how much memory an idle process occupies , the system's total PGA usage is basically stable between 1.29G and 2G (the maximum value of the PGA set is 5G), which is a very reasonable state compared to the usual.
sql> select SUM (pga_used_mem) from v$process;
SUM (PGA_USED_MEM)
-----------------
1298679359
We use IPCS to observe the use of shared memory segments:
Essdb4_oracle[/var/adm/syslog] $ipcs-MB
IPC status From/dev/kmem as of Thu June 23 10:18:00 2016
T ID KEY MODE OWNER GROUP segsz
Shared Memory:
M 0 0X411C06CB--rw-rw-rw-root Root 348
M 1 0x4e0c0002--rw-rw-rw-root root 61760
M 2 0x4120077b--rw-rw-rw-root root 8192
M 3 0x00a5c581--rw-------Sfmdb users 10469376
M 4 0x411c14fb--rw-------root root 4096
M 13893637 0x00000000--rw-r-----Oracle Oinstall 18790481920
M 6 0x06347849--rw-rw-rw-root root 65544
M 7 0x0c6629c9--rw-r-----root root 22614264
M 32776 0x01205c0a--rw-rw-r--root root 4192
M 9 0x00000000 d-rw-rw-r--root root 4192
M 0x00000000 d-rw-rw-r--root root 4192
M 0x4918a061--rw-r--r--root root 22912
M 0x0120468b--rw-rw-r--root root 4192
M 0x00000000--rw-r-----Oracle Oinstall 18807521280
M. 0x00000000--rw-r-----Oracle Oinstall 18807259136
M. 0x00000000--rw-r-----Oracle Oinstall 12367142912
M 0x3546761c--rw-r-----Oracle Oinstall 135168
Detailed usage information for shared memory:
essdb4_oracle[/] #ipcs-MB
IPC status From/dev/kmem as of Thu June 23 13:42:57 2016
T ID KEY MODE OWNER GROUP segsz
Shared Memory:
M 0 0X411C06CB--rw-rw-rw-root Root 348
M 1 0x4e0c0002--rw-rw-rw-root root 61760
M 2 0x4120077b--rw-rw-rw-root root 8192
M 3 0x00a5c581--rw-------Sfmdb users 10469376
M 4 0x411c14fb--rw-------root root 4096
M 13893637 0x00000000 d-rw-r-----Oracle Oinstall 18790481920
M 6 0x06347849--rw-rw-rw-root root 65544
M 7 0x0c6629c9--rw-r-----root root 22614264
M 32776 0x01205c0a--rw-rw-r--root root 4192
M 9 0x00000000 d-rw-rw-r--root root 4192
M 0x00000000 d-rw-rw-r--root root 4192
M 0x4918a061--rw-r--r--root root 22912
M 0x0120468b--rw-rw-r--root root 4192
M 0x00000000--rw-r-----Oracle Oinstall 18807521280
M. 0x00000000--rw-r-----Oracle Oinstall 18807259136
M. 0x00000000--rw-r-----Oracle Oinstall 12367142912
M 0x3546761c--rw-r-----Oracle Oinstall 135168
The shared memory segment state is D-delete, which is normally not normal, which is an Oracle user-occupied shared memory segment, because the shared memory segment with the status of D itself is a memory segment that is not normally used, so full of the Ipcrm–m ID to delete this shared memory segment, Should be able to solve the problem, however, the result of the above practice is that the system report can not find an ID.
essdb4_oracle[/] #ipcrm-M 13893637
Now let's use Shminfo to use root permissions to see which process is currently using a shared memory segment:
essdb4_oracle[/] #shminfo-S 13893637
Sh:shminfo:not found.
Finally restart the node database still hold the shared memory, no result can only restart the operating system, released, the current system available memory 40G.
This article is from the Yang Junfeng blog, so be sure to keep this source http://yangjunfeng.blog.51cto.com/539796/1792231
Superficial logging of Oracle RAC system memory is not released