ORA-00845: MEMORY_TARGET not supported on this system
Oracle Database – Enterprise Edition
Information in this document applies to any platform.
[oracle@lovetech dbs]$ . oraenv
ORACLE_SID = [dbaprood] ? dbaprod
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@lovetech dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Sep 5 12:23:52 2021
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system
Evidence
ORA-00845: MEMORY_TARGET not supported on this system
Change
Total Server memory was initially 10G and oracle database instance was created with MEMORY_TARGET=3.862647. The database has been running with no issues. To make efficient use of memory, total server memory was reduced from 10G to 3G. After the change, Starting up oracle databases instance failed with ORA-00845.
Cause:
The value allocated for MEMORY_TARGET in the parameter file is higher than the total available memory on the system.
Also the value allocated for/dev/shm file system is too small
Findings
Determine the value allocated for /dev/shm
[oracle@lovetech dbs]$ df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 1.5G 0 1.5G 0% /dev
tmpfs 1.6G 0 1.6G 0% /dev/shm———————->
tmpfs 1.6G 9.2M 1.5G 1% /run
tmpfs 1.6G 0 1.6G 0% /sys/fs/cgroup
/dev/sda3 96G 65G 32G 67% /
/dev/sda1 19G 297M 19G 2% /boot
tmpfs 309M 12K 309M 1% /run/user/42
tmpfs 309M 0 309M 0% /run/user/54321
Determine total memory allocated to the server
[oracle@lovetech ~]$ free -h
total used free shared buff/cache available
Mem: 3.0G 459M 1.9G 11M 657M 2.5G
Swap: 487M 0B 487M
Detemine memory_max_size and memory_target by creating pfile from database spfile
[oracle@lovetech dbs]$ . oraenv
ORACLE_SID = [dbaprood] ? dbaprod
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@lovetech dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Sep 5 12:23:52 2021
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create pfile from spfile;
File created.
Change directory to the pfile directory.
[oracle@lovetech dbs]$ cd $ORACLE_HOME/dbs/
[oracle@lovetech dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@lovetech dbs]$ ll
total 22352
-rw-rw—-. 1 oracle oinstall 1544 Sep 4 17:36 hc_dbaprod.dat
-rwxrwxr-x. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r—–. 1 oracle oinstall 24 Aug 18 23:12 lkDBAPROD
-rw-r–r–. 1 oracle oinstall 944 Sep 5 12:24 initdbaprod.ora——->
-rw-r—–. 1 oracle oinstall 1536 Aug 23 23:46 orapwdbaprod
-rw-r—–. 1 oracle oinstall 2560 Sep 4 17:16 spfiledbaprod.ora
Review current memory_target value settings in pfile
[oracle@lovetech dbs]$ cat initdbaprod.ora
dbaprod.__db_cache_size=1744830464
dbaprod.__java_pool_size=67108864
dbaprod.__large_pool_size=67108864
dbaprod.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
dbaprod.__pga_aggregate_target=1811939328
dbaprod.__sga_target=2617245696
dbaprod.__shared_io_pool_size=0
dbaprod.__shared_pool_size=671088640
dbaprod.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/dbaprod/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/u01/app/oracle/dbaprod/control01.ctl’,’/u01/app/oracle/dbaprod/control02.ctl’#Restore Controlfile
*.db_block_size=8192
*.db_domain=”
*.db_name=’dbaprod’
*.db_recovery_file_dest_size=10737418240
*.db_recovery_file_dest=’/u01/app/oracle/fast_recovery_area’
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbaprodXDB)’
*.memory_target=4370464768—————————>3.862647 Gigabytes
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
Solution:
Edit pfile and Allocate appropriate value to memory_target parameter.
[oracle@lovetech dbs]$ cd $ORACLE_HOME/dbs/
[oracle@lovetech dbs]$ vi initdbaprod.ora
Pfile content Afer edit
[oracle@lovetech dbs]$ cat initdbaprod.ora
dbaprod.__db_cache_size=1744830464
dbaprod.__java_pool_size=67108864
dbaprod.__large_pool_size=67108864
dbaprod.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
dbaprod.__pga_aggregate_target=1811939328
dbaprod.__sga_target=2617245696
dbaprod.__shared_io_pool_size=0
dbaprod.__shared_pool_size=671088640
dbaprod.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/dbaprod/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/u01/app/oracle/dbaprod/control01.ctl’,’/u01/app/oracle/dbaprod/control02.ctl’#Restore Controlfile
*.db_block_size=8192
*.db_domain=”
*.db_name=’dbaprod’
*.db_recovery_file_dest_size=10737418240
*.db_recovery_file_dest=’/u01/app/oracle/fast_recovery_area’
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbaprodXDB)’
*.memory_target=1073741274—————————————->1GB
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
[oracle@lovetech dbs]$
To make sure instance startup with new pfile that have been edited
[oracle@lovetech dbs]$ mv spfiledbaprod.ora spfiledbaprod.ora_bk
As root increase /dev/shm size to a reasonable size
[oracle@lovetech dbs]$ su – root
Password:
Last login: Sun Sep 5 12:28:39 EDT 2021 on pts/0
[root@lovetech ~]# mount -t tmpfs shmfs -o size=2g /dev/shm
Switch to oracle and start the instance
[oracle@lovetech ~]$ . oraenv
ORACLE_SID = [dbatest] ? dbaprod
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@lovetech ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Sep 5 12:34:26 2021
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 616566616 bytes
Database Buffers 444596224 bytes
Redo Buffers 5554176 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile;
File created.
Now bounce the instance such that it start with the spfile
SQL> startup force;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 616566616 bytes
Database Buffers 444596224 bytes
Redo Buffers 5554176 bytes
Database mounted.
Database opened.
The database instance started successfully after setting memory_target parameter to 1G
Note: The use of any materials on this website is at your own risk. It is provided for educational purposes only. It has been tested internally, but we do not guarantee that it will work for you. We recommend you test in your test environment before using.
Recent Comments