We have a server (Linux 5.7, 11.2.0.3 64-bit, 16Gb RAM) which unfortunately has more than Oracle running on it (SAS and R).
The requirements changed recently where both groups of users (Oracle / non-Oracle) came to an agreement about server usage. Oracle users want Thu-Sun and non-Oracle Mon-Wed.
I set the memory_max_size recently to 10G, and was trying to reduce it to 2Gb (for our SAS / R users), but when I do this, I get the error:
PREDICT: SYS AS SYSDBA> alter system set memory_target=2G scope=both;
alter system set memory_target=2G scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 6576M
The database is using an spfile, and when I look at the SGA setting, I see the below:
PREDICT: SYS AS SYSDBA> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 10G
memory_target big integer 10G
shared_memory_address integer 0
PREDICT: SYS AS SYSDBA>
PREDICT: SYS AS SYSDBA>
PREDICT: SYS AS SYSDBA> show sga
Total System Global Area 8551575552 bytes
Fixed Size 2245480 bytes
Variable Size 6241127576 bytes
Database Buffers 2298478592 bytes
Redo Buffers 9723904 bytes
PREDICT: SYS AS SYSDBA>
PREDICT: SYS AS SYSDBA> select pool, sum(bytes) from v$sgastat group by pool;
POOL SUM(BYTES)
------------ ----------
2310447976
java pool 16777216
streams pool 16777216
shared pool 1442840576
large pool 16777216
5 rows selected.
PREDICT: SYS AS SYSDBA> select pool, name, sum(bytes) from v$sgastat where name = 'free memory' group by pool, name;
POOL NAME SUM(BYTES)
------------ -------------------------- ----------
streams pool free memory 15447672
shared pool free memory 176283512
java pool free memory 16777216
large pool free memory 5271552
4 rows selected.
Yeah, sure, I have space to bounce the database, but I was curious why Oracle thinks it can't resize below 6.5G. We have plenty of other databases which survive on less RAM.
Is there some caveat to AMM I'm not aware of (or perhaps my brain isn't functioning after a trip to the dentist this morning)...