快捷導(dǎo)航

在Oracle關(guān)閉情況下如何修改spfile的參數(shù)

更新時間：2016年12月15日 09:38:52 作者：Wing''''s Notes

大家都知道在Oracle中pfile參數(shù)是可以手動更改的，但是spfile是二進(jìn)制文件所以不可以手動更改，但我最近遇到了一個問題，修改參數(shù)錯誤，導(dǎo)致Oracle啟動不了，一定要修改spfile該怎么辦呢？下面通過這篇文章來一起看看吧。

發(fā)現(xiàn)問題

我使用的Oracle11g，當(dāng)我敲下如下一段命令后，就讓我傻眼了。。

alter system set sga_max_size=960M scope=spfile;
shutdown immediate
startup

此時的startup報(bào)錯了，錯誤為：

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

原因分析

原來在Oracle11g中增加了memory_target參數(shù)，sga_max_size必須比memory_target參數(shù)小。那么問題來了，此時我已經(jīng)關(guān)閉Oracle了，spfile文件是二進(jìn)制文件，又不能手動修改，那么我該怎么辦呢。。好捉急好捉急。。。

解決思路

通過pfile啟動Oracle–>在Oracle中通過create pfile='' from spfile=''取出spfile的內(nèi)容（pfile是可以手動修改的）–>修改新建的pfile–>以新的pfile啟動Oracle–>在Oracle中通過create spfile='' from pfile=''獲得修改后的spfile

實(shí)戰(zhàn)

[oracle@wing ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 15 14:04:46 2016
Copyright (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> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
通過vi修改pfile.new文件中相應(yīng)的參數(shù)（本文檔中是memory_target參數(shù)），修改后保存 
[oracle@wing ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 15 14:04:46 2016
Copyright (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 bytes
Fixed Size     2257520 bytes
Variable Size    415239568 bytes
Database Buffers   390070272 bytes
Redo Buffers    2523136 bytes
Database 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 immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@wing ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 15 14:08:40 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> 
SQL> startup
ORACLE instance started.
Total System Global Area 810090496 bytes
Fixed Size     2257520 bytes
Variable Size    415239568 bytes
Database Buffers   390070272 bytes
Redo Buffers    2523136 bytes
Database mounted.
Database opened.
SQL> show 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> show parameter sga
NAME         TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
lock_sga        boolean
FALSE
pre_page_sga       boolean
FALSE
sga_max_size       big integer
776M
sga_target       big integer
740M
# 至此Oracle使用新的spfile啟動成功，參數(shù)也得到相應(yīng)的修改

總結(jié)

以上就是關(guān)于如何在Oracle關(guān)閉的情況下修改spfile里面參數(shù)的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作能帶來一定的幫助，如果有疑問大家可以留言交流。

您可能感興趣的文章: