ORA-04031: unable to allocate ** bytes of shared memory and could not startup the database
Posted by Mir Sayeed Hassan on December 20th, 2021
ORA-04031: unable to allocate ** bytes of shared memory and could not startup the database
This ORA error occur due the less shared memory allocated in shared pool.
Start up database failed
SQL> startup ORA-01012: not logged on Process ID: 0 Session ID: 0 Serial number: 0
Check the current available memory assign to this server
[oracle@pri-db ~]$ free -m
total used free shared buff/cache available
-----------------------------------------------------------------
Mem: 96173 5124 12591 1298 78457 88898
Swap: 32767 380 32387
Check the alert log of database
[oracle@pri-db ~]$ tail -100 /u01/app/oracle/diag/rdbms/pri-db/pri-db/trace/alert_pri-db.log
ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^436","kglseshtTable")
2021-12-20T13:45:42.660699+03:30
Process J000 died, see its trace file
2021-12-20T13:45:42.660817+03:30
kkjcre1p: unable to spawn jobq slave process
2021-12-20T13:45:42.660912+03:30
Errors in file /u01/app/oracle/diag/rdbms/pri-db/pri-db/trace/pri-db_cjq0_1405375.trc:
2021-12-20T13:45:46.326490+03:30
Check the pmon process running on database or not
[oracle@pri-db ~]$ ps -ef | grep pmon oracle 8639 1 0 2020 ? 01:02:21 ora_pmon_pri-db oracle 2031986 2030966 0 13:52 pts/2 00:00:00 grep --color=auto pmon
To resolve this issue & bring up the database then changed the shared memory into it.
Use the orphaned shared memory segments by using the sysresc utility
This SYSRESV Command will list the currently allocated IPC resources for shared memory & its remove the shared memory segments
[oracle@pri-db ~]$ sysresv IPC Resources for ORACLE_SID "pri-db" : Maximum shared memory segment size (shmmax): 4398046511104 bytes Total system shared memory (shmall): 4398046511104 bytes Total system shared memory count (shmmni): 4096 *********************** Dumping ipcs output ******************** ------ Message Queues -------- key msqid owner perms used-bytes messages ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x00000000 2 gdm 600 16384 1 dest 0x00000000 5 gdm 600 3145728 2 dest 0x00000000 6 zabbix 600 576 5 dest 0x00000000 7 zabbix 600 2409056 5 dest 0x00000000 8 oracle 600 9142272 477 0x00000000 9 oracle 600 738197504 239 0x00000000 10 oracle 600 11829248 239 0x7752edcc 11 oracle 600 20480 239 ------ Semaphore Arrays -------- key semid owner perms nsems 0xf615aa08 7 oracle 600 250 0xf615aa09 8 oracle 600 250 0xf615aa0a 9 oracle 600 250 0xf615aa0b 10 oracle 600 250 0xf615aa0c 11 oracle 600 250 *********************** End of ipcs command dump ************** ***************** Dumping Resource Limits(s/h) ***************** core file size UNLIMITED/UNLIMITED data seg size UNLIMITED/UNLIMITED scheduling priority 0 KB/0 KB file size UNLIMITED/UNLIMITED pending signals 375 KB/375 KB max locked memory 128 GB/128 GB max memory size UNLIMITED/UNLIMITED open files 64 KB/64 KB POSIX message queues 800 KB/800 KB real-time priority 0 KB/0 KB stack size 32 MB/32 MB cpu time UNLIMITED/UNLIMITED max user processes 16 KB/16 KB virtual memory UNLIMITED/UNLIMITED file locks UNLIMITED/UNLIMITED ***************** End of Resource Limits Dump ****************** Maximum map count configured per process: 65530 Total /dev/shm size: 50422767616 bytes, used: 0 bytes Shared Memory: ID KEY 9 0x00000000 10 0x00000000 8 0x00000000 11 0x7752edcc Semaphores: ID KEY 7 0xf615aa08 8 0xf615aa09 9 0xf615aa0a 10 0xf615aa0b 11 0xf615aa0c Oracle Instance alive for sid "pri-db"
Try to Kill the Shared Memory process
[oracle@pri-db ~]$ ipcrm -m 9 [oracle@pri-db ~]$ ipcrm -m 10 [oracle@pri-db ~]$ ipcrm -m 8 [oracle@pri-db ~]$ ipcrm -m 11
Try to Kill the Semaphore Process
[oracle@pri-db ~]$ ipcrm -m 7
SQL> startup mount ORACLE instance started. Total System Global Area 759167328 bytes Fixed Size 9140576 bytes Variable Size 603979776 bytes Database Buffers 134217728 bytes Redo Buffers 11829248 bytes Database mounted.
SQL> alter database open; Database altered.
As we could see with below status of SGA & PGA are allocated., It was too less
SQL> show parameter sga_ NAME TYPE VALUE --------------------------------------------------- sga_max_size big integer 480M sga_min_size big integer 0 sga_target big integer 0 unified_audit_sga_queue_size integer 1048576
SQL> show parameter pga NAME TYPE VALUE -------------------------------------------------- pga_aggregate_limit big integer 1024M pga_aggregate_target big integer 1024M
SQL> show parameter memory_target NAME TYPE VALUE --------------------------------------------- memory_target big integer 0
To overcome this issue., reconfigure from SGA & PGA to Memory_target or set the SGA & PGA size as per expected
SQL> alter system set sga_target=0; System altered.
SQL> alter system set sga_max_size=0 scope=spfile; System altered.
SQL> alter system set pga_aggregate_target=0 scope=spfile; System altered.
SQL> alter system set memory_target=60g scope=spfile; System altered.
SQL> alter system set memory_max_target=65g scope=spfile; System altered.
To apply into the database., we need to bounce it once & start the database & verify
SQL> shu immediate SQL> startup;
SQL> select status from V$instance; STATUS ------------ OPEN
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- --------------------------------------- 2 PDB$SEED READ ONLY NO 3 SHOP MOUNTED
SQL> alter pluggable database shop open; Pluggable database altered.
Note: In case if you have the PDB’S Configure on database., immediately open the pliggable database as shown above.
Note: You need to update this changes into the vi /etc/fstab for /dev/shm
Note: In case if you database is up., temporary workaround : alter system flush shared_pool;
Login as root & add the below parameter into /etc/fstab
[root@pri-db1 ~]# cat /etc/fstab tmpfs /dev/shm tmpfs defaults,size=25g 0 0
Without reboot of the server., issue the below parameter.
[root@pri-db1 ~]# mount -o remount /dev/shm