ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0What 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)
- 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 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:
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;
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 ...
Thanks Rainer (idk how to reply to comments here)
It solved my problem.
Post a Comment