记一次ORACLE启动失败ORA-00845: MEMORY_TARGET not supported on this system

使用oracle用户，像往常一样启动

[oracle@localhost ~]$ sqlplus  /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sun Oct 12 07:07:15 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn /as sysdba

Connected to an idle instance.
SQL>
SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system

 

呃，问题发生了该怎么办。上网查了下资料，说是MEMORY_MAX_TARGET 的设置不能超过 /dev/shm 的大小。我们来查看下

[oracle@localhost ~]$ df -h

Filesystem            Size  Used Avail Use% Mounted on
/dev/sdb1             9.5G  2.5G  6.6G  28% /
/dev/sda3              17G  4.6G   12G  29% /home
/dev/sdb2              20G  7.5G   12G  40% /usr/local
/dev/sdc1              30G  174M   28G   1% /opt
/dev/sda1             9.5G  151M  8.9G   2% /tmp
tmpfs                 395M     0  395M   0% /dev/shm

 

这里是395M。按照上面所述，MEMORY_MAX_TARGET将不能超过395M。

 

我们知道，oracle在startup的时候，可以指定参数。比如可以指定是使用pfile文件还是spfile文件。两者可以相互转换。还有，两者的关系，就是前者是普通的文本文件（可以手动修改），后面是二进制的格式。好，让我们来创建一个pfile文件。

SQL> create pfile from spfile;

File created.

产生的文件将放在ORACLE_HOME/dbs目录下，文件名为initorcl.ora

[oracle@localhost dbs]$ env | grep ORACLE
ORACLE_SID=orcl
ORACLE_BASE=/usr/local/oracle
ORACLE_HOME=/usr/local/oracle/11.2.0

[oracle@localhost oracle]$ pwd

/usr/local/oracle
[oracle@localhost oracle]$ cd 11.2.0/dbs/

 

进录入dbs目录下，查看一下文件的内容

*.memory_target=620756992

很明显，大于350M了

 

修改文件initorcl.ora，改完后的内容如下所示

[oracle@localhost dbs]$ more initorcl.ora
orcl.__db_cache_size=239075328
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/usr/local/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=251658240
orcl.__sga_target=369098752
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=113246208
orcl.__streams_pool_size=0
*.audit_file_dest='/usr/local/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/usr/local/oracle/oradata/orcl/control01.ctl','/usr/local/oracle/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain='tianjin'
*.db_name='orcl'
*.diagnostic_dest='/usr/local/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=390M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@localhost dbs]$

 

这里改为了392M，即小于395M。好，让我们来测试来oracle是否能启动了 

SQL> startup pfile=$ORACLE_HOME/dbs/initorcl.ora;

ORACLE instance started.

Total System Global Area  410112000 bytes

Fixed Size                  1336876 bytes
Variable Size             251660756 bytes
Database Buffers          150994944 bytes
Redo Buffers                6119424 bytes
Database mounted.
Database opened.

 

呵呵，启动成功了。启动以后的参数

SQL> show parameter mem;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 392M
memory_target                        big integer 392M
shared_memory_address                integer     0

 

好，到目前为止，问题解决了。我们继续往下做实验。

例如这里，强行把MEMORY_TARGET设置为大于350M时

SQL> alter system set MEMORY_TARGET=1G;  
alter system set MEMORY_TARGET=1G
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET

 

总结：MEMORY_TARGET 不能够大于 MEMORY_MAX_TARGET。此时的MEMORY_MAX_TARGET为392M。

 

好，强行更改MEMORY_MAX_TARGET为1G时

SQL> alter system set MEMORY_MAX_TARGET=1G scope=spfile;

alter system set MEMORY_MAX_TARGET=1G scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE is in use

 

呃，现在是用pfile模式下启动的

总结：使用pfile模式下，不能够通过alter system 来修改参数文件

好，那就转成spfile吧

 

SQL> create spfile from pfile;

File created.

 

使用spfile文件启动后，即startup

总结：再次试验，让问题再重现下

SQL> alter system set memory_max_target =1G scope=spfile;

System altered.

SQL> shutdown abort;

ORACLE instance shut down.
SQL>
SQL>

 

再次启动

SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system
SQL>

 

问题又重现了，于是，就有一本文的开始之处。

 

最后，再做下测试，MEMORY_TARGET和MEMORY_MAX_TARGET两个参数之中，任何一个大于395时，会怎么样呢

 

设置MEMORY_TARGET为1G时

SQL> alter system set memory_target=1G scope=spfile;

 

System altered.

 

查看spfile文件的变化

[oracle@localhost dbs]$ strings spfileorcl.ora

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.memory_target=1073741824

*.open_cursors=300

 

启动时的报错

SQL> startup

ORA-00845: MEMORY_TARGET not supported on this system

 

设置MEMORY_MAX_TARGET为1G时

 

SQL> alter system set memory_max_target =1G scope=spfile;

查看文件的变化

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.memory_max_target=1073741824

*.memory_target=390M

 *.open_cursors=300

 

这里的memory_target =390M是因为使用pfile时指定的，接着又转成了spfile，没有改过，可以理解为默认值。

 

启动时的报错。

SQL> startup

ORA-00845: MEMORY_TARGET not supported on this system

 

虽然是memory_max_target大于了395M，而不是memory_target报错而导致，便依然提示MEMORY_TARGET的问题。所以可以总结为，memory_max_target和memory_target中的任何一个出现问题，都将收到MEMORY_TARGET not supported on this system的提示。

 

 

 

最后再总结下问题解决的过程

从spfile文件创建一个pfile文件

修改memory_max_target、memory_target的值

startup pfile=$ORACLE_HOME/dbs/initorcl.ora;

从pfile文件创建一个spfile文件

关闭，再次重启即可 

最后，附上参考资料

ORACLE的解释

Starting with Oracle Database 11g, the Automatic Memory Management feature requires more shared memory (/dev/shm)and file descriptors. The size of the shared memory should be at least the greater of MEMORY_MAX_TARGET and MEMORY_TARGET for each Oracle instance on the computer. If MEMORY_MAX_TARGET or MEMORY_TARGET is set to a non zero value, and an incorrect size is assigned to the shared memory, it will result in an ORA-00845 error at startup.