Spfile&pfile

server parameter file (SPFILE)是oracle9i引入的新的参数机制,可以在线修改系统参数(on disk);

1.启动
在默认情况下,oracle在以下路径按照顺序搜寻相应的参数文件
Path
unix:$ORACLE_HOME/dbs
win: $ORACLE_HOMEdatabase
Filename
spfile$ORACLE_SID.ora
spfile.ora
init$ORACLE_SID.ora
也就是说:默认启动方式是spfile;当然也可以指定pfile方式:
STARTUP PFILE = /u01/oracle/dbs/init.ora

2.创建
spfile创建须来自pfile;
CREATE SPFILE FROM PFILE='/u01/oracle/dbs/init.ora';
--覆盖原默认spfile:spfile$ORACLE_SID.ora
CREATE SPFILE='/u01/oracle/dbs/test_spfile.ora'
FROM PFILE='/u01/oracle/dbs/test_init.ora';

建立pfile备份
CREATE PFILE FROM SPFILE;
--覆盖原默认pfile:init$ORACLE_SID.ora
CREATE PFILE='/u01/oracle/dbs/test_init.ora'
FROM SPFILE='/u01/oracle/dbs/test_spfile.ora';

3.修改
如果使用spfile,那么可以通过alter system set **** scope=spfile/memory/both来修改
参数的值,默认scope=both;

SCOPE = SPFILE
The change is applied in the server parameter file only. The effect is as follows:

*For dynamic parameters, the change is effective at the next startup and is persistent.
*For static parameters, the behavior is the same as for dynamic parameters.
This is the only SCOPE specification allowed for static parameters.

ALTER SYSTEM
SET LOG_ARCHIVE_DEST_4='LOCATION=/u02/oracle/rbdb1/',MANDATORY,'REOPEN=2'
COMMENT='Add new destimation on Nov 29'
SCOPE=SPFILE;

SCOPE = MEMORY
The change is applied in memory only. The effect is as follows:

*For dynamic parameters, the effect is immediate, but it is not persistent because the
server parameter file is not updated.
*For static parameters, this specification is not allowed.

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=50
COMMENT='temporary change on Nov 29'
SCOPE=MEMORY

SCOPE = BOTH
The change is applied in both the server parameter file and memory.
The effect is as follows:

*For dynamic parameters, the effect is immediate and persistent.
*For static parameters, this specification is not allowed.

4.辨别
系统使用spfile还是pfile,可以通过V$SPPARAMETER来查看;
V$SPPARAMETER column:ISSPECIFIED
Indicates whether the parameter was specified in the server parameter file (TRUE) or not (FALSE)

select decode(count(*), 1, 'spfile', 'pfile' )
from v$spparameter
where rownum=1
and isspecified='TRUE';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/45259/viewspace-134870/，如需转载，请注明出处，否则将追究法律责任。