1、Oracle 10g/11gSPFILE與初始化參數趙元杰 zyj56812009.604 九月 20222 / 42前言Oracle 系統的調整就是相關參數的調整(修改)，而Oracle系統參數的在不同版本存在許多變化，如在Oracle9i有的參數在Oracle 10g可能被淘汰；此外，有些參數的修改也存在些風險，所以本課件整理了DBA在修改Oracle 10g或11g數據時的一些方法。在搜集與整理中時間較緊，可能存在一些遺漏，所以讀者可將本課件當成初步的入門來看待，而對實際環境的調整建議查閱原版，以免出現錯誤。04 九月 20223 / 42內容提要Oracle8i 初始化參數文件PFIL

2、EOracle9i/10g服務器參數文件SPFILE管理服務器參數文件SPFILESPFILE 與V$PARAMETERSPFILE與參數的修改Oracle 10g 參數高級管理Oracle 11g 新增加參數04 九月 20224 / 42Oracle 8i PFILE Oracle8i及以前的版本，系統啟動時訪問的參數文件為INITsid.ora ；(sid 是實例名)初始化參數文件INITsid.ora通常稱為pfile(parameter file)；INITsid.ora參數文件可用文本編輯器編輯；管理初始化參數通過編輯INITsid.ora修改來實現；任何修改需要重啟動實例才能生效

3、。04 九月 20225 / 42Oracle 9i/10g SPFILE Oracle9i/10g使用初始化參數文件SPFILEsid.oraSPFILEsid.ora簡稱為spfile；Spfile以二進制存放參數；Spfile用于在服務器端管理初始化參數；可使用ALTER SYSTEM或者ALTER SESSION來動態修改動態參數；有的參數更改可以立即生效；但有的參數修改后需要重啟動實例方可生效(如log_buffer)。04 九月 20226 / 42Oracle 9i/10g SPFILEOracle9i/10g的SPFILE可選擇使更改在當前實例有效；也可(或同時)將參數寫到sp

4、file參數文件中；所有對spfile的修改都可以在命令行完成，省去修改初始化參數文件的麻煩； 由于SPFILE是一個二進制文件，可以使用RMAN進行備份，即Oracle把參數文件也納入了備份恢復管理； 除了第一次啟動數據庫需要PFILE（然后可以根據PFILE創建SPFILE）,我們可以不再需要PFILE,ORACLE強烈推薦使用spfile，應用其新特性來存儲和維護初始化參數設置。 04 九月 20227 / 42Oracle 9i/10g SPFILESPFILE 所在目錄：Unix: $ORACLE_HOME/dbs; NT: $ORACLE_HOMEdatabase) 如果SPFIL

5、E已經存在，那么創建會返回以下錯誤： ERROR 位于第 1 行: ORA-32002: 無法創建已由例程使用的 SPFILE 04 九月 20228 / 42Oracle 9i/10g SPFILEOracle啟動時使用SPFILE 用startup重新啟動數據庫時，Oralce將會按照以下順序在缺省目錄中搜索參數文件： a. spfile$ORACLE_SID.ora 缺省目錄 UNIX: $ORACLE_HOME/dbs/ NT: $ORACLE_HOMEdatabase b. spfile.ora 缺省目錄 UNIX: $ORACLE_HOME/dbs/ NT: $ORACLE_HOM

6、Edatabase c. init$ORACLESID.ora 缺省目錄 UNIX: $ORACLE_HOME/dbs/ NT: $ORACLE_HOMEdatabase or $ORACLE_HOMEadmindb_namepfile 創建了spfile,重新啟動數據庫，Oracle會按順序搜索以上目錄，spfile就會自動生效。04 九月 20229 / 42管理 SPFILE從PFILE(initsid.ora)中創建SPFILE9i/10g安裝后SPFILE自動創建，默認使用SPFILE啟動數據庫；SPFILE也可由PFILE創建，新創建的SPFILE在下一次啟動數據庫時生效；CREA

7、TE SPFILE需要SYSDBA或者SYSOPER的權限；CREATE SPFILE語法如下： SQLCONNECT SYS/password AS SYSDBACREATE SPFILE=SPFILE-NAME FROM PFILE=PFILE-NAME -CREATE SPFILE例子： SQL create spfile from pfile;文件已創建。04 九月 202210 / 42管理 SPFILE從PFILEsid.ORA創建SPFILE參數文件：當需要PFILE文件前，要了解PFILE所在路徑；SQLCONNECT SYS/password AS SYSDBASQLCREA

8、TE SPFILE=c:ORACLEora92databasespfileORACLE9i.oraFROM PFILE= c:ORACLEora92databaseinitORACLE9i.ora;文件已創建。04 九月 202211 / 42管理 PFILE從SPFILEsid.ORA中創建PFILE文件：當需要閱讀SPFILE文件時，可將器創建成PFILE；具有sysdba角色，及創建PFILE：SQLCONNECT SYS/password AS SYSDBASQLCREATE PFILE=c:ORACLEora92databaseinitORACLE9i.oraFROM SPFILE=

9、c:ORACLEora92databasespfileORACLE9i.ora;文件已創建。04 九月 202212 / 42管理 PFILE-11g11g: Create PFILE from memory ：可從內存創建成PFILE；具有sysdba角色，及創建PFILE：SQLCONNECT SYS/password AS SYSDBASQLCREATE PFILE FROM MEMORY ;文件已創建。04 九月 202213 / 42SPFILE與V$PARAMETER可用下面兩種方法得到SPFILE信息：從V$parameter查詢實例使用的SPFILE: 用show命令得到實例使

10、用的SPFILE: SQL SHOW PARAMETER spfile NAME TYPE VALUE spfile string %ORACLE_HOME%DATABASESPFILE%ORACLE_SID%.ORA SQL SELECT name, value FROM v$parameter WHERE name = spfile; NAME VALUE spfile %ORACLE_HOME%DATABASESPFILE%ORACLE_SID%.ORA04 九月 202214 / 42SPFILE與V$PARAMETEROracle 的SPFILE存儲的參數可v$parameter視圖

11、查到；v$parameter 數據字典存放有用Alter system語句和alter session 修改的參數；V$parameter 的ISSES_MODIFIABLE列值=TRUE表示可用alter session語句修改：有的參數可用alter system/session修改，如：SQL select name,value,ISSES_MODIFIABLE from v$parameter where name=sort_area_size;NAME VALUE ISSES sort_area_size 65536 TRUEselect name,value,ISSES_MODIF

12、IABLE,ISSYS_MODIFIABLEfrom v$parameter where name=sort_area_size;NAME VALUE ISSES ISSYS_MOD sort_area_size 65536 TRUE DEFERRED04 九月 202215 / 42SPFILE與V$PARAMETER2Oracle 9i 增加了v$parameter2視圖；V$parameter2與v$parameter 具有同樣的作用；V$parameter存放的數據為一行，如：V$parameter2存放的數據為一行，如：SQL select name,value from v$par

13、ameter2;sga_target 167772160control_files C:ORACLEPRODUCT10.2.0ORADATAORA10GCONTROL01.CTLcontrol_files C:ORACLEPRODUCT10.2.0ORADATAORA10GCONTROL02.CTLcontrol_files C:ORACLEPRODUCT10.2.0ORADATAORA10GCONTROL03.CTLSQL select name,value from v$parameter;sga_target 167772160control_files C:ORACLEPRODUCT1

14、0.2.0ORADATAORA10GCONTROL01.CTL, C:ORACLEPRODUCT10.2.0ORADATAORA10GCONTROL02.CTL, C:ORACLEPRODUCT10.2.0ORADATAORA10GCONTROL03.CTL04 九月 202216 / 42SPFILE與參數的修改用SQL語句修改 SPFILE 參數用 “ALTER SYSTEM” 命令修改：ALTER SYSTEM SET = comment deferred SCOPE = memory | spfile | both sid = | * ;是參數的完整名稱，而是要設置的具體值，如 Aud

15、it_trail=TRUE comment是注釋字串 deferred表示參數延遲生效 MEMORY Default : 在當前實例內存有效SPFILE : 只寫到spfileBOTH : memory + spfile04 九月 202217 / 42SPFILE與參數的修改可用ALTER SYSTEM修改SPFILE文件參數值:例1.修改參數使實例和SPFILE都生效：例2.修改參數只寫到SPFILE中： SQL alter system set db_cache_size=24M scope=both; 系統已更改。ALTER SYSTEM SET control_files =$HOM

16、E/ORADATA/u01/ctrl01.ctl,$HOME/ORADATA/u02/ctrl02.ctl SCOPE=SPFILE;04 九月 202218 / 42SPFILE與參數的修改假設在RAC下有SALES兩個實例，則修改參數只是sales實例有效：修改參數使兩個實例都有效：SQL ALTER SYSTEM SET timed_statistics=FALSE SID=*; System altered.SQL ALTER SYSTEM SET timed_statistics=TRUE SID=sales; System altered.04 九月 202219 / 42SPFI

17、LE與參數的修改ALTER SYSTEM修改參數注意：SQL show parameter log_bufferNAME TYPE VALUE log_buffer integer 262144SQL ALTER SYSTEM SET LOG_BUFFER=6553600 scope=both;ALTER SYSTEM SET LOG_BUFFER=6553600 scope=both *ERROR at line 1:ORA-02095: specified initialization parameter cannot be modifiedSQL ALTER SYSTEM SET LOG

18、_BUFFER=6553600 scope=spfile;System altered.04 九月 202220 / 42SPFILE與參數的修改能否用ALTER SYSTEM帶BOTH選項修改參數：V$parameter的ISINSTANCE_MODIFIABLE=TRUE = BOTH修改V$parameter的ISINSTANCE_MODIFIABLE=FALSE=SPFILE修改select name,ISDEFAULT,ISINSTANCE_MODIFIABLE,ISDEPRECATED from v$parameter where name in (db_cache_size,lo

19、g_buffer);NAME ISDEFAULT ISINS ISDEP db_cache_size TRUE TRUE FALSElog_buffer TRUE FALSE FALSE04 九月 202221 / 42SPFILE與參數的修改只能用ALTER SYSTEM帶SPFILE修改參數：V$parameter的ISINSTANCE_MODIFIABLE=FALSE下面是10g版本修改后需要重啟動的參數: select name,value,ISINSTANCE_MODIFIABLEFrom v$parameter where ISINSTANCE_MODIFIABLE=FALSE;N

20、AME VALUE ISINS tracefile_identifier FALSElock_name_space FALSEprocesses 160 FALSEsessions 181 FALSEinstance_groups FALSEevent FALSEsga_max_size 167772160 FALSEpre_page_sga FALSE FALSEshared_memory_address 0 FALSE. . .已選擇109行04 九月 202222 / 42SPFILE與參數的修改只能用ALTER SYSTEM帶SPFILE修改參數：V$parameter的ISINSTA

21、NCE_MODIFIABLE=FALSE下面是10g版本修改后需要重啟動的參數: SQL -下面語句設置查詢環境 ：SQL -查詢修改后需要重啟動的參數：SQL select name,value,ISINSTANCE_MODIFIABLE2* From v$parameter where ISINSTANCE_MODIFIABLE=FALSE;NAME VALUE ISINS lock_name_space FALSEprocesses 200 FALSEsessions 225 FALSEinstance_groups FALSE client_result_cache_size 0 FA

22、LSEclient_result_cache_lag 3000 FALSEtracefile_identifier FALSE已選擇113行。 04 九月 202223 / 42SPFILE與參數的修改可用ALTER SESSION 修改的參數：V$parameter的ISSES_MODIFIABLE=TRUE:表示會話級可修改(設置)內存排序區大小是可以在會話級修改： SQL select name,value,ISSES_MODIFIABLE from v$parameter where name=sort_area_size;NAME VALUE ISSES sort_area_size

23、 65536 TRUE. . .SQLALTER SESSION SET SORT_AREA_SIZE=20m;04 九月 202224 / 42SPFILE與參數的修改可用ALTER SESSION 修改的參數：V$parameter的ISSES_MODIFIABLE=TRUEcol name for a50col value for a20select name,value,ISSES_MODIFIABLEFROM V$PARAMETERWHERE ISSES_MODIFIABLE=TRUE;NAME VALUE ISSES timed_statistics TRUE TRUEtimed_

24、os_statistics 0 TRUEnls_language AMERICAN TRUEnls_territory AMERICA TRUE04 九月 202225 / 42SPFILE與參數的修改可用ALTER SESSION 修改的參數只對該會話有效：左邊SYSTEM會話修改的參數右邊的會話并沒有被改變: SQL connect system/zhaoabc已連接。SQL show parameter SORT_AREA_SIZENAME TYPE VALUE sort_area_size integer 65536SQL ALTER SESSION SET SORT_AREA_SIZ

25、E=4m;ALTER SESSION SET SORT_AREA_SIZE=4m *第 1 行出現錯誤:ORA-02248: 無效的 ALTER SESSION 選項SQL ALTER SESSION SET SORT_AREA_SIZE=4096000;會話已更改。SQL show parameter SORT_AREA_SIZENAME TYPE VALUE sort_area_size integer 4096000SQL set time on23:15:08 SQL SQL show parameter SORT_AREA_SIZENAME TYPE VALUE sort_area_

26、size integer 65536SQL show userUSER 為 SYSTEMSQL SQL set time on23:15:10 SQL 04 九月 202226 / 42Oracle 10g /11g參數高級管理一般參數：不贊成使用與廢棄的參數；隱藏的參數；04 九月 202227 / 42Oracle 參數高級管理Oracle基本參數28個：SESSIONSSGA_TARGET高級參數228個:DB_CACHE_SIZESORT_AREA_SIZE帶_的參數:未歸檔的參數：04 九月 202228 / 42Oracle 參數高級管理Oracle基本參數28個：CLUSTER_

27、DATABASECOMPATIBLECONTROL_FILESDB_BLOCK_SIZEDB_CREATE_FILE_DESTDB_CREATE_ONLINE_LOG_DEST_nDB_DOMAINDB_NAMEDB_RECOVERY_FILE_DESTDB_RECOVERY_FILE_DEST_SIZEINSTANCE_NUMBERJOB_QUEUE_PROCESSESLOG_ARCHIVE_DEST_nLOG_ARCHIVE_DEST_STATE_n04 九月 202229 / 42Oracle 參數高級管理Oracle基本參數28個：NLS_LANGUAGENLS_TERRITORYOP

28、EN_CURSORSPGA_AGGREGATE_TARGETPROCESSESREMOTE_LISTENERREMOTE_LOGIN_PASSWORDFILEROLLBACK_SEGMENTSSESSIONSSGA_TARGETSHARED_SERVERSSTAR_TRANSFORMATION_ENABLEDUNDO_MANAGEMENTUNDO_TABLESPACE04 九月 202230 / 42Oracle 參數高級管理10g反對使用的參數：select name from v$parameter where isdeprecated =TRUE;NAMEbuffer_pool_keep

29、 (replaced by DB_KEEP_CACHE_SIZE)buffer_pool_recycle (replaced by DB_RECYCLE_CACHE_SIZE)drs_start (replaced by DG_BROKER_START - 9.2)fast_start_io_target (replaced by FAST_START_MTTR_TARGET - 9.0)global_context_pool_sizelock_name_spacelog_archive_startmax_enabled_rolesparallel_automatic_tuningparall

30、el_server (replaced by CLUSTER_DATABASE - 9.0)parallel_server_instances (replaced by CLUSTER_DATABASE_INSTANCES)plsql_compiler_flags (replaced by PLSQL_CODE_TYPE and PLSQL_DEBUG)serial_reuse04 九月 202231 / 42Oracle 參數高級管理11g反對使用的參數：SQL select name from v$parameter where isdeprecated =TRUE;NAMElock_na

31、me_spaceinstance_groupsbuffer_pool_keepbuffer_pool_recyclemax_commit_propagation_delaylog_archive_startstandby_archive_destlog_archive_local_firstparallel_serverparallel_server_instancesfast_start_io_targetserial_reusemax_enabled_rolesremote_os_authentglobal_context_pool_sizeplsql_v2_compatibilitypl

32、sql_debugbackground_dump_destuser_dump_destcommit_writesql_traceparallel_automatic_tuningsql_versiondrs_start已選擇24行。04 九月 202232 / 42Oracle 參數高級管理下面是10g過時的參數：OPTIMIZER_MAX_PERMUTATIONSORACLE_TRACE_COLLECTION_NAMEORACLE_TRACE_COLLECTION_PATHORACLE_TRACE_COLLECTION_SIZEORACLE_TRACE_ENABLEORACLE_TRACE_

33、FACILITY_NAMEORACLE_TRACE_FACILITY_PATHPARTITION_VIEW_ENABLEDPLSQL_NATIVE_C_COMPILERPLSQL_NATIVE_LINKERPLSQL_NATIVE_MAKE_FILE_NAMEPLSQL_NATIVE_MAKE_UTILITYROW_LOCKINGSERIALIZABLETRANSACTION_AUDITINGUNDO_SUPPRESS_ERRORSselect name from v$obsolete_parameter;DBLINK_ENCRYPT_LOGINHASH_JOIN_ENABLEDLOG_PAR

34、ALLELISMMAX_ROLLBACK_SEGMENTSMTS_CIRCUITSMTS_DISPATCHERSMTS_LISTENER_ADDRESSMTS_MAX_DISPATCHERSMTS_MAX_SERVERSMTS_MULTIPLE_LISTENERSMTS_SERVERSMTS_SERVICEMTS_SESSIONS04 九月 202233 / 42Oracle 參數高級管理下面是11g過時的參數-121個：plsql_compiler_flagsplsql_native_c_compilerplsql_native_linkerplsql_native_make_utility

35、plsql_native_make_file_name_plsql_conditional_compilationjob_queue_intervaljob_queue_keep_connectionssnapshot_refresh_processessnapshot_refresh_intervalsnapshot_refresh_keep_connectionsparallel_default_max_instancescache_size_thresholdparallel_server_idle_timeallow_partial_sn_resultsops_admin_groupp

36、arallel_min_message_poolhash_join_enabledhash_multiblock_io_countoracle_trace_enableoracle_trace_facility_pathoracle_trace_collection_pathoracle_trace_facility_nameoracle_trace_collection_nameoracle_trace_collection_size_oracle_trace_events_oracle_trace_facility_versionclose_cached_open_cursorssort_

37、direct_writessort_write_bufferssort_write_buffer_sizesort_spacemap_sizesort_read_facsort_multiblock_read_countalways_anti_joinpartition_view_enabledb_tree_bitmap_planscomplex_view_mergingNAMEspin_countuse_ismlock_sga_areasinstance_nodesetlarge_pool_min_allocshared_pool_reserved_min_alloc_kspptbl_mem

38、_usageenqueue_resourceslgwr_io_slavesarch_io_slavesbackup_disk_io_slavesops_interconnectslm_procsogms_homeparallel_transaction_resource_timeout_lm_statisticslm_lockslm_resslm_procs_lm_multiple_receivers_lm_direct_sends_lm_rcv_buffer_size_dlm_send_timeoutdb_block_max_dirty_targetdb_block_lru_latchesd

39、b_block_checkpoint_batchdb_block_lru_statisticsdb_block_lru_extended_statistics_compatible_no_recoveryremote_archive_enable_log_archive_buffer_size_average_dirties_half_lifelog_block_checksumlog_small_entry_max_sizelog_simultaneous_copieslog_parallelismdb_file_simultaneous_writeslog_filesplsql_compi

40、ler_flagsplsql_native_c_compilerplsql_native_linkerplsql_native_make_utilityplsql_native_make_file_name_plsql_conditional_compilationjob_queue_intervaljob_queue_keep_connectionssnapshot_refresh_processessnapshot_refresh_intervalsnapshot_refresh_keep_connectionsparallel_default_max_instancescache_siz

41、e_thresholdparallel_server_idle_timeallow_partial_sn_resultsops_admin_groupparallel_min_message_poolhash_join_enabledhash_multiblock_io_countoracle_trace_enableoracle_trace_facility_pathoracle_trace_collection_pathoracle_trace_facility_nameoracle_trace_collection_nameoracle_trace_collection_size_ora

42、cle_trace_events_oracle_trace_facility_versionclose_cached_open_cursorssort_direct_writessort_write_bufferssort_write_buffer_sizesort_spacemap_sizesort_read_facsort_multiblock_read_countalways_anti_joinpartition_view_enabledb_tree_bitmap_planscomplex_view_mergingpush_join_predicatefast_full_scan_ena

43、bledparallel_broadcast_enabledalways_semi_joinoptimizer_max_permutationsoptimizer_percent_paralleloptimizer_search_limit已選擇121行04 九月 202234 / 42Oracle 參數高級管理過時參數使用時的提示：BUFFER_POOL_KEEP gesetzt:ORA-32006: BUFFER_POOL_KEEP initialization parameter hasbeen deprecatedORA-00381: cannot use both new and o

44、ld parameters forbuffer cache size specifinMAX_ENABLED_ROLES gesetzt:ORA-32006: MAX_ENABLED_ROLES initialization parameter hasbeen deprecatedORACLE instance started.HASH_JOIN_ENABLED gesetzt:ORA-25138: HASH_JOIN_ENABLED initialization parameter hasbeen made obsoleteORACLE instance started.04 九月 2022

45、35 / 42Oracle 參數高級管理高級的參數：asm_diskgroupsasm_diskstringasm_power_limitdb_flashback_retention_targetdb_recovery_file_destdb_recovery_file_dest_sizedb_unique_nameddl_wait_for_locksfileio_network_adaptersgcs_server_processesinstance_typeldap_directory_accesslog_archive_configalog_archive_local_firstplsq

46、l_code_typeplsql_debugplsql_optimize_levelplsql_warningsresumable_timeoutsga_targetskip_unusable_indexessmtp_out_serversp_namesqltune_categorystreams_pool_size04 九月 202236 / 42Oracle 參數高級管理Oracle 9i 文檔能查不到的參數：從x$ksppi 和 x$ksppcv col KSPPINM for a36col KSPPSTVL for a10col KSPPSTDF for a12col KSPPDESC

47、 for a40set line 160select a.ksppinm,b.ksppstvl,b.ksppstdf,a.ksppdescfrom x$ksppi a,x$ksppcv b where a.indx=b.indx and substr(ksppinm,1,1)=_ order by ksppinm;KSPPINM KSPPSTVL KSPPSTDF KSPPDESC _NUMA_instance_mapping Not spec TRUE Set of nodes that this instance should run 。 。04 九月 202237 / 42Oracle

48、參數高級管理Oracle 9i 文檔能查到的參數：從x$ksppi 和 x$ksppcv SQL Set line 104SQL Set pagesize 50SQL Col ksppinm for a30SQL Col KSPPSTVL for a8SQL Col ksppstdf for a8SQL Col ksppdesc for a50SQL select a.ksppinm,b.ksppstvl,b.ksppstdf,a.ksppdesc 2 from x$ksppi a,x$ksppcv b 3 where a.indx=b.indx and substr(ksppinm,1,1)

49、 !=_ order by ksppinm ;KSPPINM KSPPSTVL KSPPSTDF KSPPDESC O7_DICTIONARY_ACCESSIBILITY FALSE TRUE Version 7 Dictionary04 九月 202238 / 42Oracle 參數高級管理Oracle 10g 文檔能查不到的參數：從x$ksppi 和 x$ksppcv col KSPPINM for a36col KSPPSTVL for a10col KSPPSTDF for a12col KSPPDESC for a40set line 160select a.ksppinm,b.ks

50、ppstvl,b.ksppstdf,a.ksppdescfrom x$ksppi a,x$ksppcv b where a.indx=b.indx and substr(ksppinm,1,1)=_ order by ksppinm;KSPPINM KSPPSTVL KSPPSTDF KSPPDESC _4031_dump_bitvec 67194879 TRUE bitvec to specify dumps prior to 4031 er ror_4031_dump_interval 300 TRUE Dump 4031 error once for each n-second i nt

51、erval_xtts_set_platform_info FALSE TRUE set cross platform info during file head er read。 。_yield_check_interval 100000 TRUE interval to check whether actses should yield已選擇1124行。04 九月 202239 / 42Oracle 參數高級管理Oracle 11g 文檔能查不到的參數：從x$ksppi 和 x$ksppcv col KSPPINM for a36col KSPPSTVL for a10col KSPPSTD

52、F for a12col KSPPDESC for a40select a.ksppinm,b.ksppstvl,b.ksppstdf,a.ksppdescfrom x$ksppi a,x$ksppcv b where a.indx=b.indx and substr(ksppinm,1,1)=_ order by ksppinm;KSPPINM KSPPSTVL KSPPSTDF KSPPDESC _xtts_allow_pre10 FALSE TRUE allow cross platform for pre10 compatibl e tablespace_xtts_set_platform_info FALSE TRUE set cross platform info during file head er read_yield_check_interval 100000 TRUE interval to ch