在Oracle关闭情况下如何修改spfile的参数

数据库  /  管理员 发布于 6年前   259

发现问题

我使用的Oracle11g，当我敲下如下一段命令后，就让我傻眼了。。

alter system set sga_max_size=960M scope=spfile;shutdown immediatestartup

此时的startup报错了，错误为：

SQL> startupORA-00844: Parameter not taking MEMORY_TARGET into accountORA-00851: SGA_MAX_SIZE 985661440 cannot be set to more than MEMORY_TARGET 784334848.

原因分析

原来在Oracle11g中增加了memory_target参数，sga_max_size必须比memory_target参数小。那么问题来了，此时我已经关闭Oracle了，spfile文件是二进制文件，又不能手动修改，那么我该怎么办呢。。好捉急好捉急。。。

解决思路

通过pfile启动OracleC>在Oracle中通过create pfile='' from spfile=''取出spfile的内容（pfile是可以手动修改的）C>修改新建的pfileC>以新的pfile启动OracleC>在Oracle中通过create spfile='' from pfile=''获得修改后的spfile

实战

[oracle@wing ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 15 14:04:46 2016Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to an idle instance.SQL> create pfile='/home/oracle/pfile.new' from spfile='/u01/app/oracle/product/11.2.0/db_1/dbs/spfilewingdb.ora';File created.SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options通过vi修改pfile.new文件中相应的参数（本文档中是memory_target参数），修改后保存 [oracle@wing ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 15 14:04:46 2016Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to an idle instance.SQL> startup pfile='/home/oracle/pfile.new'ORACLE instance started.Total System Global Area 810090496 bytesFixed Size     2257520 bytesVariable Size    415239568 bytesDatabase Buffers   390070272 bytesRedo Buffers    2523136 bytesDatabase mounted.Database opened.SQL> create spfile='/u01/app/oracle/product/11.2.0/db_1/dbsspfilewingdb.ora' from pfile='/home/oracle/pfile.new';File created.SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@wing ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 15 14:08:40 2016Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to an idle instance.SQL> SQL> startupORACLE instance started.Total System Global Area 810090496 bytesFixed Size     2257520 bytesVariable Size    415239568 bytesDatabase Buffers   390070272 bytesRedo Buffers    2523136 bytesDatabase mounted.Database opened.SQL> show parameter memory NAME         TYPE------------------------------------ --------------------------------VALUE------------------------------hi_shared_memory_address    integer0memory_max_target     big integer800Mmemory_target      big integer800Mshared_memory_address    integer0SQL> show parameter sgaNAME         TYPE------------------------------------ --------------------------------VALUE------------------------------lock_sga        booleanFALSEpre_page_sga       booleanFALSEsga_max_size       big integer776Msga_target       big integer740M# 至此Oracle使用新的spfile启动成功，参数也得到相应的修改

总结

以上就是关于如何在Oracle关闭的情况下修改spfile里面参数的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流。