对spfile 误操作的处理

来源：互联网发布：mac中安装好ruby环境编辑：程序博客网时间：2024/05/09 01:25

If you edit the spfile, due to the file is a binary file,
so this file is not allowed to edit.

if you edit the spfile, following steps can help you fix this issue.

Error:

oracle@a05scpgapp01/oracle/ora102/product/10.2/dbs>exit

SQL> create pfile='/datafile/dbsrun/pfile_dbsrun.ora' from spfile;
create pfile='/datafile/dbsrun/pfile_dbsrun.ora' from spfile
*
ERROR at line 1:
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
Additional information: 1265
Additional information: 512

Step1:

Copy the parameter and values into the init_(SID).ora file.

Step2:
remove the spfile from $ORACLE_HOME/dbs

Step3:
startup the Database

Step4:
SQL:>create spfile from pfile:

SQL> create spfile from pfile;

File created.

SQL> !
oracle@a05scpgapp01/datafile>cd /oracle/ora102/product/10.2/dbs
oracle@a05scpgapp01/oracle/ora102/product/10.2/dbs>ls -lrt
total 15880
-rw-r-----   1 oracle   dba            8385 Sep 11 1998 init.ora
-rw-r--r--   1 oracle   dba           12920 May 3 2001 initdw.ora
-rw-r-----   1 oracle   dba              24 Jun 27 2008 lkDBSRUN
-rw-r-----   1 oracle   dba            1536 Feb 16 2011 orapwdbsrun
-rw-r-----   1 oracle   dba         8077312 May 20 11:34 snapcf_dbsrun.f
-rw-r-----   1 oracle   dba            1359 Jul 7 10:44 alert_dbsrun.log
-rw-r-----   1 oracle   dba             869 Sep 27 16:18 initdbsrun.ora
-rw-rw----   1 oracle   dba            1552 Sep 27 16:51 hc_dbsrun.dat
-rw-r-----   1 oracle   dba            2560 Sep 27 16:53 spfiledbsrun.ora

--------------------
Summary:

使用startup命令启动数据库，，Oralce将会按照以下顺序在缺省目录中(9i,10i在oracle_home/database目录下）搜索参数文件：（１）spfile***.ora文件（２）如果没有spfile***.ora文件就用spfile.ora文件（３）如果没有spfile.ora文件就用init***.ora（４）如果没有init***.ora文件就用 pfile.ora

所以上面我们采取的解决方法是将破坏的ＳＰＦＩＬＥ中的参数复制到init***.ora中，然后删除ＳＰＦＩＬＥ文件，这样启动时就会用init***.ora文件启动，然后用create spfile from pfile　命令生成ＳＰＦＩＥＬ

以后如果要修改ＳＰＦＩＬＥ中的参数绝对不能在手工修改ＳＰＦＩＬＥ文件了，用下面命令：alter system set parameter=Value scope=spfile|both|memory　例如：alter system set db_cache_size=24M scope=both;来修改