Oracle Database 12c 参数文件（Spfile 和 Pfile）

1. 前言

oracle 数据库的参数文件是在打开Oracle实例时创建的，我们可以动态或静态的更改这些参数文件的内容。oracle 在我们的数据库中有2个相同的参数文件，分别命名为SPFILE和 PFILE。

2. SPFILE（Server Parameter File）文件

SPFILE（Server Parameter File）：是一个扩展名为 .ora 的二进制格式的物理文件，其中存储了打开Oracle 实例时所需要的参数。该文件通常位于 $ORACLE_HOME/dbs 目录下，采用 spfile<ORACLE_SID>.ora 格式。可以说这个文件是Oracle Instance 配置文件，保存了所有参数用于Oracle实例的启动。

从数据库中找到该文件的位置：

[oracle@MehmetSalih ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 1 14:26:31 2019

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /oracle/product/12.1.2/db/dbs/
                                                 spfileDEVECI.ora
SQL>

该文件用于启动数据库时的nomount步骤，其中 Oracle Instance 读取一下参数，如果没有spfile或pfile文件，则实例无法启动，因为控制文件的地址，既数据库的大脑，包含在此参数文件中。因为这个文件是二进制格式的，我们不能直接打开他，但是我们可以用字符串将其转换为用户可以理解的格式，如下所示，为了描述Spfile文件的重要性，将这个参数文件的参数举例如下：

[oracle@MehmetSalih ~]$ strings /oracle/product/12.1.2/db/dbs/spfileDEVECI.ora
DEVECIDB.__data_transfer_cache_size=0
DEVECI.__data_transfer_cache_size=0
DEVECIDB.__db_cache_size=2818572288
DEVECI.__db_cache_size=2332033024
DEVECIDB.__inmemory_ext_roarea=0
DEVECI.__inmemory_ext_roarea=0
DEVECIDB.__inmemory_ext_rwarea=0
DEVECI.__inmemory_ext_rwarea=0
DEVECIDB.__java_pool_size=16777216
DEVECI.__java_pool_size=16777216
DEVECIDB.__large_pool_size=33554432
DEVECI.__large_pool_size=33554432
DEVECIDB.__oracle_base='/oracle/product'#ORACLE_BASE set from environment
DEVECI.__oracle_base='/oracle/product'#ORACLE_
BASE set from environment
DEVECIDB.__pga_aggregate_target=1191182336
DEVECI.__pga_aggregate_target=1191182336
DEVECIDB.__sga_target=3774873600
DEVECI.__sga_target=3774873600
DEVECIDB.__shared_io_pool_size=201326592
DEVECI.__shared_io_pool_size=201326592
DEVECIDB.__shared_pool_size=687865856
DEVECI.__shared_pool_size=1174405120
DEVECIDB.__streams_pool_size=0
DEVECI.__streams_pool_size=0
*.audit_file_dest='/oracle/product/admin/DEVECIDB/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/u01/oradata/DEVECIDB/control01.ctl','/oracle/product/fast_recovery_area/DEVECIDB/control02.ctl'
*.db_block_size=8192
*.db_name='DEVECIDB'
*.db_recovery_file_dest='/oracle/product/fast_recovery_area/DEVECIDB'
*.db_recovery_file_dest_size=10g
*.diagnostic_dest='/oracle/product'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DEVECIDBXDB)'
*.local_listener='LISTENER_DEVECIDB'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1121m
*.processes=300
emote_login_passwordfile='EXCLUSIVE'
*.sga_target=3600m
*.undo_tablespace='UNDOTBS1'
[oracle@MehmetSalih ~]$

上面的 *.control_files 参数和 .db_name 是非常重要的参数，没有阅读到这些参数，Oracle 无法启动实例。数据库第一次启动时，我们可以从数据库中查询到这些非常重要的参数：

SQL> select name from v$parameter where isbasic='TRUE';

NAME
--------------------------------------------------------------------------------
processes
sessions
nls_language
nls_territory
sga_target
control_files
db_block_size
compatible
log_archive_dest_1
log_archive_dest_2
log_archive_dest_state_1

NAME
--------------------------------------------------------------------------------
log_archive_dest_state_2
cluster_database
db_create_file_dest
db_create_online_log_dest_1
db_create_online_log_dest_2
db_recovery_file_dest
db_recovery_file_dest_size
undo_tablespace
instance_number
ldap_directory_sysauth
remote_login_passwordfile

NAME
--------------------------------------------------------------------------------
db_domain
shared_servers
remote_listener
db_name
db_unique_name
open_cursors
star_transformation_enabled
pga_aggregate_target

3. PFILE（Parameter File）文件

Pfile文件的内容和作用与Spfile 相同，但pfile在操作系统上以正常的字符串格式保存，而不是二进制。PFile 文件在操作系统上的 $ORACLE_HOME/dbs 目录下创建的，名为init <ORACLE_SID>.ora。如果存在 spfile，则数据库不需要 PFILE。spfile 文件是在安装数据库时创建的，pfile文件不是自动创建的。pfile文件是从spfile 文件创建的，因为有时更改pfile文件更容易，因为pfile文件时文本文件。pfile文件创建如下：

[oracle@MehmetSalih ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 19 14:39:55 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create pfile from spfile;

File created.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@MehmetSalih ~]$
[oracle@MehmetSalih ~]$
[oracle@MehmetSalih ~]$ ls -l /oracle/product/12.1.2/db/dbs/init*
-rw-r--r--. 1 oracle oinstall 3079 May 15  2015 /oracle/product/12.1.2/db/dbs/init.ora
-rw-r--r--. 1 oracle oinstall 1549 Mar 19 14:39 /oracle/product/12.1.2/db/dbs/initDEVECI.ora
[oracle@MehmetSalih ~]$

可以使用如下所示的 linux cat 命令查看pfile文件内容：

[ oracle@MehmetSalih ~]$ cat /oracle/product/12.1.2/db/dbs/initDEVECI.ora 
DEVECIDB.__data_transfer_cache_size=0 
DEVECI.__data_transfer_cache_size=0 
DEVECIDB.__db_cache_size=2818572288 
DEVECI.__ 
extdb_30cache4.__ 
extdb_3rocache4 .__ inmemory_ext_roarea = 0 
DEVECIDB .__ inmemory_ext_rwarea = 0 
DEVECI .__ inmemory_ext_rwarea = 0 
DEVECIDB .__ JAVA_POOL_SIZE = 16777216 
DEVECI .__ JAVA_POOL_SIZE = 16777216 
DEVECIDB .__ LARGE_POOL_SIZE = 33554432 
DEVECI .__ LARGE_POOL_SIZE = 33554432 
DEVECIDB .__ ORACLE_BASE = '/ ORACLE /产物'从环境＃ORACLE_BASE集
DEVECI .__oracle_base='/oracle/product'#ORACLE_BASE 从环境中设置
DEVECIDB .__ PGA_AGGREGATE_TARGET = 1191182336 
DEVECI .__ PGA_AGGREGATE_TARGET = 1191182336 
DEVECIDB .__ SGA_TARGET = 3774873600 
DEVECI .__ SGA_TARGET = 3774873600 
DEVECIDB .__ shared_io_pool_size = 201326592 
DEVECI .__ shared_io_pool_size = 201326592 
DEVECIDB .__ SHARED_POOL_SIZE = 687865856 
DEVECI .__ SHARED_POOL_SIZE = 1174405120 
DEVECIDB .__ STREAMS_POOL_SIZE = 0 
DEVECI .__ STREAMS_POOL_SIZE = 0 
*.audit_file_dest='/oracle/product/admin/DEVECIDB/adump' 
*.audit_trail='db' 
*.compatible='12.2.0 ' *.control_files=' 
/u01/oradata/DEVECIDB/control01.ctl',' /oracle/product/fast_recovery_area/DEVECIDB/control02.ctl' 
*.db_block_size=8192 
*.db_name='DEVECIDB'
*.db_recovery_file_dest='/oracle/product/fast_recovery_area/DEVECIDB' 
*.db_recovery_file_dest_size=10g 
*.diagnostic_dest='/oracle/product' 
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DEVECIDBXDB)' 
*.local_listener=' LISTENER_DEVECIDB' 
*.log_archive_format='%t_%s_%r.dbf' 
*.nls_language='AMERICAN' 
*.nls_territory='AMERICA' 
*.open_cursors=300 
*.pga_aggregate_target=1121m 
* .processes =300 
*.remote_login独家' 
*.sga_target=3600m 
*.undo_tablespace='UNDOTBS1' 
[ oracle@MehmetSalih ~]$

使用其中之一就足以启动Oracle 实例，但是如果启动时，两个文件都存在，则oracle 自动使用spfile 启动。

由于spfile文件非常重要，因此有必要使用完整备份连续备份spfile。为了自动备份Spfile文件，数据库必须处于自动备份模式。我们可以在RMAN 工具上执行此操作：

[oracle@MehmetSalih ~]$  rman target /

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN>

如果如上所述的自动检索备份文件，则将spfile的备份副本复制到设置 autobackup 的路径

[oracle@MehmetSalih ~]$ rman target / 

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name DEVECI are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oracle/recovery_area/ctrl_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2 G;
CONFIGURE MAXSETSIZE TO 4 G;
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES128';
CONFIGURE COMPRESSION ALGORITHM 'LOW' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/product/12.1.0/db/dbs/snapcf_TESTDB.f';

RMAN>

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/oracle/recovery_area/ctrl_%F’;