Oracle实例初始化参数文件(Initialization Parameter Files)

2023-08-03 22:18:40

原文链接：http://www.cnblogs.com/oradragon/archive/2012/09/05/2672638.html

当 Oracle 实例启动时，它从初始化参数文件中读取参数进行实例初始化，最小化初始化参数文件只需要指定参数 DB_NAME 的值，所有其他参数有默认值。

初始化参数文件可以是只读(静态)的文本文件 (pfile)，或是读/写(动态)的二进制文件 (spfile)，二进制参数文件被称为服务器参数文件(Server Parameter File)。

pfile 可以用记事本进行直接修改，且必须重启才能生效。spfile 须用 alter system 命令来进行修改，生效的时限和作用域由修改命令的参数 scope 来决定。

如：alter system set job_queue_processed=50 scope=memory;

SCOPE Clause	Description
SCOPE = SPFILE	The change is applied in the server parameter file only. The effect is as follows: No change is made to the current instance. For both dynamic and static parameters, the change is effective at the next startup and is persistent. This is the only SCOPE specification allowed for static parameters.
SCOPE = MEMORY	The change is applied in memory only. The effect is as follows: The change is made to the current instance and is effective immediately. For dynamic parameters, the effect is immediate, but it is not persistent because the server parameter file is not updated. For static parameters, this specification is not allowed.
SCOPE = BOTH	The change is applied in both the server parameter file and memory. The effect is as follows: The change is made to the current instance and is effective immediately. For dynamic parameters, the effect is persistent because the server parameter file is updated. For static parameters, this specification is not allowed.

实例启动初始化参数文件查找顺序：spfileORACLE_SID.ora -> spfile.ora -> initORACLE_SID.ora

可以通过 show parameter spfile 或者 select value from v$parameter where name = 'spfile' 命令查看服务器参数文件的位置，如果启动用的是 pfile，则 spfile 值为空。

[oracle@rac1 dbs]$ ls -hl
total 6.8M
-rw-r----- 1 oracle oinstall 1.6K Aug 28 11:56 hc_byisdb.dat
-rw-r----- 1 oracle oinstall  13K May  3  2001 initdw.ora
-rw-r----- 1 oracle oinstall 8.2K Sep 11  1998 init.ora.bak
-rw-r----- 1 oracle oinstall   24 Aug 28 11:58 lkBYISDB
-rw-r----- 1 oracle oinstall 1.5K Sep  2 10:44 orapwbyisdb
-rw-r----- 1 oracle oinstall 6.8M Aug 29 04:13 snapcf_byisdb.f
-rw-r----- 1 oracle oinstall 3.5K Sep  5 10:11 spfilebyisdb.ora

当前只有服务器初始化参数文件 spfilebyisdb.ora，下面再生成两个参数文件，一个是服务器参数文件 spfile.ora，另外一个是静态参数文件 pfile，看三个参数文件并存的时候，看实例启动时优先使用哪个文件作为初始化参数文件？

SQL> create pfile from spfile
  2  /

File created.

[oracle@rac1 dbs]$ cp spfilebyisdb.ora spfile.ora
[oracle@rac1 dbs]$ ls -hl
total 6.8M
-rw-r----- 1 oracle oinstall 1.6K Aug 28 11:56 hc_byisdb.dat
-rw-r--r-- 1 oracle oinstall 1.2K Sep  5 10:12 initbyisdb.ora
-rw-r----- 1 oracle oinstall  13K May  3  2001 initdw.ora
-rw-r----- 1 oracle oinstall 8.2K Sep 11  1998 init.ora.bak
-rw-r----- 1 oracle oinstall   24 Aug 28 11:58 lkBYISDB
-rw-r----- 1 oracle oinstall 1.5K Sep  2 10:44 orapwbyisdb
-rw-r----- 1 oracle oinstall 6.8M Aug 29 04:13 snapcf_byisdb.f
-rw-r----- 1 oracle oinstall 3.5K Sep  5 10:11 spfilebyisdb.ora
-rw-r----- 1 oracle oinstall 3.5K Sep  5 10:16 spfile.ora

可以看到实例正常启动，使用的初始化参数文件为 spfilebyisdb.ora。

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

Total System Global Area  285212672 bytes
Fixed Size                  1218968 bytes
Variable Size              79693416 bytes
Database Buffers          197132288 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/spfilebyisdb.ora
SQL>

模仿spfilebyisdb.ora不存在，可以看到，实例正常启动，使用的初始化参数文件为：spfile.ora

[oracle@rac1 dbs]$ mv spfilebyisdb.ora spfilebyisdb.ora.bak

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

Total System Global Area  285212672 bytes
Fixed Size                  1218968 bytes
Variable Size              79693416 bytes
Database Buffers          197132288 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SQL>show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/spfile.ora
SQL>

模仿spfile.ora也不存在，可以看到实例还是可以正常启动，此时的参数 spfile 为空，表明没有用到服务器参数文件，而是用到静态参数文件 pfile 即 initbyisdb.ora。

[oracle@rac1 dbs]$ mv spfile.ora spfile.ora.bak

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

Total System Global Area  285212672 bytes
Fixed Size                  1218968 bytes
Variable Size              79693416 bytes
Database Buffers          197132288 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.

SQL> show parameter spfile

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

模仿initbyisdb.ora参数文件也不存在，只剩下它的副本init.ora文件。

[oracle@rac1 dbs]$ cp initbyisdb.ora init.ora
[oracle@rac1 dbs]$ mv initbyisdb.ora initbyisdb.ora.bak
[oracle@rac1 dbs]$ ls -hl
total 6.9M
-rw-r----- 1 oracle oinstall 1.6K Aug 28 11:56 hc_byisdb.dat
-rw-r--r-- 1 oracle oinstall 1.2K Sep  5 10:12 initbyisdb.ora.bak
-rw-r----- 1 oracle oinstall  13K May  3  2001 initdw.ora
-rw-r--r-- 1 oracle oinstall 1.2K Sep  5 10:31 init.ora
-rw-r----- 1 oracle oinstall 8.2K Sep 11  1998 init.ora.bak
-rw-r----- 1 oracle oinstall   24 Aug 28 11:58 lkBYISDB
-rw-r----- 1 oracle oinstall 1.5K Sep  2 10:44 orapwbyisdb
-rw-r----- 1 oracle oinstall 6.8M Aug 29 04:13 snapcf_byisdb.f
-rw-r----- 1 oracle oinstall 3.5K Sep  5 10:21 spfilebyisdb.ora.bak
-rw-r----- 1 oracle oinstall 3.5K Sep  5 10:24 spfile.ora.bak

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initbyisdb.ora'
SQL>

此时只保留了静态参数文件 pfile 的副本 init.ora，重启实例报错，说明最后找的参数文件为 initbyisdb.ora，即 initORACLE_SID.ora，这个跟 Oracle 联机文档描述的相吻合。在网上看到说当前面三个参数文件不存在的时候，最后会找 init.ora 参数文件，所以就动手试验了一下，得出结论是不会找 init.ora 参数文件。

Oracle 联机文档描述：

In the platform-specific default location, Oracle Database locates your initialization parameter file by examining file names in the following order:

spfileORACLE_SID.ora
spfile.ora
initORACLE_SID.ora

转载于:https://www.cnblogs.com/oradragon/archive/2012/09/05/2672638.html

码农公寓

相关文章