ORA-04031: unable to allocate bytes of shared memory - Why it Happens and How to Resolve

March 8, 2024 | 7 minute read
Gareth Chapman
Senior Principal Program Manager
Text Size 100%:

ORA-04031: unable to allocate bytes of shared memory, occurs because more shared memory was needed than was available.

The error message shows how much memory was unavailable, the memory pool, which encountered the error and details about the type of memory allocation that failed.

Follow these step-by-step instructions for everything you need to do to resolve ORA-04031.

 

 

Understanding ORA-04031

The ORA-04031: unable to allocate bytes of shared memory error occurs because more shared memory was needed than was available.

The error message will show how much memory was unavailable, the memory pool encountering the error and details about the type of failed allocation.

ORA-04031 Error Structure

 

If the error is raised by a database process, then an entry will be made in the database alert log, which will point to a trace file showing the process experiencing the problem.

ORA-04031 alert log and trace file

 

However, if the error is encountered by a user process, then nothing will be found in the alert log.

 

Irrespective of where the error occurs, the process encountering the problem is often the victim of the situation and typically not the cause.

The underlying cause could be one of varying different things such as:

  • Initialization parameters for memory not being set high enough.
  • Problems with auto tuning.
  • Fragmentation in application design.
  • Bug or memory leak.

 

ORA-04031 Error Troubleshooting Steps

The basic Fix Flow steps for ORA-04031 are:

  1. Use AHF to generate and ORA-04031 Service Request Data Collection (SRDC)
  2. Use ORA-04031 Troubleshooting Tool to find recommendations
  3. Log a new SR using the diagnostic collection

 

1. Use AHF to generate an ORA-04031 Service Request Data Collection (SRDC)

In the first step we're going to use AHF to generate an ORA-04031 diagnostic collection.

Oracle Autonomous Health Framework (AHF) works autonomously 24x7 to keep database systems healthy and running while minimizing human reaction time. Utilizing applied machine-learning technologies, AHF provides early warnings or automatically solves operational runtime issues faced by Database and System administrators in the areas of availability and performance. 

AHF is used by Oracle in the cloud to support the Autonomous Database, and it's available for you as part of your existing software support agreement at no extra cost.

Autonomous Health Framework - AHF

You can download AHF by logging into My Oracle Support and searching for "AHF" or you can just go to http://bit.ly/oracleahf, which will take you directly to the download page.

 

AHF Diagnostic Collection

AHF is capable of collecting various types of diagnostic collections, with problem specific SRDCs being one of them.

Before AHF, Oracle Support might have asked you to go through 8 different steps as part of SRDC or Service Request Data Collection for ORA-04031. This would involve collecting and trimming different logs and running scripts, to understand how to perform it correctly required watching a video and reading other knowledge documents.

Now all this SRDC logic is packaged within AHF and you can capture it all with a single command.

 

Login to the machine where the ORA-04031 occurred and as the Oracle user and run the command:

tfactl diagcollect –srdc ORA-04031

You'll be prompted to enter the date and time of the ORA-04031 you're interested in, and then the database name

For example

$ tfactl diagcollect -srdc ora4031
Enter the time of the ORA-04031 [YYYY-MM-DD HH24:MI:SS,<RETURN>=ALL] : 2024-02-11 13:33:58
Enter the Database Name [Required for this SRDC] : CDB12

Components included in this collection: OS DATABASE CHMOS SOSREPORT

Preparing to execute support diagnostic scripts.
 Executing DB Script srdc_db_ora4031.sql on CDB12 with timeout of 300 seconds...

Collecting data for all nodes

TFA is using system timezone for collection, All times shown in PST.
Scanning files from 2023-10-27 13:03:58 PDT to 2023-10-27 14:03:58 PDT

Collection Id : 20240212112211mymachine

Detailed Logging at : /opt/oracle.ahf/data/repository/srdc_ora4031_collection_Mon_Feb_12_11_22_14_PST_2024_node_all/diagcollect_20240212112211_mymachine.log

Waiting up to 120 seconds for collection to start
2024/02/12 11:22:20 PST : NOTE : Any file or directory name containing the string .com will be renamed to replace .com with dotcom
2024/02/12 11:22:20 PST : Collection Name : tfa_srdc_ora4031_Mon_Feb_12_11_22_13_PST_2024.zip
2024/02/12 11:22:20 PST : Collecting diagnostics from hosts : [mymachine2, mymachine]
2024/02/12 11:22:21 PST : Collecting Additional Diagnostic Information...
2024/02/12 11:22:21 PST : Scanning of files for Collection in progress...
2024/02/12 11:22:31 PST : Getting list of files satisfying time range [10/27/2023 13:03:58, 10/27/2023 14:03:58]
2024/02/12 11:22:36 PST : Executing DB Script runawr on cdb12 with timeout of 3600 seconds...
2024/02/12 11:22:42 PST : Executing TFA rdahcve with timeout of 600 seconds...
2024/02/12 11:22:51 PST : Collecting ADR incident files...
2024/02/12 11:24:20 PST : Executing IPS Incident Package Collection(s)...
2024/02/12 11:24:22 PST : Unexpected Error from ADR, please review the <hostname>_collection.log for details and consult Oracle Support if necessary
2024/02/12 11:24:22 PST : Executing SQL Script db_feature_usage.sql on cdb12 with timeout of 600 seconds...
2024/02/12 11:24:22 PST : Executing Collection for OS with timeout of 1800 seconds...
2024/02/12 11:24:29 PST : Executing Collection for SOSREPORT with timeout of 1860 seconds...
2024/02/12 11:25:29 PST : Completed Collection of Additional Diagnostic Information...
2024/02/12 11:25:32 PST : Completed Local Collection
2024/02/12 11:25:32 PST : Not Redacting this Collection on Exadata with no redaction option passed ..
2024/02/12 11:25:32 PST : Not Redacting this Collection ...
2024/02/12 11:25:32 PST : Remote Collection in Progress...
2024/02/12 11:26:13 PST : Collection completed on host: mymachine2 
2024/02/12 11:26:13 PST : Collection completed on host: mymachine 
2024/02/12 11:26:12 PST : Completed collection of zip files.

.---------------------------------------.
|           Collection Summary          |
+-------------+-----------+------+------+
| Host        | Status    | Size | Time |
+-------------+-----------+------+------+
| mymachine2  | Completed | 14MB | 182s |
| mymachine   | Completed | 27MB | 192s |
'-------------+-----------+------+------'

Logs are being collected to: /opt/oracle.ahf/data/repository/srdc_ora4031_collection_Mon_Feb_12_11_22_14_PST_2024_node_all
/opt/oracle.ahf/data/repository/srdc_ora4031_collection_Mon_Feb_12_11_22_14_PST_2024_node_all/mymachine.tfa_srdc_ora4031_Mon_Feb_12_11_22_13_PST_2024.zip
/opt/oracle.ahf/data/repository/srdc_ora4031_collection_Mon_Feb_12_11_22_14_PST_2024_node_all/mymachine2.tfa_srdc_ora4031_Mon_Feb_12_11_22_13_PST_2024.zip

 

Once it's finished AHF will package everything for you in a zip file for each machine, as you progress you'll only need the one from the node where the problem occurred.

 

Now we can move on to step number two. Use the My Oracle Support ORA-04031 troubleshooting tool to find recommendations. 

 

2. Use ORA-04031 Troubleshooting Tool to find recommendations

Log into My Oracle Support and search for "ORA-04031" or alternatively go to bit.ly/ORA-04031 to access it directly.

When you get to the troubleshooting tool click the Next button at the top right

ORA-04031 MOS Troubleshooting tool 1

Select the first radio button to choose to upload a TFA package. (TFA is one of the components of AHF)

 

Then click the "Choose file" button, select the zip from the failing node that AHF captured for you in step 1.

Then press the "Upload" button.

 

ORA-04031 MOS Troubleshooting tool 2

 

Once this is uploaded click the "Next" button at the top right again.

ORA-04031 MOS Troubleshooting tool 3

The troubleshooting tool will then analyze the contents of the diagnostic collection and compare the log entries against it's list of known problems and recommend a solution.

ORA-04031 MOS Troubleshooting tool 4

If you go through the MOS troubleshooting tool and can't find a solution, or you just need some more help, then Oracle Support is just a click away.

 

3. Log a new SR using the diagnostic collection

Press the "Create SR" button at the bottom.

ORA-04031 Create SR Step 1

You'll then be prompted to clarify your:

  • Product
  • Product Version
  • Support Identifier
  • Operating System
  • SR severity

Then press the "Create SR" button.

And you'll get a new SR number.

ORA-04031 Create SR Step 2

 

That's it!

The AHF diagnostic collection you uploaded originally will be attached to your SR and Oracle Support will take over.

 

Get started now

To get started today download Oracle Autonomous Health Framework.

Gareth Chapman

Senior Principal Program Manager

Gareth is Senior Principal Program Manager for DevOps within Oracle Autonomous Health Framework.


Previous Post

Autonomous Health Framework (AHF) Version 24.2 Released

Gareth Chapman | 4 min read

Next Post


Introducing Zero to low-cost Autonomous Database for Developers

Simon Law | 3 min read