• Initialization Parameter files: PFILEs vs. SPFILEs


     

    When an Oracle Instance is started, the characteristics of the Instance are established by parameters specified within the initialization parameter file. These initialization parameters are either stored in a PFILE or SPFILE. SPFILEs are available in Oracle 9i and above. All prior releases of Oracle are using PFILEs.

    SPFILEs provide the following advantages over PFILEs:

    • An SPFILE can be backed-up with RMAN (RMAN cannot backup PFILEs)
    • Reduce human errors. The SPFILE is maintained by the server. Parameters are checked before changes are accepted.
    • Eliminate configuration problems (no need to have a local PFILE if you want to start Oracle from a remote machine)
    • Easy to find - stored in a central location

    What is the difference between a PFILE and SPFILE:

    A PFILE is a static, client-side text file that must be updated with a standard text editor like "notepad" or "vi". This file normally reside on the server, however, you need a local copy if you want to start Oracle from a remote machine. DBA's commonly refer to this file as the INIT.ORA file.

    An SPFILE (Server Parameter File), on the other hand, is a persistent server-side binary file that can only be modified with the "ALTER SYSTEM SET" command. This means you no longer need a local copy of the pfile to start the database from a remote machine. Editing an SPFILE will corrupt it, and you will not be able to start your database anymore.

    How will I know if my database is using a PFILE or SPFILE:

    Execute the following query to see if your database was started with a PFILE or SPFILE:

    SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" 
           FROM sys.v_$parameter WHERE name = 'spfile';

    You can also use the V$SPPARAMETER view to check if you are using a PFILE or not: if the "value" column is NULL for all parameters, you are using a PFILE.

    Viewing Parameters Settings:

    One can view parameter values using one of the following methods (regardless if they were set via PFILE or SPFILE):

    • The "SHOW PARAMETERS" command from SQL*Plus (i.e.: SHOW PARAMETERS timed_statistics)
    • V$PARAMETER view - display the currently in effect parameter values
    • V$PARAMETER2 view - display the currently in effect parameter values, but "List Values" are shown in multiple rows
    • V$SPPARAMETER view - display the current contents of the server parameter file.

    Starting a database with a PFILE or SPFILE:

    Oracle searches for a suitable initialization parameter file in the following order:

    • Try to use the spfile${ORACLE_SID}.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)
    • Try to use the spfile.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)
    • Try to use the init${ORACLE_SID}.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)

    One can override the default location by specifying the PFILE parameter at database startup:

    SQL> STARTUP PFILE='/oradata/spfileORCL.ora'

    Note that there is not an equivalent "STARTUP SPFILE=" command. One can only use the above option with SPFILE's if the PFILE you point to (in the example above), contains a single 'SPFILE=' parameter pointing to the SPFILE that should be used. Example:

    SPFILE=/path/to/spfile

    Changing SPFILE parameter values:

    While a PFILE can be edited with any text editor, the SPFILE is a binary file. The "ALTER SYSTEM SET" and "ALTER SYSTEM RESET" commands can be used to change parameter values in an SPFILE. Look at these examples:

    SQL> ALTER SYSTEM SET open_cursors=300 SCOPE=SPFILE;
    
    SQL> ALTER SYSTEM SET timed_statistics=TRUE
    	COMMENT='Changed by Frank on 1 June 2003'
    	SCOPE=BOTH
     	SID='*';

    The SCOPE parameter can be set to SPFILE, MEMORY or BOTH:

    - MEMORY: Set for the current instance only. This is the default behaviour if a PFILE was used at STARTUP.

    - SPFILE: update the SPFILE, the parameter will take effect with next database startup

    - BOTH: affect the current instance and persist to the SPFILE. This is the default behaviour if an SPFILE was used at STARTUP.
    The COMMENT parameter (optional) specifies a user remark.

    The SID parameter (optional; only used with RAC) indicates the instance for which the parameter applies (Default is *: all Instances).

    Use the following syntax to set parameters that take multiple (a list of) values:

    SQL> ALTER SYSTEM SET utl_file_dir='/tmp/','/oradata','/home/' SCOPE=SPFILE;

    Use this syntax to set unsupported initialization parameters (obviously only when Oracle Support instructs you to set it):

    SQL> ALTER SYSTEM SET "_allow_read_only_corruption"=TRUE SCOPE=SPFILE;

    Execute one of the following command to remove a parameter from the SPFILE:

    SQL> ALTER SYSTEM RESET timed_statistics SCOPE=SPFILE SID=‘*’;
    SQL> ALTER SYSTEM SET timed_statistics = '' SCOPE=SPFILE;

    Converting between PFILES and SPFILES:

    One can easily migrate from a PFILE to SPFILE or vice versa. Execute the following commands from a user with SYSDBA or SYSOPER privileges:

    SQL> CREATE PFILE FROM SPFILE; 
    SQL> CREATE SPFILE FROM PFILE;

    One can also specify a non-default location for either (or both) the PFILE and SPFILE parameters. Look at this example:

    SQL> CREATE SPFILE='/oradata/spfileORCL.ora' from PFILE='/oradata/initORCL.ora';

    Here is an alternative procedure for changing SPFILE parameter values using the above method:

    • Export the SPFILE with: CREATE PFILE=‘pfilename’ FROM SPFILE = ‘spfilename’;
    • Edit the resulting PFILE with a text editor
    • Shutdown and startup the database with the PFILE option: STARTUP PFILE=filename
    • Recreate the SPFILE with: CREATE SPFILE=‘spfilename’ FROM PFILE=‘pfilename’;
    • On the next startup, use STARTUP without the PFILE parameter and the new SPFILE will be used.

    Parameter File Backups:

    RMAN (Oracle's Recovery Manager) will backup the SPFILE with the database control file if setting "CONFIGURE CONTROLFILE AUTOBACKUP" is ON (the default is OFF). PFILEs cannot be backed-up with RMAN. Look at this example:

    RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

    Use the following RMAN command to restore an SPFILE:

    RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

    References:

    • Oracle9i Database Administrator's Guide Release 2 (9.2)
      Chapter 2: Creating an Oracle Database
    • Oracle9i Recovery Manager User's Guide Release 2 (9.2)
      Chapter 5: "RMAN Concepts I: Channels, Backups, and Copies"
    • Oracle9i SQL Reference Release 2 (9.2)
  • 相关阅读:
    【干货】整理分布式技术框架常用的算法及策略
    模块(类)之间解耦利器:EventPublishSubscribeUtils 事件发布订阅工具类
    SqlDapperEasyUtil:.NET CORE下的Dapper封装操作类
    JAVA并发同步互斥实现方式总结
    Elasticsearch必知必会的干货知识一:ES索引文档的CRUD
    整理在Spring IOC容器初始化后可以处理特定逻辑的多种实现方式
    C#编写了一个基于Lucene.Net的搜索引擎查询通用工具类:SearchEngineUtil
    干货分享:ASP.NET CORE(C#)与Spring Boot MVC(JAVA)异曲同工的编程方式总结
    .NET CORE与Spring Boot编写控制台程序应有的优雅姿势
    分享基于.NET动态编译&Newtonsoft.Json封装实现JSON转换器(JsonConverter)原理及JSON操作技巧
  • 原文地址:https://www.cnblogs.com/weaver1/p/2312169.html
Copyright © 2020-2023  润新知