pfile和spfile
- 概念
ORACLE中的参数文件是一个包含一系列参数以及参数对应值的操作系统文件，可以分为两种类型。它们是在数据库实例启动时候加载的，决定了数据库的物理结构、内存、数据库的限制及系统大量的默认值、数据库的各种物理属性、指定数据库控制文件名和路径等信息，是进行数据库设计和性能调优的重要文件。
初始化参数文件（Initialization Parameters Files），Oracle 9i之前，ORACLE一直采用PFILE方式存储初始化参数，该文件为文本文件。
服务器参数文件（Server Parameter Files），从Oracle 9i开始，Oracle引入了SPFILE文件，该文件为二进制格式，不能通过手工修改。
- 区别
1、PFILE是文本文件的，而SPFILE是二进制格式的。PFILE文件可以用文本编辑器打开手工配置、而SPFILE不行，只能通过SQL命令在线修改。从操作系统上可以看到这两者的区别，初始化参数文件为ASCII文本文件，SPFILE为数据文件。
2、SPFILE的修改是可以通过SQL命令在线修改，不再需要通过手工修改，对于动态参数所有更改可以立即生效，而PFILE的修改必须重启实例才能生效。
3、手动创建数据库而不是通过DBCA，则开始创建数据库时，只能定义PFILE。
- 修改spfile参数的三种模式：
scope=both 立即并永久生效，（默认模式）
scope=spfile 下次启动才能生效。
scope=memory 立即生效但下次启动时失效
- 查询位置
SQL> show parameter pfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/12.2.0 /db_1/dbs/spfilegnnt2.ora
默认显示的是spfile，说明数据库是使用spfile启动的。
当查询不显示结果说明是使用pfile启动的，pfile的路径：$ORACLE_HOME/dbs/init.ora
- 参数文件的搜索顺序
1）spfile<sid>.ora
Unix/Linux缺省目录 $ORACLE_HOME/dbs/
Windows缺省目录 %ORACLE_HOME%database
2) spfile.ora
Unix/Linux缺省目录 $ORACLE_HOME/dbs/
Windows缺省目录 %ORACLE_HOME%database
3) init<sid>.ora
Unix/Linux缺省目录 $ORACLE_HOME/dbs/
Windows缺省目录 %ORACLE_HOME%database
- 重建PFILE或SPFILE
create spfile[='xxxxx'] from pfile[='xxxx'];
create pfile[='xxxxx'] from spfile[='xxxx'];
- scope参数说明:
静态参数 必须指定为scope
动态参数issys_modifiable为IMMEDIATE不加scope默认的是 both,而动态参数issys_modifiable为DEFERRED的必须加上scope=spfile 或者 加上derferred,
|
参数类型 |
spfile |
memory |
both |
deferred |
|
静态参数 |
可以,重启服务器生效 |
不可以 |
不可以 |
不可以 |
|
动态参数(issys_modifiable为immediate |
可以,重启服务器生效 |
可以,立即生效,重启服务失效 |
可以,立即生效,重启服务器仍然有效果 |
可以 |
|
动态参数(issys_modifiable为deferred) |
|
参数查看
- sql下查看
查看全部：
SQL> show parameter NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ DBFIPS_140 boolean FALSE O7_DICTIONARY_ACCESSIBILITY boolean FALSE active_instance_count integer allow_global_dblinks boolean FALSE allow_group_access_to_sga boolean FALSE approx_for_aggregation boolean FALSE approx_for_count_distinct boolean FALSE approx_for_percentile string none aq_tm_processes integer 1 archive_lag_target integer 0 asm_diskgroups string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ asm_diskstring string asm_io_processes integer 20 asm_power_limit integer 1 asm_preferred_read_failure_groups string audit_file_dest string /data/u01/app/oracle/admin/gnn t/adump audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string NONE autotask_max_active_pdbs integer 2 awr_pdb_autoflush_enabled boolean FALSE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ awr_snapshot_time_offset integer 0 background_core_dump string partial background_dump_dest string /u01/app/oracle/product/12.2.0 /db_1/rdbms/log backup_tape_io_slaves boolean FALSE bitmap_merge_area_size integer 1048576 blank_trimming boolean FALSE buffer_pool_keep string buffer_pool_recycle string cdb_cluster boolean FALSE cdb_cluster_name string gnnt NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cell_offload_compaction string ADAPTIVE cell_offload_decryption boolean TRUE cell_offload_parameters string cell_offload_plan_display string AUTO cell_offload_processing boolean TRUE cell_offloadgroup_name string circuits integer client_result_cache_lag big integer 3000 client_result_cache_size big integer 0 clonedb boolean FALSE clonedb_dir string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cluster_database boolean FALSE cluster_database_instances integer 1 cluster_interconnects string commit_logging string commit_point_strength integer 1 commit_wait string commit_write string common_user_prefix string C## compatible string 12.2.0 connection_brokers string ((TYPE=DEDICATED)(BROKERS=1)), ((TYPE=EMON)(BROKERS=1)) NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ containers_parallel_degree integer 65535 control_file_record_keep_time integer 15 control_files string /data/conf_dir/current.261.972 470897 control_management_pack_access string DIAGNOSTIC+TUNING core_dump_dest string /u01/app/oracle/diag/rdbms/gnn t/gnnt2/cdump cpu_count integer 1 create_bitmap_area_size integer 8388608 create_stored_outlines string cursor_bind_capture_destination string memory+disk NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cursor_invalidation string IMMEDIATE cursor_sharing string EXACT cursor_space_for_time boolean FALSE data_guard_sync_latency integer 0 data_transfer_cache_size big integer 0 db_16k_cache_size big integer 0 db_2k_cache_size big integer 0 db_32k_cache_size big integer 0 db_4k_cache_size big integer 0 db_8k_cache_size big integer 0 db_big_table_cache_percent_target string 0 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_buffers integer 0 db_block_checking string FALSE db_block_checksum string TYPICAL db_block_size integer 8192 db_cache_advice string ON db_cache_size big integer 0 db_create_file_dest string /data/oradata db_create_online_log_dest_1 string db_create_online_log_dest_2 string db_create_online_log_dest_3 string db_create_online_log_dest_4 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_online_log_dest_5 string db_domain string db_file_multiblock_read_count integer 128 db_file_name_convert string db_files integer 200 db_flash_cache_file string db_flash_cache_size big integer 0 db_flashback_retention_target integer 1440 db_index_compression_inheritance string NONE db_keep_cache_size big integer 0 db_lost_write_protect string NONE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string gnnt db_performance_profile string db_recovery_file_dest string db_recovery_file_dest_size big integer 0 db_recycle_cache_size big integer 0 db_securefile string PREFERRED db_ultra_safe string OFF db_unique_name string gnnt db_unrecoverable_scn_tracking boolean TRUE db_writer_processes integer 1 dbwr_io_slaves integer 0 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ ddl_lock_timeout integer 0 default_sharing string metadata deferred_segment_creation boolean TRUE dg_broker_config_file1 string /u01/app/oracle/product/12.2.0 /db_1/dbs/dr1gnnt.dat dg_broker_config_file2 string /u01/app/oracle/product/12.2.0 /db_1/dbs/dr2gnnt.dat dg_broker_start boolean FALSE diagnostic_dest string /u01/app/oracle disable_pdb_feature big integer 0 disk_asynch_io boolean TRUE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dispatchers string (PROTOCOL=TCP) (SERVICE=gnntXD B) distributed_lock_timeout integer 60 dml_locks integer 99096 dnfs_batch_size integer 4096 dst_upgrade_insert_conv boolean TRUE enable_automatic_maintenance_pdb boolean TRUE enable_ddl_logging boolean FALSE enable_dnfs_dispatcher boolean FALSE enable_goldengate_replication boolean FALSE enable_pluggable_database boolean TRUE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ enabled_PDBs_on_standby string * encrypt_new_tablespaces string CLOUD_ONLY event string exafusion_enabled integer 1 external_keystore_credential_locatio string n fal_client string fal_server string fast_start_io_target integer 0 fast_start_mttr_target integer 0 fast_start_parallel_rollback string LOW NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ file_mapping boolean FALSE fileio_network_adapters string filesystemio_options string none fixed_date string gcs_server_processes integer 0 global_names boolean FALSE global_txn_processes integer 1 hash_area_size integer 131072 heat_map string OFF hi_shared_memory_address integer 0 hs_autoregister boolean TRUE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ ifile file inmemory_adg_enabled boolean TRUE inmemory_clause_default string inmemory_expressions_usage string ENABLE inmemory_force string DEFAULT inmemory_max_populate_servers integer 0 inmemory_query string ENABLE inmemory_size big integer 0 inmemory_trickle_repopulate_servers_ integer 1 percent inmemory_virtual_columns string MANUAL NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ instance_abort_delay_time integer 0 instance_groups string instance_mode string READ-WRITE instance_name string gnnt2 instance_number integer 2 instance_type string RDBMS instant_restore boolean FALSE java_jit_enabled boolean TRUE java_max_sessionspace_size integer 0 java_pool_size big integer 0 java_restrict string none NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ java_soft_sessionspace_limit integer 0 job_queue_processes integer 4000 large_pool_size big integer 0 ldap_directory_access string NONE ldap_directory_sysauth string no license_max_sessions integer 0 license_max_users integer 0 license_sessions_warning integer 0 listener_networks string local_listener string -oraagent-dummy- lock_name_space string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE log_archive_config string log_archive_dest string log_archive_dest_1 string LOCATION=/acfs_arch log_archive_dest_10 string log_archive_dest_11 string log_archive_dest_12 string log_archive_dest_13 string log_archive_dest_14 string log_archive_dest_15 string log_archive_dest_16 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_17 string log_archive_dest_18 string log_archive_dest_19 string log_archive_dest_2 string log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string log_archive_dest_23 string log_archive_dest_24 string log_archive_dest_25 string log_archive_dest_26 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_27 string log_archive_dest_28 string log_archive_dest_29 string log_archive_dest_3 string log_archive_dest_30 string log_archive_dest_31 string log_archive_dest_4 string log_archive_dest_5 string log_archive_dest_6 string log_archive_dest_7 string log_archive_dest_8 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_9 string log_archive_dest_state_1 string enable log_archive_dest_state_10 string enable log_archive_dest_state_11 string enable log_archive_dest_state_12 string enable log_archive_dest_state_13 string enable log_archive_dest_state_14 string enable log_archive_dest_state_15 string enable log_archive_dest_state_16 string enable log_archive_dest_state_17 string enable log_archive_dest_state_18 string enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_19 string enable log_archive_dest_state_2 string enable log_archive_dest_state_20 string enable log_archive_dest_state_21 string enable log_archive_dest_state_22 string enable log_archive_dest_state_23 string enable log_archive_dest_state_24 string enable log_archive_dest_state_25 string enable log_archive_dest_state_26 string enable log_archive_dest_state_27 string enable log_archive_dest_state_28 string enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_29 string enable log_archive_dest_state_3 string enable log_archive_dest_state_30 string enable log_archive_dest_state_31 string enable log_archive_dest_state_4 string enable log_archive_dest_state_5 string enable log_archive_dest_state_6 string enable log_archive_dest_state_7 string enable log_archive_dest_state_8 string enable log_archive_dest_state_9 string enable log_archive_duplex_dest string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_format string %t_%s_%r.dbf log_archive_max_processes integer 4 log_archive_min_succeed_dest integer 1 log_archive_start boolean FALSE log_archive_trace integer 0 log_buffer big integer 118656K log_checkpoint_interval integer 0 log_checkpoint_timeout integer 1800 log_checkpoints_to_alert boolean FALSE log_file_name_convert string long_module_action boolean TRUE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_datapump_jobs_per_pdb integer 100 max_dispatchers integer max_dump_file_size string unlimited max_idle_time integer 0 max_iops integer 0 max_mbps integer 0 max_pdbs integer 4098 max_shared_servers integer max_string_size string STANDARD memory_max_target big integer 0 memory_target big integer 0 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ nls_calendar string GREGORIAN nls_comp string BINARY nls_currency string $ nls_date_format string DD-MON-RR nls_date_language string AMERICAN nls_dual_currency string $ nls_iso_currency string AMERICA nls_language string AMERICAN nls_length_semantics string BYTE nls_nchar_conv_excp string FALSE nls_numeric_characters string ., NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ nls_sort string BINARY nls_territory string AMERICA nls_time_format string HH.MI.SSXFF AM nls_time_tz_format string HH.MI.SSXFF AM TZR nls_timestamp_format string DD-MON-RR HH.MI.SSXFF AM nls_timestamp_tz_format string DD-MON-RR HH.MI.SSXFF AM TZR noncdb_compatible boolean FALSE object_cache_max_size_percent integer 10 object_cache_optimal_size integer 10240000 ofs_threads integer 4 olap_page_pool_size big integer 0 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ one_step_plugin_for_pdb_with_tde boolean FALSE open_cursors integer 300 open_links integer 4 open_links_per_instance integer 4 optimizer_adaptive_plans boolean TRUE optimizer_adaptive_reporting_only boolean FALSE optimizer_adaptive_statistics boolean FALSE optimizer_capture_sql_plan_baselines boolean FALSE optimizer_dynamic_sampling integer 2 optimizer_features_enable string 12.2.0.1 optimizer_index_caching integer 0 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_index_cost_adj integer 100 optimizer_inmemory_aware boolean TRUE optimizer_mode string ALL_ROWS optimizer_secure_view_merging boolean TRUE optimizer_use_invisible_indexes boolean FALSE optimizer_use_pending_statistics boolean FALSE optimizer_use_sql_plan_baselines boolean TRUE os_authent_prefix string ops$ os_roles boolean FALSE outbound_dblink_protocols string ALL parallel_adaptive_multi_user boolean FALSE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ parallel_degree_limit string CPU parallel_degree_policy string MANUAL parallel_execution_message_size integer 16384 parallel_force_local boolean FALSE parallel_instance_group string parallel_max_servers integer 40 parallel_min_percent integer 0 parallel_min_servers integer 4 parallel_min_time_threshold string AUTO parallel_servers_target integer 16 parallel_threads_per_cpu integer 2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pdb_file_name_convert string pdb_lockdown string pdb_os_credential string permit_92_wrap_format boolean TRUE pga_aggregate_limit big integer 45000M pga_aggregate_target big integer 13107M plscope_settings string IDENTIFIERS:NONE plsql_ccflags string plsql_code_type string INTERPRETED plsql_debug boolean FALSE plsql_optimize_level integer 2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ plsql_v2_compatibility boolean FALSE plsql_warnings string DISABLE:ALL pre_page_sga boolean TRUE processes integer 15000 processor_group_name string query_rewrite_enabled string TRUE query_rewrite_integrity string enforced rdbms_server_dn string read_only_open_delayed boolean FALSE recovery_parallelism integer 0 recyclebin string on NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ redo_transport_user string remote_dependencies_mode string TIMESTAMP remote_listener string remote_login_passwordfile string EXCLUSIVE remote_os_authent boolean FALSE remote_os_roles boolean FALSE remote_recovery_file_dest string replication_dependency_tracking boolean TRUE resource_limit boolean TRUE resource_manage_goldengate boolean FALSE resource_manager_cpu_allocation integer 1 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ resource_manager_plan string result_cache_max_result integer 5 result_cache_max_size big integer 268704K result_cache_mode string MANUAL result_cache_remote_expiration integer 0 resumable_timeout integer 0 rollback_segments string sec_case_sensitive_logon boolean TRUE sec_max_failed_login_attempts integer 3 sec_protocol_error_further_action string (DROP,3) sec_protocol_error_trace_action string TRACE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sec_return_server_release_banner boolean FALSE serial_reuse string disable service_names string gnnt session_cached_cursors integer 50 session_max_open_files integer 10 sessions integer 22522 sga_max_size big integer 52480M sga_min_size big integer 0 sga_target big integer 52480M shadow_core_dump string partial shared_memory_address integer 0 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ shared_pool_reserved_size big integer 295279001 shared_pool_size big integer 0 shared_server_sessions integer shared_servers integer 1 shrd_dupl_table_refresh_rate integer 60 skip_unusable_indexes boolean TRUE smtp_out_server string sort_area_retained_size integer 0 sort_area_size integer 65536 spatial_vector_acceleration boolean FALSE spfile string /u01/app/oracle/product/12.2.0 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ /db_1/dbs/spfilegnnt2.ora sql92_security boolean TRUE sql_trace boolean FALSE sqltune_category string DEFAULT standby_archive_dest string ?#/dbs/arch standby_db_preserve_states string NONE standby_file_management string MANUAL star_transformation_enabled string FALSE statistics_level string TYPICAL streams_pool_size big integer 0 tape_asynch_io boolean TRUE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ target_pdbs integer 102 temp_undo_enabled boolean FALSE thread integer 2 threaded_execution boolean FALSE timed_os_statistics integer 0 timed_statistics boolean TRUE trace_enabled boolean TRUE tracefile_identifier string transactions integer 24774 transactions_per_rollback_segment integer 5 undo_management string AUTO NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_retention integer 900 undo_tablespace string UNDOTBS2 unified_audit_sga_queue_size integer 1048576 uniform_log_timestamp_format boolean TRUE use_dedicated_broker boolean FALSE use_large_pages string TRUE user_dump_dest string /u01/app/oracle/product/12.2.0 /db_1/rdbms/log utl_file_dir string workarea_size_policy string AUTO xml_db_events string enable
查看具体参数值，可以show parameter后加参数名（参数名可以模糊匹配）
SQL> show parameter db_create_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string /data/oradat
- 文件下查看
spfile：
$ more spfilegnnt2.ora 或 strings spfilegnnt2.ora
pfile：正常查看
参数修改
- RAC 的参数文件比较特殊
因为默认情况下，RAC的spfile 是放在共享设备上（RAW设备或者ASM磁盘组）。而在各节点的pfile文件里只记录了这个spfile的位置。 所以在用spfile 生成pfile 或者修改相关参数值时都需要特别注意。
如果我们直接使用： create pfile from spfile 命令创建pfile，那么生成的pfile 文件将覆盖原有$ORACLE_HOME/dbs 目录下的pfile 文件。 而在之前的pfile文件里面值保留了一条指向spfile存放位置的记录。 这样修改之后，就会造成数据库启动时会因为找不到spfile文件而读取本地的pfile文件，而不是共享设备上的spfile文件。这样对参数管理上就会带来麻烦，也带来其他的隐患。
所以对于RAC，要慎用 create pfile from spfile 来创建pfile 文件， 在创建的时候，尽量指定pfile的生成位置。
查看spfile内容：
[oracle@swnode2 dbs]$ more spfilegnnt2.ora C" nsfer_cache_size=0 gnnt2.__data_transfer_cache_size=0 gnnt1.__db_cache_size=39325794304 gnnt2.__db_cache_size=48586817536 gnnt1.__inmemory_ext_roarea=0 gnnt2.__inmemory_ext_roarea=0 gnnt1.__inmemory_ext_rwarea=0 gnnt2.__inmemory_ext_rwarea=0 gnnt1.__java_pool_size=805306368 gnnt2.__java_pool_size=134217728 gnnt1.__large_pool_size=939524096 gnnt2.__large_pool_size=268435456 gnnt1.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment gnnt2.__oracle_base='/C gnnt1.__pga_aggregate_target=13824425984 gnnt2.__pga_aggregate_target=13824425984 gnnt1.__sga_target=55029268480 gnnt2.__sga_target=55029268480 gnnt1.__shared_io_pool_size=536870912 gnnt2.__shared_io_pool_size=0 gnnt1.__shared_pool_size=13287555072 gnnt2.__shared_pool_size=5905580032 gnnt1.__streams_pool_size=0 gnnt2.__streams_pool_size=0 *.audit_file_dest='/data/u01/app/oracle/admin/gnnt/adump' *.audit_trail='NONE' *.cluster_database=C *.compatible='12.2.0' *.control_file_record_keep_time=15 *.control_files='/data/conf_dir/current.261.972470897' *.db_block_size=8192 *.db_create_file_dest='/data/oradata' *.db_name='gnnt' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=gnntXDB)' *.enable_pluggable_database=true family:dw_helper.instance_mode='read-only' gnnt2.instance_number=2 gnnt1.instance_number=1 *.local_listener='-oraagent-dummy-' *.log_archive_dest_1='LOCATION=/acfs_arch' *.log_C *.nls_language='SIMPLIFIED CHINESE' *.nls_territory='CHINA' *.open_cursors=300 *.pga_aggregate_target=13107m *.processes=15000 *.remote_login_passwordfile='exclusive' *.sga_target=52428m gnnt2.thread=2 gnnt1.thread=1 gnnt1.undo_tablespace='UNDOTBS1' gnnt2.undo_tablespace='UNDOTBS2'
从上面的结果，我们发现参数的最前面都有一个标识符：orcl1, orcl2 和 *。 这些符号是用来区分参数的。
如： *.undo_management='AUTO' -- 所有节点通用的参数 gnnt1.undo_tablespace='UNDOTBS1' -- 代表gnnt1的参数 gnnt2.undo_tablespace='UNDOTBS2' -- 代表gnnt2 的参数
因为spfile区分实例，所以我们在修改的时候要特别注意指定实例，如果不指定，就会修改所有节点的参数。
如：
alter system set sga_target=1024M scope=spfile sid='orcl1'; alter system set sga_target=1024M scope=spfile sid='*';
- scope 参数值有三个选项
scope=memory: 只改变当前实例运行，亦即初始化参数改变了只对当前实例有效，当实例重启之后，初始化参数值还原。
scope=spfile：只改变spfile的设置。亦即改变初始化参数文件内容。实例重启后参数生效。
scope=both：既改变实例也改变spfile。需要注意的是，如果修改静态参数，必须指定SPFILE参数（scope=spfile），否则会报错。
动态参数是指可以使用 alter session 或 alter system 在数据库运行时进行修改并能立即生效的参数。
静态参数是指只能通过修改参数文件而且数据库必须要重新启动才能生效的参数。
参考：
http://blog.csdn.net/tianlesoftware/article/details/5902591
https://www.cnblogs.com/kerrycode/p/3254154.html
转载请注明出处