杨廷琨(yangtingkun)
云和恩墨 CTO
高级咨询顾问,Oracle ACE 总监,ITPUB Oracle 数据库管理版版主
由于初始化参数可能的来源很多,那么如何去判断一个参数值来自何处的设置就显得非常重要。
判断数据库启动是否启用了 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 259SQL> 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
----------------
test1SQL> select sid, name, value
2 from v$spparameter
3 where name = 'open_cursors';SID NAME VALUE
---------- ------------------------------ --------------------------------------------------
test1 open_cursors 600
test2 open_cursors 400SQL> 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 1000SQL> 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 700SQL> 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 只对当前实例有效,虽然获取的结果包含多个实例的设置,但是这些设置可能和其他实例上的真正设置并不相符。
搜索 盖国强(Eygle) :eeygle,或者扫描下面二维码,备注:云和恩墨大讲堂,即可入群。每周与千人共享免费技术分享,与讲师在线讨论。
(帐号:OraNews)回复关键字获取资源
2016YHEMSZ ,云和恩墨大讲堂2016深圳交流会;
2016GOPSZ ,2016深圳全球运维大会PPT;
DBALife ,"DBA的一天"精品海报大图;
12cArch ,“Oracle 12c体系结构”精品海报大图;
DBA01 ,《Oracle DBA手记》第一本书下载;
YunHe ,“云和恩墨大讲堂”案例文档下载;