Oracle Database is one of the most widely used databases in the world. However, like any other software, it can sometimes throw errors that can be challenging to troubleshoot. One such error is the ORA-00845: MEMORY_TARGET not supported on this system. In this guide, we will walk you through the steps to fix this issue and answer some frequently asked questions about it.
Table of Contents
- Understanding ORA-00845 Error
- Step-by-Step Guide to Fix ORA-00845 Error
- Check /dev/shm Size
- Increase /dev/shm Size
- Modify the Memory_Target Parameter
- FAQs
- Related Links
Understanding ORA-00845 Error
The ORA-00845 error occurs when the Oracle database's MEMORY_TARGET parameter is set to a value greater than the available shared memory on the system. This error is usually encountered on Linux systems where shared memory is managed by the /dev/shm filesystem.
The error message typically looks like the following:
ORA-00845: MEMORY_TARGET not supported on this system
To fix this issue, you need to either increase the shared memory on the system or decrease the MEMORY_TARGET parameter value.
Step-by-Step Guide to Fix ORA-00845 Error
Check /dev/shm Size
First, you need to check the size of the /dev/shm filesystem. You can do this by running the following command:
df -h /dev/shm
This command will display the size, used space, and available space in the /dev/shm filesystem. Make a note of the available space.
Increase /dev/shm Size
If the available space in /dev/shm is less than the MEMORY_TARGET parameter value, you need to increase the size of the /dev/shm filesystem. You can do this by modifying the /etc/fstab file.
Open the /etc/fstab file in your favorite text editor:
sudo nano /etc/fstab
Look for the following line:
tmpfs /dev/shm tmpfs defaults 0 0
Modify the line to include the desired size for the /dev/shm filesystem. For example, to set the size to 8 GB, you would change the line to:
tmpfs /dev/shm tmpfs defaults,size=8g 0 0
Save the changes and exit the text editor.
Next, remount the /dev/shm filesystem with the new size by running the following command:
sudo mount -o remount /dev/shm
Verify the new size by running the df -h /dev/shm command again.
Modify the Memory_Target Parameter
If increasing the /dev/shm size is not possible or not enough, you can modify the MEMORY_TARGET parameter value in the Oracle database to a lower value.
To do this, connect to the Oracle database as the SYS user:
sqlplus / as sysdba
Then, run the following command to set the new MEMORY_TARGET value:
ALTER SYSTEM SET MEMORY_TARGET = NEW_VALUE SCOPE=SPFILE;
Replace NEW_VALUE with the desired value for the MEMORY_TARGET parameter. Make sure it is lower than the available space in the /dev/shm filesystem.
Finally, restart the Oracle database to apply the changes:
sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
STARTUP;
FAQs
1. What is the MEMORY_TARGET parameter in Oracle?
The MEMORY_TARGET parameter in Oracle is a database initialization parameter that specifies the total amount of memory to be used by the System Global Area (SGA) and Program Global Area (PGA). It enables dynamic allocation and deallocation of memory between the SGA and PGA, improving overall database performance.
2. What is /dev/shm on Linux systems?
/dev/shm is a shared memory filesystem on Linux systems that provides an efficient way for processes to share data. It is a part of the POSIX shared memory implementation and is used by various applications, including Oracle Database, for inter-process communication.
3. Can I set MEMORY_TARGET to a value greater than the physical memory on my system?
No, it is not recommended to set MEMORY_TARGET to a value greater than the physical memory on your system. Doing so may cause performance issues and other problems, such as excessive swapping and out-of-memory errors.
4. Can I set MEMORY_TARGET to a value lower than the total memory allocated to the SGA and PGA?
Yes, you can set MEMORY_TARGET to a value lower than the total memory allocated to the SGA and PGA. However, this may cause performance issues and other problems, such as excessive swapping and out-of-memory errors.
5. Can I disable MEMORY_TARGET in Oracle Database?
Yes, you can disable MEMORY_TARGET in Oracle Database by setting its value to 0. This will revert the memory management to manual mode, where you will need to set the SGA and PGA memory allocation separately using the SGA_TARGET and PGA_AGGREGATE_TARGET parameters.