分类： Oracle
2010-12-08 11:21:58
今天使用命令alter system set db_cache_size=10M scope=both时出现如下错误：
SQL> alter system set db_cache_size=10M scope=both;
alter system set db_cache_size=10M scope=both
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup
使用命令：
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
表明实例启动的时候并没使用spfile启动
退出来用命令查看是否存在spfile
[oracle@ORACLE1 oracle]$ ls /opt/oracle/product/9.2.0/dbs
initdw.ora init.ora initwlovesly.ora lkDEFAULT lkWLOVESLY orapwwlovesly
该目录下没有使用spfile文件
创建spfile文件：
[oracle@ORACLE1 oracle]$ cp /opt/oracle/product/9.2.0/dbs/initwlovesly.ora /opt/oracle/product/9.2.0/dbs/spfile.ora
[oracle@ORACLE1 oracle]$ cp /opt/oracle/product/9.2.0/dbs/initwlovesly.ora /opt/oracle/product/9.2.0/dbs/spfilewlovesly.ora
[oracle@ORACLE1 oracle]$ ls /opt/oracle/product/9.2.0/dbs initdw.ora initwlovesly.ora lkWLOVESLY spfile.ora
init.ora lkDEFAULT orapwwlovesly spfilewlovesly.ora
启动数据库：
[oracle@ORACLE1 oracle]$ sqlplus '/as sysdba'
SQL*Plus: Release 9.2.0.4.0 - Production on Sun Nov 25 10:58:41 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 123049360 bytes
Fixed Size 451984 bytes
Variable Size 121634816 bytes
Database Buffers 819200 bytes
Redo Buffers 143360 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
还是没有用spfile启动，这是为什么呢？
在网上看到eygle写的一篇文章spfile的使用，觉得创建的只是物理文件，在数据库动态视图中并没有记录有这么一个变化，需要使用create spfile 命令创建。
SQL> create spfile from pfile;
File created.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
[oracle@ORACLE1 oracle]$ sqlplus '/as sysdba'
SQL*Plus: Release 9.2.0.4.0 - Production on Sun Nov 25 11:12:22 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 123049360 bytes
Fixed Size 451984 bytes
Variable Size 121634816 bytes
Database Buffers 819200 bytes
Redo Buffers 143360 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
呵呵，终于看到了，现在数据库启动使用的是spfile文件，检验下：
SQL> alter system set sga_max_size=256M scope=spfile;
System altered.
SQL> alter system set db_cache_size=10M scope=spfile;
System altered.
现在问题已经解决。（重起数据库出现问题）
以下的实验在另一台虚拟机上完成，因为以上的实验虽然可以解决此问题，但是重起数据库时出现了问题，从上面的可以看出参数文件的设置出现了问题。此不在本次问题的考虑之内。
下面确认create spfile 能不能创建物理文件：
[oracle@Linux oracle]$ ls /opt/oracle/product/9.2.0/dbs
01j0vos1_1_1 arch1_44.dbf arch1_49.dbf core_2200 orapwwlovely
arch1_40.dbf arch1_45.dbf arch1_50.dbf initdw.ora snapcf_wlovely.f
arch1_41.dbf arch1_46.dbf arch1_51.dbf init.ora spfilewlovely.ora
arch1_42.dbf arch1_47.dbf arch1_52.dbf initwlovely.ora
arch1_43.dbf arch1_48.dbf arch1_53.dbf lkWLOVELY
[oracle@Linux oracle]$ rm /opt/oracle/product/9.2.0/dbs/spfilewlovely.ora
[oracle@Linux oracle]$ sqlplus '/as sysdba'
SQL*Plus: Release 9.2.0.4.0 - Production on Sun Nov 25 12:42:28 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile
2 ;
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
可以看出create spfile 是可以创建物理文件
所以当创建spfile文件时：
如果用cp命令复制，一定在登陆数据库后使用命令create spfile from pfile
