Oracle 参数文件三两事儿

作者 | JiekeXu

来源 | JiekeXu DBA之路（ID: JiekeXu_IT）

大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看 Oracle 参数文件三两事儿，欢迎点击上方蓝字关注我，标星或置顶，更多干货第一时间到达！

一、参数文件

说起 Oracle 参数，那么就有必要说一下参数文件了，Oracle 中的参数文件是一个包含一系列参数以及参数对应值的操作系统文件。它们是在数据库实例启动时候加载的，决定了数据库的物理 结构、内存、数据库的限制及系统大量的默认值、数据库的各种物理属性、指定数据库控制文件名和路径等信息，是进行数据库设计和性能调优的重要文件。可分为两种类型：pfile 和 spfile。

pfile: 初始化参数文件（Initialization Parameters Files），Oracle 9i 之前，ORACLE 一直采用 pfile 方式存储初始化参数，pfile 默认的名称为“init+SID.ora”(注意这里的SID区分大小写，initPROD.ora 和 initprod.ora 是两个不同的实例)，文件路径位于：$ORACLE_HOME/dbs，这是一个文本文件，可以用任何文本编辑工具打开。

spfile:服务器参数文件（Server Parameter Files），从Oracle 9i开始，Oracle引入了Spfile文件，spfile 默认的名称为“spfile+SID.ora”，文件路径位于：$ORACLE_HOME/dbs ，不能使用 vi 编辑，但可以使用 strings 查看；如果是在 RAC环境中，spfile 一般位于ASM下的磁盘组 +DATA/数据库名/PARAMETERFILE 目录下，以二进制文本形式存在，不能用vi编辑器对其中参数进行修改，只能通过 SQL 命令在线修改。

那么在数据库中如何查看参数文件位置呢？

一般使用 show 命令或者查看 V$PARAMETER 视图，便可以查到，但有时候却查到值为空，这是因为数据库使用 pfile 启动的。

SQL>  Show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/Jieke/PARAMETERFILE/spfil
                                                 e.272.1086269487
SQL> set line 456
SQL> col name for a10
SQL> col value for a50
SQL> col DISPLAY_VALUE for a50
SQL> SELECT NAME, VALUE, DISPLAY_VALUE FROM V$PARAMETER WHERE NAME ='spfile';

NAME       VALUE                                              DISPLAY_VALUE
---------- -------------------------------------------------- --------------------------------------------------
spfile     +DATA/Jieke/PARAMETERFILE/spfile.272.1086269487     +DATA/JXRT/PARAMETERFILE/spfile.272.1086269487
SQL> show parameter spfile

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

使用 pfile 启动

数据库启动依靠参数文件，可以使用 spfile  启动也可以使用 pfile 启动。如下，指定 pfile 参数文件位置启动数据库。

Startup pfile=’/tmp/pfile.ora’

Startup pfile=’/u01/app/oracle/product/11.2.0/dbhome_1/dbs/inittest.ora’

当然上面的启动方式也等价于直接 startup 不加任何值，因为在没有 spfile 的情况下直接找名为 inittest.ora 的pfile 文件。Oracle 启动时读取参数文件的顺序为：

spfilesid.ora --> spfile.ora -->initsid.ora

注意：如果以上三个参数都没有，数据库启动则会报错找不到 initsid.ora 文件，只能通过备份恢复参数文件或者此目录下的 init.ota 参数模板 + alert 告警日志里上次启动数据库的日志中非默认系统参数“System parameters with non-default values”部分，然后新建核心参数再尝试启动数据库。

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

具体情况可查看下方实验过程。

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 27 20:19:01 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 6413680640 bytes
Fixed Size                  2265224 bytes
Variable Size            1191186296 bytes
Database Buffers         5200936960 bytes
Redo Buffers               19292160 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfile.ora
SQL> !ls -l /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfile*
-rw-r----- 1 oracle oinstall 3584 Oct 27 20:19 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfile.ora

SQL> create pfile from spfile;

File created.

SQL> !ls -l /u01/app/oracle/product/11.2.0/dbhome_1/dbs/*.ora
-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/init.ora
-rw-r--r-- 1 oracle oinstall 1019 Oct 27 20:20 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/inittest.ora
-rw-r----- 1 oracle oinstall 3584 Oct 27 20:19 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfile.ora

SQL> !rm -f /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfile.ora

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 6413680640 bytes
Fixed Size                  2265224 bytes
Variable Size            1191186296 bytes
Database Buffers         5200936960 bytes
Redo Buffers               19292160 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL> create spfile from pfile;

File created.

SQL> !ls -l /u01/app/oracle/product/11.2.0/dbhome_1/dbs/*.ora
-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/init.ora
-rw-r--r-- 1 oracle oinstall 1019 Oct 27 20:20 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/inittest.ora
-rw-r----- 1 oracle oinstall 3584 Oct 27 20:22 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletest.ora

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 6413680640 bytes
Fixed Size                  2265224 bytes
Variable Size            1191186296 bytes
Database Buffers         5200936960 bytes
Redo Buffers               19292160 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfiletest.ora

参数文件之间相互转换生成

基本语法：

create spfile[='xxxxx'] from pfile[='xxxx'];
create pfile[='xxxxx'] from spfile[='xxxx'];
SQL> create pfile from spfile ;
File created.

当使用如上创建参数文件时默认会在$ORACLE_HOME/dbs目录下生成，当然需要基于 from关键字后面的参数文件启动或者 存在 才能创建出 from 前面的文件。一般我们都会指定一个 Oracle 用户可访问的路径生成相关参数文件，例如：

如下：数据库未启动，但存在 spfile 则可生成 pfile。

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 27 20:54:34 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create pfile='/tmp/pfile1027.ora' from spfile;

File created.
SQL> !ls -l /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfile*
-rw-r----- 1 oracle oinstall 3584 Oct 27 20:25 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletest.ora

当数据库启动时，参数文件已经加载到内存中，故可以基于内存创建参数文件.

SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
shared_memory_address                integer     0
SQL> 
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfiletest.ora
SQL> create spfile='/tmp/spfile1.ora' from memory;

File created.

SQL> create spfile='/tmp/pfile1101.ora' from memory;

File created.

SQL> !more /tmp/pfile1101.ora
C"
*.__java_pool_size=32M
*.__large_pool_size=48M
*.__oracle_base='/u01/app/oracle' # ORACLE_BASE set from environment
*.__pga_aggregate_target=2G
*.__sga_target=6G
*.__shared_io_pool_size=0
*.__shared_pool_size=1008M
*.__streams_pool_size=0
*._aggregation_optimization_settings=0
*._always_anti_join='CHOOSE'
*._always_semi_join='CHOOSE'
*._and_pruning_enabled=TRUE
*._b_tree_bitmap_plans=TRUE
*._bloom_filter_enabled=TRUE
*._bloom_folding_enabled=TRUE
*._bloom_prunC
*._complex_view_merging=TRUE

当在 RAC 环境下，手动生成的 spfile 也应该位于 ASM 中，但这样容易出现一个问题就是数据库下次启动时报错找不到 pfile 文件。

create spfile=’+DATA’ from pfile='/tmp/pfile1027.ora';

那么，出现这种情况时该怎么办呢？

在 11g 中，一般都是在各自的 pfile 文件中指定 spfile 位于 ASM 中的某个地方，格式为

spfile=’XXX/spfileorcl.ora’
cat initorcl1.ora
SPFILE='+DATA/ORCL/spfileorcl.ora'  # line added by Agent

“line added by Agent”表示 agent 自动添加，这是由于配置数据库时已经指定了参数文件的位置了。如果显示的位置和你前面创建的 spfile 路径不一样，除了修改 pfile 中的路径指向外，还需要指定参数文件位置重新配置数据库。

$ srvctl config database -d orcl -a

Spfile: +DATA/orcl/spfileorcl.ora

使用 modify 配置正确的 spfile 参数文件

srvctl modify database -d JIEKE -p +JIEKE_DATA/JIEKE/PARAMETERFILE/spfile.1665.1063244346

二、参数和参数类型

上面说完参数文件，接下来说一说参数，Oracle数据库服务器参数可分为 派生参数、操作系统相关参数和可变参数三类。当然也可以分为静态参数和动态参数。下面先来看看静态参数和动态参数。

静态参数：指在数据库中需要重启实例才能生效的参数。静态参数需要修改 spfile 文件，故重启生效。

动态参数：指直接可以在数据库内存中修改不需重启实例的参数。当然，如果直接修改内存中的参数，重启后失效。

修改 spfile 参数方法:

alter session set 参数=值;   //会话级别修改
alter system set 参数=值 [scope=memory|spfile|both]   //系统级别
alter system reset 参数 [scope=memory|spfile|both] SID='*' //恢复缺省值

注意：

scope=memory ：参数修改后立刻生效，但不修改 spfile 文件,重启后参数失效。
scope=spfile ：修改了 spfile 文件，重启后生效。
scope=both ：既修改内存也要修改参数文件。
如果不写 scope，即缺省，缺省值为scope=both 。SID 缺省不写，默认为 SID='*'，当然在 RAC 中也可以单独指定一个实例，如 SID=’RAC1’;如果使用的是pfile 启动的数据库，则无法通过命令进行修改，会报ORA-02095或32001错误。

SQL> alter system set processes = 100;
alter system set processes = 100
*

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set processes = 100 scope=spfile;
alter system set processes = 100 scope=spfile
*

ERROR at line 1:

ORA-32001: write to SPFILE requested but no SPFILE is in use

查看参数

查看参数可以使用  show parameter 参数名

或者如下SQL：

select name,type,value from v$parameter where name='processes';

查看隐含参数

Oracle 中还有一些隐藏的参数无法直接通过 show parameter 的方式查询. 而且 Oracle 的隐藏参数 (hidden parameters)，由oracle内部使用，是以下划线 ‘_’ 开头。可通过关联系统视图 X$KSPPI 和 X$KSPPCV 获取。

set line 345
col NAME for a35
col VALUE for a20
col DESCRIPTION for a60
select a.ksppinm name, b.ksppstvl value,a.ksppdesc description from x$ksppi a, x$ksppcv b
where a.indx = b.indx and a.ksppinm = '&name';

下面在说一说 V$PARAMETER 视图

V$PARAMETER 参数视图结构如下：

SQL> desc V$PARAMETER
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NUM                                                NUMBER
 NAME                                               VARCHAR2(80)
 TYPE                                               NUMBER
 VALUE                                              VARCHAR2(4000)
 DISPLAY_VALUE                                      VARCHAR2(4000)
 DEFAULT_VALUE                                      VARCHAR2(255)
 ISDEFAULT                                          VARCHAR2(9)
 ISSES_MODIFIABLE                                   VARCHAR2(5)
 ISSYS_MODIFIABLE                                   VARCHAR2(9)
 ISPDB_MODIFIABLE                                   VARCHAR2(5)
 ISINSTANCE_MODIFIABLE                              VARCHAR2(5)
 ISMODIFIED                                         VARCHAR2(10)
 ISADJUSTED                                         VARCHAR2(5)
 ISDEPRECATED                                       VARCHAR2(5)
 ISBASIC                                            VARCHAR2(5)
 DESCRIPTION                                        VARCHAR2(255)
 UPDATE_COMMENT                                     VARCHAR2(255)
 HASH                                               NUMBER
 CON_ID                                             NUMBER

 重点介绍该视图的以下几列：

ISSYS_MODIFIABLE：这一列标志该参数是否可以在system级别被修改，对应 alter system set 命令。

SQL> select distinct issys_modifiable from v$parameter;

ISSYS_MOD
---------
IMMEDIATE  //对应 scope=memory
FALSE      //只能 scope=spfile,即修改 spfile 文件，下次启动才生效。
DEFERRED   //其他 session 有效

其中有三个值：

IMMEDIATE —— 表示修改之后立即生效；

DEFERRED —— 表示下个 session 才生效，当前 session 尚未生效；

FALSE —— 表示实例重启后才能生效。

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0

SQL> select count(*) from v$parameter where issys_modifiable='FALSE';

  COUNT(*)
----------
       122

SQL> select count(*) from v$parameter where issys_modifiable='IMMEDIATE';

  COUNT(*)
----------
       314

SQL> select count(*) from v$parameter where issys_modifiable='DEFERRED';

  COUNT(*)
----------
        11

SQL> select count(*) from v$parameter;

  COUNT(*)
----------
       447

SQL> select 122+314+11 from dual;

122+314+11
----------
       447

ISSES_MODIFIABLE：这一列标志该参数是否可以在 session 级别被修改，对应 alter session 命令。

SQL> select distinct isses_modifiable from v$parameter;

ISSES
-----
TRUE  //表示可以修改
FALSE //表示不能修改

SQL> select count(*) from v$parameter where isses_modifiable='TRUE';

  COUNT(*)
----------
       183     //在19.4 的 RAC中，session 级别可以修改的有 183 个

SQL> select count(*) from v$parameter where isses_modifiable='FALSE';

  COUNT(*)
----------
       264    //在19.4 的 RAC中，session 级别中不可以修改的有 264 个

其中有两个值：

TRUE —— 表示可以修改；

FALSE —— 表示不能修改。

ISDEPRECATED ：这一列表示该参数是否被弃用，是否为过期失效参数，当为‘TRUE’时，表示以被弃用，已失效；当为 'FALSE' 时表示为正常可用参数。19c 中废弃参数有 24 个，11g 中废弃参数为 25 个。

SQL> select distinct ISDEPRECATED from v$parameter;

ISDEP
-----
TRUE
FALSE
select name from v$PARAMETER where ISDEPRECATED='TRUE';

ISPDB_MODIFIABLE：指是否可以在 PDB 内修改参数，TRUE 表示可以，FALSE 表示不可以。

SQL> select  count(ISPDB_MODIFIABLE) from v$parameter where ISPDB_MODIFIABLE='TRUE';

COUNT(ISPDB_MODIFIABLE)
-----------------------
                    193
SQL> select  count(ISPDB_MODIFIABLE) from v$parameter where ISPDB_MODIFIABLE='FALSE';

COUNT(ISPDB_MODIFIABLE)
-----------------------
                    254

三类参数类型

Oracle数据库有派生参数、操作系统相关参数和可变参数三种。下面大概在说一说。

派生参数

一些初始化参数是导出的，这意味着它们的值是从其他参数的值中计算出来的。通常，您不应更改派生参数的值，但如果您这样做了，那么您指定的值将覆盖计算出的值。

例如，SESSIONS（1 到 65536）参数的默认值来自参数的值PROCESSES（6-操作系统依赖值）。如果值发生PROCESSES变化，那么默认值SESSIONS也会发生变化，除非你用指定的值覆盖它。两者的关系如下：

SESSIONS=(1.5 * PROCESSES) + 22

而事务数 TRANSACTIONS(4 --- 2 的 31次方减一)和 SESSION 有如下关系：

TRANSACTIONS=1.1*SESSIONS

操作系统相关参数

某些初始化参数的有效值或取值范围取决于主机操作系统。例如，该参数DB_BLOCK_BUFFERS表示主存中数据缓冲区的数量，其最大值取决于操作系统。这些缓冲区的大小由 DB_BLOCK_SIZE设置，具有依赖于操作系统的默认值。

变量参数

可变初始化参数为提高系统性能提供了最大的潜力。一些可变参数设置容量限制但不影响性能。例如，当 OPEN_CURSORS 的值为10 时，尝试打开其第十一个游标的用户进程会收到错误。其他可变参数影响性能但不强加绝对限制。例如，减小 DB_BLOCK_BUFFERS 的值不会阻止工作，即使它可能会降低性能。

增加可变参数的值可能会提高系统的性能，但增加大多数参数也会增加系统全局区域 (SGA) 的大小。更大的 SGA 可以在一定程度上提高数据库性能。在虚拟内存操作系统中，太大的 SGA 如果在内存中换入和换出会降低性能。设置控制虚拟内存工作区的操作系统参数时应考虑到 SGA 大小。操作系统配置还可以限制 SGA 的最大大小。

最后在说一下参数文件的一些注意点：

1、初始化参数文件应该只包含参数和注释。井号 (#) 开始注释行。该行的其余部分将被忽略。

2、注释必须使用与参数值相同的字符集。

3、可以按任意顺序指定参数。

4、仅当主机操作系统上的大小写有意义时，文件名中的大小写（大写或小写）才有意义。

5、要在一行中输入多个参数，请在参数名称和值之间使用空格，如下例所示：

PROCESSES = 100 CPU_COUNT = 1 OPEN_CURSORS = 10

6、反斜杠 (\)，也称为转义字符，表示参数规范的继续。如果反斜杠继续一行，则连续行不能有前导空格。例如：

ROLLBACK_SEGMENTS = (SEG1, SEG2, \
SEG3, SEG4, SEG5)
ROLLBACK_SEGMENTS = SEG1 SEG2
OPEN_CURSORS = 10
ROLLBACK_SEGMENTS = SEG3 SEG4
ROLLBACK_SEGMENTS 设置SEG3和SEG4将覆盖设置的SEG1和SEG2：

7、如果参数值包含特殊字符，则该特殊字符前面必须有反斜杠或整个参数值必须用引号引起来。例如：

DB_DOMAIN = 'JAPAN.ACME#.COM' 
DB_DOMAIN = JAPAN.ACME\#.COM

特殊字符包含井号# 括号（）单双引号“ ” ‘’  等号 = 逗号 、 减号 - 反斜杠 \

NLS_DATE_FORMAT = '''Today is'' MM/DD/YYYY'

NLS_DATE_FORMAT = '"Today is" MM/DD/YYYY'

三、基本初始化参数

下面列出了一些数据库基本的初始化参数，参数详细信息可查看官方文档：

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/index.html

CLUSTER_DATABASE
COMPATIBLE
CONTROL_FILES
DB_BLOCK_SIZE
DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_n
DB_DOMAIN
DB_NAME
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
DB_UNIQUE_NAME
INSTANCE_NUMBER
LDAP_DIRECTORY_SYSAUTH
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST_STATE_n
NLS_DATE_LANGUAGE
NLS_TERRITORY
OPEN_CURSORS
PGA_AGGREGATE_TARGET
PROCESSES
REMOTE_LISTENER
REMOTE_LOGIN_PASSWORDFILE
SESSIONS
SGA_TARGET
SHARED_SERVERS
STAR_TRANSFORMATION_ENABLED
UNDO_TABLESPACE

最最后，根据生产环境总结的 Oracle 19c 最佳参数实践也分享给大家，限于篇幅，则作为另一篇文章发送出来，感兴趣的小伙伴可查看历史消息阅读。

本次分享到此结束啦~

❤️ 欢迎关注我的公众号，来一起玩耍吧！！！

——————————————————————--—--————

公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
腾讯云：https://cloud.tencent.com/developer/user/5645107

————————————————————————----———