Increase memory_target on Oracle DB 12c Linux ORA-00845: MEMORY_TARGET not supported on this system


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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s