1. Oracle Autonomous Health Framework User's Guide
  2. Resolve ORA-04031 Unable to Allocate Shared Memory Errors

Resolve ORA-04031 Unable to Allocate Shared Memory Errors

For more information and videos, see https://blogs.oracle.com/database/post/ora-04031.

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

ORA-04031 occurs when an Oracle Database process requires additional shared memory but sufficient memory is not available.

The error message identifies the amount of memory that could not be allocated, the affected memory pool, and information about the failed allocation.

When a database process encounters the error, Oracle Database records the event in the database instance alert log. The alert log points to a trace file that contains additional diagnostic information about the process that encountered the problem.

If a user process encounters the error, an entry might not be recorded in the database alert log.

The process that reports ORA-04031 is often affected by an underlying memory problem rather than being the source of the problem itself.

Possible causes include:

  • Insufficient memory-related initialization parameter settings
  • Automatic memory tuning issues
  • Memory fragmentation caused by application design
  • Software bugs or memory leaks

For additional information and videos about troubleshooting ORA-04031, see the Oracle Database resources for ORA-04031 errors.

Troubleshoot ORA-04031

Use the following workflow to diagnose and resolve an ORA-04031 error:

  1. Use Oracle Autonomous Health Framework (AHF) to generate an ORA-04031 Service Request Data Collection (SRDC).
  2. Upload the diagnostic collection to the My Oracle Support ORA-04031 troubleshooting tool and review the recommendations.
  3. If the problem cannot be resolved using the recommendations, create a Service Request (SR) and use the diagnostic collection to assist Oracle Support.

Generate an ORA-04031 Diagnostic Collection

Log in to the system where the ORA-04031 error occurred.

As the Oracle software owner, run:

tfactl diagcollect -srdc ORA-04031

AHF prompts you to specify the date and time when the ORA-04031 occurred and 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

AHF collects the diagnostic information required to investigate the error and creates a ZIP file for each system included in the collection.

For subsequent troubleshooting, use the diagnostic ZIP file generated for the node on which the ORA-04031 problem occurred.

Review Recommendations in My Oracle Support

After generating the AHF diagnostic collection, use the My Oracle Support ORA-04031 troubleshooting tool to analyze the problem.

  1. Sign in to My Oracle Support.
  2. Search for ORA-04031, or open the ORA-04031 troubleshooting tool directly.
  3. Proceed to the diagnostic collection step.
  4. Select the option to upload a TFA package.
  5. Select the ZIP file that AHF generated for the node on which the error occurred.
  6. Upload the file.
  7. After the upload completes, proceed to the next step in the troubleshooting tool.

The troubleshooting tool analyzes the contents of the diagnostic collection and compares information in the logs with known ORA-04031 problems.

Review the recommended solution or solutions returned by the troubleshooting tool and determine whether they resolve the issue.

Create a Service Request

If the My Oracle Support troubleshooting tool does not identify an appropriate solution, or if you require additional assistance, create a Service Request with Oracle Support.

  1. Select Create SR in the troubleshooting tool.

  2. Provide the requested information, including:

    • Product
    • Product version
    • Support Identifier
    • Operating system
    • SR severity
  3. Select Create SR.

My Oracle Support creates the Service Request and assigns an SR number.

The AHF diagnostic collection that was uploaded during troubleshooting is associated with the Service Request so that Oracle Support can use the collected diagnostic information to continue the investigation.