【体系结构】Oracle参数介绍
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：
① Oracle中的各种参数介绍及其查询方法
② Oracle中V$PARAMETER及V$PARAMETER2的区别
③ 隐含参数的查询、重置、清除
④ 会话参数和实例参数的查询
⑤ 静态参数和动态参数、延迟参数
⑥ V$PARAMETER视图的每列含义（重点）
Tips：
① 本文在itpub（http://blog.itpub.net/26736162）、博客园(http://www.cnblogs.com/lhrbest)和微信公众号（xiaomaimiaolhr）上有同步更新。
② 文章中用到的所有代码、相关软件、相关资料及本文的pdf版本都请前往小麦苗的云盘下载，小麦苗的云盘地址见：http://blog.itpub.net/26736162/viewspace-1624453/。
③ 若网页文章代码格式有错乱，请下载pdf格式的文档来阅读。
④ 在本篇BLOG中，代码输出部分一般放在一行一列的表格中。其中，需要特别关注的地方我都用灰色背景和粉红色字体来表示，比如在下边的例子中，thread 1的最大归档日志号为33，thread 2的最大归档日志号为43是需要特别关注的地方；而命令一般使用黄色背景和红色字体标注；对代码或代码输出部分的注释一般采用蓝色字体表示。
| List of Archived Logs in backup set 11 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48 1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58 2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49 2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53 [ZHLHRDB1:root]:/>lsvg -o T_XLHRD_APP1_vg rootvg [ZHLHRDB1:root]:/> 00:27:22 SQL> alter tablespace idxtbs read write; ====》2097152*512/1024/1024/1024=1G |
本文如有错误或不完善的地方请大家多多指正，ITPUB留言或QQ皆可，您的批评指正是我写作的最大动力。
其实很早就想把杨廷琨大神的参数系列的文章整理一下了，一直没空，最近写书碰到了这里问题，正好就抽空整理一下。
Oracle数据库根据SPFILE或PFILE中设置的参数来配置数据库的启动。每个数据库实例在启动之前，首先读取这些参数文件中设置的不同参数。Oracle系统中的参数，根据系统使用情况可以简单分为两大类：
l 普通参数：Oracle系统正常使用的一些参数
l 非凡参数：包括3种，过时参数、强调参数和隐含参数。
图 3-8 Oracle参数分类
首先介绍一下参数的设置方法。初始化参数的设置方法有很多种：
l 通过“ALTER SYSTEM/SESSION SET 参数名=参数值 SCOPE = MEMORY;”的方式仅在内存里修改。
l 通过“ALTER SYSTEM SET 参数名=参数值 SCOPE = SPFILE;”的方式只修改SPFILE里的值。
l 通过“ALTER SYSTEM SET 参数名=参数值 DEFERRED SCOPE = SPFILE;”的方式设置延迟生效，也就是说这个修改只对以后连接到数据库的会话生效，而对当前会话以及其它已经连接到Oracle的会话不会生效。
l 通过“ALTER SYSTEM/SESSION SET 参数名=参数值 SCOPE = BOTH;”或省略BOTH这个关键词可以同时修改SPFILE和MEMORY中的值。
ALTER SESSION和ALTER SYSTEM的区别如下：
| ALTER SESSION | 修改的参数只限于本次会话，退出会话再进入时修改失效 |
| ALTER SYSTEM | 修改的参数适用于数据库实例的所有会话，数据库关闭则修改失效。有特权用户和DBA可以执行 |
| ALTER SYSTEM DEFERRED | 修改是延迟修改，退出会话，下次进入会话时生效。有特权用户和DBA可以执行 |
Oracle参数变更生效范围如下表所示：
在RAC环境中，若想修改所有实例，则可以在ALTER SYSTEM的最后加上“SID=‘*‘”或“SID=‘实例名‘”即可。“*”代表所有实例。
过时参数（Obsolete Parameters），顾名思义就是在Oracle以前的版本中存在，但在新版本中已经淘汰了的参数，已经不再使用的参数。在视图V$OBSOLETE_PARAMETER中，包含这些参数的名称和一个列ISSPECIFIED，该列用来指出这个参数是否在参数文件中已实际设置。下面的SQL脚本列出了当前系统中所有的过时参数名称以及它们是否在当前系统中设定。
| SELECT NAME, ISSPECIFIED FROM V$OBSOLETE_PARAMETER; |
强调参数（Underscored Parameters），是指那些在新版本中保留了下来，但是除非非常需要否则不希望用户使用的那些参数。强调参数可以通过系统视图X$KSPPO来查看，该视图中包含一个名为KSPPOFLAG的字段。该字段用来指明该参数在当前版本中是被丢弃还是被强调。若该值为1，则表示该参数已被丢弃，若该值为2，则表明该参数现为强调参数。
| SYS@lhrdb> SELECT KSPPONM, DECODE(KSPPOFLG, 1, ‘Obsolete‘, 2, ‘Underscored‘) 2 FROM X$KSPPO T 3 WHERE T.KSPPONM IN (‘hash_join_enabled‘,‘job_queue_interval‘) 4 ORDER BY KSPPONM; KSPPONM DECODE(KSPP ---------------------------------------------------------------- ----------- hash_join_enabled Underscored job_queue_interval Obsolete |
可以看到HASH_JOIN_ENABLED这个参数为强调参数，在隐含参数中表现为“_HASH_JOIN_ENABLED”，而JOB_QUEUE_INTERVAL已变为了过时参数。
Oracle系统中还有一类参数称之为隐含参数（Hidden Parameters），在系统中使用，但Oracle官方没有公布的参数，这些参数可能是那些还没有成熟或者是系统开发中使用的参数。这些参数在所有Oracle官方提供的文档中都没有介绍，它们的命名有一个共同特征就是都以“_”作为参数的首字符。下面的查询可以得到当前系统中的所有隐藏参数，需要以SYS用户登陆，查看两个视图：X$KSPPI和X$KSPPCV。下面作者给出具体的SQL语句。
| SET PAGESIZE 9999 SET LINE 9999 COL NAME FORMAT A40 COL KSPPDESC FORMAT A50 COL KSPPSTVL FORMAT A20 SELECT A.INDX, A.KSPPINM NAME, A.KSPPDESC, B.KSPPSTVL FROM X$KSPPI A, X$KSPPCV B WHERE A.INDX = B.INDX AND A.KSPPINM LIKE ‘/_%‘ ESCAPE ‘/‘ AND LOWER(A.KSPPINM) LIKE LOWER(‘%&PARAMETER%‘); |
举个例子，如果需要查询隐含参数“_LM_DD_INTERVAL”的值，那么执行上面的代码后输入“_LM_DD_INTERVAL”就可以看到该隐含参数的值了，如下所示：
| SYS@lhrdb> SET PAGESIZE 9999 SYS@lhrdb> SET LINE 9999 SYS@lhrdb> COL NAME FORMAT A40 SYS@lhrdb> COL KSPPDESC FORMAT A50 SYS@lhrdb> COL KSPPSTVL FORMAT A20 SYS@lhrdb> SELECT A.INDX, 2 A.KSPPINM NAME, 3 A.KSPPDESC, 4 B.KSPPSTVL 5 FROM X$KSPPI A, 6 X$KSPPCV B 7 WHERE A.INDX = B.INDX 8 AND A.KSPPINM LIKE ‘/_%‘ ESCAPE ‘/‘ 9 AND LOWER(A.KSPPINM) LIKE LOWER(‘%&PARAMETER%‘); Enter value for parameter: _lm_dd_interval old 9: AND LOWER(A.KSPPINM) LIKE LOWER(‘%&PARAMETER%‘) new 9: AND LOWER(A.KSPPINM) LIKE LOWER(‘%_lm_dd_interval%‘) INDX NAME KSPPDESC KSPPSTVL ---------- ---------------------------------------- -------------------------------------------------- -------------------- 578 _lm_dd_interval dd time interval in seconds 10 |
可以看到该隐含参数的值为10。
对于隐含参数而言，修改隐含参数的值的时候需要将隐含参数用双引号括起来。若要清除SPFILE中的隐含参数可以使用RESET命令。
| SYS@lhrdb> alter system set _lm_dd_interval=20 scope=spfile; alter system set _lm_dd_interval=20 scope=spfile * ERROR at line 1: ORA-00911: invalid character
SYS@lhrdb> alter system set "_lm_dd_interval"=20 scope=spfile;
System altered.
SYS@lhrdb> alter system reset "_lm_dd_interval" scope=spfile sid=‘*‘;
System altered.
|
普通用户是不具备查询隐含参数的权限的，可以通过创建视图和同义词的方式来解决这个问题，如下所示：
| CREATE OR REPLACE VIEW VW_YH_PARAMETER_LHR AS SELECT A.INDX, A.KSPPINM NAME, A.KSPPDESC, B.KSPPSTVL FROM X$KSPPI A, X$KSPPCV B WHERE A.INDX = B.INDX AND A.KSPPINM LIKE ‘/_%‘ ESCAPE ‘/‘ --TRANSLATE (ksppinm, ‘_‘, ‘#‘) LIKE ‘#%‘ ; GRANT SELECT ON VW_YH_PARAMETER_LHR TO PUBLIC; CREATE PUBLIC SYNONYM VW_YH_PARAMETER_LHR FOR SYS.VW_YH_PARAMETER_LHR; |
注意：本小节内容多数来源于杨廷琨老师的博客。
1、普通参数
普通参数就是Oracle系统正常使用的一些参数。查询Oracle初始化参数的方式大约有如下几种：
表 3-8 查询Oracle初始化参数的方式
一般在查询初始化参数的时候都习惯性的使用SHOW PARAMETER，也就是查询V$PARAMETER视图。V$PARAMETER视图反映的是初始化参数在当前会话中生效的值，而V$SYSTEM_PARAMETER反映的才是实例级上的初始化参数。有关视图V$PARAMETER的解释参考下表：
表 3-9 V$PARAMETER视图解释
首先看一下V$PARAMETER和V$PARAMETER2的区别，这个区别同样适用于V$SYSTEM_PARAMETER和V$SYSTEM_PARAMETER2：
SELECT NAME, VALUE FROM V$PARAMETER
MINUS
SELECT NAME, VALUE FROM V$PARAMETER2;
SELECT NAME, VALUE FROM V$PARAMETER2
MINUS
SELECT NAME, VALUE FROM V$PARAMETER;
现在这两个视图的结果一目了然了。进一步的研究可以看到底层的数据源来自两个不同的位置，V$PARAMETER来自x$ksppcv ，V$PARAMETER2来自x$ksppcv2，数据源的不同也正是前端的不同。
一般在查询初始化参数的时候都习惯性的使用SHOW PARAMETER，也就是查询V$PARAMETER视图。V$PARAMETER视图反映的是初始化参数在当前会话中生效的值，而V$SYSTEM_PARAMETER反映的才是实例级上的初始化参数。
我们通过query_rewrite_enabled这个参数来做一个验证。
| SQL> show parameter query_rewrite_enabled NAME TYPE VALUE ------------------------------------ ----------- -------------------- query_rewrite_enabled string TRUE SQL> select name, value 2 from v$parameter 3 where name = ‘query_rewrite_enabled‘; NAME VALUE ---------------------------------------- ---------------------------- query_rewrite_enabled TRUE SQL> select name, value 2 from v$system_parameter 3 where name = ‘query_rewrite_enabled‘; NAME VALUE ---------------------------------------- ---------------------------- query_rewrite_enabled TRUE |
这时候如果在会话级修改 query_rewrite_enabled 这个初始化参数：
| SQL> alter session set query_rewrite_enabled = false; 会话已更改。 SQL> show parameter query_rewrite_enabled NAME TYPE VALUE ------------------------------------ ----------- ------------------- query_rewrite_enabled string FALSE SQL> select name, value 2 from v$parameter 3 where name = ‘query_rewrite_enabled‘; NAME VALUE ---------------------------------------- --------------------------- query_rewrite_enabled FALSE SQL> select name, value 2 from v$system_parameter 3 where name = ‘query_rewrite_enabled‘; NAME VALUE ---------------------------------------- --------------------------- query_rewrite_enabled TRUE |
可以看到，show parameter 和查询 v$parameter 视图的结果都是 FALSE，而刚才做的修改只是会话级，并没有修改系统的初始化参数。我们应该形成的知识常识：V$PARAMETER 视图反映的是初始化参数在当前会话中生效的值，而 V$SYSTEM_PARAMETER 反映的才是实例级上的初始化参数。
再来看看延迟参数修改的情况：
| SQL> select name, value 2 from v$parameter 3 where name = ‘recyclebin‘; NAME VALUE ---------------------------------------- ------------------------------------ recyclebin on SQL> select name, value 2 from v$system_parameter 3 where name = ‘recyclebin‘; NAME VALUE ---------------------------------------- ------------------------------------ recyclebin on SQL> alter system set recyclebin = off deferred scope = memory; 系统已更改。 SQL> select name, value 2 from v$parameter 3 where name = ‘recyclebin‘; NAME VALUE ---------------------------------------- ------------------------------------ recyclebin on SQL> select name, value 2 from v$system_parameter 3 where name = ‘recyclebin‘; NAME VALUE ---------------------------------------- ------------------------------------ recyclebin OFF |
结果和前面的恰好反过来，v$parameter 视图中的结果没有发生变化，而 v$system_parameter 视图的结果变成了 OFF。
这是因为延迟修改对数据库中当前存在的会话不生效，因此反映当前会话情况的 v$parameter 视图结果不变，而对于系统而言，初始化参数已经改变，而且所有新建会话的参数也会改变，所以 v$system_parameter 视图的结果发生了改变。
| SQL> CONN / as sysdba 已连接。 SQL> select name, value 2 from v$parameter 3 where name = ‘recyclebin‘; NAME VALUE ---------------------------------------- --------------------------- recyclebin OFF SQL> select name, value 2 from v$system_parameter 3 where name = ‘recyclebin‘; NAME VALUE ---------------------------------------- --------------------------- recyclebin OFF |
根据这两个例子可以获得的常识是：利用 V$PARAMETER 视图获取系统的启动初始化参数是不准确的，因为它获取的是当前会话的参数，应该从 V$SYSTEM_PARAMETER 视图来获取。
使用 SHOW PARAMETER 查询，看到的是当前会话可以看到的初始化参数，那么这个参数导致是全局设置还是当前实例设置的，是从这个命令中看不到的。虽然 Oracle 提供了 GV$ 开头的初始化参数，可以用来查询两个实例上的设置，但是情况并不是这么简单的。
一个简单的例子：
| SQL> show parameter open_cursors NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ open_cursors integer 300 SQL> alter system set open_cursors = 500 scope = both sid = ‘test1‘; 系统已更改。 SQL> disc 从 Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options 断开 SQL> set instance test2 Oracle Database 11g Release 11.1.0.0.0 - Production SQL> conn sys as sysdba 输入口令: 已连接。 SQL> alter system set open_cursors = 400 scope = both sid = ‘test2‘; 系统已更改。 SQL> disc 从 Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options 断开 SQL> set instance local Oracle Database 11g Release 11.1.0.0.0 - Production SQL> conn / as sysdba 已连接。 |
现在来看看不同的查询方法得到的结果：
| SQL> select name, value 2 from v$parameter 3 where name = ‘open_cursors‘; NAME VALUE ------------------------------ -------------------------------------------------- open_cursors 500 SQL> select inst_id, name, value 2 from gv$parameter 3 where name = ‘open_cursors‘; INST_ID NAME VALUE ---------- ------------------------------ -------------------------------------------------- 1 open_cursors 500 2 open_cursors 400 SQL> show parameter open_cursors NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ open_cursors integer 500 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 400 SQL> show spparameter open_cursors SID NAME TYPE VALUE -------- ----------------------------- ----------- ---------------------------- * open_cursors integer 300 test2 open_cursors integer 400 test1 open_cursors integer 500 |
似乎除了看不到全局设置外，GV$PARAMETER 参数和 V$SPPARAMETER 没有什么不同，其实不然，如果 alter system set 的时候只修改了 spfile 或只修改了 memory 参数，结果就会不同：
| SQL> alter system set open_cursors = 600 scope = memory sid = ‘test1‘; 系统已更改。 SQL> alter system set open_cursors = 700 scope = spfile sid = ‘test2‘; 系统已更改。 SQL> select name, value 2 from v$parameter 3 where name = ‘open_cursors‘; NAME VALUE ------------------------------ -------------------------------------------------- open_cursors 600 SQL> select inst_id, name, value 2 from gv$parameter 3 where name = ‘open_cursors‘; INST_ID NAME VALUE ---------- ------------------------------ -------------------------------------------------- 1 open_cursors 600 2 open_cursors 400 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 |
从上面的对比就可以看出，通过 GV$ 视图访问的结果和 SPFILE 中包含的信息其实是两回事。
除了上面介绍的几种视图之外，CREATE PFILE 其实也是一个不错的选择，在10g 以前只能 CREATE PFILE FROM SPFILE，得到的结果类似于对 VSPPARAMETER 视图的查询，而11g增加了 CREATE PFILE FROM MEMORY 选项，这个得到的结果类似于从 GV$SYSTEM_PARAMETER 视图获取的查询。
其实这里还有一个问题，就是 GV$SPPARAMETER 是否有意义。因为 V$SPPARAMETER 参数本身就包含了 SID 列，SPFILE 中本身就包含了所有实例的设置，那么查询 GV$SPPARAMETER 视图是否就意义不大呢，其实不然。
因为 RAC 的各个节点可以使用统一的 SPFILE 启动，同样也可以选择不同的 SPFILE 来进行启动，这时 GV$SPPARAMETER 视图中获取结果，才是真正各个实例 SPFILE 中设置的结果。
这样说比较难以理解，看一个简单的例子：
| SQL> select inst_id, name, value 2 from gv$system_parameter 3 where name = ‘open_cursors‘; INST_ID NAME VALUE ---------- ------------------------------ -------------------------------------------------- 1 open_cursors 600 2 open_cursors 400 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, sid, name, value 2 from gv$spparameter 3 where name = ‘open_cursors‘; INST_ID SID NAME VALUE ---------- ---------- ------------------------------ ------------------------------------ 1 * open_cursors 300 1 test1 open_cursors 500 1 test2 open_cursors 700 2 * open_cursors 300 2 test1 open_cursors 500 2 test2 open_cursors 700 已选择6行。 SQL> select inst_id, name, value 2 from gv$system_parameter 3 where name = ‘spfile‘; INST_ID NAME VALUE ---------- ------------------------------ -------------------------------------------------- 1 spfile +DATA/test/spfiletest.ora 2 spfile +DATA/test/spfiletest.ora |
下面里面内存中参数来创建 SPFILE，并利用新建的 SPFILE 来启动当前实例：
| SQL> create spfile=‘/export/home/oracle/spfiletest1.ora‘ from memory; 文件已创建。 SQL> host $ vi /export/home/oracle/inittest1.ora "/export/home/oracle/inittest1.ora" [New file] spfile=/export/home/oracle/spfiletest1.ora "/export/home/oracle/inittest1.ora" [New file] 2 lines, 44 characters $ exit 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 数据库装载完毕。 数据库已经打开。 |
下面检查 spfile 中的设置：
| SQL> select inst_id, name, value 2 from gv$system_parameter 3 where name = ‘spfile‘; INST_ID NAME VALUE ---------- ------------------------------ -------------------------------------------------- 1 spfile /export/home/oracle/spfiletest1.ora 2 spfile +DATA/test/spfiletest.ora SQL> select inst_id, name, value 2 from gv$system_parameter 3 where name = ‘open_cursors‘; INST_ID NAME VALUE ---------- ------------------------------ -------------------------------------------------- 1 open_cursors 600 2 open_cursors 400 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> select inst_id, sid, name, value 2 from gv$spparameter 3 where name = ‘open_cursors‘; INST_ID SID NAME VALUE ---------- ---------- ------------------------------ -------------------------------- 2 * open_cursors 300 2 test1 open_cursors 500 2 test2 open_cursors 700 1 test1 open_cursors 600 1 test2 open_cursors 400 |
可以看到，由于两个实例采用了不同的 SPFILE，导致两个实例上设置的对方实例的初始化参数值，与对方实例上当前设置值不符。
在上面的例子中，两个实例上真正的参数设置查询方式为：
| SQL> select inst_id, sid, name, value 2 from gv$spparameter 3 where name = ‘open_cursors‘ 4 and substr(sid, -1) = to_char(inst_id); INST_ID SID NAME VALUE ---------- ---------- ------------------------------ ----------------------------------- 2 test2 open_cursors 700 1 test1 open_cursors 600
|
判断数据库启动是否启用了 SPFILE 很简单，只需要通过 SHOW PARAMETER SPFILE 命令就可以看到：
| 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 |
上面就列出了 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 中的设置并不相同。
| 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 test1.__oracle_base=‘/data/oracle‘#ORACLE_BASE set from environment test2.__oracle_base=‘/data/oracle‘#ORACLE_BASE set from environment test2.__pga_aggregate_target=260046848 test1.__pga_aggregate_target=260046848 test2.__sga_target=775946240 test1.__sga_target=775946240 test2.__shared_io_pool_size=0 test1.__shared_io_pool_size=0 test2.__shared_pool_size=218103808 test1.__shared_pool_size=234881024 test2.__streams_pool_size=0 test1.__streams_pool_size=0 *.audit_file_dest=‘/data/oracle/admin/test/adump‘ *.audit_trail=‘db‘ *.cluster_database=true *.cluster_database_instances=3 *.compatible=‘11.1.0.0.0‘ *.control_files=‘+DATA/test/controlfile/current.529.684067899‘ *.db_block_size=8192 *.db_create_file_dest=‘+DATA‘ *.db_domain=‘‘ *.db_name=‘test‘ *.diagnostic_dest=‘/data/oracle‘ test1.instance_number=1 test2.instance_number=2 *.log_archive_config=‘‘ *.log_archive_dest_1=‘LOCATION=/data/oracle/oradata/test/archivelog‘ *.open_cursors=300 test1.open_cursors=500 test2.open_cursors=700 *.pga_aggregate_target=256901120 *.processes=150 *.remote_login_passwordfile=‘EXCLUSIVE‘ test1.remote_login_passwordfile=‘SHARED‘ *.sga_target=772800512 test1.thread=1 test2.thread=2 *.undo_tablespace=‘UNDOTBS1‘ test2.undo_tablespace=‘UNDOTBS2‘ |
使用了这个语句，所有的 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 *.__pga_aggregate_target=248M *.__sga_target=740M *.__shared_io_pool_size=0 test1.__shared_pool_size=224M test2.__shared_pool_size=208M *.__streams_pool_size=0 *._always_anti_join=‘CHOOSE‘ *._always_semi_join=‘CHOOSE‘ 《《《《。。。。。。。。篇幅原因，有省略。。。。。。。。》》》》 *.processes=150 *.query_rewrite_enabled=‘TRUE‘ test1.remote_login_passwordfile=‘SHARED‘ test2.remote_login_passwordfile=‘EXCLUSIVE‘ *.resource_manager_plan=‘‘ *.result_cache_max_size=3808K *.sga_target=740M *.skip_unusable_indexes=TRUE test1.thread=1 test2.thread=2 test1.undo_tablespace=‘UNDOTBS1‘ test2.undo_tablespace=‘UNDOTBS2‘ |
但是这种方法显然也存在问题，首先从得到的结果看，里面除了包含用户设置的初始化参数外，还包含了大量的隐含参数。如果这些隐患参数是 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 只对当前实例有效，虽然获取的结果包含多个实例的设置，但是这些设置可能和其他实例上的真正设置并不相符。
如何判断一个初始化参数的值是否是默认参数值？Oracle在视图V$SYSTEM_PARAMETER或V$PARAMETER中提供了一个列ISDEFAULT，表示当前设置的值是否是数据库的默认值。
Oracle 在视图 V$SYSTEM_PARAMETER 中提供了一个列 ISDEFAULT，表示当前设置的值是否是数据库的默认值：
| SQL> select name, value, isdefault 2 from v$system_parameter 3 where name = ‘open_cursors‘; NAME VALUE ISDEFAULT ------------------------------ -------------------------------------------------- --------- open_cursors 400 FALSE SQL> select isdefault, count(*) 2 from v$system_parameter 3 group by isdefault; ISDEFAULT COUNT(*) --------- ---------- TRUE 267 FALSE 22 |
根据这个结果可以看到，数据库中绝大部分的初始化参数设置都是默认值。
| SQL> select name, value, isdefault 2 from v$system_parameter 3 where name = ‘undo_retention‘; NAME VALUE ISDEFAULT ------------------------------ -------------------------------------------------- --------- undo_retention 900 TRUE SQL> select sid, name, value 2 from v$spparameter 3 where name = ‘undo_retention‘; SID NAME VALUE ---------- ------------------------------ -------------------------------------------------- * undo_retention SQL> alter system set undo_retention = 900; 系统已更改。 SQL> select name, value, isdefault 2 from v$system_parameter 3 where name = ‘undo_retention‘; NAME VALUE ISDEFAULT ------------------------------ -------------------------------------------------- --------- undo_retention 900 TRUE SQL> select sid, name, value 2 from v$spparameter 3 where name = ‘undo_retention‘; SID NAME VALUE ---------- ------------------------------ -------------------------------------------------- * undo_retention 900 |
对于手工设置的初始化参数与系统默认值相同的情况，通过 v$system_parameter 视图是无法区分的。
同样通过查询 V$SPPARAMETER 视图检查 SPFILE 的设置也不准确，因为初始化参赛可能是通过 PFILE 设置的，或者是实例启动后由 ALTER SYSTEM 命令进行过修改。
对于这种情况，其实上一篇文章中介绍的 CREATE PFILE FROM MEMORY 或 CREATE 的方式是可以看到的，不过既然这种方法能够查询得到，那么数据库中一定是在默写地方进行了记录。
实际上查询 V$SYSTEM_PARAMETER4 视图就可以获取到所有用户设置的初始化参数。
| SQL> select sid, name, display_value value 2 from v$system_parameter4 3 where name not like ‘/_%‘ escape ‘/‘; SID NAME VALUE ---------- ------------------------------ ----------------------------------------------- test1 processes 150 test1 spfile +DATA/test/spfiletest.ora test1 resource_manager_plan SCHEDULER[0x2C0E]:DEFAULT_MAINTENANCE_PLAN test1 sga_target 740M test1 control_files +DATA/test/controlfile/current.529.684067899 test1 db_block_size 8192 test1 compatible 11.1.0.0.0 test1 log_archive_config test1 log_archive_dest_1 LOCATION=/data/oracle/oradata/test/archivelog test1 log_buffer 4197376 test1 cluster_database TRUE test1 cluster_database_instances 3 test1 db_create_file_dest +DATA test1 thread 1 test1 undo_tablespace UNDOTBS1 test1 undo_retention 900 test1 instance_number 1 test1 remote_login_passwordfile SHARED test1 db_domain test1 plsql_warnings DISABLE:ALL test1 result_cache_max_size 3808K test1 core_dump_dest /data/oracle/diag/rdbms/test/test1/cdump test1 audit_file_dest /data/oracle/admin/test/adump test1 audit_trail DB test1 db_name test test1 open_cursors 400 test1 optimizer_mode ALL_ROWS test1 query_rewrite_enabled TRUE test1 pga_aggregate_target 245M test1 optimizer_dynamic_sampling 2 test1 skip_unusable_indexes TRUE test1 diagnostic_dest /data/oracle 已选择32行。 |
而事实上，当数据库执行 CREATE PFILE FROM MEMORY 命令时，Oracle 创建 PFILE 的数据源就是 V$SYSTEM_PARAMETER4 这个视图。
前面介绍了很多种查询初始化参数的方法，其实还有一个方法也是很有用的，就是通过 alert 文件检查数据库加载的所有非默认值的初始化参数。
| SQL> shutdown immediate 数据库已经关闭。 已经卸载数据库。 ORACLE 例程已经关闭。 SQL> startup ORACLE 例程已经启动。 Total System Global Area 776896512 bytes Fixed Size 2098776 bytes Variable Size 246077864 bytes Database Buffers 524288000 bytes Redo Buffers 4431872 bytes 数据库装载完毕。 数据库已经打开。 SQL> host bash-3.00$ tail -200 /data/oracle/diag/rdbms/test/test1/trace/alert_test1.log Sat Jun 13 15:20:03 2009 Stopping background process SMCO Stopping background process FBDA Shutting down instance: further logons disabled Sat Jun 13 15:20:05 2009 Stopping background process CJQ0 Stopping background process QMNC Stopping background process MMNL Stopping background process MMON Shutting down instance (immediate) License high water mark = 6 ALTER DATABASE CLOSE NORMAL Sat Jun 13 15:20:10 2009 SMON: disabling tx recovery SMON: disabling cache recovery Sat Jun 13 15:20:10 2009 Shutting down archive processes Archiving is disabled . . . Sat Jun 13 15:20:19 2009 Instance shutdown complete Sat Jun 13 15:20:22 2009 Some alert messages have been suppressed because they were produced too early Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 Interface type 1 ce1 255.255.255.0 configured from OCR for use as a cluster interconnect WARNING 255.255.255.0 could not be translated to a network address error 1 Interface type 1 ce0 255.255.255.0 configured from OCR for use as a public interface WARNING 255.255.255.0 could not be translated to a network address WARNING: No cluster interconnect has been specified. Depending on the communication driver configured Oracle cluster traffic may be directed to the public interface of this machine. Oracle recommends that RAC clustered databases be configured with a private interconnect for enhanced security and performance. Picked latch-free SCN scheme 3 Autotune of undo retention is turned on. LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up ORACLE RDBMS Version: 11.1.0.6.0. Using parameter settings in server-side pfile /data/oracle/product/11.1/database/dbs/inittest1.ora System parameters with non-default values: processes = 150 spfile = "+DATA/test/spfiletest.ora" sga_target = 740M 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" cluster_database = TRUE cluster_database_instances= 3 db_create_file_dest = "+DATA" thread = 1 undo_tablespace = "UNDOTBS1" undo_retention = 900 instance_number = 1 remote_login_passwordfile= "SHARED" db_domain = "" audit_file_dest = "/data/oracle/admin/test/adump" audit_trail = "DB" db_name = "test" open_cursors = 500 pga_aggregate_target = 245M diagnostic_dest = "/data/oracle" Cluster communication is configured to use the following interface(s) for this instance 172.0.2.62 cluster interconnect IPC version:Oracle UDP/IP (generic) IPC Vendor 1 proto 2 Sat Jun 13 15:20:23 2009 PMON started with pid=2, OS id=19138 Sat Jun 13 15:20:23 2009 VKTM started with pid=4, OS id=19140 at elevated priority VKTM running at (20)ms precision Sat Jun 13 15:20:24 2009 DIAG started with pid=6, OS id=19144 Sat Jun 13 15:20:24 2009 DBRM started with pid=8, OS id=19146 . . . Completed: ALTER DATABASE OPEN Sat Jun 13 15:20:41 2009 Starting background process CJQ0 Sat Jun 13 15:20:41 2009 CJQ0 started with pid=64, OS id=19434 Setting Resource Manager plan SCHEDULER[0x2C0E]:DEFAULT_MAINTENANCE_PLAN via scheduler window Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter |
检查 alert 文件的方法不但可以获取当前实例所有非默认初始化参数的信息，还是初始化参数文件丢失后用来恢复初始化参数文件的一种方法。
如何判断一个初始化参数的值是否是延迟生效、是否是动态参数？动态参数指的是可以使用ALTER SESSION或ALTER SYSTEM在数据库运行时进行修改并能立即生效的参数。静态参数指的是只能通过修改参数文件且数据库必须要重启才能生效的参数。Oracle在视图V$PARAMETER中提供了一个列ISSYS_MODIFIABLE，若值为IMMEDIATE代表参数可用ALTER SYSTEM更改，且立刻生效，该参数属于动态参数；若值为DEFERRED代表参数可以用ALTER SYSTEM更改，但是在新连接的会话中生效，该参数属于动态参数；若值为FALSE代表参数不能使用ALTER SYSTEM更改，但是若当前参数文件使用的是SPFILE，则可以使用ALTER SYSTEM更改，且下次实例启动生效，该参数属于静态参数。
静态参数举例：
| SYS@lhrdb> alter system set processes=300; alter system set processes=300 * ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified |
动态参数举例：
| SYS@lhrdb> alter system set undo_retention=10800 ;
System altered. |
动态延迟（DEFERRED）参数举例。DEFERRED指定系统修改是否只对以后的会话生效（对当前建立的会话无效，包括执行此修改的会话）。默认情况下，ALTER SYSTEM命令会立即生效，但是有些参数不能“立即”修改，只能为新建立的会话修改这些参数。
| SYS@lhrdb> alter system set sort_area_size = 65536; alter system set sort_area_size = 65536 * ERROR at line 1: ORA-02096: specified initialization parameter is not modifiable with this option
SYS@lhrdb> alter system set sort_area_size = 65536 deferred;
System altered. |
About Me
| ............................................................................................................................... ● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用 ● 本文在itpub（http://blog.itpub.net/26736162）、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2127338/ ● 本文博客园地址：http://www.cnblogs.com/lhrbest/p/6013675.html ● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● QQ群：230161599 微信群：私聊 ● 联系我请加QQ好友(642808185)，注明添加缘由 ● 于 2016-10-27 12:00 ~ 2016-10-28 19:00 在中行完成 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 【版权所有，文章允许转载，但须以链接方式注明源地址，否则追究法律责任】 ............................................................................................................................... 手机长按下图识别二维码或微信客户端扫描下边的二维码来关注小麦苗的微信公众号：xiaomaimiaolhr,免费学习最实用的数据库技术。
|
原文：http://www.cnblogs.com/lhrbest/p/6013675.html
