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:
- http://askdba.org/weblog/2008/04/tweaking-_shared_pool_reserved_min_alloc-and-ora-4031/
- 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.