/**PageBeginHtml Block Begin **/ /***自定义返回顶部小火箭***/ /*生成博客目录的JS 开始*/ /*生成博客目录的JS 结束*/

参数文件



参数文件

 

            官方文档的位置


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参数没有路径值的时候，表示使用pfile

  1 [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   
  1.               true:表示可以使用alter session命令进行修改
  2.               false:表示不能使用alter session命令进行修改
  •    issys_modifiable        
  1.          false:表示不能使用alter system命令进行修改        
  2.          deferred:表示可以使用alter system命令进行修改，但要加上deferred选项      
  3.          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;

image



     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>

 image   





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 标题的操作内容步骤





————————————————————————————————————————————————————————————————————————————————————————————————


————————————————————————————————————————————————————————————————————————————————————————————————

posted @ 2018-04-19 21:45  一品堂.技术学习笔记  阅读(1247)  评论(0)  编辑  收藏  举报