【原】返璞归真：如何判断一个初始化参数是否来自默认设置

数据和云 2020-07-01

展开全文

杨廷琨（yangtingkun）

云和恩墨 CTO

高级咨询顾问，Oracle ACE 总监，ITPUB Oracle 数据库管理版版主

Oracle初始化参数之多，变化之繁复可能，我们又如何去分辨哪些参数来自缺省设置，哪些又经过修改调整呢？

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
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
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