Increase memory_target on Oracle DB 12c Linux ORA-00845: MEMORY_TARGET not supported on this system
Aziz Prastyo Wibowo
Assalamu’alaikum Wr. Wb.
In this moment I will share how to resolve error ORA-00845: MEMORY_TARGET not supported on this system when I tried to increase memory target on Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit under platform Red Hat Enterprise Linux 6.5 or Oracle Linux 6.5 64 bit. Detail of machine shown as Table 1.
Table 1: Detail of Machine
| [orekel@rabbit ~]$ uname -a && lsb_release -a
Linux rabbit 3.8.13-16.2.1.el6uek.x86_64 #1 SMP Thu Nov 7 17:01:44 PST 2013 x86_64 x86_64 x86_64 GNU/Linux LSB Version: :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch Distributor ID: OracleServer Description: Oracle Linux Server release 6.5 Release: 6.5 Codename: n/a [orekel@rabbit ~]$ |
As DBA, you have to always monitoring the alert log of database. You can monitoring the alert log by using tail syntax as shown as Table 2.
Table 2: Monitoring Alert Log
| [orekel@rabbit ~]$ tail -555f /oraengine/app/orekel/diag/rdbms/kelinci/kelinci/trace/alert_kelinci.log |
Check current size of memory_target as shown as Table 3. You can see that the current memory size of database is 800MB.
Table 3: Check Current Memory Size
| [orekel@rabbit ~]$ rlwrap sqlplus sys as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun Jan 18 09:26:05 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password: Connected to an idle instance.
SQL> STARTUP; ORACLE instance started.
Total System Global Area 835104768 bytes Fixed Size 2293880 bytes Variable Size 562040712 bytes Database Buffers 264241152 bytes Redo Buffers 6529024 bytes Database mounted. Database opened. SQL> SELECT NAME, OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;
NAME OPEN_MODE DATABASE_ROLE ——— ——————– —————- KELINCI READ WRITE PRIMARY
SQL> SHO PARAMETER MEMORY;
NAME TYPE VALUE ———————————— ———– —————————— hi_shared_memory_address integer 0 memory_max_target big integer 800M memory_target big integer 800M shared_memory_address integer 0 SQL> EXIT; Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options |
As DBA, you have to think about fallback plan if what you will do is fail. In this case, I will backup controlfile (optional) and pfile. If the process of increase memory database fail, you can start the database using pfile. This step shown as Table 4.
Table 4: Backup PFILE
| [orekel@rabbit ~]$ mkdir pfile_`date +%Y%m%d` && ls -lh pfile* && cd pfile_`date +%Y%m%d` && pwd && rlwrap sqlplus sys as sysdba
total 0 /home/orekel/pfile_20150118
SQL*Plus: Release 12.1.0.1.0 Production on Sun Jan 18 09:36:27 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME —————- kelinci
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ‘/home/orekel/pfile_20150118/CTL_BKP.TRC’;
Database altered.
SQL> CREATE PFILE=’/home/orekel/pfile_20150118/initkelinci.ora’ FROM SPFILE;
File created.
SQL> !ls -lh total 12K -rw-r–r–. 1 orekel oinstall 7.0K Jan 18 09:38 CTL_BKP.TRC -rw-r–r–. 1 orekel oinstall 1.2K Jan 18 09:38 initkelinci.ora
SQL> |
SYMPTOMPS
Increase memory from 800M to 1G using ALTER SYSTEM SET and need to restart (bounce) the database because scope of changing is spfile. When start the database an error occured, ORA-00845: MEMORY_TARGET not supported on this system. You can see this error on Table 5.
Table 5: Increase DB Memory
| SQL> ALTER SYSTEM SET memory_max_target=1G SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET memory_target=1G SCOPE=SPFILE;
System altered.
SQL> SHO PARAMETER MEMORY;
NAME TYPE VALUE ———————————— ———– —————————— hi_shared_memory_address integer 0 memory_max_target big integer 800M memory_target big integer 800M shared_memory_address integer 0 SQL> SHUT IMMEDIATE; Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP; ORA-00845: MEMORY_TARGET not supported on this system SQL> |
CAUSE
Check message in alert log, and the root cause is memory_target needs larger /dev/shm because Oracle database save memory file in tmpfs operating system (/dev/shm). Look the message at Table 6.
Table 6: Error on Alert Log
| Sun Jan 18 09:42:55 2015
WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be mounted for at least 1073741824 bytes. /dev/shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. Current available is 1046261760 and used is 81920 bytes. Ensure that the mount point is /dev/shm for this directory. Sun Jan 18 09:42:55 2015 memory_target needs larger /dev/shm |
SOLUTION
Check disk free using df syntax. You can fix it by increase the /dev/shm value from current to 2G. Increase this value by editing file /etc/fstab (for permanent condition!) by level root. Make sure that Oracle DB is shutdown. Find the “tmpfs /dev/shm tmpfs defaults 0 0” and edit to “tmpfs /dev/shm tmpfs defaults,size=2g 0 0”. Save /etc/fstab and restart the machine. You can follow the steps in Table 7.
Table 7: Edit /etc/fstab and Restart Machine
| SQL> !df -h
Filesystem Size Used Avail Use% Mounted on /dev/sda1 22G 14G 6.9G 67% / tmpfs 998M 80K 998M 1% /dev/shm
SQL> SHUT IMMEDIATE; ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux-x86_64 Error: 2: No such file or directory SQL> EXIT; Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [orekel@rabbit pfile_20150118]$ su Password: [root@rabbit pfile_20150118]# vi /etc/fstab … tmpfs /dev/shm tmpfs defaults,size=2g 0 0 … [root@rabbit pfile_20150118]# init 6 |
Check the /dev/shm after restart machine, start the database, and check current memory value. Look at Table 8. Enjoy it Bro! 🙂
Table 8: Startup Database
| [orekel@rabbit ~]$ df -h
Filesystem Size Used Avail Use% Mounted on /dev/sda1 22G 14G 6.9G 67% / tmpfs 2.0G 72K 2.0G 1% /dev/shm [orekel@rabbit ~]$ rlwrap sqlplus SYS AS SYSDBA
SQL*Plus: Release 12.1.0.1.0 Production on Sun Jan 18 09:54:22 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password: Connected to an idle instance.
SQL> STARTUP; ORACLE instance started.
Total System Global Area 1068937216 bytes Fixed Size 2296576 bytes Variable Size 713032960 bytes Database Buffers 348127232 bytes Redo Buffers 5480448 bytes Database mounted. Database opened. SQL> SELECT NAME, OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;
NAME OPEN_MODE DATABASE_ROLE ——— ——————– —————- KELINCI READ WRITE PRIMARY
SQL> SHO PARAMETER MEMORY;
NAME TYPE VALUE ———————————— ———– —————————— hi_shared_memory_address integer 0 memory_max_target big integer 1G memory_target big integer 1G shared_memory_address integer 0 SQL> |
Wassalamu’alaikum Wr. Wb.
ORA-00845 | 5
AzizPW's Blog 