ORA-00821: Specified value of sga_target 100M is too small, needs to be at least 116M

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This article applies to Oracle 10g R1 and R2, including XE.
Even if it is going to be published on the 1-st of April, it's not an hoax ;-)

Although i conducted my test on a Windows box, i am confident that most of it still holds true on Linux platforms as well, once you have adapted some minor details like the slashes (/) in place of the backslashes (\) and after substituting the folder %ORACLE_HOME%/database with its unix platform counterpart $ORACLE_HOME/dbs.

This error occurs at instance startup when an incompatible combination of values for SGA_TARGET and at least one of the following four parameters: DB_CACHE_SIZE, JAVA_POOL_SIZE, SHARED_POOL_SIZE and LARGE_POOL_SIZE have been specified either in the relevant SPFILE or PFILE.

On Oracle 10g for Windows, trace of this error can be found in the file oradim.log, located in %ORACLE_HOME%\database.

So, let's assume here to be working on an XE version, but I've got the same results with 10gR1 on Windows 2000.

Windows Service OracleServiceXE is listed as started however the instance is down and ordinary users, obviously, cannot connect to the database (they are probably trying to call you on the phone...).

In order to properly start the database instance, you need to override the default SPFILE containing the bogus values.
Supposing you didn't make a backup of the SPFILE (either in its semi-binary format or in clear text as a PFILE), you can proceed as follows:

1. open the default SPFILE called SPFILEXE.ORA in %ORACLE_HOME%\database with a text editor;
   
2. copy the rows written in clear text to the clipboard and save them in a new text file called safeXE.ora;
3. change the line(s) containing the faulty parameters (scroll further down for guidance on the values that you should specify);
4. put this file in the same directory %ORACLE_HOME%\database;
5. connect to the idle oracle instance from an OS command prompt: sqlplus "sys/pwd as sysdba";
6. startup pfile=%ORACLE_HOME%\database\safeXE.ora restrict;
   

Restricted mode will prevent ordinary users to connect while you are still working on the db.

Once the db is up, save the changes back to the default SPFILE:

create spfile from pfile='%ORACLE_HOME%/database/safeXE.ora';
shutdown immediate
startup

and there you go.

And now for the explanation of the faulty values:
you have specified the parameter SGA_TARGET in the SPFILE (and also in the PFILE).
As a consequence Oracle is trying to allocate the memory for the various buffers automatically, but without exceeding the limit you imposed on the SGA (say 100M).
However, if you got this error, there are probably other parameters specified in the SPFILE like:
DB_CACHE_SIZE, JAVA_POOL_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE.
If any of these is set to a number greater than zero, the value will be considered the lowest acceptable boundary, for instance:

alter system set db_cache_size=50M scope=spfile;
alter system set shared_pool_size=50M scope=spfile;

when you try to restart the db, you'll get:

ORA-00821: Specified value of sga_target is too small, needs to be at least 116M

which means that you cannot use up all the SGA allowance for the db cache and the shared pool, because Oracle needs another 16Mb of RAM, some of which are needed for other buffers and some for adjusting the number of units to the nearest SGA granule size (multiple of 4Mb on my machine).
So, you have at least three choices here:

1. to decrease by 16Mb either of the values (or the sum thereof);
2. to leave Oracle free to allocate the required buffers by commenting out those two parameters (prepend a pound # sign to the line to transform the text into a comment);
3. to increase, if you have free memory available, SGA_TARGET and make it at least 116Mb or larger.

Final considerations:
i could not start an instance of Oracle 10gR1 with less than 48M of SGA_TARGET (all other buffers autoallocated).
In order to run Oracle Application Express (Apex) 3.1 or earlier, there is one requirement in terms of minimum buffer size:

SHARED_POOL_SIZE >= 100M

however there are several articles where they add also (but is not mentioned in the official documentation):

JAVA_POOL_SIZE >= 150M

which implies a minimum SGA_TARGET of 264Mb (but it could be slightly more or slightly less depending on the platform and the round-up to the nearest multiple of the SGA granule size.

Let's spend a few words on this round-up mechanism.
100Mb is multiple of 4Mb (25 *4), but 150Mb isn't a multiple of 4Mb (the granule size on my machine), the nearest multiple is 152Mb (38 * 4Mb). This explains why if i set the value to 152Mb, it will still complain and say that the minimum SGA_TARGET is 264Mb, that is:
66 * 4Mb = (25 + 38 + 3 ) * 4Mb
So, I found out the actual value of the overhead, in this case it's 3 granules, for an extra 12Mb.

In the original example both values were 50Mb, neither of which is a multiple of 4Mb, so Oracle rounded up both buffers to 52Mb (104Mb total), plus the overhead of 3 granules, another 12Mb, which gives the final amount of 116Mb.

You can find out more about this overhead by querying the following view:

select * from V$SGAINFO;

Clearly you don't want to run Apex with only 4Mb of buffer cache, it will be necessary to specify some meaningful value for DB_CACHE_SIZE and increase SGA_TARGET consequently.

See message translations for ORA-00821 and search additional resources.