Oracle RAC system memory cannot be released Solution
The environment is a real-time production database host on the current network. Hp-ux 11.31 + Oracle 10.2.0.4.0 RAC cluster.
Two machines: RAC and essdb3 and essdb4.
Physical memory 128 GB, SGA 64 GB, and PGA 5 GB. In the morning, we found that the glance of the essdb4 host only runs 6 GB of idle memory.
Use the following command for simple analysis:
SQL> select distinct sid from v $ mystat; obtain the sid, and calculate the size of the pga occupied by a idle process.
SQL> select B. PGA_USED_MEM, B. PGA_ALLOC_MEM, B. PGA_FREEABLE_MEM, B. PGA_MAX_MEM from v $ session a, v $ process B where. PADDR = B. ADDR and. sid = '& sid'
Enter the obtained SID number. Calculate the memory occupied by a idle process. The total usage of the PGA in the system is basically between 1.29G and 2G (the maximum value of the PGA set by the system is 5G ), it is also a very reasonable status than usual.
SQL> select sum (PGA_USED_MEM) from v $ process;
SUM (PGA_USED_MEM)
-----------------
1298679359
We use ipcs to observe the usage of shared memory segments:
Essdb4_oracle [/var/adm/syslog] $ ipcs-mb
IPC status from/dev/kmem as of Thu Jun 23 10:18:00 2016
T ID KEY MODE OWNER GROUP SEGSZ
Shared Memory:
M 0 0x411c06cb -- rw-root 348
M 1 0x4e0c0002 -- rw-root 61760
M 2 0x4120077b -- rw-root 8192
M 3 0x00a5c581 -- rw ------- sfmdb users 10469376
M 4 0x411c14fb -- rw ------- root 4096
M 13893637 0x00000000 -- rw-r ----- oracle oinstall 18790481920
M 6 0x06347849 -- rw-root 65544
M 7 0x0c6629c9 -- rw-r ----- root 22614264
M 32776 0x01205c0a -- rw-r -- root 4192
M 9 0x00000000 D-rw-r -- root 4192
M 10 0x00000000 D-rw-r -- root 4192
M 11 0x0000a061 -- rw-r -- root 22912
M 12 0x0120468b -- rw-r-root 4192
M 13 0x00000000 -- rw-r ----- oracle oinstall 18807521280
M 14 0x00000000 -- rw-r ----- oracle oinstall 18807259136
M 15 0x00000000 -- rw-r ----- oracle oinstall 12367142912
M 16 0x3546761c -- rw-r ----- oracle oinstall 135168
Shared memory usage details:
Essdb4_oracle [/] # ipcs-mb
IPC status from/dev/kmem as of Thu Jun 23 13:42:57 2016
T ID KEY MODE OWNER GROUP SEGSZ
Shared Memory:
M 0 0x411c06cb -- rw-root 348
M 1 0x4e0c0002 -- rw-root 61760
M 2 0x4120077b -- rw-root 8192
M 3 0x00a5c581 -- rw ------- sfmdb users 10469376
M 4 0x411c14fb -- rw ------- root 4096
M 13893637 0x00000000 D-rw-r ----- oracle oinstall 18790481920
M 6 0x06347849 -- rw-root 65544
M 7 0x0c6629c9 -- rw-r ----- root 22614264
M 32776 0x01205c0a -- rw-r -- root 4192
M 9 0x00000000 D-rw-r -- root 4192
M 10 0x00000000 D-rw-r -- root 4192
M 11 0x0000a061 -- rw-r -- root 22912
M 12 0x0120468b -- rw-r-root 4192
M 13 0x00000000 -- rw-r ----- oracle oinstall 18807521280
M 14 0x00000000 -- rw-r ----- oracle oinstall 18807259136
M 15 0x00000000 -- rw-r ----- oracle oinstall 12367142912
M 16 0x3546761c -- rw-r ----- oracle oinstall 135168
The shared memory segment status is D-Delete, which is usually abnormal. This is the shared memory segment occupied by an Oracle user, because the shared memory segments in the D state are not normally used, the problem should be solved when the shared memory segments are deleted using ipcrm-m id, at that time, the result of the above practice was that the system report could not find a ID.
Essdb4_oracle [/] # ipcrm-m 13893637
Now we need to use shminfo with the root permission to check which process is currently using a shared memory segment:
Essdb4_oracle [/] # shminfo-s 13893637
Sh: shminfo: not found.
At last, the database on the node is restarted and still holds the shared memory. If nothing happens, the operating system can only be restarted and released. The current system has 40 Gb memory available.