返璞归真：如何判断Oracle实例一个初始化参数的来源

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /export/home/oracle/spfiletest1.ora

但是判断一个初始化参数是否由 SPFILE 设置，并不是那么容易。首先 V$SPPARAMETER 里面包含了所有可以设置初始化参数的记录：

SQL> select sid, count(*)
2  from v$spparameter
3  group by sid;

SID          COUNT(*)
---------- ----------
*                 391
test2               6
test1               6

不过这个问题并不难解决，对于通过 SPFILE 指定的参数，V$SPPARAMETER 视图中的 ISSPECIFIED 列的值为 TRUE，如果在 SPFILE 中没有指定，则这个值为 FALSE。

SQL> select isspecified, count(*)
2  from v$spparameter
3  group by isspecified;

ISSPEC   COUNT(*)
------ ----------
TRUE          144
FALSE         259

SQL> select sid, name, value
2  from v$spparameter
3  where isspecified = 'TRUE'
4  and name not like '\_%' escape '\';

SID        NAME                           VALUE
---------- ------------------------------ --------------------------------------------------
*          processes                      150
*          resource_manager_plan
*          sga_target                     775946240
*          control_files                  +DATA/test/controlfile/current.529.684067899
*          db_block_size                  8192
*          compatible                     11.1.0.0.0
*          log_archive_config
*          log_archive_dest_1             LOCATION=/data/oracle/oradata/test/archivelog
*          log_buffer                     4197376
*          cluster_database               TRUE
*          cluster_database_instances     3
*          db_create_file_dest            +DATA
test1      thread                         1
test2      thread                         2
test1      undo_tablespace                UNDOTBS1
test2      undo_tablespace                UNDOTBS2
test1      instance_number                1
test2      instance_number                2
test1      remote_login_passwordfile      SHARED
test2      remote_login_passwordfile      EXCLUSIVE
*          db_domain
*          plsql_warnings                 DISABLE:ALL
*          result_cache_max_size          3899392
test1      core_dump_dest                 data/oracle/diag/rdbms/test/test1/cdump
test2      core_dump_dest                 data/oracle/diag/rdbms/test/test2/cdump
*          audit_file_dest                /data/oracle/admin/test/adump
*          audit_trail                    DB
*          db_name                        test
test2      open_cursors                   400
*          open_cursors                   500
*          optimizer_mode                 ALL_ROWS
*          query_rewrite_enabled          TRUE
*          pga_aggregate_target           256901120
*          optimizer_dynamic_sampling     2
*          skip_unusable_indexes          TRUE
*          diagnostic_dest                /data/oracle

已选择36行。

上面就列出了 SPFILE 中所有指定的参数，不过并不能以为 SPFILE 中设置的参数就一定会生效。

比如在使用 PFILE 指定 SPFILE 参数的方式启动时，PFILE 里面可以在 SPFILE 之前指定实例级的初始化参数，用来覆盖 SPFILE 里相同的数据库级的初始化参数设置。

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
test1

SQL> select sid, name, value
2  from v$spparameter
3  where name = 'open_cursors';

SID        NAME                           VALUE
---------- ------------------------------ --------------------------------------------------
test1      open_cursors                   600
test2      open_cursors                   400

SQL> alter system reset open_cursors scope = spfile sid = 'test1';

系统已更改。

SQL> alter system set open_cursors = 500;                      

系统已更改。

SQL> select sid, name, value
2  from v$spparameter
3  where name = 'open_cursors';

SID        NAME                           VALUE
---------- ------------------------------ --------------------------------------------------
test2      open_cursors                   400
*          open_cursors                   500

下面通过修改 inittest1.ora 参数，在 spfile 参数前面，加上 open_cursors 参数：

SQL> host vi export/home/oracle/inittest1.ora

test1.open_cursors=1000
spfile=/export/home/oracle/spfiletest1.ora

"/export/home/oracle/inittest1.ora" 3 lines, 68 characters

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup pfile=/export/home/oracle/inittest1.ora
ORACLE 例程已经启动。

Total System Global Area  776896512 bytes
Fixed Size                  2098776 bytes
Variable Size             246069672 bytes
Database Buffers          524288000 bytes
Redo Buffers                4440064 bytes
数据库装载完毕。
数据库已经打开。
SQL> select name, value
2  from v$system_parameter
3  where name = 'open_cursors';

NAME                           VALUE
------------------------------ --------------------------------------------------
open_cursors                   1000

SQL> select sid, name, value, isspecified
2  from v$spparameter
3  where name = 'open_cursors';

SID        NAME                           VALUE                                    ISSPEC
---------- ------------------------------ ---------------------------------------- ------
test2      open_cursors                   400                                      TRUE
*          open_cursors                   500                                      TRUE

检查当前的数据库设置可以发现，虽然当前 SPFILE 中 open_cursors 是明确设置的，但是由于 pfile 中设置了实例级的初始化参数覆盖了数据库级的初始化参数，导致系统当前的参数设置和 SPFILE 中的设置并不相同。

查询初始化参数的设置，其实还有一种方法更加的一目了然，就是 CREATE PFILE 的方式：

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/test/spfiletest.ora
SQL> create pfile = '/export/home/oracle/inittest1.ora' from spfile;

文件已创建。

SQL> host more export/home/oracle/inittest1.ora
test2.__db_cache_size=541065216
test1.__db_cache_size=524288000
test2.__java_pool_size=4194304
test1.__java_pool_size=4194304
test2.__large_pool_size=4194304
test1.__large_pool_size=4194304

使用了这个语句，所有的 SPFILE 中设置的初始化参数设置都一目了然。

除了 CREATE PFILE FROM SPFILE 外，11g 还增加了 CREATE PFILE FROM MEMORY 选项，使得用户可以直接从数据库当前生效的参数来生成 PFILE 文件，利用这个方法，就可以解决 PFILE 中设置的实例级参数覆盖 SPFILE 中数据库级参数的情况。

SQL> create pfile = '/export/home/oracle/inittest1.ora' from memory;

文件已创建。

SQL> host more export/home/oracle/inittest1.ora
# Oracle init.ora parameter file generated by instance test1 on 06/12/2009 15:18:46
test1.__db_cache_size=500M
test2.__db_cache_size=516M
*.__java_pool_size=4M
*.__large_pool_size=4M
*.__oracle_base='/data/oracle' # ORACLE_BASE set from environment

但是这种方法显然也存在问题，首先从得到的结果看，里面除了包含用户设置的初始化参数外，还包含了大量的隐含参数。如果这些隐患参数是 Oracle 用于自动调整的双下划线参数也不奇怪，问题是大部分都是 Oracle 不推荐设置的单下划线隐含参数。不过这倒是一个查看 Oracle 隐含参数的好办法。

另外一个问题是，这个方法只对当前实例设置的参数有效，而无法合并多个实例的设置，对比上面的 OPEN_CURSORS 参数的设置和下面查询的结果就可以发现这个问题：

SQL> select sid, name, value 
2  from v$spparameter
3  where name = 'open_cursors';

SID        NAME                           VALUE
---------- ------------------------------ --------------------------------------------------
*          open_cursors                   300
test1      open_cursors                   500
test2      open_cursors                   700

SQL> select inst_id, name, value
2  from gv$system_parameter
3  where name = 'open_cursors';

INST_ID NAME                           VALUE
---------- ------------------------------ --------------------------------------------------
      1 open_cursors                   500
      2 open_cursors                   500

显然 CREATE PFILE 获取的结果和 SPFILE 中的设置并不相符，下面修改一下初始化参数 OPEN_CURSORS 的值：

SQL> alter system set open_cursors = 400;

系统已更改。

SQL> create pfile = '/export/home/oracle/inittest1.ora' from memory;

文件已创建。

SQL> host more export/home/oracle/inittest1.ora | grep open_cursors
*.open_cursors=400

从这个结果可以看到，CREATE PFILE 获取的 PFILE 只对当前实例有效，虽然获取的结果包含多个实例的设置，但是这些设置可能和其他实例上的真正设置并不相符。