ORA-00851 – MEMORY_TARGET

ORA-00851:SGA_MAX_SIZE **** cannot be set to be more than MEMORY_TARGET ****

This is a real-life scenario on a production system. I was trying to use the 11gR2 AMM feature while setting up the OEM Grid repository. When I came across this error, I proceeded to increase the MEMORY_TARGET size on the gsrep instance to be at least double the SGA_MAX_SIZE.

When I connected to an idle instance and tried to startup using the init file:

startup pfile=’/home/ora_rep/app/product/11.2.0/dbs/initgsrep.ora’;

I got another error:

ORA-00845: MEMORY_TARGET not supported on this system

This error arises when we try to use the Automatic Memory Management (AMM) feature of 11gR2 while the shared memory filesystem (shmfs) is not sized to be big enough.

Corrective steps:

  1. Log in as root and issue a

$ df –h

(right-click on image below and open link in new tab to have a clearer view)

df -h

When I initially checked, the highlighted value was 2G significantly smaller than is required to support the value we are setting for TARGET_MEMORY so as to accommodate SGA_MAX_SIZE.

  1. The size of tmpfs from 2 to a more appropriate number of say 12g by issuing the following command as root:

$ mount –t tmpfs shmfs –o size=12g /dev/shm

This remounts the the shmfs file system with the size 12g.

  1. This change is never persistent until the fstab file in the etc directory is updated

$ vi /etc/fstab

(right-click on image below and open link in new tab to have a clearer view) 

 

vi etc_fsatab_

Save the changes [shift zz]

  1. Connect to an ideal instance and startup the database by passing the pfile path since the default spfile with which the database starts has not been updated with the parameter changes:

SQL> startup pfile=’/home/ora_rep/app/product/11.2.0/dbs/initgsrep.ora’;

  1. Ensure that your spfile is equally updated for subsequent startups as well as to keep both in sync (above all else).

SQL> create spfile=’/home/ora_rep/app/product/11.2.0/dbs/spfilegsrep.ora’ from            pfile=’/home/ora_rep/app/product/11.2.0/dbs/initgsrep.ora’;

File created.

SQL>

References: http://www.krenger.ch/blog/ora-00845-memory_target-not-supported-on-this-system/

Leave a comment