Thursday 22 November 2012

Fixing ORA-00838: Specified value of MEMORY_TARGET is too small

I had an interesting surprise today as I restarted the VM I'm using to run through the 2 Day DBA online tutorial Oracle offer and i hit this error...

SQL> startup
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 1172M

After a quick google,, it seems this configuration option lives in the spfile which can't be directly edited. The way to change settings is to export the spfile into a pfile, edit it, and then re-import the settings.

SQL> create pfile='/tmp/pfile.backup' from spfile;

File created.

From another session, alter the setting from the bad value to the value recommended. In my case this bad setting was set as *.memory_target=536870912. Doing the maths on the 1172M returned by the error message it needs to be  1172*1024*1024 = 1228931072.

-bash-4.1$ vi /tmp/pfile.backup

*.memory_target=1228931072

Startup sqlplus, and re-import the pfile settings into the spfile

SQL> create spfile from pfile='/tmp/pfile.backup';

File created.



In my case, whatever had happened, this needed adjusting again - it recommends a new value again.

SQL> startup
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 1240M

So, repeat the process again, 1240*1024*1024 = 1300234240

-bash-4.1$ vi /tmp/pfile.backup

*.memory_target=1300234240

Fire up sqlplus again...

SQL> create spfile from pfile='/tmp/pfile.backup';

File created.


SQL> startup
ORACLE instance started.

Total System Global Area  534462464 bytes
Fixed Size                  2212736 bytes
Variable Size             524291200 bytes
Database Buffers            4194304 bytes
Redo Buffers                3764224 bytes
Database mounted.
Database opened.

No comments: