Chinaunix首页 | 论坛 | 博客
  • 博客访问: 204225
  • 博文数量: 48
  • 博客积分: 856
  • 博客等级: 军士长
  • 技术积分: 520
  • 用 户 组: 普通用户
  • 注册时间: 2006-03-29 23:18
文章分类

全部博文(48)

文章存档

2011年(18)

2010年(30)

分类: 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

阅读(2029) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~