这是一个模拟的实验。参数设置不当导致数据库启动失败当前的SGA为2Ginmemory设置为521M。SQLshowparameter inmemory_size;NAMETYPEVALUE------------------------------------ ----------- ------------------------------inmemory_size biginteger512MSQLshowparameter sga_;NAMETYPEVALUE------------------------------------ ----------- ------------------------------sga_max_size biginteger2G sga_target biginteger2G我们将inmemory_size调整为10G由于inmemory是SGA的一部分显然这是不可能成功的。果然启动失败SQLaltersystemsetinmemory_size10G scopespfile;System altered.SQLshutdownimmediateDatabaseclosed.Databasedismounted.ORACLE instance shut down.SQLstartup ORA-00821: Specifiedvalueofsga_target2048Mistoo small,needstobe at least10624M有spfile备份时的启动恢复假设之前备份了spfile$cd$ORACLE_HOME/dbs[oracleoracle-12102-vagrant dbs]$ls-l*.ora -rw-r--r--.1oracle oinstall2992Feb32012init.ora -rw-r-----.1oracle oinstall3584May2008:48 spfileORCLCDB.ora $cpspfileORCLCDB.ora spfileORCLCDB.ora.orig那直接恢复spfile即可$cpspfileORCLCDB.ora.orig spfileORCLCDB.ora $ sqlplus / as sysdba SQL*Plus: Release12.1.0.2.0 Production on Wed May2009:17:382026Copyright(c)1982,2014, Oracle. All rights reserved. Connected to an idle instance. SQLstartup ORACLE instance started. Total System Global Area2147483648bytes Fixed Size2926472bytes Variable Size520095864bytes Database Buffers1073741824bytes Redo Buffers13848576bytes In-Memory Area536870912bytes Database mounted. Database opened.无spfile备份时的启动恢复先基于当前出错的spfile生成pfileSQLcreatepfile/tmp/pfile.recoverfromspfile1;Entervaluefor1:/opt/oracle/product/12.1.0.2/dbhome_1/dbs/spfileORCLCDB.ora old1:createpfile/tmp/pfile.recoverfromspfile1new1:createpfile/tmp/pfile.recoverfromspfile/opt/oracle/product/12.1.0.2/dbhome_1/dbs/spfileORCLCDB.oraFilecreated.这是由于pfile是可编辑的文本文件而spfile是二进制的。修改pfile设置正确的inmemory_size$cat/tmp/pfile.recover|grepinmemory *.inmemory_size10737418240$echo$((512*1024*1024))536870912$vi/tmp/pfile.recover $cat/tmp/pfile.recover|grepinmemory *.inmemory_size536870912然后数据库启动正常$ sqlplus / as sysdba SQL*Plus: Release12.1.0.2.0 Production on Wed May2009:27:222026Copyright(c)1982,2014, Oracle. All rights reserved. Connected to an idle instance. SQLstartuppfile/tmp/pfile.recoverORACLE instance started. Total System Global Area2147483648bytes Fixed Size2926472bytes Variable Size520095864bytes Database Buffers1073741824bytes Redo Buffers13848576bytes In-Memory Area536870912bytes Database mounted. Database opened. SQL再由当前正确的pfile生成spfile然后验证使用spfile启动正常SQLcreatespfilefrommemory;Filecreated.SQLshutdownimmediateDatabaseclosed.Databasedismounted.ORACLE instance shut down.SQLstartup ORACLE instance started.Total SystemGlobalArea2147483648bytesFixedSize2926472bytes Variable Size520095864bytesDatabaseBuffers1073741824bytes Redo Buffers13848576bytesIn-Memory Area536870912bytesDatabasemounted.Databaseopened.生成的spfile会覆盖之前的spfile尽管之前的那个是错的建议还是备份一下。spfile还是pfile判断启动使用spfile还是pfile可以使用以下SQLSELECTDECODE(COUNT(*),1,SPFILE,PFILE)ASSTARTUP_FILE_TYPEFROMV$SPPARAMETERWHEREROWNUM1ANDISSPECIFIEDTRUE;以下是使用spfile时的输出STARTU------SPFILE以下是使用pfile时的输出STARTU------PFILE说明ISSISPECIFIED列标识该参数是否在SPFILE中显式定义。如果查询结果返回SPFILE说明至少有一个参数定义在SPFILE中因此实例必然是通过SPFILE启动的反之如果返回PFILE则说明实例启动时未使用SPFILE。如果使用了spfile查看spfile的位置SQLshowparameter spfile NAMETYPEVALUE------------------------------------ ----------- ------------------------------spfile string/opt/oracle/product/12.1.0.2/d bhome_1/dbs/spfileORCLCDB.ora无论如何还是建议在修改参数前备份spfile。参考Oracle ASM中的spfile恢复Oracle数据库从alert log恢复spfile