Friday, 19 February 2016

ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0

Today I run into the following error:
ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0
What was the most annoying, it was the fact, I can not start the instance due to this error occurence. In general within a screen session I opened sqlplus session and within it the vim for editing the newly generated pfile. Then I shut down the instance and further I edited some memory settings. So far, so good - I started the instance and got this error. And I did not even specified this parameter in the configuration.
Tried to find something on the internet and on the Metalink:
  1. http://askdba.org/weblog/2008/04/tweaking-_shared_pool_reserved_min_alloc-and-ora-4031/
  2. Ora-00828 With Automatic Shared Memory Management (Doc ID 1251784.1)
I have checked all the possibilities - look for mistakes and misspellings in settings specification and all for nothing. Finally the colleague of mine created a pfile, then created a spfile from that pfile (no changes at all), then started instance with force and voila, the instance worked like a charm.
Not sure what was wrong - the only clue is the possibility I introduced into pfile some hidden character, so the true reason was similar to the one described under the 1st link mentioned above.
Such theory is supported by the following facts:
  • the error message as the upper bound displays 0 (which is nonsense) - however I suppose the lower bound is simply hard coded as 4000 (or 4400) bytes and the upper bound derives its value from the calculation based on the value of shared pool size.
  • assuming something was wrong with the shared pool size specification I suppose it would be set to 0 and hence the upper bound also would be 0 (as any % of 0 is still 0 ;-))
The conclusion: if the upper bound of the ORA-00093 message is set to 0, check Your pfile and spfile byte by byte.
The update Hit this error again, and from this occurrence I draw another conclusion. This is rather not about character in the pfile or spfile. This time the version used is 12c. I set the SGA settings too low and here is the dialog with the database:
-- SGA set to 1G
SQL> startup
ORA-00821: Specified value of sga_target 1024M is too small, needs to be at least 1456M
SQL> create spfile from pfile='db4b.pfile.20170321' ;

File created.

-- SGA increased to 1600M, 1800M, finally 2G (which was the initial setting)
-- still ORA-00093
SQL> startup
ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0
[..]
SQL> startup
ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0

-- and here the only difference - force
SQL> startup force
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size      3712904 bytes
Variable Size   1577060472 bytes
Database Buffers   553648128 bytes
Redo Buffers     13062144 bytes
Database mounted.
Database opened.

As You see the startup force show up as the key step to solve the problem - apparently when I opened sqlplus some settings were established and not cleared on startup failure.

3 comments:

Unknown said...

In my case, I get something different. I've increase

SQL> startup
ORA-00821: Specified value of sga_target 1504M is too small, needs to be at least 2192M
ORA-01078: failure in processing system parameters
SQL> startup force
ORA-00821: Specified value of sga_target 1504M is too small, needs to be at least 2192M
ORA-01078: failure in processing system parameters

It started to appear after I've increased number of processes to 10000 using instruction:
1. Login as sysdba
sqlplus / as sysdba

2. Check Current Setting of Parameters;
sql> show parameter sessions;
sql> show parameter processes;
sql> show parameter transactions;

3. If you are planning to increase "PROCESSES" parameter you should also plan to increase "sessions and "transactions" parameters
A basic formula for determining these parameter values is as follows:

processes=x
sessions=x*1.1+5
transactions=sessions*1.1

4. These paramters can't be modified in memory. You have to modify the spfile only (scope=spfile) and bounce the instance.
alter system set processes=10000 scope=spfile;
alter system set sessions=11005 scope=spfile;
alter system set transactions=12106 scope=spfile;
shutdown abort;
startup;

Rainer Hartwig said...

I got the same issue:
SQL> startup
ORA-00821: Specified value of sga_target 1504M is too small, needs to be at least 2192M
ORA-01078: failure in processing system parameters
due to a typo ..

Thereafter I get this error ora-93 again and again

Finaly I execute
SQL> shutdown abort
SQL> startup
and every thing works fine ...

Abdelhamid Fadel said...

Thanks Rainer (idk how to reply to comments here)
It solved my problem.