参数文件
官方文档的位置
Database Administration--->Reference--->1 Initialization Parameters
1、参数文件的位置
1 /u01/app/oracle/product/11.2.0/db_1/dbs/1 [oracle@localhost dbs]$ cd $ORACLE_HOME/dbs; 2 [oracle@localhost dbs]$ pwd 3 /u01/app/oracle/product/11.2.0/db_1/dbs 4 [oracle@localhost dbs]$ ll 5 total 52 6 -rw-r----- 1 oracle oinstall 5120 Dec 9 2016 dbsorapwPROD1 7 -rw-rw---- 1 oracle oinstall 1544 Nov 30 2016 hc_orcl.dat 8 -rw-rw---- 1 oracle oinstall 1544 Dec 9 2016 hc_PROD1.dat 9 -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora 10 -rw-r--r-- 1 oracle oinstall 1062 Jan 10 00:07 initorcl.ora 11 -rw-r--r-- 1 oracle oinstall 134 Dec 9 2016 initPROD1.ora 12 -rw-r----- 1 oracle oinstall 24 Nov 30 2016 lkORCL 13 -rw-r----- 1 oracle oinstall 24 Dec 9 2016 lkPROD1 14 -rw-r----- 1 oracle oinstall 1536 Apr 3 17:08 orapworcl 15 -rw-r----- 1 oracle oinstall 4608 Apr 18 23:14 spfileorcl.ora 16 -rw-r----- 1 oracle oinstall 2560 Dec 9 2016 spfilePROD1.ora 17 [oracle@localhost dbs]$
2、在nomount阶段打开参数文件
3、参数文件类型
1）服务器的参数文件或spfile
1）二进制的参数文件 2）命令规则 spfile+sid.ora 3)不能使用文本编辑器进行修改 4）只能使用命令的方式修改参数,不能直接修改spfile文件2）静态参数文件或pfile
1）文本的参数文件 2）命令规则 init+sid.ora
1 [oracle@localhost dbs]$ cd $ORACLE_HOME/dbs; 2 [oracle@localhost dbs]$ pwd 3 /u01/app/oracle/product/11.2.0/db_1/dbs 4 [oracle@localhost dbs]$ ll 5 total 52 6 -rw-r----- 1 oracle oinstall 5120 Dec 9 2016 dbsorapwPROD1 7 -rw-rw---- 1 oracle oinstall 1544 Nov 30 2016 hc_orcl.dat 8 -rw-rw---- 1 oracle oinstall 1544 Dec 9 2016 hc_PROD1.dat 9 -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora 10 -rw-r--r-- 1 oracle oinstall 1062 Jan 10 00:07 initorcl.ora 11 -rw-r--r-- 1 oracle oinstall 134 Dec 9 2016 initPROD1.ora 12 -rw-r----- 1 oracle oinstall 24 Nov 30 2016 lkORCL 13 -rw-r----- 1 oracle oinstall 24 Dec 9 2016 lkPROD1 14 -rw-r----- 1 oracle oinstall 1536 Apr 3 17:08 orapworcl 15 -rw-r----- 1 oracle oinstall 4608 Apr 18 23:14 spfileorcl.ora 16 -rw-r----- 1 oracle oinstall 2560 Dec 9 2016 spfilePROD1.ora 17 18 [oracle@localhost dbs]$ sqlplus / as sysdba; 19 20 SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 19 21:49:44 2018 21 22 Copyright (c) 1982, 2011, Oracle. All rights reserved. 23 24 25 Connected to: 26 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production 27 With the Partitioning, OLAP, Mining and Real Application Testing options 28 # 29 SYS@orcl> 30 31 created. 32 33 SYS@orcl> quit 34 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production 35 With the Partitioning, OLAP, Mining and Real Application Testing options 36 [oracle@localhost dbs]$ ll 37 total 52 38 -rw-r----- 1 oracle oinstall 5120 Dec 9 2016 dbsorapwPROD1 39 -rw-rw---- 1 oracle oinstall 1544 Nov 30 2016 hc_orcl.dat 40 -rw-rw---- 1 oracle oinstall 1544 Dec 9 2016 hc_PROD1.dat 41 -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora 42 44 -rw-r--r-- 1 oracle oinstall 134 Dec 9 2016 initPROD1.ora 45 -rw-r----- 1 oracle oinstall 24 Nov 30 2016 lkORCL 46 -rw-r----- 1 oracle oinstall 24 Dec 9 2016 lkPROD1 47 -rw-r----- 1 oracle oinstall 1536 Apr 3 17:08 orapworcl 48 -rw-r----- 1 oracle oinstall 4608 Apr 18 23:14 spfileorcl.ora 49 -rw-r----- 1 oracle oinstall 2560 Dec 9 2016 spfilePROD1.ora 50 [oracle@localhost dbs]$3)可以使用文本编辑器进行修改
1 [oracle@localhost dbs]$ cat initorcl.ora 2 orcl.__db_cache_size=251658240 3 orcl.__java_pool_size=16777216 4 orcl.__large_pool_size=33554432 5 orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment 6 orcl.__pga_aggregate_target=419430400 7 orcl.__sga_target=805306368 8 orcl.__shared_io_pool_size=0 9 orcl.__shared_pool_size=452984832 10 orcl.__streams_pool_size=16777216 11 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' 12 *.audit_trail='db' 13 *.compatible='11.2.0.0.0' 14 *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl','/u01/app/oracle/fast_recovery_area/orcl/control03.ctl' 15 *.cursor_space_for_time=TRUE 16 *.db_16k_cache_size=16777216 17 *.db_block_size=8192 18 *.db_domain='' 19 *.db_name='orcl' 20 *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' 21 *.db_recovery_file_dest_size=4322230272 22 *.diagnostic_dest='/u01/app/oracle' 23 *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' 24 *.large_pool_size=33554432 25 *.log_archive_dest_1='location=/home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive1' 26 *.log_archive_dest_10='location=/home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive2' 27 *.log_archive_dest_2='location=/home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive2_1 optional ' 28 *.log_archive_dest_3='location=/home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive3_1 mandatory ' 29 *.log_archive_dest_4='location=/home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive4 mandatory reopen=400 ' 30 *.log_archive_dest_5='location=/home/oracle/oracle_system_files_back/archivelog/archivelog_20180305/archive5 ' 31 *.log_archive_dest_state_4='DEFER' 32 *.log_archive_format='%t_%s_%r.dbf' 33 *.log_archive_max_processes=8 34 *.log_archive_min_succeed_dest=3 35 *.memory_target=1214251008 36 *.open_cursors=350 37 *.processes=150 38 *.remote_login_passwordfile='EXCLUSIVE' 39 *.resource_limit=TRUE 40 *.session_cached_cursors=100 41 *.undo_tablespace='UNDOTBS1' 42 [oracle@localhost dbs]$4）可以直接修改init+sid.ora的方式来修改参数
4、参数文件的使用顺序
优先使用spfile，当spfile不存在时，使用pfile， 当pfile不存在时，直接出错，不能nomount
5、如何查看使用什么类型的参数文件
show parameter spfile 当spfile参数有路径值的时候，表示使用spfile 当spfile参数没有路径值的时候，表示使用pfile1 [oracle@localhost dbs]$ sqlplus / as sysdba; 2 3 SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 19 22:01:55 2018 4 5 Copyright (c) 1982, 2011, Oracle. All rights reserved. 6 7 8 Connected to: 9 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production 10 With the Partitioning, OLAP, Mining and Real Application Testing options 11 12 SYS@orcl> show parameter spfile; 13 14 NAME TYPE VALUE 15 ------------------------------------ ----------- ------------------------------ 16 spfile string /u01/app/oracle/product/11.2.0 17 /db_1/dbs/spfileorcl.ora 18 SYS@orcl>
6、查看参数
1)show parameter 2)show parameter 参数名称 3)show parameter 参数名称的部分关键字 4)v$parameter
- ISSES_MODIFIABLE
- true:表示可以使用alter session命令进行修改
- false:表示不能使用alter session命令进行修改
- issys_modifiable
- false:表示不能使用alter system命令进行修改
- deferred:表示可以使用alter system命令进行修改，但要加上deferred选项
- immediate：表示可以使用alter system命令进行修改
scope参数说明:
静态参数 必须指定为scope 动态参数issys_modifiable为IMMEDIATE不加scope默认的是 both,而动态参数issys_modifiable为DEFERRED的必须加上scope=spfile 或者 加上derferred,
参数类型
spfile
memory
both
deferred
静态参数
可以,重启服务器生效
不可以
不可以
不可以
动态参数(issys_modifiable为immediate
可以,重启服务器生效
可以,立即生效,重启服务失效
可以,立即生效,重启服务器仍然有效果
可以
动态参数(issys_modifiable为deferred)
1 NAME,ISSES_MODIFIABLE,ISSYS_MODIFIABLE v$parameter ISSES_MODIFIABLE='FALSE' ISSYS_MODIFIABLE='FALSE';1 SYS@orcl> show parameter 2 3 NAME TYPE 4 ------------------------------------ ----------- ------------------------------ 5 O7_DICTIONARY_ACCESSIBILITY boolean 6 active_instance_count 7 aq_tm_processes 1 8 archive_lag_target 0 9 asm_diskgroups string 10 asm_diskstring string 11 asm_power_limit 1 12 asm_preferred_read_failure_groups string 13 audit_file_dest string /u01/app/oracle/admin/orcl/adu 14 mp 15 audit_sys_operations boolean 16 17 NAME TYPE 18 ------------------------------------ ----------- ------------------------------ 19 audit_syslog_level string 20 audit_trail string DB 21 awr_snapshot_time_offset 0 22 background_core_dump string 23 background_dump_dest string /u01/app/oracle/diag/rdbms/orc 24 l/orcl/trace 25 backup_tape_io_slaves boolean 26 bitmap_merge_area_size 1048576 27 blank_trimming boolean 28 buffer_pool_keep string 29 buffer_pool_recycle string 30 31 NAME TYPE 32 ------------------------------------ ----------- ------------------------------ 33 cell_offload_compaction string ADAPTIVE 34 cell_offload_decryption boolean 35 cell_offload_parameters string 36 cell_offload_plan_display string AUTO 37 cell_offload_processing boolean 38 circuits 39 client_result_cache_lag big 3000 40 client_result_cache_size big 0 41 clonedb boolean 42 cluster_database boolean 43 cluster_database_instances 1 44 45 NAME TYPE 46 ------------------------------------ ----------- ------------------------------ 47 cluster_interconnects string 48 commit_logging string 49 commit_point_strength 1 50 commit_wait string 51 commit_write string 52 compatible string 11.2.0.0.0 53 control_file_record_keep_time 7 54 control_files string /u01/app/oracle/oradata/orcl/c 55 ontrol01.ctl, /u01/app/oracle/ 56 fast_recovery_area/orcl/contro 57 l02.ctl, /u01/app/oracle/fast_ 58 59 NAME TYPE 60 ------------------------------------ ----------- ------------------------------ 61 recovery_area/orcl/control03.c 62 tl 63 control_management_pack_access string DIAGNOSTIC+TUNING 64 core_dump_dest string /u01/app/oracle/diag/rdbms/orc 65 l/orcl/cdump 66 cpu_count 4 67 create_bitmap_area_size 8388608 68 create_stored_outlines string 69 cursor_bind_capture_destination string memory+ 70 cursor_sharing string EXACT 71 cursor_space_for_time boolean 72 73 NAME TYPE 74 ------------------------------------ ----------- ------------------------------ 75 db_16k_cache_size big 16M 76 db_2k_cache_size big 0 77 db_32k_cache_size big 0 78 db_4k_cache_size big 0 79 db_8k_cache_size big 0 80 db_block_buffers 0 81 db_block_checking string 82 db_block_checksum string TYPICAL 83 db_block_size 8192 84 db_cache_advice string 85 db_cache_size big 0 86 87 NAME TYPE 88 ------------------------------------ ----------- ------------------------------ 89 db_create_file_dest string 90 db_create_online_log_dest_1 string 91 db_create_online_log_dest_2 string 92 db_create_online_log_dest_3 string 93 db_create_online_log_dest_4 string 94 db_create_online_log_dest_5 string 95 db_domain string 96 db_file_multiblock_read_count 124 97 db_file_name_convert string 98 db_files 200 99 db_flash_cache_file string100 101 NAME TYPE 102 ------------------------------------ ----------- ------------------------------103 db_flash_cache_size big 0104 db_flashback_retention_target 1440105 db_keep_cache_size big 0106 db_lost_write_protect string 107 db_name string orcl108 db_recovery_file_dest string /u01/app/oracle/fast_recovery_109 area110 db_recovery_file_dest_size big 4122M111 db_recycle_cache_size big 0112 db_securefile string PERMITTED113 db_ultra_safe string 114 115 NAME TYPE 116 ------------------------------------ ----------- ------------------------------117 db_unique_name string orcl118 db_unrecoverable_scn_tracking boolean 119 db_writer_processes 1120 dbwr_io_slaves 0121 ddl_lock_timeout 0122 deferred_segment_creation boolean 123 dg_broker_config_file1 string /u01/app/oracle/product/11.2.0124 /db_1/dbs/dr1orcl.dat125 dg_broker_config_file2 string /u01/app/oracle/product/11.2.0126 /db_1/dbs/dr2orcl.dat127 dg_broker_start boolean 128 129 NAME TYPE 130 ------------------------------------ ----------- ------------------------------131 diagnostic_dest string /u01/app/oracle132 disk_asynch_io boolean 133 dispatchers string (PROTOCOL=TCP) (SERVICE=orclXD134 B)135 distributed_lock_timeout 60136 dml_locks 1088137 dst_upgrade_insert_conv boolean 138 enable_ddl_logging boolean 139 event string140 fal_client string141 fal_server string142 143 NAME TYPE 144 ------------------------------------ ----------- ------------------------------145 fast_start_io_target 0146 fast_start_mttr_target 0147 fast_start_parallel_rollback string LOW148 file_mapping boolean 149 fileio_network_adapters string150 filesystemio_options string 151 fixed_date string152 gcs_server_processes 0153 global_context_pool_size string154 global_names boolean 155 global_txn_processes 1156 157 NAME TYPE 158 ------------------------------------ ----------- ------------------------------159 hash_area_size 131072160 hi_shared_memory_address 0161 hs_autoregister boolean 162 ifile 163 instance_groups string164 instance_name string orcl165 instance_number 0166 instance_type string RDBMS167 java_jit_enabled boolean 168 java_max_sessionspace_size 0169 java_pool_size big 0170 171 NAME TYPE 172 ------------------------------------ ----------- ------------------------------173 java_soft_sessionspace_limit 0174 job_queue_processes 1000175 large_pool_size big 32M176 ldap_directory_access string 177 ldap_directory_sysauth string 178 license_max_sessions 0179 license_max_users 0180 license_sessions_warning 0181 listener_networks string182 local_listener string183 lock_name_space string184 185 NAME TYPE 186 ------------------------------------ ----------- ------------------------------187 lock_sga boolean 188 log_archive_config string189 log_archive_dest string190 log_archive_dest_1 string location=/home/oracle/oracle_s191 ystem_files_back/archivelog/ar192 chivelog_20180305/archive1193 log_archive_dest_10 string location=/home/oracle/oracle_s194 ystem_files_back/archivelog/ar195 chivelog_20180305/archive2196 log_archive_dest_11 string197 log_archive_dest_12 string198 199 NAME TYPE 200 ------------------------------------ ----------- ------------------------------201 log_archive_dest_13 string202 log_archive_dest_14 string203 log_archive_dest_15 string204 log_archive_dest_16 string205 log_archive_dest_17 string206 log_archive_dest_18 string207 log_archive_dest_19 string208 log_archive_dest_2 string location=/home/oracle/oracle_s209 ystem_files_back/archivelog/ar210 chivelog_20180305/archive2_1 o211 ptional212 213 NAME TYPE 214 ------------------------------------ ----------- ------------------------------215 log_archive_dest_20 string216 log_archive_dest_21 string217 log_archive_dest_22 string218 log_archive_dest_23 string219 log_archive_dest_24 string220 log_archive_dest_25 string221 log_archive_dest_26 string222 log_archive_dest_27 string223 log_archive_dest_28 string224 log_archive_dest_29 string225 log_archive_dest_3 string location=/home/oracle/oracle_s226 227 NAME TYPE 228 ------------------------------------ ----------- ------------------------------229 ystem_files_back/archivelog/ar230 chivelog_20180305/archive3_1231 mandatory232 log_archive_dest_30 string233 log_archive_dest_31 string234 log_archive_dest_4 string location=/home/oracle/oracle_s235 ystem_files_back/archivelog/ar236 chivelog_20180305/archive4 man237 datory reopen=400238 log_archive_dest_5 string location=/home/oracle/oracle_s239 ystem_files_back/archivelog/ar240 241 NAME TYPE 242 ------------------------------------ ----------- ------------------------------243 chivelog_20180305/archive5244 log_archive_dest_6 string245 log_archive_dest_7 string246 log_archive_dest_8 string247 log_archive_dest_9 string248 log_archive_dest_state_1 string enable249 log_archive_dest_state_10 string enable250 log_archive_dest_state_11 string enable251 log_archive_dest_state_12 string enable252 log_archive_dest_state_13 string enable253 log_archive_dest_state_14 string enable254 255 NAME TYPE 256 ------------------------------------ ----------- ------------------------------257 log_archive_dest_state_15 string enable258 log_archive_dest_state_16 string enable259 log_archive_dest_state_17 string enable260 log_archive_dest_state_18 string enable261 log_archive_dest_state_19 string enable262 log_archive_dest_state_2 string enable263 log_archive_dest_state_20 string enable264 log_archive_dest_state_21 string enable265 log_archive_dest_state_22 string enable266 log_archive_dest_state_23 string enable267 log_archive_dest_state_24 string enable268 269 NAME TYPE 270 ------------------------------------ ----------- ------------------------------271 log_archive_dest_state_25 string enable272 log_archive_dest_state_26 string enable273 log_archive_dest_state_27 string enable274 log_archive_dest_state_28 string enable275 log_archive_dest_state_29 string enable276 log_archive_dest_state_3 string enable277 log_archive_dest_state_30 string enable278 log_archive_dest_state_31 string enable279 log_archive_dest_state_4 string DEFER280 log_archive_dest_state_5 string enable281 log_archive_dest_state_6 string enable282 283 NAME TYPE 284 ------------------------------------ ----------- ------------------------------285 log_archive_dest_state_7 string enable286 log_archive_dest_state_8 string enable287 log_archive_dest_state_9 string enable288 log_archive_duplex_dest string289 log_archive_format string %t_%s_%r.dbf290 log_archive_local_first boolean 291 log_archive_max_processes 8292 log_archive_min_succeed_dest 3293 log_archive_start boolean 294 log_archive_trace 0295 log_buffer 12386304296 297 NAME TYPE 298 ------------------------------------ ----------- ------------------------------299 log_checkpoint_interval 0300 log_checkpoint_timeout 1800301 log_checkpoints_to_alert boolean 302 log_file_name_convert string303 max_dispatchers 304 max_dump_file_size string unlimited305 max_enabled_roles 150306 max_shared_servers 307 memory_max_target big 1168M308 memory_target big 1168M309 nls_calendar string310 311 NAME TYPE 312 ------------------------------------ ----------- ------------------------------313 nls_comp string 314 nls_currency string315 nls_date_format string316 nls_date_language string317 nls_dual_currency string318 nls_iso_currency string319 nls_language string AMERICAN320 nls_length_semantics string BYTE321 nls_nchar_conv_excp string 322 nls_numeric_characters string323 nls_sort string324 325 NAME TYPE 326 ------------------------------------ ----------- ------------------------------327 nls_territory string AMERICA328 nls_time_format string329 nls_time_tz_format string330 nls_timestamp_format string331 nls_timestamp_tz_format string332 object_cache_max_size_percent 10333 object_cache_optimal_size 102400334 olap_page_pool_size big 0335 open_cursors 350336 open_links 4337 open_links_per_instance 4338 339 NAME TYPE 340 ------------------------------------ ----------- ------------------------------341 optimizer_capture_sql_plan_baselines boolean 342 optimizer_dynamic_sampling 2343 optimizer_features_enable string 11.2.0.3344 optimizer_index_caching 0345 optimizer_index_cost_adj 100346 optimizer_mode string ALL_ROWS347 optimizer_secure_view_merging boolean 348 optimizer_use_invisible_indexes boolean 349 optimizer_use_pending_statistics boolean 350 optimizer_use_sql_plan_baselines boolean 351 os_authent_prefix string ops$352 353 NAME TYPE 354 ------------------------------------ ----------- ------------------------------355 os_roles boolean 356 parallel_adaptive_multi_user boolean 357 parallel_automatic_tuning boolean 358 parallel_degree_limit string CPU359 parallel_degree_policy string MANUAL360 parallel_execution_message_size 16384361 parallel_force_local boolean 362 parallel_instance_group string363 parallel_io_cap_enabled boolean 364 parallel_max_servers 135365 parallel_min_percent 0366 367 NAME TYPE 368 ------------------------------------ ----------- ------------------------------369 parallel_min_servers 0370 parallel_min_time_threshold string AUTO371 parallel_server boolean 372 parallel_server_instances 1373 parallel_servers_target 64374 parallel_threads_per_cpu 2375 permit_92_wrap_format boolean 376 pga_aggregate_target big 0377 plscope_settings string IDENTIFIERS:378 plsql_ccflags string379 plsql_code_type string INTERPRETED380 381 NAME TYPE 382 ------------------------------------ ----------- ------------------------------383 plsql_debug boolean 384 plsql_optimize_level 2385 plsql_v2_compatibility boolean 386 plsql_warnings string DISABLE:387 pre_page_sga boolean 388 processes 150389 processor_group_name string390 query_rewrite_enabled string 391 query_rewrite_integrity string enforced392 rdbms_server_dn string393 read_only_open_delayed boolean 394 395 NAME TYPE 396 ------------------------------------ ----------- ------------------------------397 recovery_parallelism 0398 recyclebin string 399 redo_transport_user string400 remote_dependencies_mode string 401 remote_listener string402 remote_login_passwordfile string EXCLUSIVE403 remote_os_authent boolean 404 remote_os_roles boolean 405 replication_dependency_tracking boolean 406 resource_limit boolean 407 resource_manager_cpu_allocation 4408 409 NAME TYPE 410 ------------------------------------ ----------- ------------------------------411 resource_manager_plan string412 result_cache_max_result 5413 result_cache_max_size big 3008K414 result_cache_mode string MANUAL415 result_cache_remote_expiration 0416 resumable_timeout 0417 rollback_segments string418 sec_case_sensitive_logon boolean 419 sec_max_failed_login_attempts 10420 sec_protocol_error_further_action string 421 sec_protocol_error_trace_action string TRACE422 423 NAME TYPE 424 ------------------------------------ ----------- ------------------------------425 sec_return_server_release_banner boolean 426 serial_reuse string disable427 service_names string orcl428 session_cached_cursors 100429 session_max_open_files 10430 sessions 248431 sga_max_size big 1168M432 sga_target big 0433 shadow_core_dump string 434 shared_memory_address 0435 shared_pool_reserved_size big 22649241436 437 NAME TYPE 438 ------------------------------------ ----------- ------------------------------439 shared_pool_size big 0440 shared_server_sessions 441 shared_servers 1442 skip_unusable_indexes boolean 443 smtp_out_server string444 sort_area_retained_size 0445 sort_area_size 65536446 spfile string /u01/app/oracle/product/11.2.0447 /db_1/dbs/spfileorcl.ora448 92_security boolean 449 sql_trace boolean 450 451 NAME TYPE 452 ------------------------------------ ----------- ------------------------------453 sqltune_category string 454 standby_archive_dest string ?/dbs/arch455 standby_file_management string MANUAL456 star_transformation_enabled string 457 statistics_level string TYPICAL458 streams_pool_size big 0459 tape_asynch_io boolean 460 thread 0461 timed_os_statistics 0462 timed_statistics boolean 463 trace_enabled boolean 464 465 NAME TYPE 466 ------------------------------------ ----------- ------------------------------467 tracefile_identifier string468 transactions 272469 transactions_per_rollback_segment 5470 undo_management string AUTO471 undo_retention 900472 undo_tablespace string UNDOTBS1473 use_indirect_data_buffers boolean 474 use_large_pages string 475 user_dump_dest string /u01/app/oracle/diag/rdbms/orc476 l/orcl/trace477 utl_file_dir string478 479 NAME TYPE 480 ------------------------------------ ----------- ------------------------------481 workarea_size_policy string AUTO482 xml_db_events string enable483 488 489 490 SYS@orcl> (*) v$parameter;491 492 (*)493 ----------494 347495 496 SYS@orcl>
7、创建pfile
8、创建spfile
create pfile from spfile; create pfile='路径' from spfile; create pfile from memory;
create spfile from pfile;
create spfile='路径' from pfile; create spfile from memory;
9、修改参数
一、使用spfile文件
1）动态参数
- 1、当参数的isses_modifiable 为TRUE 时，可以使用alter session 修改,仅对当前会话生效，其它会话不生效，重启库后参数值丢失
- 2、当参数的issys_modifiable 为IMMEDIATE 时，可以使用alter system 修改，修改所有的会话生效，重启库后参数值不丢失。
- 3、当参数的issys_modifiable 为deferred 时，可以使用alter system 修改，只对新建立的会话起作用，对已存在会话不起作用，重启库后参数值不丢失，所有会话生效。
- 4、利用spfile 创建pfile,然后修改pfile 的文件，再重创建spfile
2）静态参数
- 1、直接修改参数文件 ; 先创建一个pfile，然后修改pfile文件，使用pfile启动数据库，创建spfile
- 2、使用alter system ...scope=spfile;重启数据库后参数生效
scope=spfile 修改spfile 参数文件，需要重启数据库
scope=both 同时修改spfile 和memory scope=memory 修改memory 注意：当使用alter system命令时，没有加scope选项，表示使用scope=both二、使用pfile文件
1）动态参数
- 1)直接修改参数文件 直接修改pfile文件，使用pfile启动数据库
- 2)可以使用alter session修改参数，但不会直接修改pfile，重启数据库后参数值丢失，临时修改参数 nls_date_format
- 3)可以使用alter system修改参数，但不会直接修改pfile，重启数据库后参数值丢失，临时修改参数
2）静态参数
- 1)直接修改参数文件 直接修改pfile文件，使用pfile启动数据库
- 2）不能使用alter system ...scope=spfile修改参数
示例参数：
nls_date_format sort_area_size trace_enabled control_files
10、ORACLE建议使用spfile的参数文件，为什么 ？
1）rman对参数文件的备份 2）可以在数据库open下，修改参数
11、参数文件丢失后的恢复
1、利用rman的备份进行恢复参数文件 2、使用备份的pfile文件进行创建spfile，从而实现参数文件的恢复 3、从模板文件init.ora中生成pfile文件，然后再创建spfile 4、利用/u01/app/oracle/admin/orcl/pfile/init.ora.2262016111447生成pfile文件，然后再创建spfile
12：spfile 和 pfile 文件之间的切换生成和启动数据库
1 2 3 #进入 $ORACLE_HOME/dbs 控制文件目录文件夹 4 [oracle@localhost ~]$ cd $ORACLE_HOME/dbs; 5 #显示文件路径 6 [oracle@localhost dbs]$ pwd 7 /u01/app/oracle/product/11.2.0/db_1/dbs 8 #查看文件信息 此时磁盘并没有 initorcl.ora 文件 9 [oracle@localhost dbs]$ ls 10 dbsorapwPROD1 hc_orcl.dat hc_PROD1.dat init.ora initPROD1.ora lkORCL lkPROD1 orapworcl spfileorcl.ora spfilePROD1.ora 11 #登录sqlplus 命令工具 12 [oracle@localhost dbs]$ sqlplus / as sysdba; 13 14 SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 19 22:42:20 2018 15 16 Copyright (c) 1982, 2011, Oracle. All rights reserved. 17 18 19 Connected to: 20 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production 21 With the Partitioning, OLAP, Mining and Real Application Testing options 22 ---启动数据库 23 24 SYS@orcl> startup 25 26 ORACLE instance started. 27 28 Total System Global Area 1221992448 bytes 29 Fixed Size 1344596 bytes 30 Variable Size 939527084 bytes 31 Database Buffers 268435456 bytes 32 Redo Buffers 12685312 bytes 33 Database mounted. 34 Database opened. 35 # 查看系统参数 spfile 的情况。如果有value 值表是此时数据库是以spfiel文件启动的。 36 SYS@orcl> show parameter spfile; 37 38 NAME TYPE VALUE 39 ------------------------------------ ----------- ------------------------------ 40 spfile string /u01/app/oracle/product/11.2.0 41 #创建 pfile 文件 /db_1/dbs/spfileorcl.ora 42 SYS@orcl> create pfile from spfile; 43 44 created. 45 #关闭数据库 46 SYS@orcl> shutdown immediate; 47 Database closed. 48 Database dismounted. 49 ORACLE instance shut down. 50 #退出sqlplus 命令工具 51 SYS@orcl> quit 52 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production 53 With the Partitioning, OLAP, Mining and Real Application Testing options 54 # 查看参数文件目录下的文件信息；此时 该文件目录中已经多了一个 initorcl.ora 文件。 55 [oracle@localhost dbs]$ ls 56 dbsorapwPROD1 hc_orcl.dat hc_PROD1.dat init.ora initorcl.ora initPROD1.ora lkORCL lkPROD1 orapworcl spfileorcl.ora spfilePROD1.ora 57 #查看文件路径信息 58 [oracle@localhost dbs]$ pwd 59 /u01/app/oracle/product/11.2.0/db_1/dbs 60 #登录sqlplus 命令工具 61 [oracle@localhost dbs]$ sqlplus / as sysdba; 62 63 SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 19 22:45:47 2018 64 65 Copyright (c) 1982, 2011, Oracle. All rights reserved. 66 67 Connected to an idle instance. 68 # 在控制文件存在spfile、pfile 文件的前提下，以pfile方式启动数据库 69 SYS@orcl> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora'; 70 71 ORACLE instance started. 72 73 Total System Global Area 1221992448 bytes 74 Fixed Size 1344596 bytes 75 Variable Size 939527084 bytes 76 Database Buffers 268435456 bytes 77 Redo Buffers 12685312 bytes 78 Database mounted. 79 Database opened. 80 #显示系统参数 spfile的信息。 此时 value 并没有值。表示 该次是以 pfile 方式启动数据库的 81 SYS@orcl> show parameter spfile; 82 83 NAME TYPE VALUE 84 ------------------------------------ ----------- ------------------------------ 85 spfile string 86 #关闭数据库 87 SYS@orcl> shutdown immediate; 88 Database closed. 89 Database dismounted. 90 ORACLE instance shut down. 91 # 退出 92 SYS@orcl> quit; 93 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production 94 With the Partitioning, OLAP, Mining and Real Application Testing options 95 #查看文件信息 96 [oracle@localhost dbs]$ ls 97 dbsorapwPROD1 hc_orcl.dat hc_PROD1.dat init.ora initorcl.ora initPROD1.ora lkORCL lkPROD1 orapworcl spfileorcl.ora spfilePROD1.ora 98 #删除oracl 实例的spfile文件: spfileorcl.ora 99 [oracle@localhost dbs]$ rm spfileorcl.ora100 #查看文件信息 可以看出，此时数据库实例orcl的spfile文件 spfileorcl.ora 已经不存在了101 [oracle@localhost dbs]$ ls102 dbsorapwPROD1 hc_orcl.dat hc_PROD1.dat init.ora initorcl.ora initPROD1.ora lkORCL lkPROD1 orapworcl spfilePROD1.ora103 #登录 sqlplus 工具104 [oracle@localhost dbs]$ sqlplus / as sysdba;105 106 SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 19 22:48:48 2018107 108 Copyright (c) 1982, 2011, Oracle. All rights reserved.109 110 Connected to an idle instance.111 #启动数据库112 SYS@orcl> startup113 114 ORACLE instance started.115 116 Total System Global Area 1221992448 bytes117 Fixed Size 1344596 bytes118 Variable Size 939527084 bytes119 Database Buffers 268435456 bytes120 Redo Buffers 12685312 bytes121 Database mounted.122 Database opened.123 #查看系统参数 spfile 信息；此时 value 并没有值。表示 oracle数据库的实例orcl在没有spfileorcl.orcl文件而存在pfileorcl.orcl文件的前提下，会默认以pfileorcl.ora文件的方式启动数据库实例orcl.124 SYS@orcl> show parameter spfile;125 126 NAME TYPE VALUE127 ------------------------------------ ----------- ------------------------------128 spfile string129 #创建数据库实例orcl的spfile文件。130 SYS@orcl> create spfile from pfile;131 132 created.133 #关闭数据库134 SYS@orcl> shutdown immediate;135 Database closed.136 Database dismounted.137 ORACLE instance shut down.138 139 #退出140 SYS@orcl> quit;141 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production142 With the Partitioning, OLAP, Mining and Real Application Testing options143 #查看文件目录信息 此时可以观察得知，此时已经多了一个 spfileorcl.ora 文件 144 [oracle@localhost dbs]$ ls145 dbsorapwPROD1 hc_orcl.dat hc_PROD1.dat init.ora initorcl.ora initPROD1.ora lkORCL lkPROD1 orapworcl spfileorcl.ora spfilePROD1.ora146 # 进入sqlplus工具中147 [oracle@localhost dbs]$ sqlplus / as sysdba;148 149 SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 19 22:50:22 2018150 151 Copyright (c) 1982, 2011, Oracle. All rights reserved.152 153 Connected to an idle instance.154 #启动数据库155 SYS@orcl> startup156 ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance157 ORACLE instance started.158 159 Total System Global Area 1221992448 bytes160 Fixed Size 1344596 bytes161 Variable Size 939527084 bytes162 Database Buffers 268435456 bytes163 Redo Buffers 12685312 bytes164 Database mounted.165 Database opened.166 #查看spfile系统参数信息：此时 value有值。进一步表明了：数据库实例orcl在同时存在 pfile 和 spfile 文件情况下，数据库实例默认是以spfile方式启动。167 SYS@orcl> show parameter spfile;168 169 NAME TYPE VALUE170 ------------------------------------ ----------- ------------------------------171 spfile string /u01/app/oracle/product/11.2.0172 /db_1/dbs/spfileorcl.ora173 SYS@orcl>
13：从模板文件init.ora中生成pfile文件，然后再创建spfile
1 [oracle@localhost ~]$ ls 2 database Desktop grid h:1dept.sql h:1emp.txt h:1.lst h:1spooltest.txt h:emp.txt oracle_system_files_back oyt.lst rlwrap-0.37 rlwrap-0.37.tar.gz 3 [oracle@localhost ~]$ cd $ORACLE_HOME/dbs 4 [oracle@localhost dbs]$ pwd 5 /u01/app/oracle/product/11.2.0/db_1/dbs 6 [oracle@localhost dbs]$ ls 7 dbsorapwPROD1 hc_orcl.dat hc_PROD1.dat init.ora initorcl.ora initPROD1.ora lkORCL lkPROD1 orapworcl spfileorcl.ora spfilePROD1.ora 8 [oracle@localhost dbs]$ rm initorcl.ora 9 [oracle@localhost dbs]$ ls 10 dbsorapwPROD1 hc_orcl.dat hc_PROD1.dat init.ora initPROD1.ora lkORCL lkPROD1 orapworcl spfileorcl.ora spfilePROD1.ora 11 [oracle@localhost dbs]$ rm spfileorcl.ora 12 [oracle@localhost dbs]$ ls 13 dbsorapwPROD1 hc_orcl.dat hc_PROD1.dat init.ora initPROD1.ora lkORCL lkPROD1 orapworcl spfilePROD1.ora 14 [oracle@localhost dbs]$ sqlplus / as sysdba; 15 16 SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 23 22:50:00 2018 17 18 Copyright (c) 1982, 2011, Oracle. All rights reserved. 19 20 21 Connected to: 22 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production 23 With the Partitioning, OLAP, Mining and Real Application Testing options 24 25 SYS@orcl> shutdown immediate; 26 Database closed. 27 Database dismounted. 28 ORACLE instance shut down. 29 SYS@orcl> startup 30 ORA-01078: failure in processing system parameters 31 LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora' 32 SYS@orcl> quit 33 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production 34 With the Partitioning, OLAP, Mining and Real Application Testing options 35 [oracle@localhost dbs]$ ls 36 dbsorapwPROD1 hc_orcl.dat hc_PROD1.dat init.ora initPROD1.ora lkORCL lkPROD1 orapworcl spfilePROD1.ora 37 [oracle@localhost dbs]$ vat init.ora 38 -bash: vat: command not found 39 [oracle@localhost dbs]$ cat init.ora 40 # 41 # $Header: rdbms/admin/init.ora /main/23 2009/05/15 13:35:38 ysarig Exp $ 42 # 43 # Copyright (c) 1991, 1997, 1998 by Oracle Corporation 44 # NAME 45 # init.ora 46 # FUNCTION 47 # NOTES 48 # MODIFIED 49 # ysarig 05/14/09 - Updating compatible to 11.2 50 # ysarig 08/13/07 - Fixing the sample for 11g 51 # atsukerm 08/06/98 - fix for 8.1. 52 # hpiao 06/05/97 - fix for 803 53 # glavash 05/12/97 - add oracle_trace_enable comment 54 # hpiao 04/22/97 - remove ifile=, events=, etc. 55 # alingelb 09/19/94 - remove vms-specific stuff 56 # dpawson 07/07/93 - add more comments regarded archive start 57 # maporter 10/29/92 - Add vms_sga_use_gblpagfile=TRUE 58 # jloaiza 03/07/92 - change ALPHA to BETA 59 # danderso 02/26/92 - change db_block_cache_protect to _db_block_cache_p 60 # ghallmar 02/03/92 - db_directory -> db_domain 61 # maporter 01/12/92 - merge changes from branch 1.8.308.1 62 # maporter 12/21/91 - bug 76493: Add control_files parameter 63 # wbridge 12/03/91 - use of %c in archive format is discouraged 64 # ghallmar 12/02/91 - add global_names=true, db_directory=us.acme.com 65 # thayes 11/27/91 - Change default for cache_clone 66 # jloaiza 08/13/91 - merge changes from branch 1.7.100.1 67 # jloaiza 07/31/91 - add debug stuff 68 # rlim 04/29/91 - removal of char_is_varchar2 69 # Bridge 03/12/91 - log_allocation no longer exists 70 # Wijaya 02/05/91 - remove obsolete parameters 71 # 72 ############################################################################## 73 # Example INIT.ORA file 74 # 75 # This file is provided by Oracle Corporation to help you start by providing 76 # a starting point to customize your RDBMS installation for your site. 77 # 78 # NOTE: The values that are used in this file are only intended to be used 79 # as a starting point. You may want to adjust/tune those values to your 80 # specific hardware and needs. You may also consider using Database 81 # Configuration Assistant tool (DBCA) to create INIT file and to size your 82 # initial set of tablespaces based on the user input. 83 ############################################################################### 84 85 # Change '' to point to the oracle base (the one you specify at 86 # install time) 87 88 db_name='ORCL' 89 memory_target=1G 90 processes = 150 91 audit_file_dest=' /admin/orcl/adump' 92 audit_trail ='db' 93 db_block_size=8192 94 db_domain='' 95 db_recovery_file_dest=' /flash_recovery_area' 96 db_recovery_file_dest_size=2G 97 diagnostic_dest=' ' 98 dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)' 99 open_cursors=300100 remote_login_passwordfile='EXCLUSIVE'101 undo_tablespace='UNDOTBS1'102 # You may want to ensure that control files are created on separate physical103 # devices104 control_files = (ora_control1, ora_control2)105 compatible ='11.2.0'106 [oracle@localhost dbs]$ cat init.ora |107 > grep108 Usage: grep [OPTION]... PATTERN [FILE]...109 Try `grep --help' for more information.110 [oracle@localhost dbs]$ cat init.ora | grep -v ^# > initorcl.ora111 [oracle@localhost dbs]$ ls112 dbsorapwPROD1 hc_orcl.dat hc_PROD1.dat init.ora initorcl.ora initPROD1.ora lkORCL lkPROD1 orapworcl spfilePROD1.ora113 [oracle@localhost dbs]$ cat initorcl.ora114 115 116 db_name='ORCL'117 memory_target=1G118 processes = 150119 audit_file_dest=' /admin/orcl/adump'120 audit_trail ='db'121 db_block_size=8192122 db_domain=''123 db_recovery_file_dest=' /flash_recovery_area'124 db_recovery_file_dest_size=2G125 diagnostic_dest=' '126 dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'127 open_cursors=300128 remote_login_passwordfile='EXCLUSIVE'129 undo_tablespace='UNDOTBS1'130 control_files = (ora_control1, ora_control2)131 compatible ='11.2.0'132 [oracle@localhost dbs]$ vi initorcl.ora133 134 135 136 db_name='orcl'137 memory_target=500m138 processes = 150139 audit_file_dest='/u01/app/oracle/admin/orcl/adump'140 audit_trail ='db'141 db_block_size=8192142 db_domain=''143 db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'144 db_recovery_file_dest_size=2G145 diagnostic_dest='/u01/app/oracle'146 dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'147 open_cursors=300148 remote_login_passwordfile='EXCLUSIVE'149 undo_tablespace='UNDOTBS1'150 control_files = ('/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl')151 compatible ='11.2.0.3'152 ~153 ~154 ~155 ~156 ~157 ~158 ~159 ~160 ~161 ~162 ~163 "initorcl.ora" 18L, 549C written164 [oracle@localhost dbs]$ sqlplus / as sysdba;165 166 SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 23 23:06:23 2018167 168 Copyright (c) 1982, 2011, Oracle. All rights reserved.169 170 Connected to an idle instance.171 172 SYS@orcl> startup173 ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated174 ORA-01262: Stat failed on a file destination directory175 Linux Error: 2: No such file or directory176 SYS@orcl> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora'177 ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated178 ORA-01262: Stat failed on a file destination directory179 Linux Error: 2: No such file or directory180 SYS@orcl> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora';181 ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated182 ORA-01262: Stat failed on a file destination directory183 Linux Error: 2: No such file or directory184 SYS@orcl> quit185 Disconnected186 [oracle@localhost dbs]$ cat initorcl.ora187 188 189 db_name='orcl'190 memory_target=500m191 processes = 150192 audit_file_dest='/u01/app/oracle/admin/orcl/adump'193 audit_trail ='db'194 db_block_size=8192195 db_domain=''196 db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'197 db_recovery_file_dest_size=2G198 diagnostic_dest='/u01/app/oracle'199 dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'200 open_cursors=300201 remote_login_passwordfile='EXCLUSIVE'202 undo_tablespace='UNDOTBS1'203 control_files = ('/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl')204 compatible ='11.2.0.3'205 [oracle@localhost dbs]$ cd /u01/app/oracle/admin/orcl/adump/206 [oracle@localhost adump]$ cd /u01/app/oracle/fast_recovery_area/207 [oracle@localhost fast_recovery_area]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/208 [oracle@localhost dbs]$ ls209 dbsorapwPROD1 hc_orcl.dat hc_PROD1.dat init.ora initorcl.ora initPROD1.ora lkORCL lkPROD1 orapworcl spfilePROD1.ora210 [oracle@localhost dbs]$ vi initorcl.ora211 212 213 214 db_name='orcl'215 memory_target=500m216 processes = 150217 audit_file_dest='/u01/app/oracle/admin/orcl/adump'218 audit_trail ='db'219 db_block_size=8192220 db_domain=''221 db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'222 db_recovery_file_dest_size=2G223 diagnostic_dest='/u01/app/oracle'224 dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'225 open_cursors=300226 remote_login_passwordfile='EXCLUSIVE'227 undo_tablespace='UNDOTBS1'228 control_files = ('/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl')229 compatible ='11.2.0.3'230 ~231 ~232 ~233 ~234 ~235 ~236 ~237 ~238 ~239 ~240 ~241 "initorcl.ora" 18L, 548C written242 [oracle@localhost dbs]$ cat initorcl.ora243 244 245 db_name='orcl'246 memory_target=500m247 processes = 150248 audit_file_dest='/u01/app/oracle/admin/orcl/adump'249 audit_trail ='db'250 db_block_size=8192251 db_domain=''252 db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'253 db_recovery_file_dest_size=2G254 diagnostic_dest='/u01/app/oracle'255 dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'256 open_cursors=300257 remote_login_passwordfile='EXCLUSIVE'258 undo_tablespace='UNDOTBS1'259 control_files = ('/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl')260 compatible ='11.2.0.3'261 [oracle@localhost dbs]$ cd /u01/app/oracle/fast_recovery_area/262 [oracle@localhost fast_recovery_area]$ ls263 orcl ORCL264 [oracle@localhost fast_recovery_area]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/265 [oracle@localhost dbs]$ ls266 dbsorapwPROD1 hc_orcl.dat hc_PROD1.dat init.ora initorcl.ora initPROD1.ora lkORCL lkPROD1 orapworcl spfilePROD1.ora267 [oracle@localhost dbs]$ sqlplus / as sysdba;268 269 SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 23 23:13:17 2018270 271 Copyright (c) 1982, 2011, Oracle. All rights reserved.272 273 Connected to an idle instance.274 275 SYS@orcl> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora';276 ORACLE instance started.277 278 Total System Global Area 523108352 bytes279 Fixed Size 1346052 bytes280 Variable Size 314574332 bytes281 Database Buffers 201326592 bytes282 Redo Buffers 5861376 bytes283 Database mounted.284 Database opened.285 SYS@orcl> create spfile from pfile;286 287 created.288 289 SYS@orcl> shutdown immediate;290 Database closed.291 Database dismounted.292 ORACLE instance shut down.293 SYS@orcl> quit294 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production295 With the Partitioning, OLAP, Mining and Real Application Testing options296 [oracle@localhost dbs]$ ls297 dbsorapwPROD1 hc_orcl.dat hc_PROD1.dat init.ora initorcl.ora initPROD1.ora lkORCL lkPROD1 orapworcl spfileorcl.ora spfilePROD1.ora298 [oracle@localhost dbs]$ sqlplus / as sysdba;299 300 SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 23 23:15:12 2018301 302 Copyright (c) 1982, 2011, Oracle. All rights reserved.303 304 Connected to an idle instance.305 306 SYS@orcl> ls307 SP2-0042: unknown command "ls" - rest of line ignored.308 SYS@orcl> startup309 ORACLE instance started.310 311 Total System Global Area 523108352 bytes312 Fixed Size 1346052 bytes313 Variable Size 314574332 bytes314 Database Buffers 201326592 bytes315 Redo Buffers 5861376 bytes316 Database mounted.317 Database opened.318 SYS@orcl> 如果出现了 ora-00845 的错误：请查看 《 》；
1 SYS@orcl> show parameter spfile; 2 3 NAME TYPE VALUE 4 ------------------------------------ ----------- ------------------------------ 5 spfile string /u01/app/oracle/product/11.2.0 6 /db_1/dbs/spfileorcl.ora 7 SYS@orcl>
14：利用/u01/app/oracle/admin/orcl/pfile/init.ora.2262016111447生成pfile文件，然后再创建spfile
1 [oracle@localhost dbs]$ cd /u01/app/oracle/admin/orcl/pfile/ 2 [oracle@localhost pfile]$ ls 3 init.ora.1030201634358 4 [oracle@localhost pfile]$ cp /u01/app/oracle/admin/orcl/pfile/init.ora.1030201634358 /u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora 5 [oracle@localhost pfile]$注：后续启动数据库生成spfile文件的步骤请参考 13 标题的操作内容步骤