查看ORACLE启动时使用spfile还是pfile

Oracle 9i以后启动的时候默认使用的初始化文件是spfile,但是如何判断数据库启动是使用spfile还是pfile初始化文件.可以用3种方法,
1、show parameter spfile
2、show parameter pfile
3、看v$spparameter视图[@more@][oracle@Rhel4 dbs]$ sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Jun 18 07:30:49 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> conn /as sysdba;
Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 306186368 bytes
Database Buffers 109051904 bytes
Redo Buffers 6131712 bytes
Database mounted.
Database opened.

SQL> col name format a10;
SQL> col type format a10;
SQL> col value format a30;

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
spfile string /home/oracle/oracle_11g/product/11.1.0/db_1/dbs/spfileorcl.ora

SQL> show parameter pfile;

NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
spfile string /home/oracle/oracle_11g/product/11.1.0/db_1/dbs/spfileorcl.ora

发现show parameter spfile和show parameter pfile结果一样。

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

DECODE(COUNT
------------
spfile

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

默认使用的是spfile，现在指定启动时读取的初始化文件，这样指定的pfile会覆盖查找初始化文件的优先顺序.
SQL> create pfile from spfile;

File created.

SQL> startup pfile=$ORACLE_HOME/dbs/initorcl.oraORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 306186368 bytes
Database Buffers 109051904 bytes
Redo Buffers 6131712 bytes
Database mounted.
Database opened.

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
spfile string

SQL> show parameter pfile;

NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
spfile string

show parameter spfile和show parameter pfile结果仍然一样，此时spfile的值为NULL了。

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

DECODE(COUNT
------------
pfile

from:http://blog.csdn.net/test_me/article/details/5678316

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

转载于:http://blog.itpub.net/22036495/viewspace-1057496/