IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
I got “ORA-27106: system pages not available to allocate memory” error in Oracle database during startup of instance on Exadata.
ORA-27106: system pages not available to allocate memory
Details of error are as follows.
[MSDB4]/home/oracle $ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 22 13:23:26 2022 Version 19.14.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORA-27106: system pages not available to allocate memory Additional information: 6187 Additional information: 3 SQL>
Sometimes you may need restart of Oracle database, if you got ORA-27106 error, it means there are system pages not available to allocate memory. So you need to release this memory by killing the related zombie process.
Run the IPCS command to list Interprocess Communication (IPC) status as follows.
[root@msdbadm04 ~]# ipcs -a ------ Message Queues -------- key msqid owner perms used-bytes messages ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x00000000 196608 grid 600 8908800 92 0x00000000 229377 grid 600 10703863808 46 0x60636b6c 262146 grid 600 53248 46 0x2eafa3ac 4554755 grid 600 12288 42 0x00000000 399802372 oracle 600 37748736 2 dest 0x00000000 399835141 oracle 600 214211493888 6 dest 0x00000000 399867910 oracle 600 209715200 2 dest 0x00000000 399900679 oracle 600 2097152 2 dest 0x00000000 16515080 zabbix1 600 576 6 dest 0x00000000 16547849 zabbix1 600 7081056 6 dest ------ Semaphore Arrays -------- key semid owner perms nsems 0xf60f3e88 131072 grid 600 1024 0xf60f3e89 163841 grid 600 1024 0xf60f3e8a 196610 grid 600 1024 0x8d342720 327683 grid 600 124 [root@msdbadm04 ~]#
You should find the process of destroyed process using their shmid as follows.
[root@msdbadm04 ~]# [root@msdbadm04 ~]# lsof | grep 399835141 oracle_19 194509 oracle DEL REG 0,15 399835141 /SYSV00000000 oracle_24 247335 oracle DEL REG 0,15 399835141 /SYSV00000000 [root@msdbadm04 ~]# [root@msdbadm04 ~]#
Now kill these process and list their status if they are already destroyed or not.
[root@msdbadm04 ~]# [root@msdbadm04 ~]# kill -9 194509 247335 [root@msdbadm04 ~]# [root@msdbadm04 ~]# [root@msdbadm04 ~]# lsof | grep 399835141 [root@msdbadm04 ~]# [root@msdbadm04 ~]# ipcs -a ------ Message Queues -------- key msqid owner perms used-bytes messages ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x00000000 196608 grid 600 8908800 94 0x00000000 229377 grid 600 10703863808 47 0x60636b6c 262146 grid 600 53248 47 0x2eafa3ac 4554755 grid 600 12288 42 0x00000000 16515080 zabbix1 600 576 6 dest 0x00000000 16547849 zabbix1 600 7081056 6 dest ------ Semaphore Arrays -------- key semid owner perms nsems 0xf60f3e88 131072 grid 600 1024 0xf60f3e89 163841 grid 600 1024 0xf60f3e8a 196610 grid 600 1024 0x8d342720 327683 grid 600 124 [root@msdbadm04 ~]#
Now our memory spaces are released, we can startup the database again as follows.
[MSDB4]/home/oracle $ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 22 13:25:46 2022 Version 19.14.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 2.1446E+11 bytes Fixed Size 37223248 bytes Variable Size 1.3314E+11 bytes Database Buffers 8.1068E+10 bytes Redo Buffers 207720448 bytes Database mounted. Database opened. SQL>
Other Case:
Getting the following message when trying to increase the memory parameters in oracle 12c database.
SQL> alter system set memory_max_target=20G scope=spfile; System altered. SQL> alter system set memory_target=20G scope=spfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORA-27106: system pages not available to allocate memory Additional information: 5846 Additional information: 1
Once the above error is thrown, details of the error can be seen in the alert log file
Fri Apr 07 11:38:26 2017 Adjusting the requested value of parameter parallel_max_servers from 409 to 401 due to the value of parameter processes (500) Starting ORACLE instance (normal) (OS id: 54926) Fri Apr 07 11:38:26 2017 CLI notifier numLatches:29 maxDescs:862 Fri Apr 07 11:38:26 2017 ERROR: Failed to get available system pages to allocate memory............................. Fri Apr 07 11:38:26 2017 ********************************************************************** Fri Apr 07 11:38:26 2017 Dump of system resources acquired for SHARED GLOBAL AREA (SGA) Fri Apr 07 11:38:26 2017 Per process system memlock (soft) limit = 64K....................................culprit Fri Apr 07 11:38:26 2017 Expected per process system memlock (soft) limit to lock SHARED GLOBAL AREA (SGA) into memory: 20G Fri Apr 07 11:38:26 2017 Available system pagesizes: 4K, 2048K Fri Apr 07 11:38:26 2017 Supported system pagesize(s): Fri Apr 07 11:38:26 2017 PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s) Fri Apr 07 11:38:26 2017 4K Configured 5242887 1293 NONE Fri Apr 07 11:38:26 2017 Reason for not supporting certain system pagesizes: Fri Apr 07 11:38:26 2017 2048K - Dynamic allocate and free memory regions Fri Apr 07 11:38:26 2017 RECOMMENDATION: Fri Apr 07 11:38:26 2017 1. Increase per process memlock (soft) limit to at least 20GB to lock 100% of SHARED GLOBAL AREA (SGA) pages into physical memory Fri Apr 07 11:38:26 2017 **********************************************************************
To further confirm the information in the alert log file, check the ulimit for the User.
oracle@/home/oracle> ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 516119 max locked memory (kbytes, -l) 64....................................................The same as soft limit warning in the alert log max memory size (kbytes, -m) unlimited open files (-n) 65536 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) unlimited cpu time (seconds, -t) unlimited max user processes (-u) 1024 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited
The value of memlock limit is not sufficient to lock 100% of SHARED GLOBAL AREA (SGA) pages into physical memory.
Ensure the soft and hard values in kilobytes of memlock that are configured in /etc/security/limits.conf are slightly smaller than the amount of installed memory and larger than the MEMORY_MAX_TARGET. For example, if the system has 64GB of RAM, the values shown here would be appropriate:
soft memlock 60397977 hard memlock 60397977
Log in as the Oracle account owner (usually oracle) and use the following command to verify the value of memlock:
$ ulimit -l
Change memory_target to the new value.
Other Case:
On 12.1.0.2 version, Real Application Cluster, instance may not come up and fails with below error
[oracle@*******-db ~]$ srvctl start instance -d ******* -i ******* PRCR-1013 : Failed to start resource ora.***prd.db PRCR-1064 : Failed to start resource ora.***prd.db on node ******* -db CRS-5017: The resource action "ora.***prd.db start" encountered the following error: ORA-27106: system pages not available to allocate memory Additional information: 5845 Additional information: 1 . For details refer to "(:CLSN00107:)" in "/grid/grid_base/diag/crs/*******-db/crs/trace/crsd_oraagent_oracle.trc". CRS-2674: Start of 'ora.***prd.db' on '*******-db' failed
Parameter file will contain the following configuration and RAC instances may not come up when the huge pages configurations are not set to appropriate values.
.use_large_pages='ONLY'
The issue happen due when setting the parameter use_large_pages = only, which means that the database won’t start if there aren’t enough large pages on the system for the configured sga.
As per the reference 1392497.1:
– The default value of “true” preserves the current behavior of trying to use hugepages if they are available on the OS.
– Setting it to “false” means do not use hugepages;
– A setting of “only” means do not start up the instance if hugepages cannot be used for the whole memory
In order to solve the issue the options would be the following:
a. set the use_large_pages = false;
b. increase the sga to the desired values or use V$SGA_TARGET_ADVICE which will provide appropriate values for SGA
c. compute the recommended huge pages (using the script) for the new value
Oracle Linux: Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration (Doc ID 401749.1)
d. set the huge pages to the recommended value as per the script output
e. change the value of the parameter use_large_pages back to ONLY
—OR—
a. increase the value for huge_pages to a higher one than what is configured now (to allow the instance to start)
b. increase the sga to the desired value
c. compute the correct value for the huge_pages (using the script) and make the changes.
-or-
Apart from the script, you can calculate the number of hugePages required to be configured in the following way,
HugePages=(Greater than sum of all SGA’s of databases)/HugePagesize
For example,
Let’s consider if A and B databases are running in the server.
For database A, if you have SGA_TARGET=20 GB
For database B, if you have SGA_TARGET=15 GB,
Your hugePage size is 2 MB
Then Number of huge pages might be required actually,
HugePages=(20 GB+15 GB)/2MB
=35GB/2MB
=35*1024 MB/2MB
=17920
Then you can specify at the server level slightly greater than above value let’s say 18000 to accommodate both database’s SGAs. And it is recommended to have the higher value for HugePages compared to what we get using the above calculations.
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )
