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

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

[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.ora
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> 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