ORA-27104: system-defined limits for shared memory was misconfigured problem handling

(1) Reason

Increase the memory of the oracle 12c database, adjust the SGA size, from 8G to 16G

alter system set sga_max_size = 12G scope=spfile;
alter system set sga_target = 12G scope=spfile;
alter system set pga_aggregate_target = 4G scope=spfile;

Close the database and restart:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-27104: system-defined limits for shared memory was misconfigured

(2) Environment

oracle version:

SQL> select banner from v$version;

BANNER
————————————————– —————————— Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit
Production PL/SQL Release 12.2.0.1.0 – Production CORE 12.2.0.1.0
Production TNS for Linux: Version 12.2.0.1.0 – Production NLSRTL
Version 12.2.0.1.0 – Production

Key memory parameters:

SQL> show parameter memory_

NAME TYPE VALUE
------------------------------------ ----------- --- ---------------------------
hi_shared_memory_address integer 0
inmemory_adg_enabled boolean TRUE
inmemory_clause_default string
inmemory_expressions_usage string ENABLE
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 0
inmemory_query string ENABLE
inmemory_size big integer 0
inmemory_trickle_repopulate_servers_ integer 1
percent
inmemory_virtual_columns string MANUAL

NAME TYPE VALUE
------------------------------------ ----------- --- ---------------------------
memory_max_target big integer 0
memory_target big integer 0
optimizer_inmemory_aware boolean TRUE
shared_memory_address integer 0

Both memory_max_target and memory_target are 0.
Set Oracle memory through SGA and PGA parameters.

The shm size is 24G, which is enough.

df -h /dev/shm
Filesystem Size Used Avail Use% Mounted on
tmpfs 24G 84K 24G 1% /dev/shm

(3) Oracle memory management concepts

<1>The difference between AMM and ASMM

the difference:
AMM (Automatic Memory Management) automatic memory management; (features only available in 11G)
ASMM (Automatic shared Memory Management) automatic shared memory management; (10G some features)
AMM does not support HugePage, while ASMM supports HugePage;
AMM allows the database to fully manage the size of SGA and PGA;
ASMM can only manage the size of the SGA.

<2>Enable AMM

Enable AMMs:
Set MEMORY_TARGET to a non-zero value to enable it. It will automatically adjust SGA and PGA.
Note: If SGA and PGA are also set manually, it means that the automatic adjustment is not less than the value set manually.

The relationship between AMM memory parameters:
If memory_target is set to a non-zero value (it means AMM is started with automatic memory management)

Case 1:
The size of sga_target and pga_aggregate_target has been set, then the automatically adjusted value of these two parameters will not be less than the set size
memory_target = sga_target + pga_aggregate_target

Case 2:
sga_target sets the size, pga_aggregate_target does not set the size
Then pga_aggregate_target initialization value = memory_target-sga_target

Case 3:
sga_target does not set the size, pga_aggregate_target sets the size
Then sga_target initialization value = memory_target-pga_aggregate_target

Case 4:
Neither sga_target nor pga_aggregate_target has a size set
Oracle will allocate size based on database health. But there will be a default ratio when the database starts:
sga_target = memory_target60%
pga_aggregate_target = memory_target40%

<3>Enable ASMM

Enable ASMM:
Set MEMORY_TARGET to 0, SGA_TARGET to non-zero, and STATISTICS_LEVEL to TYPICAL (default value) or ALL to enable the ASMM function.

SGA and PGA can be manually configured according to business requirements.

ASMM memory relation:
If memory_target is not set or = 0 (defaults to 0 in 11g)

Case 1:
sga_target set size (ASMM)
Automatically adjust the memory size of shared pool, buffer cache, redo log buffer, java pool, larger pool, etc. in SGA.
PGA is determined by the size of pga_aggregate_target.
sga and pga cannot grow and shrink automatically.

Case 2:
Neither sga_target nor pga_aggregate_target is set
The size of each component in the SGA must be clearly set, and the size of each component cannot be automatically adjusted.
PGA cannot grow and shrink automatically.

<4>Description of core parameters

MEMORY_MAX_TARGET
1. Indicates the maximum memory size that can be achieved by the oracle instance (that is, the maximum value of the operating system memory that can be used);
2. Static parameters need to be restarted to take effect after modification;
3. If the memory_max_target parameter is not set, the parameter value of memory_target will be automatically taken;
4. The default value is the value of the shm of the system, and the shm value is half of the physical memory of the system.

MEMORY_TARGET
1. Indicates the actual memory size used by the oracle instance, which cannot be greater than memory_max_target;
2. Dynamic parameters can be modified at any time;
3. If memory_target is set to non-0, it means that AMM is enabled. If set to 0, it means that automatic memory management (AMM) is not enabled;
4. To increase memory_target, you need to adjust the value of shm first, then modify memory_max_target, and finally adjust memory_target.

SGA_MAX_SIZE
1. Indicates the maximum memory size that SGA can achieve;
2. Static parameters need to be restarted to take effect after modification.

SGA_TARGET
1. Indicates the actual memory size actually used by the SGA, which cannot be greater than sga_max_size;
2. Dynamic parameters can be modified at any time;
3. If sga_target is set to 0, it means that automatic shared memory management (ASMM) is not enabled, and all related memory components need to be manually set in size (provided that AMM is not enabled)

In a production environment, in order to improve efficiency, SGA parameters are generally set:
SGA_MAX_SIZE=SGA_TARGET .
Similarly, if you set the MEMORY parameter:
MEMORY_MAX_TARGET=MEMORY_TARGET

(3) Troubleshooting process

<1>Check sysctl.conf

/etc/sysctl.conf
Shm parameters adjusted to 24G
kernel.shmmax = 25769803776

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 25769803776
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=262144
net.core.rmem_max = 4194304
net.core.wmem_default=262144
net.core.wmem_max = 1048586

<2>Check shm device

Also 24G, no problem.

# df -h /dev/shm
Filesystem Size Used Avail Use% Mounted on
tmpfs 24G 84K 24G 1% /dev/shm

Try to set SGA=16G again, always report an error.

See log.xml under alter
The 12G has been lowered from 20G, and the error is still reported! ! !

# cat log.xml | grep "cannot support SGA"
 <txt>System cannot support SGA size of 21474836480 bytes
 <txt>System cannot support SGA size of 17179869184 bytes
 <txt>System cannot support SGA size of 17179869184 bytes
 <txt>System cannot support SGA size of 12884901888 bytes
 <txt>System cannot support SGA size of 12884901888 bytes

(4) Problem solving

sysctl.conf pay attention to the relationship between parameters! ! !

You can calculate what the kernel.shmall value should be with a
formula as follows. kernel.shmall = kernel.shmmax / kernel.shmmni

Look at the parameter file:

kernel.shmall = 2097152
kernel.shmmax = 25769803776
kernel.shmmni = 4096

The parameter relationship is incorrect, after adjusting kernel.shmall:

kernel.shmall = 6291456
kernel.shmmax = 25769803776
kernel.shmmni = 4096

Database started successfully! ! !

Finally don’t forget to update the pfile:

create pfile from spfile;