Oracle培训资料gg参数SPFILE(页)

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