Shulou(Shulou.com)06/01 Report--

Oracle daily problem handling ORA-04031

Environment:

DB:Oracle 11.2.0.1.0

OS:Windows Server 2008

Problem phenomenon:

The database often goes down, and problems occur a few days after the restart.

The cause of the problem:

If you check the alarm log during the problem, you can see that the database is unavailable due to ORA-04031 error:

ORA-04031: unable to allocate 2968 bytes of shared memory ("shared pool", "insert into smon_scn_time (t...", "sga heap (2jin0)", "call")

As you can see from the corresponding trace log, the "KGH: NO ACCESS" type of memory takes up a lot of space.

This problem is explained in the following documentation:

ORA-04031 in 11g & 11gR2, Excess "KGH: NO ACCESS" Memory Allocation (Doc ID 1127833.1)

This type of memory is the memory in the intermediate state when the shared pool and DB Cache switch, and if the switch is too frequent, it will cause this kind of memory to accumulate and cannot be used properly.

The related Bug is usually fixed in 11.2.0.2 or 11.2.0.3 version 11.2.0.4.0.

Solution:

A fundamental solution:

1 upgrade to version 11.2.0.4

Recommended supplementary options:

2 set a minimum for shared_pool_size and db_cache_size to reduce the possibility of automatic memory switching

Options:

3 set alter system set "_ memory_broker_stat_interval" = 999

It is mandatory to switch at least once in 999 seconds.

Related documentation:

ORA-04031 and ORA-04030 are often easily confused. You can read the following article to learn more about ORA-04031 and ORA-04030 errors.

Troubleshooting and diagnosis of ORA-4031 errors [Video] (Doc ID 2016002.1)

OERR: ORA-4031 "unable to allocate% s bytes of shared memory ("% s ","% s ","% s ")" (Doc ID 4031.1)

Diagnose and resolve ORA-4030 errors (Doc ID 1548826.1)

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.