spfile详解

SPFILE

VERSION

SQL> select * from v$version;   BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE       11.2.0.3.0        Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production

 

MOS

The value ofthis parameter is the name of the current server parameter file (SPFILE) inuse. This parameter can be defined in a client side PFILE to indicate the nameof the server parameter file to use.

When the defaultserver parameter file is used by the server, the value of SPFILE is internallyset by the server.

The SPFILEresides in the ORACLE_HOME/dbs directory; however, users can place it anywhereon their machine as long as it is specified in an initialization parameterfile.

By Hand

show

SQL> show parameter spfile;   NAME                                     TYPE      VALUE ------------------------------------ ----------- ------------------------------ spfile                                       string     /u01/app/oracle/product/11.2.0                                                              /dbhome_1/dbs/spfileorcl.ora

check

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

 

[oracle@orcl01 /]$ cd $ORACLE_HOME/dbs  [oracle@orcl01 dbs]$ mv spfileorcl.ora spfileorcl.ora.bak

 

SQL> startup nomount; ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'

Manage

         oracle启动时如果不指定pfile的时候，首先会先找spfileORACLE_SID.ora文件，如果没有找到的话会查找initORACLE_SID.ora这个文件，如果都没找到的话就会报错。

         spfileORACLE_SID.ora为二进制的文件，没办法查看和编辑，如果想要查看这个文件的内容可以将spfile转换成pfile文件，pfile文件是可以查看和编辑的文本文件。

        

SQL> startup; ORACLE instance started.   Total System Global Area 1603411968 bytes Fixed Size                      2228784 bytes Variable Size              956304848 bytes Database Buffers      637534208 bytes Redo Buffers               7344128 bytes Database mounted. Database opened. SQL> show parameter spfile; NAME                                     TYPE      VALUE ------------------------------------ ----------- ------------------------------ spfile                                       string     /u01/app/oracle/product/11.2.0                                                       /dbhome_1/dbs/spfileorcl.ora SQL> create spfile='$ORACLE_HOME/dbs/spfileorcl_frommemory.ora' from memory; File created. SQL> create pfile='$ORACLE_HOME/dbs/initorcl_frominit.ora' from memory; File created. SQL> conn / as sysdba Connected to an idle instance. SQL> create pfile='$ORACLE_HOME/dbs/initorcl_fromspfile.ora' from spfile; File created. SQL> create spfile='$ORACLE_HOME/dbs/spfileorcl_frompfile.ora' from pfile;      File created.

         提醒下show parameterspfile要是空的话，说明不是采用spfile启动的数据库，另外一点就是创建spfile from pfile的时候没有指定pfile的地址默认找initORACLE_SID.ORA。

Security

backup

         spfile的备份应该是最简单的，备份的频率个人认为是每次修改参数后都应该对参数文件进行备份。

         方法一：

cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora/rmp/spfileorcl.ora

方法二：

[oracle@orcl01 ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Thu Dec 31 14:23:10 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. connected to target database: ORCL (DBID=1427177664) RMAN> backup spfile format'/rmp/spfileorcl02.ora'; Starting backup at 31-DEC-15 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 31-DEC-15 channel ORA_DISK_1: finished piece 1 at 31-DEC-15 piece handle=/rmp/spfileorcl02.ora tag=TAG20151231T143005 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 31-DEC-15

recovery

如果利用方法一备份，那么恢复起来就相对简单，直接将文件拷贝回来就可以了。

如果才用方法二：可以通过rman来恢复，这个暂时不做测试。

Issue

看了mos和很多人的blog，都说除了spfile外还有个initORACLE_SID.ora，如果startup时候先找spfile如果没有找到就会找initORACLE_SID.ora文件启动数据库，但是我的数据库根本就没有找到这个文件，详见如下：

[oracle@orcl01 orcl]$ cd $ORACLE_BASE [oracle@orcl01 app]$ find -name init*.ora* ./oracle/admin/orcl/pfile/init.ora.11252015163140 ./oracle/product/11.2.0/dbhome_1/srvm/admin/init.ora ./oracle/product/11.2.0/dbhome_1/hs/admin/initdg4odbc.ora ./oracle/product/11.2.0/dbhome_1/dbs/init.ora

         使用与spfile同路径下的init.ora启动库

SQL> startup pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/init.ora' ORA-48108: invalid value given for the diagnostic_dest init.ora parameter ORA-48140: the specified ADR Base directory does not exist [/u01/app/oracle/product/11.2.0/dbhome_1/dbs/<ORACLE_BASE>] ORA-48187: specified directory does not exist Linux-x86_64 Error: 2: No such file or directory Additional information: 1

         原因：这个init.ora是pfile文件的例子，那么我们来看下另外的文件$ORACLE_BASE/oracle/admin/orcl/pfile/init.ora.11252015163140

SQL> startup pfile='$ORACLE_BASE/oracle/admin/orcl/pfile/init.ora.11252015163140' ORACLE instance started. Total System Global Area 1603411968 bytes Fixed Size                      2228784 bytes Variable Size              939527632 bytes Database Buffers      654311424 bytes Redo Buffers               7344128 bytes Database mounted. Database opened.

         怀疑这个文件是不是就是上面提到的initORACLE_SID.ora文件呢，如果是的话，这个文件应该起到是spfile文件的备份左右，那么如果我采用spfile文件启动数据库之后修改参数那么在这个文件中也应该得到相应的修改值。

SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started.   Total System Global Area 1603411968 bytes Fixed Size                      2228784 bytes Variable Size              956304848 bytes Database Buffers      637534208 bytes Redo Buffers               7344128 bytes Database mounted. Database opened. SQL> show parameter spfile; NAME                                     TYPE      VALUE ------------------------------------ ----------- ------------------------------ spfile                                       string     /u01/app/oracle/product/11.2.0                                                       /dbhome_1/dbs/spfileorcl.ora SQL> show parameter processes; NAME                                     TYPE      VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes                integer  1 db_writer_processes                    integer  1 gcs_server_processes                  integer  0 global_txn_processes                  integer  1 job_queue_processes                  integer  1000 log_archive_max_processes      integer  4 processes                               integer  150 SQL> alter system set processes=200 scope=spfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. [oracle@orcl01 app]$ cat $ORACLE_BASE/oracle/admin/orcl/pfile/init.ora.11252015163140 | grep processes processes=150

         没变，猜想不对，莫非是名字的问题吗?之前我们mv了spfile启动数据库会报错，也就是没找到initORACLE_SID.ora。那就修改下这个文件试试。

[oracle@orcl01 app]$ cp $ORACLE_BASE/oracle/admin/orcl/pfile/init.ora.11252015163140 $ORACLE_HOME/dbs/initorcl.ora  [oracle@orcl01 app]$ mv /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora.bak SQL> startup;  ORACLE instance started. Total System Global Area 1603411968 bytes Fixed Size                      2228784 bytes Variable Size              939527632 bytes Database Buffers      654311424 bytes Redo Buffers               7344128 bytes Database mounted. Database opened. SQL> show parameter spfile; NAME                                     TYPE      VALUE ------------------------------------ ----------- ------------------------------ spfile                                       string SQL> show parameter processes; NAME                                     TYPE      VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes                integer  1 db_writer_processes                    integer  1 gcs_server_processes                  integer  0 global_txn_processes                  integer  1 job_queue_processes                  integer  1000 log_archive_max_processes      integer  4 processes                               integer  150

         从结果来看还真是会找这个文件，那么会不会自动备份呢?继续尝试。

[oracle@orcl01 app]$ mv /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora.bak /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 1603411968 bytes Fixed Size                      2228784 bytes Variable Size              956304848 bytes Database Buffers      637534208 bytes Redo Buffers               7344128 bytes Database mounted. Database opened. SQL> show parameter spfile; NAME                                     TYPE      VALUE ------------------------------------ ----------- ------------------------------ spfile                                       string     /u01/app/oracle/product/11.2.0                                                       /dbhome_1/dbs/spfileorcl.ora SQL> show parameter processes; NAME                                     TYPE      VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes                integer  1 db_writer_processes                    integer  1 gcs_server_processes                  integer  0 global_txn_processes                  integer  1 job_queue_processes                  integer  1000 log_archive_max_processes      integer  4 processes                               integer  200 SQL> alter system set processes=180 scope=spfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. [oracle@orcl01 app]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora | grep processes processes=150

         还是没变，看来万一spfile换了，这个文件靠不住了。