基於oracle 11.2.0.4如何獲取變更或修改後的所有資料庫引數資訊

分析結論
1,基於oracle 11.2.0.4之資料庫變更過的資料庫引數（包括隱含引數和非隱含引數），會體現在資料庫告警日誌啟動資訊中


2，提示資訊如下
System parameters with non-default values


3，也可以透過資料庫spfile獲取上述同樣的功能


分析明細
1,作業系統版本
[oracle@mygirl ~]$ more /etc/redhat-release 
Red Hat Enterprise Linux Server release 6.5 (Santiago)


2，資料庫版本
SQL> select * from v$version where rownum=1;


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production


3，非隱含引數共計 352個
SQL> select count(*) from v$parameter;


  COUNT(*)
----------
       352




所有的引數共計 2914個
SQL> select 
  2  count(ksppinm),count(distinct ksppinm)
  3    from x$ksppi x, x$ksppcv y
  4   where (x.indx = y.indx);


COUNT(KSPPINM) COUNT(DISTINCTKSPPINM)
-------------- ----------------------
          2914                   2914             




4,啟動資料庫檢視告警日誌，未修改資料庫引數前的資料庫啟動資訊，由告警日誌會有提示資訊 System parameters with non-default values
SQL> startup
ORACLE instance started.


Total System Global Area  526110720 bytes
Fixed Size                  2254864 bytes
Variable Size             381683696 bytes
Database Buffers          138412032 bytes
Redo Buffers                3760128 bytes
Database mounted.
Database opened.
SQL> 


[oracle@mygirl ~]$ cd /oracle/diag/rdbms/other/other/trace/
[oracle@mygirl trace]$ tail -f alert_other.log 


SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /oracle/product/11.2.0/db_1
System name:    Linux
Node name:      mygirl
Release:        2.6.32-431.el6.x86_64
Version:        #1 SMP Sun Nov 10 22:19:54 EST 2013
Machine:        x86_64
Using parameter settings in server-side spfile /oracle/product/11.2.0/db_1/dbs/spfileother.ora
System parameters with non-default values:
  processes                = 150
  memory_target            = 504M
  control_files            = "/oracle/mygirl/other/control01.ctl"
  control_files            = "/oracle/fast_recovery_area/other/control02.ctl"
  db_block_size            = 8192
  compatible               = "11.2.0.4.0"
  db_recovery_file_dest    = "/oracle/fast_recovery_area"
  db_recovery_file_dest_size= 4977M
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=otherXDB)"
  audit_file_dest          = "/oracle/admin/other/adump"
  audit_trail              = "NONE"
  db_name                  = "other"
  open_cursors             = 300
  diagnostic_dest          = "/oracle"




5,修改隱含引數及非隱含引數
SQL> select 
  2         x.inst_id as inst_id,
  3         ksppinm as name,
  4         ksppstvl as value_1,
  5         ksppdesc as desc_1
  6    from x$ksppi x, x$ksppcv y
  7   where (x.indx = y.indx) and
  8         lower(x.ksppinm) like '%_optimizer_autostats_job%'; 


   INST_ID NAME                           VALUE_1                                            DESC_1
---------- ------------------------------ -------------------------------------------------- --------------------------------------------------
         1 _optimizer_autostats_job       TRUE                                               enable/disable auto stats collection job




SQL> alter system set "_optimizer_autostats_job"=false scope=spfile;


System altered.


SQL> show parameter db_cache


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice                      string      ON
db_cache_size                        big integer 0


SQL> alter system set db_cache_size=150m;


System altered.


6,調整資料庫引數後的資料庫啟動告警日誌資訊
Using parameter settings in server-side spfile /oracle/product/11.2.0/db_1/dbs/spfileother.ora
System parameters with non-default values:
  processes                = 150
  memory_target            = 504M
  control_files            = "/oracle/mygirl/other/control01.ctl"
  control_files            = "/oracle/fast_recovery_area/other/control02.ctl"
  db_block_size            = 8192
  db_cache_size            = 152M
  compatible               = "11.2.0.4.0"
  db_recovery_file_dest    = "/oracle/fast_recovery_area"
  db_recovery_file_dest_size= 4977M
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=otherXDB)"
  audit_file_dest          = "/oracle/admin/other/adump"
  audit_trail              = "NONE"
  db_name                  = "other"
  open_cursors             = 300
  _optimizer_autostats_job = FALSE
  diagnostic_dest          = "/oracle"




7,如果資料庫已經執行了許久，可能是幾年，資料庫告警日誌的資料庫啟動資訊可能已截斷或清除，該怎麼辦呢，檢視資料庫SPFILE,可見資料庫所有變更的引數全部存在在spfile
SQL> show parameter spfile


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /oracle/product/11.2.0/db_1/db
                                                 s/spfileother.ora






[oracle@mygirl ~]$ cd $ORACLE_HOME/dbs
[oracle@mygirl dbs]$ strings spfileother.ora >all.ora


[oracle@mygirl dbs]$ more all.ora 
other.__db_cache_size=276824064
other.__java_pool_size=4194304
other.__large_pool_size=8388608
other.__oracle_base='/oracle'#ORACLE_BASE set from environment
other.__pga_aggregate_target=125829120
other.__sga_target=402653184
other.__shared_io_pool_size=0
other.__shared_pool_size=104857600
other.__streams_pool_size=0
*._optimizer_autostats_job=FALSE
*.audit_file_dest='/oracle/admin/other/adump'
*.audit_trail='none'
*.compatible='11.2.0.4.0'
*.control_files='/oracle/mygirl/other/contr
ol01.ctl','/oracle/fast_recovery_area/other/control02.ctl'
*.db_block_size=8192
*.db_cache_size=159383552
*.db_domain=''
*.db_name='other'
*.db_recovery_file_dest='/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=5218762752
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=otherXDB)'
*.memory_target=525336576
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'