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

Dear readers,

In order to share industry cases in time, notify common problems, and achieve sharing and prevention in advance, we have sorted out and edited the Cloud and Enmo Technical Newsletter, through knowledge review and fault induction for a period of time in the past. in order to provide valuable information for your reference. At the same time, we also hope to be able to gather hot events, new product features and other valuable information to provide you with forward-looking support information and keep abreast of the latest database news and events, including important database product releases, alerts, updates, new versions, patches, etc.

Contents of this issue:

News: database popularity ranking in November 2019

Experience: Oracle RAC accesses data blocks across nodes, and long node transactions aggravate gc waiting

Experience: fault analysis when the number of IBM MQ channel connections reaches maximum

Problem: too many subcursors cause database HANG

Frequent occurrence: talking about Library Cache Lock again

Frequent occurrence: see ORA-4031 for troubleshooting

Warning: forced shutdown of the OGG process triggers bug to abended

Announcement: the selection of the first Mo Tianlun's Top Ten Outstanding contributors of the year

Highlights of Yunhe Enmo Technology Newsletter: https://www.modb.pro/doc/topic/5927

Partial selection-frequent occurrence: troubleshooting see also ORA-4031 author: Hou Jingyuan

When you encounter an ORA-4031 error, will you feel nervous? When the Oracle process requests memory from SGA, it throws this error if the request fails, mostly when requesting memory from shared pool in SGA. In serious cases, it may cause the database to crash abnormally. This article shares a recent case of abnormal database downtime due to ORA-4031 problems encountered by customers for your reference.

Problem description

Around 3: 00 a.m. on September 4, 2019, I received an alarm from the monitoring system that the database was abnormal and could not be connected. Log in to the database 1 node to check the background alert log and find that there are a large number of ORA-04031 errors and a small number of errors in the 2 nodes. In order to resume business as soon as possible, try to restart the 1-node database directly and return to normal after the restart is completed.

Analysis of problems

1. Node backend corresponds to alert log:

Wed Sep 04 03:57:50 2019

Errors in file / u01/app/oracle/diag/rdbms/xxxxx/xxxxx/trace/xxxxx_smon_29747.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool", "update sys.col_usage$ set...", "sga heap (2Magin0)", "kglsim object batch")

Wed Sep 04 03:58:10 2019

Errors in file / u01/app/oracle/diag/rdbms/xxxxx/xxxxx/trace/xxxxx_smon_29747.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool", "update sys.col_usage$ set...", "sga heap (1meme 0)", "kglsim object batch")

Wed Sep 04 03:58:26 2019

Errors in file / u01/app/oracle/diag/rdbms/xxxxx/xxxxx/trace/xxxxx_smon_29747.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool", "update sys.col_usage$ set...", "sga heap (7jin0)", "kglsim object batch")

Wed Sep 04 03:58:42 2019

Errors in file / u01/app/oracle/diag/rdbms/xxxxx/xxxxx/trace/xxxxx_smon_29747.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool", "update sys.col_usage$ set...", "sga heap (6jin0)", "kglsim object batch")

Wed Sep 04 03:58:57 2019

Errors in file / u01/app/oracle/diag/rdbms/xxxxx/xxxxx/trace/xxxxx_smon_29747.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool", "update sys.mon_mods$ set ins...", "sga heap"), "kglsim object batch")

Wed Sep 04 03:59:08 2019

Errors in file / u01/app/oracle/diag/rdbms/xxxxx/xxxxx/trace/xxxxx_xxx0_42548.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool", "select count (*) from sys.job...", "sga heap (3jin0)", "kglsim object batch")

Wed Sep 04 03:59:10 2019

License high water mark = 97

USER (ospid: 28750): terminating the instance

Count the number of 04031 occurrences of each subpool and duration of node 1. Sga heap (ncent0)-n represents the number of subpools, and 0 represents the number of duration:

As can be seen from the alert log, all ora-4031 occurs on the 0th duration of the shared pool subpool.

Summary of resize operations history:

Shared pool start 3.19 GB now 3.19 GB 0 grows 0 shrinks

Large pool start 0.50 GB now 0.50 GB 0 grows 0 shrinks

Java pool start 0.50 GB now 0.50 GB 0 grows 0 shrinks

SGA Target start 32.00 GB now 32.00 GB 0 grows 0 shrinks

DEFAULT buffer cache start 27.59 GB now 27.59 GB 0 grows 0 shrinks

PGA Target start 11.00 GB now 11.00 GB 0 grows 0 shrinks

It was found that shared pool did not do resize.

=

TOP 20 MEMORY USES ACROSS SGA HEAP 1-7

-

"KGLH0" 1103 MB 19%

"SQLA" 1081 MB 18%

"free memory" 835 MB 14%

"gcs resources" 794 MB 14%

"gcs shadows" 550 MB 9

"db_block_hash_buckets" 178 MB 3

"ASH buffers" 160 MB 3

"KGLHD" 157 MB 3

"Checkpoint queue" 156 MB 3

"kglsim object batch" 90 MB 2

"kglsim heap" 56 MB 1

"ges resource" 53 MB 1

"ges enqueues" 43 MB 1

"KGLDA" 41 MB 1

"dbwriter coalesce buffer" 40 MB 1

"dirty object counts array" 40 MB 1

"object queue" 35 MB 1%

"gcs res hash bucket" 32 MB 1%

"dbktb: trace buffer" 31 MB 1%

"FileOpenBlock" 30 MB 1

TOTALS--

Total free memory 830 MB

Total memory alloc. 5026 MB

Grand total 5856 MB

=

two。 Alert log corresponding to node backend:

Wed Sep 04 03:23:18 2019

Emon ping encountered error 12801

Errors in file / u01/app/oracle/diag/rdbms/xxxxx/xxxxx2/trace/xxxxx2_q002_35378.trc:

ORA-12801: error signaled in parallel query server PZ99, instance dnfwglpt1:xxxxx (1)

ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool", "select inst_id, reg_id, num_...", "sga heap (4jin0)", "kglsim object batch")

Wed Sep 04 03:23:23 2019

Emon ping encountered error 12801

Errors in file / u01/app/oracle/diag/rdbms/xxxxx/xxxxx2/trace/xxxxx2_q003_35453.trc:

ORA-12801: error signaled in parallel query server PZ99, instance dnfwglpt1:xxxxx (1)

ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool", "select inst_id, reg_id, num_...", "sga heap (1mem0)", "kglsim object batch")

Wed Sep 04 03:23:29 2019

Emon ping encountered error 12801

Errors in file / u01/app/oracle/diag/rdbms/xxxxx/xxxxx2/trace/xxxxx2_q004_35725.trc:

ORA-12801: error signaled in parallel query server PZ99, instance dnfwglpt1:xxxxx (1)

ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool", "select inst_id, reg_id, num_...", "sga heap (5jin0)", "kglsim object batch")

Wed Sep 04 03:23:34 2019

Emon ping encountered error 12801

Errors in file / u01/app/oracle/diag/rdbms/xxxxx/xxxxx2/trace/xxxxx2_q001_35778.trc:

ORA-12801: error signaled in parallel query server PZ99, instance dnfwglpt1:xxxxx (1)

ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool", "select inst_id, reg_id, num_...", "sga heap (2jin0)", "kglsim object batch")

Wed Sep 04 03:23:39 2019

Emon ping encountered error 12801

Errors in file / u01/app/oracle/diag/rdbms/xxxxx/xxxxx2/trace/xxxxx2_q002_36069.trc:

ORA-12801: error signaled in parallel query server PZ99, instance dnfwglpt1:xxxxx (1)

ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool", "select inst_id, reg_id, num_...", "sga heap (6. 0)", "kglsim object batch")

Wed Sep 04 03:23:45 2019

Emon ping encountered error 12801

Errors in file / u01/app/oracle/diag/rdbms/xxxxx/xxxxx2/trace/xxxxx2_q003_36151.trc:

ORA-12801: error signaled in parallel query server PZ99, instance dnfwglpt1:xxxxx (1)

ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool", "select inst_id, reg_id, num_...", "sga heap (3jin0)", "kglsim object batch")

Wed Sep 04 03:23:50 2019

Emon ping encountered error 12801

Errors in file / u01/app/oracle/diag/rdbms/xxxxx/xxxxx2/trace/xxxxx2_q004_36242.trc:

ORA-12801: error signaled in parallel query server PZ99, instance dnfwglpt1:xxxxx (1)

ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool", "select inst_id, reg_id, num_...", "sga heap (7pc0)", "kglsim object batch")

Wed Sep 04 03:23:55 2019

Emon ping encountered error 12801

Errors in file / u01/app/oracle/diag/rdbms/xxxxx/xxxxx2/trace/xxxxx2_q001_36305.trc:

ORA-12801: error signaled in parallel query server PZ99, instance dnfwglpt1:xxxxx (1)

ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool", "select inst_id, reg_id, num_...", "sga heap (4jin0)", "kglsim object batch")

Count the number of 04031 occurrences of each subpool and duration of the 2 nodes:

According to the alert log, all ora-4031 also occur on the 0th duration of the shared pool subpool, and the root cause of 4031 is that the 0th duration of the shared pool subpool is out of memory.

After setting up ASMM management for sga_target, the shared pool (shared_pool) and the stream pool (streams pool) are each 4 duration. They are: instance,session,cursor,execution, only the fourth duration, that is, execution, can be resize, and when the 0th duration is out of memory can not resize, it will directly report an error ora-4031.

Problem solving

By disabling duration, you must set the parameter "_ enable_shared_pool_durations=fales" and restart the database.

Alter system set "_ enable_shared_pool_durations" = false scope=spfile

After setting this parameter, merge all four duration into one pool, so that one duration will not run out of memory, while the other duration will still have free memory, for both shared and stream pools After sga_target is set, all pools will transfer granules (granule) memory through buffer cache. If shrink, buffer cache will be returned. There is no direct transfer from one pool to another pool. All memory resize will take buffer cache as the source and destination.

The only negative effect of setting this parameter is that when SGA resize is used, memory cannot be fetched from shared pool to other pool.

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.