参数文件
官方文档的位置
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, Data Mining and Real Application Testing options 28 #创建 pfile 29 SYS@orcl> create pfile from spfile; 30 31 File 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, Data 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 #initorcl.ora 为pfile 文件 43 -rw-r--r-- 1 oracle oinstall 1948 Apr 19 21:49 initorcl.ora 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, Data 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 select NAME,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where ISSES_MODIFIABLE='FALSE' and ISSYS_MODIFIABLE='FALSE';
1 SYS@orcl> show parameter 2 3 NAME TYPE VALUE 4 ------------------------------------ ----------- ------------------------------ 5 O7_DICTIONARY_ACCESSIBILITY boolean FALSE 6 active_instance_count integer 7 aq_tm_processes integer 1 8 archive_lag_target integer 0 9 asm_diskgroups string 10 asm_diskstring string 11 asm_power_limit integer 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 FALSE 16 17 NAME TYPE VALUE 18 ------------------------------------ ----------- ------------------------------ 19 audit_syslog_level string 20 audit_trail string DB 21 awr_snapshot_time_offset integer 0 22 background_core_dump string partial 23 background_dump_dest string /u01/app/oracle/diag/rdbms/orc 24 l/orcl/trace 25 backup_tape_io_slaves boolean FALSE 26 bitmap_merge_area_size integer 1048576 27 blank_trimming boolean FALSE 28 buffer_pool_keep string 29 buffer_pool_recycle string 30 31 NAME TYPE VALUE 32 ------------------------------------ ----------- ------------------------------ 33 cell_offload_compaction string ADAPTIVE 34 cell_offload_decryption boolean TRUE 35 cell_offload_parameters string 36 cell_offload_plan_display string AUTO 37 cell_offload_processing boolean TRUE 38 circuits integer 39 client_result_cache_lag big integer 3000 40 client_result_cache_size big integer 0 41 clonedb boolean FALSE 42 cluster_database boolean FALSE 43 cluster_database_instances integer 1 44 45 NAME TYPE VALUE 46 ------------------------------------ ----------- ------------------------------ 47 cluster_interconnects string 48 commit_logging string 49 commit_point_strength integer 1 50 commit_wait string 51 commit_write string 52 compatible string 11.2.0.0.0 53 control_file_record_keep_time integer 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 VALUE 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 integer 4 67 create_bitmap_area_size integer 8388608 68 create_stored_outlines string 69 cursor_bind_capture_destination string memory+disk 70 cursor_sharing string EXACT 71 cursor_space_for_time boolean TRUE 72 73 NAME TYPE VALUE 74 ------------------------------------ ----------- ------------------------------ 75 db_16k_cache_size big integer 16M 76 db_2k_cache_size big integer 0 77 db_32k_cache_size big integer 0 78 db_4k_cache_size big integer 0 79 db_8k_cache_size big integer 0 80 db_block_buffers integer 0 81 db_block_checking string FALSE 82 db_block_checksum string TYPICAL 83 db_block_size integer 8192 84 db_cache_advice string ON 85 db_cache_size big integer 0 86 87 NAME TYPE VALUE 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 integer 124 97 db_file_name_convert string 98 db_files integer 200 99 db_flash_cache_file string 100 101 NAME TYPE VALUE 102 ------------------------------------ ----------- ------------------------------ 103 db_flash_cache_size big integer 0 104 db_flashback_retention_target integer 1440 105 db_keep_cache_size big integer 0 106 db_lost_write_protect string NONE 107 db_name string orcl 108 db_recovery_file_dest string /u01/app/oracle/fast_recovery_ 109 area 110 db_recovery_file_dest_size big integer 4122M 111 db_recycle_cache_size big integer 0 112 db_securefile string PERMITTED 113 db_ultra_safe string OFF 114 115 NAME TYPE VALUE 116 ------------------------------------ ----------- ------------------------------ 117 db_unique_name string orcl 118 db_unrecoverable_scn_tracking boolean TRUE 119 db_writer_processes integer 1 120 dbwr_io_slaves integer 0 121 ddl_lock_timeout integer 0 122 deferred_segment_creation boolean TRUE 123 dg_broker_config_file1 string /u01/app/oracle/product/11.2.0 124 /db_1/dbs/dr1orcl.dat 125 dg_broker_config_file2 string /u01/app/oracle/product/11.2.0 126 /db_1/dbs/dr2orcl.dat 127 dg_broker_start boolean FALSE 128 129 NAME TYPE VALUE 130 ------------------------------------ ----------- ------------------------------ 131 diagnostic_dest string /u01/app/oracle 132 disk_asynch_io boolean TRUE 133 dispatchers string (PROTOCOL=TCP) (SERVICE=orclXD 134 B) 135 distributed_lock_timeout integer 60 136 dml_locks integer 1088 137 dst_upgrade_insert_conv boolean TRUE 138 enable_ddl_logging boolean FALSE 139 event string 140 fal_client string 141 fal_server string 142 143 NAME TYPE VALUE 144 ------------------------------------ ----------- ------------------------------ 145 fast_start_io_target integer 0 146 fast_start_mttr_target integer 0 147 fast_start_parallel_rollback string LOW 148 file_mapping boolean FALSE 149 fileio_network_adapters string 150 filesystemio_options string none 151 fixed_date string 152 gcs_server_processes integer 0 153 global_context_pool_size string 154 global_names boolean FALSE 155 global_txn_processes integer 1 156 157 NAME TYPE VALUE 158 ------------------------------------ ----------- ------------------------------ 159 hash_area_size integer 131072 160 hi_shared_memory_address integer 0 161 hs_autoregister boolean TRUE 162 ifile file 163 instance_groups string 164 instance_name string orcl 165 instance_number integer 0 166 instance_type string RDBMS 167 java_jit_enabled boolean TRUE 168 java_max_sessionspace_size integer 0 169 java_pool_size big integer 0 170 171 NAME TYPE VALUE 172 ------------------------------------ ----------- ------------------------------ 173 java_soft_sessionspace_limit integer 0 174 job_queue_processes integer 1000 175 large_pool_size big integer 32M 176 ldap_directory_access string NONE 177 ldap_directory_sysauth string no 178 license_max_sessions integer 0 179 license_max_users integer 0 180 license_sessions_warning integer 0 181 listener_networks string 182 local_listener string 183 lock_name_space string 184 185 NAME TYPE VALUE 186 ------------------------------------ ----------- ------------------------------ 187 lock_sga boolean FALSE 188 log_archive_config string 189 log_archive_dest string 190 log_archive_dest_1 string location=/home/oracle/oracle_s 191 ystem_files_back/archivelog/ar 192 chivelog_20180305/archive1 193 log_archive_dest_10 string location=/home/oracle/oracle_s 194 ystem_files_back/archivelog/ar 195 chivelog_20180305/archive2 196 log_archive_dest_11 string 197 log_archive_dest_12 string 198 199 NAME TYPE VALUE 200 ------------------------------------ ----------- ------------------------------ 201 log_archive_dest_13 string 202 log_archive_dest_14 string 203 log_archive_dest_15 string 204 log_archive_dest_16 string 205 log_archive_dest_17 string 206 log_archive_dest_18 string 207 log_archive_dest_19 string 208 log_archive_dest_2 string location=/home/oracle/oracle_s 209 ystem_files_back/archivelog/ar 210 chivelog_20180305/archive2_1 o 211 ptional 212 213 NAME TYPE VALUE 214 ------------------------------------ ----------- ------------------------------ 215 log_archive_dest_20 string 216 log_archive_dest_21 string 217 log_archive_dest_22 string 218 log_archive_dest_23 string 219 log_archive_dest_24 string 220 log_archive_dest_25 string 221 log_archive_dest_26 string 222 log_archive_dest_27 string 223 log_archive_dest_28 string 224 log_archive_dest_29 string 225 log_archive_dest_3 string location=/home/oracle/oracle_s 226 227 NAME TYPE VALUE 228 ------------------------------------ ----------- ------------------------------ 229 ystem_files_back/archivelog/ar 230 chivelog_20180305/archive3_1 231 mandatory 232 log_archive_dest_30 string 233 log_archive_dest_31 string 234 log_archive_dest_4 string location=/home/oracle/oracle_s 235 ystem_files_back/archivelog/ar 236 chivelog_20180305/archive4 man 237 datory reopen=400 238 log_archive_dest_5 string location=/home/oracle/oracle_s 239 ystem_files_back/archivelog/ar 240 241 NAME TYPE VALUE 242 ------------------------------------ ----------- ------------------------------ 243 chivelog_20180305/archive5 244 log_archive_dest_6 string 245 log_archive_dest_7 string 246 log_archive_dest_8 string 247 log_archive_dest_9 string 248 log_archive_dest_state_1 string enable 249 log_archive_dest_state_10 string enable 250 log_archive_dest_state_11 string enable 251 log_archive_dest_state_12 string enable 252 log_archive_dest_state_13 string enable 253 log_archive_dest_state_14 string enable 254 255 NAME TYPE VALUE 256 ------------------------------------ ----------- ------------------------------ 257 log_archive_dest_state_15 string enable 258 log_archive_dest_state_16 string enable 259 log_archive_dest_state_17 string enable 260 log_archive_dest_state_18 string enable 261 log_archive_dest_state_19 string enable 262 log_archive_dest_state_2 string enable 263 log_archive_dest_state_20 string enable 264 log_archive_dest_state_21 string enable 265 log_archive_dest_state_22 string enable 266 log_archive_dest_state_23 string enable 267 log_archive_dest_state_24 string enable 268 269 NAME TYPE VALUE 270 ------------------------------------ ----------- ------------------------------ 271 log_archive_dest_state_25 string enable 272 log_archive_dest_state_26 string enable 273 log_archive_dest_state_27 string enable 274 log_archive_dest_state_28 string enable 275 log_archive_dest_state_29 string enable 276 log_archive_dest_state_3 string enable 277 log_archive_dest_state_30 string enable 278 log_archive_dest_state_31 string enable 279 log_archive_dest_state_4 string DEFER 280 log_archive_dest_state_5 string enable 281 log_archive_dest_state_6 string enable 282 283 NAME TYPE VALUE 284 ------------------------------------ ----------- ------------------------------ 285 log_archive_dest_state_7 string enable 286 log_archive_dest_state_8 string enable 287 log_archive_dest_state_9 string enable 288 log_archive_duplex_dest string 289 log_archive_format string %t_%s_%r.dbf 290 log_archive_local_first boolean TRUE 291 log_archive_max_processes integer 8 292 log_archive_min_succeed_dest integer 3 293 log_archive_start boolean FALSE 294 log_archive_trace integer 0 295 log_buffer integer 12386304 296 297 NAME TYPE VALUE 298 ------------------------------------ ----------- ------------------------------ 299 log_checkpoint_interval integer 0 300 log_checkpoint_timeout integer 1800 301 log_checkpoints_to_alert boolean FALSE 302 log_file_name_convert string 303 max_dispatchers integer 304 max_dump_file_size string unlimited 305 max_enabled_roles integer 150 306 max_shared_servers integer 307 memory_max_target big integer 1168M 308 memory_target big integer 1168M 309 nls_calendar string 310 311 NAME TYPE VALUE 312 ------------------------------------ ----------- ------------------------------ 313 nls_comp string BINARY 314 nls_currency string 315 nls_date_format string 316 nls_date_language string 317 nls_dual_currency string 318 nls_iso_currency string 319 nls_language string AMERICAN 320 nls_length_semantics string BYTE 321 nls_nchar_conv_excp string FALSE 322 nls_numeric_characters string 323 nls_sort string 324 325 NAME TYPE VALUE 326 ------------------------------------ ----------- ------------------------------ 327 nls_territory string AMERICA 328 nls_time_format string 329 nls_time_tz_format string 330 nls_timestamp_format string 331 nls_timestamp_tz_format string 332 object_cache_max_size_percent integer 10 333 object_cache_optimal_size integer 102400 334 olap_page_pool_size big integer 0 335 open_cursors integer 350 336 open_links integer 4 337 open_links_per_instance integer 4 338 339 NAME TYPE VALUE 340 ------------------------------------ ----------- ------------------------------ 341 optimizer_capture_sql_plan_baselines boolean FALSE 342 optimizer_dynamic_sampling integer 2 343 optimizer_features_enable string 11.2.0.3 344 optimizer_index_caching integer 0 345 optimizer_index_cost_adj integer 100 346 optimizer_mode string ALL_ROWS 347 optimizer_secure_view_merging boolean TRUE 348 optimizer_use_invisible_indexes boolean FALSE 349 optimizer_use_pending_statistics boolean FALSE 350 optimizer_use_sql_plan_baselines boolean TRUE 351 os_authent_prefix string ops$ 352 353 NAME TYPE VALUE 354 ------------------------------------ ----------- ------------------------------ 355 os_roles boolean FALSE 356 parallel_adaptive_multi_user boolean TRUE 357 parallel_automatic_tuning boolean FALSE 358 parallel_degree_limit string CPU 359 parallel_degree_policy string MANUAL 360 parallel_execution_message_size integer 16384 361 parallel_force_local boolean FALSE 362 parallel_instance_group string 363 parallel_io_cap_enabled boolean FALSE 364 parallel_max_servers integer 135 365 parallel_min_percent integer 0 366 367 NAME TYPE VALUE 368 ------------------------------------ ----------- ------------------------------ 369 parallel_min_servers integer 0 370 parallel_min_time_threshold string AUTO 371 parallel_server boolean FALSE 372 parallel_server_instances integer 1 373 parallel_servers_target integer 64 374 parallel_threads_per_cpu integer 2 375 permit_92_wrap_format boolean TRUE 376 pga_aggregate_target big integer 0 377 plscope_settings string IDENTIFIERS:NONE 378 plsql_ccflags string 379 plsql_code_type string INTERPRETED 380 381 NAME TYPE VALUE 382 ------------------------------------ ----------- ------------------------------ 383 plsql_debug boolean FALSE 384 plsql_optimize_level integer 2 385 plsql_v2_compatibility boolean FALSE 386 plsql_warnings string DISABLE:ALL 387 pre_page_sga boolean FALSE 388 processes integer 150 389 processor_group_name string 390 query_rewrite_enabled string TRUE 391 query_rewrite_integrity string enforced 392 rdbms_server_dn string 393 read_only_open_delayed boolean FALSE 394 395 NAME TYPE VALUE 396 ------------------------------------ ----------- ------------------------------ 397 recovery_parallelism integer 0 398 recyclebin string on 399 redo_transport_user string 400 remote_dependencies_mode string TIMESTAMP 401 remote_listener string 402 remote_login_passwordfile string EXCLUSIVE 403 remote_os_authent boolean FALSE 404 remote_os_roles boolean FALSE 405 replication_dependency_tracking boolean TRUE 406 resource_limit boolean TRUE 407 resource_manager_cpu_allocation integer 4 408 409 NAME TYPE VALUE 410 ------------------------------------ ----------- ------------------------------ 411 resource_manager_plan string 412 result_cache_max_result integer 5 413 result_cache_max_size big integer 3008K 414 result_cache_mode string MANUAL 415 result_cache_remote_expiration integer 0 416 resumable_timeout integer 0 417 rollback_segments string 418 sec_case_sensitive_logon boolean TRUE 419 sec_max_failed_login_attempts integer 10 420 sec_protocol_error_further_action string CONTINUE 421 sec_protocol_error_trace_action string TRACE 422 423 NAME TYPE VALUE 424 ------------------------------------ ----------- ------------------------------ 425 sec_return_server_release_banner boolean FALSE 426 serial_reuse string disable 427 service_names string orcl 428 session_cached_cursors integer 100 429 session_max_open_files integer 10 430 sessions integer 248 431 sga_max_size big integer 1168M 432 sga_target big integer 0 433 shadow_core_dump string partial 434 shared_memory_address integer 0 435 shared_pool_reserved_size big integer 22649241 436 437 NAME TYPE VALUE 438 ------------------------------------ ----------- ------------------------------ 439 shared_pool_size big integer 0 440 shared_server_sessions integer 441 shared_servers integer 1 442 skip_unusable_indexes boolean TRUE 443 smtp_out_server string 444 sort_area_retained_size integer 0 445 sort_area_size integer 65536 446 spfile string /u01/app/oracle/product/11.2.0 447 /db_1/dbs/spfileorcl.ora 448 sql92_security boolean FALSE 449 sql_trace boolean FALSE 450 451 NAME TYPE VALUE 452 ------------------------------------ ----------- ------------------------------ 453 sqltune_category string DEFAULT 454 standby_archive_dest string ?/dbs/arch 455 standby_file_management string MANUAL 456 star_transformation_enabled string FALSE 457 statistics_level string TYPICAL 458 streams_pool_size big integer 0 459 tape_asynch_io boolean TRUE 460 thread integer 0 461 timed_os_statistics integer 0 462 timed_statistics boolean TRUE 463 trace_enabled boolean TRUE 464 465 NAME TYPE VALUE 466 ------------------------------------ ----------- ------------------------------ 467 tracefile_identifier string 468 transactions integer 272 469 transactions_per_rollback_segment integer 5 470 undo_management string AUTO 471 undo_retention integer 900 472 undo_tablespace string UNDOTBS1 473 use_indirect_data_buffers boolean FALSE 474 use_large_pages string TRUE 475 user_dump_dest string /u01/app/oracle/diag/rdbms/orc 476 l/orcl/trace 477 utl_file_dir string 478 479 NAME TYPE VALUE 480 ------------------------------------ ----------- ------------------------------ 481 workarea_size_policy string AUTO 482 xml_db_events string enable 483 488 489 490 SYS@orcl> select count(*) from v$parameter; 491 492 COUNT(*) 493 ---------- 494 347 495 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, Data 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 File 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, Data 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, Data 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.ora 100 #查看文件信息 可以看出，此时数据库实例orcl的spfile文件 spfileorcl.ora 已经不存在了 101 [oracle@localhost dbs]$ ls 102 dbsorapwPROD1 hc_orcl.dat hc_PROD1.dat init.ora initorcl.ora initPROD1.ora lkORCL lkPROD1 orapworcl spfilePROD1.ora 103 #登录 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 2018 107 108 Copyright (c) 1982, 2011, Oracle. All rights reserved. 109 110 Connected to an idle instance. 111 #启动数据库 112 SYS@orcl> startup 113 114 ORACLE instance started. 115 116 Total System Global Area 1221992448 bytes 117 Fixed Size 1344596 bytes 118 Variable Size 939527084 bytes 119 Database Buffers 268435456 bytes 120 Redo Buffers 12685312 bytes 121 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 VALUE 127 ------------------------------------ ----------- ------------------------------ 128 spfile string 129 #创建数据库实例orcl的spfile文件。 130 SYS@orcl> create spfile from pfile; 131 132 File 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 - Production 142 With the Partitioning, OLAP, Data Mining and Real Application Testing options 143 #查看文件目录信息 此时可以观察得知，此时已经多了一个 spfileorcl.ora 文件 144 [oracle@localhost dbs]$ ls 145 dbsorapwPROD1 hc_orcl.dat hc_PROD1.dat init.ora initorcl.ora initPROD1.ora lkORCL lkPROD1 orapworcl spfileorcl.ora spfilePROD1.ora 146 # 进入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 2018 150 151 Copyright (c) 1982, 2011, Oracle. All rights reserved. 152 153 Connected to an idle instance. 154 #启动数据库 155 SYS@orcl> startup 156 ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance 157 ORACLE instance started. 158 159 Total System Global Area 1221992448 bytes 160 Fixed Size 1344596 bytes 161 Variable Size 939527084 bytes 162 Database Buffers 268435456 bytes 163 Redo Buffers 12685312 bytes 164 Database mounted. 165 Database opened. 166 #查看spfile系统参数信息：此时 value有值。进一步表明了：数据库实例orcl在同时存在 pfile 和 spfile 文件情况下，数据库实例默认是以spfile方式启动。 167 SYS@orcl> show parameter spfile; 168 169 NAME TYPE VALUE 170 ------------------------------------ ----------- ------------------------------ 171 spfile string /u01/app/oracle/product/11.2.0 172 /db_1/dbs/spfileorcl.ora 173 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, Data 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, Data 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 '<ORACLE_BASE>' 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='<ORACLE_BASE>/admin/orcl/adump' 92 audit_trail ='db' 93 db_block_size=8192 94 db_domain='' 95 db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area' 96 db_recovery_file_dest_size=2G 97 diagnostic_dest='<ORACLE_BASE>' 98 dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)' 99 open_cursors=300 100 remote_login_passwordfile='EXCLUSIVE' 101 undo_tablespace='UNDOTBS1' 102 # You may want to ensure that control files are created on separate physical 103 # devices 104 control_files = (ora_control1, ora_control2) 105 compatible ='11.2.0' 106 [oracle@localhost dbs]$ cat init.ora | 107 > grep 108 Usage: grep [OPTION]... PATTERN [FILE]... 109 Try `grep --help' for more information. 110 [oracle@localhost dbs]$ cat init.ora | grep -v ^# > initorcl.ora 111 [oracle@localhost dbs]$ ls 112 dbsorapwPROD1 hc_orcl.dat hc_PROD1.dat init.ora initorcl.ora initPROD1.ora lkORCL lkPROD1 orapworcl spfilePROD1.ora 113 [oracle@localhost dbs]$ cat initorcl.ora 114 115 116 db_name='ORCL' 117 memory_target=1G 118 processes = 150 119 audit_file_dest='<ORACLE_BASE>/admin/orcl/adump' 120 audit_trail ='db' 121 db_block_size=8192 122 db_domain='' 123 db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area' 124 db_recovery_file_dest_size=2G 125 diagnostic_dest='<ORACLE_BASE>' 126 dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)' 127 open_cursors=300 128 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.ora 133 134 135 136 db_name='orcl' 137 memory_target=500m 138 processes = 150 139 audit_file_dest='/u01/app/oracle/admin/orcl/adump' 140 audit_trail ='db' 141 db_block_size=8192 142 db_domain='' 143 db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' 144 db_recovery_file_dest_size=2G 145 diagnostic_dest='/u01/app/oracle' 146 dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)' 147 open_cursors=300 148 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 written 164 [oracle@localhost dbs]$ sqlplus / as sysdba; 165 166 SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 23 23:06:23 2018 167 168 Copyright (c) 1982, 2011, Oracle. All rights reserved. 169 170 Connected to an idle instance. 171 172 SYS@orcl> startup 173 ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated 174 ORA-01262: Stat failed on a file destination directory 175 Linux Error: 2: No such file or directory 176 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 translated 178 ORA-01262: Stat failed on a file destination directory 179 Linux Error: 2: No such file or directory 180 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 translated 182 ORA-01262: Stat failed on a file destination directory 183 Linux Error: 2: No such file or directory 184 SYS@orcl> quit 185 Disconnected 186 [oracle@localhost dbs]$ cat initorcl.ora 187 188 189 db_name='orcl' 190 memory_target=500m 191 processes = 150 192 audit_file_dest='/u01/app/oracle/admin/orcl/adump' 193 audit_trail ='db' 194 db_block_size=8192 195 db_domain='' 196 db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' 197 db_recovery_file_dest_size=2G 198 diagnostic_dest='/u01/app/oracle' 199 dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)' 200 open_cursors=300 201 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]$ ls 209 dbsorapwPROD1 hc_orcl.dat hc_PROD1.dat init.ora initorcl.ora initPROD1.ora lkORCL lkPROD1 orapworcl spfilePROD1.ora 210 [oracle@localhost dbs]$ vi initorcl.ora 211 212 213 214 db_name='orcl' 215 memory_target=500m 216 processes = 150 217 audit_file_dest='/u01/app/oracle/admin/orcl/adump' 218 audit_trail ='db' 219 db_block_size=8192 220 db_domain='' 221 db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' 222 db_recovery_file_dest_size=2G 223 diagnostic_dest='/u01/app/oracle' 224 dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)' 225 open_cursors=300 226 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 written 242 [oracle@localhost dbs]$ cat initorcl.ora 243 244 245 db_name='orcl' 246 memory_target=500m 247 processes = 150 248 audit_file_dest='/u01/app/oracle/admin/orcl/adump' 249 audit_trail ='db' 250 db_block_size=8192 251 db_domain='' 252 db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' 253 db_recovery_file_dest_size=2G 254 diagnostic_dest='/u01/app/oracle' 255 dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)' 256 open_cursors=300 257 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]$ ls 263 orcl ORCL 264 [oracle@localhost fast_recovery_area]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/ 265 [oracle@localhost dbs]$ ls 266 dbsorapwPROD1 hc_orcl.dat hc_PROD1.dat init.ora initorcl.ora initPROD1.ora lkORCL lkPROD1 orapworcl spfilePROD1.ora 267 [oracle@localhost dbs]$ sqlplus / as sysdba; 268 269 SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 23 23:13:17 2018 270 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 bytes 279 Fixed Size 1346052 bytes 280 Variable Size 314574332 bytes 281 Database Buffers 201326592 bytes 282 Redo Buffers 5861376 bytes 283 Database mounted. 284 Database opened. 285 SYS@orcl> create spfile from pfile; 286 287 File created. 288 289 SYS@orcl> shutdown immediate; 290 Database closed. 291 Database dismounted. 292 ORACLE instance shut down. 293 SYS@orcl> quit 294 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production 295 With the Partitioning, OLAP, Data Mining and Real Application Testing options 296 [oracle@localhost dbs]$ ls 297 dbsorapwPROD1 hc_orcl.dat hc_PROD1.dat init.ora initorcl.ora initPROD1.ora lkORCL lkPROD1 orapworcl spfileorcl.ora spfilePROD1.ora 298 [oracle@localhost dbs]$ sqlplus / as sysdba; 299 300 SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 23 23:15:12 2018 301 302 Copyright (c) 1982, 2011, Oracle. All rights reserved. 303 304 Connected to an idle instance. 305 306 SYS@orcl> ls 307 SP2-0042: unknown command "ls" - rest of line ignored. 308 SYS@orcl> startup 309 ORACLE instance started. 310 311 Total System Global Area 523108352 bytes 312 Fixed Size 1346052 bytes 313 Variable Size 314574332 bytes 314 Database Buffers 201326592 bytes 315 Redo Buffers 5861376 bytes 316 Database mounted. 317 Database opened. 318 SYS@orcl>
如果出现了 ora-00845 的错误：请查看 《 处理数据库 Ora-00845: memory_traget not supported on this system 的错误 》；
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 标题的操作内容步骤