About the author:

Author: LuciferLiu member of China DBA alliance (ACDU). I am now working as Oracle DBA and used to develop Oracle database, mainly serving manufacturing, auto finance and other industries. Now I have Oracle OCP and OceanBase OBCA certifications, and I am good at Oracle database operation and maintenance development, backup and recovery, installation and migration, and Linux automatic operation and maintenance script writing.

preface

Ora-04030 error was reported in database during inspection today. The error log is as follows:

ORA-04030: out of process memory when trying to allocate 169040 bytes (pga heap,kgh stack)

ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)

Key words: ORA-04030, (PGA heap, KGH Stack), (KXS-heap-c, KPRbalo Temp memory)

I. Problem analysis

1. Use Oracle OErr to check the error code:

Typically, this error is caused by running out of private memory. To allocate more private memory, you can change the page count at the operating system level or adjust the Realfree heap page size in the database.

2. Capture key information of trace file:

Found in the trace file: “Process Map” shows 65532 mapped memory blocks. However, there are only 65,536 memory-mapped entries per process, and these errors usually occur when the operating system runs out of mapped entries.

3. By querying the MOS document, find the document corresponding to the error:

PLSQL Procedure Causing ORA-04030: (pga heap,control file i/o buffer) And ORA-04030: (koh-kghu sessi,pmuccst: Ora-04030: (KOh-kghucall, pmucALM coll) Errors (Doc ID 1325100.1) orA-04030: (Koh-kghucall, pmucALM COLl) Errors (Doc ID 1325100.1)

Second, solutions

You can choose one of two solutions. You are advised to modify system parameters.

1. Modify the page count at the operating system level

Check the current value of max_map_count
more /proc/sys/vm/max_map_count

## Close the database instance

Modified max_map_count # #Sysctl -w vm. Max_map_count =200000 or cat <<EOF>>/etc/sysctl.conf
vm.max_map_count=200000
EOF

# # to take effect
sysctl -p

# restart the host
reboot

Start the database instance
Copy the code

Note: Because modifying system parameters requires restarting the host, the database needs to be stopped for operation. Restart the database after restarting the host system parameters take effect.

2, modify database implicit parameters

Oracle 11204 pre-release:

## Set spfilealter system set "_use_realfree_heap"=TRUE scope=spfile;
alter system set "_realfree_heap_pagesize_hint"=200000 scope=spfile; # restart the databaseCopy the code

After Oracle 12.1:

## Set spfilealter system set "_use_realfree_heap"=TRUE scope=spfile;
alter system set "_realfree_heap_pagesize"=200000 scope=spfile; # restart the databaseCopy the code

PLSQL Procedure Causing ORA-04030: (PGA heap,control file I/O buffer) And ORA-04030: (Koh-Kghu Sessi, Pmuccst: Ora-04030: (KOh-kghucall, pmucALM coll) Errors (Doc ID 1325100.1) orA-04030: (Koh-kghucall, pmucALM COLl) Errors (Doc ID 1325100.1)

If you think the article is helpful to you, please like it, bookmark it, follow it, comment it, and support it four times with one button. Your support is the biggest motivation for my creation. Thank you

CSDN: Lucifer think twice before you act