Database reboot caused by ORA-04031 Error

Linux community (www.linuxidc.com) is a professional Linux System Portal website that publishes the latest Linux information in real time, including Linux, Ubuntu, Fedora, RedHat, Red Flag Linux, Linux tutorial, Linux certification, SUSE

Linux community (www.linuxidc.com) is a professional Linux System Portal website that publishes the latest Linux information in real time, including Linux, Ubuntu, Fedora, RedHat, Red Flag Linux, Linux tutorial, Linux certification, SUSE

Environment:

OS: AIX Version 6.1

Oracle: 10.2.0.5 rac (node rac2)

I. Problem Description

At, I received a text message, and the database crashed. I immediately connected to the database and found in the log:

From the log, the problem is obvious: ora04031 causes the database to restart.

Ii. Analysis and Solution:

Ora04031 error causes the background process LCK0 to crash, resulting in database restart:

View the parameters of the current database:

View awr:

Here we can see that the shared pool size is 8 GB, the buffer cache has 160 GB, and there are 7 sub-pools.

View the dump file/oracle/product/admin/oss139/bdump/oss1392_lck0_6685174.trc

Memory Utilization of Subpool 1

======================================

Allocation Name Size

___________________________________

"Free memory" 19127320 19 M

Memory Utilization of Subpool 2

======================================

Allocation Name Size

___________________________________

"Free memory" 131103400 130 M

Memory Utilization of Subpool 3

======================================

Allocation Name Size

___________________________________

"Free memory" 19409776 19 M

Memory Utilization of Subpool 4

======================================

Allocation Name Size

___________________________________

"Free memory" 19172224 19 M

Emory Utilization of Subpool 5

======================================

Allocation Name Size

___________________________________

"Free memory" 18623928 18 M

Memory Utilization of Subpool 6

======================================

Allocation Name Size

___________________________________

"Free memory" 18026416 18 M

Memory Utilization of Subpool 7

======================================

Allocation Name Size

___________________________________

"Free memory" 12162296 12 M

Compared with the total free memory of the previous day, it is reduced to only 200 MB. The corresponding gcs resources and gcs shadows occupy more than 5 GB space, both gcs resources and gcs shadow resources are special global cache service resources in Oracle RAC. These resources are responsible for processing global buffer cache in RAC.

Then query the remaining memory of the shared_pool:

It's only 16 MB, that is, yesterday's ora 04031 was not accidental.

Search for gcs resource in metalink:

The two guids of Gcs cause the ora 04031 error:

So how to evaluate the size of gcs?

The red line shows that its size depends on the size of the db buffer.

When the instance cache increases, the space occupied by gcs resources also increases accordingly. The specific algorithm is as follows: Example with Linux x86-64/10.2.0.4

O v $ resource_limit

Resource Name Current Max Initial Limit
------------------------------------------
Gcs_resources 585758 1110251 1113203 1113203
Gcs_shadows 909888 1111054 1113203

O Initial shared memory in theory,
Gcs resources = 1113203*120 (+ alpha) bytes = 133,584,360 (+ alpha) bytes
Gcs shadows = 1113203*72 (+ alpha) bytes = 80,150,616 (+ alpha) bytes

O Actual size in shared pool
GC resources = 185,766,864 bytes
GB shadows = 107,993,760 bytes
Practically, a little bigger memory is used because gcs resources/shadows
Structure sizes are different depending on Oracle versions and platforms.

That is, increase the shared_pool.