Oracle 参数文件

ORACLE的参数文件包括很多，比如tnsname.ora,listener.ora,sqlnet.ora,ldap.ora等，但本文所要讨论的是数据库参数文件，即init.ora初始文件。

• 1 参数文件 

我们知道数据库实例启动时，必须要读取参数文件(服务器参数文件或文本初始化文件)，里面包含了一系列参数配置。

参数文件的功能分组大概有以下几点：

Most initialization parameters belong to one of the following functional groups:

• Parameters that name entities such as files or directories

• Parameters that set limits for a process, database resource, or the database itself

• Parameters that affect capacity, such as the size of the SGA (these parameters are called variable parameters)

Variable parameters are of particular interest to database administrators because they can use these parameters to improve database performance.

Database administrators can use initialization parameters to:

• Optimize performance by adjusting memory structures, such as the number of database buffers in memory

• Set database-wide defaults, such as the amount of space initially allocated for a context area when it is created

• Set database limits, such as the maximum number of database users

• Specify names of files or directories required by the database

Many initialization parameters can be fine-tuned to improve database performance. Other parameters should never be altered or should be altered only under the supervision of Oracle Support Services.

All initialization parameters are optional(db_name是必须的). Oracle has a default value for each parameter. This value may be operating system-dependent, depending on the parameter.

参数的分类：

• Derived Parameters(可推导的参数)
  比如sessions参数就根据processes参数计算获得，sessions=1.1*processes+15。如果设置了sessions，也会被推导出的值覆盖掉。
  
  
• Operating System-Dependent Parameters(OS相关参数)
  例如DB_BLOCK_BUFFERS参数的最大值就和OS有关。
• Variable Parameters (these can be dynamic parameters or any of the preceding ones动态参数)
  该部分参数很大程度上影响到数据库的性能，也是最有发挥余地的参数。

• 2 SPFILE和PFILE

从9iR1版本引入服务器参数文件(server parameter file).

 参数文件的搜索顺序: 

SPFILE$ORACLE_SID.ora >SPFILE.ora(服务器端，init.ora客户端，可能造成混乱与不一致.) > init$ORACLE_SID.ora > init.ora

• 2.1 PFILE

A text initialization parameter file is a text file that contains a list of initialization parameters. This type of parameter file, which is a legacy implementation of the parameter file, has the following key characteristics:

• When starting up or shutting down a database, the text initialization parameter file must reside on the same host as the client application that connects to the database.

• A text initialization parameter file is text-based, not binary.

• Oracle Database can read but not write to the text initialization parameter file. To change the parameter values you must manually alter the file with a text editor.

• Changes to initialization parameter values by ALTER SYSTEM are only in effect for the current instance. You must manually update the text initialization parameter file and restart the instance for the changes to be known.

The text initialization parameter file contains a series of key=value pairs, one per line. For example, a portion of an initialization parameter file could look as follows:

db_name=samplecontrol_files=/disk1/oradata/sample_cf.dbfdb_block_size=8192open_cursors=52undo_management=autoshared_pool_size=280Mpga_aggregate_target=29M...

该文件的命名约定默认是：

%ORACLE_HOME%\database\init%ORACLE_SID%.ora(Windows平台)

$ORACLE_HOME/dbs/init$ORACLE_SID.ora(Unix平台)

参数文件可以不放在默认位置，可以指定文件位置：

SQL> startup pfile='D:\oracle\product\10.2.0\db_1\database\initabc.ora';

也可以使用IFILE包含一个非默认位置的init.ora文件。

IFILE='D:\oracle\product\10.2.0\db_1\database\initabc.ora';

或者包含spfile

IFILE='D:\oracle\product\10.2.0\db_1\database\spfileabc.ora';

如果使用pfile参数文件，发出alter  system命令改变参数值时，并不会作为一个永久修改反映到pfile文件中。如果希望这个修改是永久的，也就是成为数据库重启时的默认值，必须手工修改pfile中的参数值。

pfile不一定位于数据库服务器上。如果运行一台Unix服务器，但是通过网络使用一台Windows台式机上安装的SQL*Plus来管理，这台计算机上就需要有pfile.这在一定程度上对于维护是个灾难，每个DBA都需要在自己的台式机留有pfile的一个副本，这会导致这些pfile大面积"繁殖“。

 

• 2.2 SPFILE

server.112/e10595/create006.htm#ADMIN11119

A server parameter file is a repository for initialization parameters that is managed by Oracle Database. A server parameter file has the following key characteristics:

• Only one server parameter file exists for a database. This file must reside on the database host.

• The server parameter file is written to and read by only by Oracle Database, not by client applications.

• The server parameter file is binary and cannot be modified by a text editor.

• Initialization parameters stored in the server parameter file are persistent. Any changes made to the parameters while a database instance is running can persist across instance shutdown and startup.

A server parameter file eliminates the need to maintain multiple text initialization parameter files for client applications. A server parameter file is initially built from a text initialization parameter file using the CREATE SPFILE statement. It can also be created directly by the Database Configuration Assistant.

有了spfile，可以消除pfile存在的两个严重问题。

(1)可以杜绝参数文件的繁殖。SPFILE总是存储在数据库服务器上;必须存在于服务器主机本身，不能放在客户端上。

(2)无需再数据库之外使用文本编辑器手工维护参数文件（实际上，更确切的说法是不能手动第维护）。利用alter system命令，完全可以直接将值写入SPFILE。管理员不必再手动地查找和维护所有参数文件。

SPFILE文件的命名约定默认为：

$ORACLE_HOME/dbs/spfile$ORACLE_SID.ora (unix平台)

%ORACLE_HOME%\database\spfile%ORACLE_SID%.ora (windows平台)

unix平台查看工具：strings

• 2.3 PFILE与PFILE比较

可以指定pfile启动，而不能指定spfile，不过可以通过在pfile文件中指定spfile文件的位置启动。

 ue编辑initabc.ora:

  spfile=D:\oracle\product\10.2.0\db_1\dbs\SPFILEORCL.ORA

  这种方式在RAC环境中较常见。

下图从可为维护性角度比较了pfile和spfile的区别。

 

SPFILEPFILERMAN备份能备份不能备份存放位置保存在服务端，只能在服务端修改或者通过命令行，降低人为错误可以任意修改上传
减少配置问题，启动远端数据库，不需要本地的pfile.

• 3 参数相关操作

参数可以查看Oracle® Database Reference 11g Release 2 (11.2)

server.112/e10820/initparams.htm#g1195420

3.1 查看参数Table 13-3 Static and Dynamic Initialization Parameters

CharacteristicStaticDynamic

Requires modification of the parameter file (text or server)

Yes

No

Requires database instance restart before setting takes affect

Yes

No

Described as "Modifiable" in Oracle Database Reference initialization parameter entry

No

Yes

Modifiable only for the database or instance

Yes

No

  select value from v$parameter where name ='pga_aggregate_target';

  或者

  show parameter pga;

You can view parameter settings in several ways, as shown in the following table.

MethodDescriptionSHOW PARAMETERSThis SQL*Plus command displays the values of initialization parameters in effect for the current session.SHOW SPPARAMETERSThis SQL*Plus command displays the values of initialization parameters in the server parameter file (SPFILE).CREATE PFILEThis SQL statement creates a text initialization parameter file (PFILE) from the SPFILE or from the current in-memory settings. You can then view the PFILE with any text editor.V$PARAMETERThis view displays the values of initialization parameters in effect for the current session.V$PARAMETER2This view displays the values of initialization parameters in effect for the current session. It is easier to distinguish list parameter values in this view because each list parameter value appears in a separate row.V$SYSTEM_PARAMETERThis view displays the values of initialization parameters in effect for the instance. A new session inherits parameter values from the instance-wide values.V$SYSTEM_PARAMETER2This view displays the values of initialization parameters in effect for the instance. A new session inherits parameter values from the instance-wide values. It is easier to distinguish list parameter values in this view because each list parameter value appears in a separate row.V$SPPARAMETERThis view displays the current contents of the SPFILE. The view returns FALSE values in the ISSPECIFIED column if an SPFILE is not being used by the instance.

• 3.2 查看引隐式参数

   select x.ksppinm name, y.ksppstvl value ,x.ksppdesc describ

    from sys.x$ksppi x, sys.x$ksppcv y

    where x.inst_id =userenv( 'Instance')

      and y.inst_id =userenv( 'Instance')

      and x.indx=y.indx

     and x.ksppinm like '%&par%';

  Tom认为开发数据库唯一需要设置的隐式参数：_TRACE_FILES_PUBLIC

• 3.2 设置SPFILE参数值

语法：

  alter system  set parameter=value <comment='text'><deferred> <scope=memory|spfile|both> <sid='sid|*'>;

  alter session set parameter=value <comment='text'><deferred> <scope=memory|spfile|both> <sid='sid|*'>;

system和session的区别在于后者只针对当前会话有效，其他会话无效。

deferred指定系统修改是否只对以后的会话生效（对当前简历的会话无效，包括执行此修改的会话）。默认是立即生效的，但有些参数只能延迟生效，可以通过下面的语句查看哪些参数要求必须使用deferred.

SQL> select name from v$parameter where issys_modifiable='DEFERRED';

 

NAME

--------------------------------------------------------------------------------

backup_tape_io_slaves

recyclebin

audit_file_dest

object_cache_optimal_size

object_cache_max_size_percent

sort_area_size

sort_area_retained_size

olap_page_pool_size

 

8 rows selected

SQL> alter system set sort_area_size=65536;

 

alter system set sort_area_size=65536

 

ORA-02096: 此选项的指定初始化参数不可修改

 

SQL> alter system set sort_area_size=65536 deferred;

 

System altered

SCOPE=MEMORY|SPFILE|BOTH指定了这个参数设置的"作用域"。

  MEMORY表示只在实例中修改，数据库重启后将不再保存。

  SPFILE表示只修改SPFILE中的值，数据库重启并再次处理SPFILE之前，这个修改不会生效。有些参数只能使用这个选项修改，例如，processes参数就必须使用SCOPE_SPFILE，因为我们无法修改活动实例的processes值。

  BOTH表示内存和SPFILE中都会完成参数修改。这是使用SPFILE时的默认的作用域值。如果使用PFILE，默认值是SCOPE=MEMORY.

SID='sid|*'主要用于集群环境，默认值为sid='*'.这样可以为集群中任何给定的实例唯一地指定参数设置。除非使用RAC,否则一般不需要指定SID=设置。

• 3.3 取消SPFILE中的值设置

    alter system reset parameter <scope=memory|spfile|both> sid='sid|*';

• 3.4 修改 PFILE 参数值 

按下面的步骤操作： 

1. Open the file called init<database_name>.ora. For example, if the database is called oraprod, then the file would be called initoraprod.ora. The file can be in a variety of places, but almost always somewhere in a subdirectory of the Oracle Installation directory on the server (also known as the ORACLE_HOME).
2. Find the parameter that you wish to change. The parameter may not be in the file. If not, the default value is being used by Oracle. Add the parameter if it is not in the file and needs to be a non-default value.
3. Comment out the line containing the current setting (in case the change needs to be undone). A line will be commented out (ignored by Oracle) if a pound sign (#) is placed at the beginning of the line
4. Add a new line with the same parameter with the new setting.

Example

# Doubled db_cache_size from 30000000 on 5/4/2003, change made by Jeff Jones.

# db_cache_size = 30000000

db_cache_size=60000000

• 4 参数文件的维护

•  4.1 创建参数文件

• 4.1.1 pfile转换为spfile

首先确认是否是以spfile作为参数文件启动，如果下面命令的value不为空，则是以spfile启动。

SQL> show parameter spfile; 

NAME                TYPE        VALUE

-----------------  ----------- -----------------------------------------------------------

spfile              string      /data2/oracle/product/11.2/db_1/dbs/spfiledacsdb.ora

如果为空，则表示以pfile启动，此时可以使用

create spfile from pfile;命令生成spfile文件。

CREATE SPFILE='/u01/oracle/dbs/test_spfile.ora'  FROM PFILE='/u01/oracle/dbs/test_init.ora';

The next example illustrates creating a server parameter file in the default location from the current values of the initialization parameters in memory.

CREATE SPFILE FROM MEMORY;

Whether you use the default SPFILE name and default location or specify an SPFILE name and location, if an SPFILE of the same name already exists in the location, it is overwritten without a warning message.小心覆盖

• 4.1.2. spfile转换为pfile

You can use the CREATE PFILE statement to export a server parameter file (SPFILE) to a text initialization parameter file. Doing so might be necessary for several reasons:

• For diagnostic purposes, listing all of the parameter values currently used by an instance. This is analogous to the SQL*Plus SHOW PARAMETERS command or selecting from theV$PARAMETER or V$PARAMETER2 views.

• To modify the &spfile;server parameter file by first exporting it, editing the resulting text file, and then re-creating it using the CREATE SPFILE statement

The exported file can also be used to start up an instance using the PFILE clause.

You must have the SYSDBA or the SYSOPER system privilege to execute the CREATE PFILE statement. The exported file is created on the database server machine. It contains any comments associated with the parameter in the same line as the parameter setting.

The following example creates a text initialization parameter file from the SPFILE:

CREATE PFILE FROM SPFILE;

Because no names were specified for the files, the database creates an initialization parameter file with a platform-specific name, and it is created from the platform-specific default server parameter file.

The following example creates a text initialization parameter file from a server parameter file, but in this example the names of the files are specified:

CREATE PFILE='/u01/oracle/dbs/test_init.ora'       FROM SPFILE='/u01/oracle/dbs/test_spfile.ora';

Note:

An alternative is to create a PFILE from the current values of the initialization parameters in memory. The following is an example of the required command:

CREATE PFILE='/u01/oracle/dbs/test_init.ora' FROM MEMORY;

• 4.2 修正被破坏的SPFILE

在unix平台，可以使用strings命令提取出SPFILE中所有的设置;在windows平台，则要用write.ex(即写字板）打开这个文件，将其剪切并粘贴到init<ORACLE_SID>.ora中，就能创建启动实例的PFILE.

万一SPFILE丢失了，那么可以从数据库的警告日志恢复参数文件的信息。每次启动数据库时，警告日志都包含如下一部分内容：

          Thu May 15 13:54:44 2014

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Shared memory segment for instance monitoring created

Picked latch-free SCN scheme 2

Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST

Autotune of undo retention is turned on.

IMODE=BR

ILAT =27

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Starting up:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options.

Using parameter settings in client-side pfile D:\U01\ADMIN\ORCL11G\PFILE\INIT.ORA on machine AD-PC

System parameters with non-default values:

  processes                = 150

  memory_target            = 1232M

  control_files            = "D:\U01\ORADATA\ORCL11G\CONTROL01.CTL"

  control_files            = "D:\U01\FLASH_RECOVERY_AREA\ORCL11G\CONTROL02.CTL"

  db_block_size            = 8192

  compatible               = "11.2.0.0.0"

  db_recovery_file_dest    = "D:\u01\flash_recovery_area"

  db_recovery_file_dest_size= 3852M

  undo_tablespace          = "UNDOTBS1"

  remote_login_passwordfile= "EXCLUSIVE"

  db_domain                = ""

  dispatchers              = "(PROTOCOL=TCP) (SERVICE=orcl11gXDB)"

  audit_file_dest          = "D:\U01\ADMIN\ORCL11G\ADUMP"

  audit_trail              = "DB"

  db_name                  = "orcl11g"

  open_cursors             = 300

  diagnostic_dest          = "D:\U01"

 我们可以很容易地创建一个PFILE，再用create spfile命令将其转换为一个spfile.

• 4.3 备份参数文件

The SPFILE is backed up automatically by RMAN when you back up your database, but RMAN also enables you to specifically create a backup of the currently active SPFILE.

 rman下：

starup nomount;

set dbid ...;

restore spfile from autobackup;或者指定文件位置restore spfile from './...';

shutdown immediate;

set dbid;

startup;

 

 

•  5 RAC与参数文件

在rac下，当使用asm存储时，instance的启动参数文件就是pfile（其内容是指向一个spfile）。

bash-3.2$ less  /oracle/app/product/11.2.0/dbhome_1/dbs/initdacsdb1.ora
        SPFILE='+DATADG/dacsdb/spfiledacsdb.ora'

 如果不小心执行了“create pfile from spfile”，就真的会创建一个pfile了（里面的内容是真实的启动参数，而不再是spfile）。

 在 SPFILE='+DATADG/dacsdb/spfiledacsdb.ora'下一行开始，设置合适的参数值，比如下面的参数，设置控制文件：

  control_files   = "+DATADG/dacsdb/control02.ctl"

  这样就可以覆盖spfile中不适当的参数，从而启动实例.

或者直接通过alter system set scope=spfile命令修改。

SQL>  show parameter spfile;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      +DATADG/dacsdb/spfiledacsdb.ora

• 6 Storing the Server Parameter File on HARD-Enabled Storage

Starting with Release 11g, the server parameter file (SPFILE) is in a new format that is compliant with the Oracle Hardware Assisted Resilient Data (HARD) initiative. HARD defines a comprehensive set of data validation algorithms, implemented at both the software and storage hardware levels, to ensure that no corrupt data is written to permanent storage. To fully enable HARD protection for the data in your SPFILE, the SPFILE must reside on HARD-enabled storage, and compatibility for your database instance must be advanced to at least 11.0.0.

You can store the HARD-compliant SPFILE on non-HARD-enabled storage. In this case, the new SPFILE format supports only detection of corrupt SPFILE data. Storing the SPFILE on HARD-enabled storage prevents corrupt data from being written to storage in the first place.

For more information about HARD, and for a list of storage vendors that supply HARD-enabled storage systems, visit:http://www.oracle.com/technology/deploy/availability/htdocs/HARD.html.

Follow these guidelines for full HARD protection when installing or upgrading your Oracle database:

When Installing or Initially Creating a Release 11g Database

When first installing or creating a Release 11g database, the COMPATIBLE initialization parameter defaults to 11.2.0, so this requirement for a HARD-compliant server parameter file (SPFILE) is met. You must then ensure that the SPFILE is stored on HARD-enabled storage. To meet this requirement, do one of the following:

• For an Oracle Real Application Clusters environment without shared storage, when DBCA prompts for the location of the SPFILE, specify a location on HARD-enabled storage.

• For a single-instance installation, or for an Oracle Real Application Clusters environment with shared storage, complete these steps:

  1. Complete the database installation with Database Configuration Assistant (DBCA).

     The SPFILE is created in the default location. See Table 2-4 for information on default locations.

  2. Do one of the following:

     • Using an operating system command or the ASMCMD utility, copy the SPFILE to HARD-enabled storage.

     • In SQL*Plus or another interactive environment such as SQL Developer, connect to the database as user SYS and then submit the following command:

       CREATE SPFILE = 'spfile_name' FROM MEMORY;

       where spfile_name is a complete path name, including file name, that points to HARD-enabled storage.

  3. Create a text initialization parameter file (PFILE) in the default location with the following single entry:

     SPFILE = spfile_name

     where spfile_name is the complete path to the SPFILE on HARD-enabled storage.

     See Table 2-4 for default name and location information for PFILEs and SPFILEs.

  4. Shut down the database instance.

  5. Delete the SPFILE in the default location.

  6. Start up the database instance.

When Upgrading to Release 11g from an Earlier Database Release

When upgrading to Release 11g from an earlier database release, complete these steps to migrate your SPFILE to the HARD-compliant format and to store the SPFILE on HARD-enabled storage:

1. Start SQL*Plus or another interactive query application, log in to the database as user SYS or SYSTEM, and then enter the following command:

   ALTER SYSTEM SET COMPATIBLE = '11.2.0' SCOPE = SPFILE;

   Caution:

   Advancing the compatibility level to 11.2.0 enables Release 11g features and file formats and has wide repercussions. Consult Oracle Database Upgrade Guide before proceeding

2. Restart the database instance.

   The database is now at compatibility level 11.2.0.

3. If your SPFILE is not already on HARD-enabled storage, complete the following steps:

   1. In SQL*Plus or another interactive environment, connect to the database as user SYS and then submit the following command:

      CREATE SPFILE = 'spfile_name' FROM MEMORY;

      where spfile_name is a complete path name, including file name, that points to HARD-enabled storage.

   2. Create a text initialization parameter file (PFILE) in the default location with the following single entry:

      SPFILE = spfile_name

      where spfile_name is the complete path to the SPFILE on HARD-enabled storage.

      See Table 2-4 for default name and location information for PFILEs and SPFILEs.

   3. Shut down the database instance.

   4. Delete the SPFILE in the default location.

   5. Start up the database instance.