Shulou(Shulou.com)06/01 Report--

SPFILE is stored on the server side, and it cannot be modified by itself (thus overcoming some security risks of PFILE). If you use a text editor to view SPFILE, you will find that it is a binary file, which needs to be managed by Oracle Server using the SQL command, and the values modified by the SQL command will be saved permanently. Oracle's backup and recovery tool RMAN can back up SPFILE.

[oracle@localhost ~] $sqlplus/nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 2710 40 Production on Wed Aug 15 2014 Copyright (c) 1982, 2009, Oracle. All rights reserved.

SQL > conn / as sysdba

Connected to an idle instance.

SQL > create spfile frompfile

File created.

[oracle@localhost dbs] $ll

Total 3 RW, RW, RMI, Rafe, Rafe, Rafe. 1 oracle oinstall 2851 Aug 27 10:41 initorcl.oradrwxr-xr-x. 3 oracle oinstall 4096 Aug 27 10:20 old-rw-r-. 1 oracle oinstall 1536 Aug 27 10:42spfileorcl.ora

SQL > startup

ORACLE instance started. Total System Global Area 422670336 bytesFixed Size 1336960 bytesVariable Size 318769536 bytesDatabase Buffers 96468992 bytesRedo Buffers 6094848 bytesDatabase mounted.Database opened.

= View SPFILE=

Because SPFILE is a binary file, you cannot view it using a text editor, so use the strings command on the command line of Linux to view the file:

[oracle@localhost dbs] $strings spfileorcl.ora | more

Orcl.__db_cache_size=96468992orcl.__java_pool_size=4194304orcl.__large_pool_size=4194304orcl.__oracle_base='/oracle'#ORACLE_BASE set fromenvironmentorcl.__pga_aggregate_target=146800640orcl.__sga_target=276824064orcl.__shared_io_pool_size=0orcl.__shared_pool_size=159383552orcl.__streams_pool_size=4194304*.audit_file_dest='/oracle/admin/orcl/adump'*.audit_trail='db'*.compatible='11.2.0.0.0' * .control_files='/oracle/oradata/orcl/control01.ctl' '/ oracle/flash_recovery_area/orcl/control02.ctl'*.db_block_size=8192*.db_domain=''*.db_name='orcl'*.db_recovery_file_dest='/oracle/flash_recovery_area'*.db_recovery_file_dest_size=4039114752*.diagnostic_dest='/oracle'*.dispatchers=' (PROTOCOL=TCP) (SERVICE=orclXDB)' * .memory _ target=421527552*.open_cursors=300*.processes=150*.remote_login_passwordfile='EXCLUSIVE'*.undo_tablespace='UNDOTBS1'

SPFILE cannot be modified manually using a text editor, but must be modified using the SQL command in the following format:

ALTER system set parameter=value

Indicates to annotate a parameter for later viewing

Indicates that the instance will not take effect until the next time the instance is launched.

After manual modification of PFILE, it usually cannot take effect immediately. It will not take effect until the next time you start Instance. And SPFILE is a built-in binary file in Oracle, by specifying, you can make it clear that the modified parameter values are acting in memory to make it take effect immediately, or save them to the SPFILE file on disk, or modify both at the same time, by default, both are modified at the same time, both in memory and saved to the disk file.

Usually an instance (Instance) corresponds to a database, but in a RAC (large-scale clustered database) environment, multiple instances usually correspond to a database, so you need to specify a value to distinguish the specific instance.

If you use the strings command to view SPFILE, you will find that many files are preceded by a *, which indicates that this parameter is applicable to all Instance; without a * sign, so you need to specify SID. For example, in orcl.__db_cache_size=96468992, orcl is SID.

/ * = modify SPFILE instance demo = * /

[oracle@localhost dbs] $strings spfileorcl.ora | less

Orcl.__db_cache_size=96468992orcl.__java_pool_size=4194304... * .dispatchers=' (PROTOCOL=TCP) (SERVICE=orclXDB)'* .memory _ target=421527552

/ * now change the value of SPFILE =

SQL > show parameter fast_

NAME TYPE VALUE---fast_start_io_target integer 0fast_start _ mttr_target integer 0fast_start_parallel_rollback string LOW

SQL > alter system setfast_start_mttr_target=250

If no scope is added, the value will be saved to memory and disk file System altered.

SQL > show parameter fast_

NAME TYPE VALUE-- fast_start_io_target integer 0fast_ Start_mttr_target integer 250fast_start_parallel_rollback string LOW

[oracle@localhost dbs] $strings spfileorcl.ora | more

Orcl.__db_cache_size=96468992orcl.__java_pool_size=4194304... * .fast_start_mttr_target=250*.memory_target=421527552

/ * = again, if you add scope, = * /

SQL > alter system setfast_start_mttr_target=260 scope=memory

System altered.

SQL > show parameter fast_

NAME TYPE VALUE-- fast_start_io_target integer 0fast _ start_mttr_target integer 260 fast_start_parallel_rollback string LOW-in memory has been modified to 260

[oracle@localhost dbs] $strings spfileorcl.ora | more

The value on orcl.__db_cache_size=96468992orcl.__java_pool_size=4194304*.dispatchers=' (PROTOCOL=TCP) (SERVICE=orclXDB)'* .fast _ start_mttr_target=250# disk is still 250*.memory_target=421527552

/ * = add comments when modifying SPFILE = * /

SQL > alter system set fast_start_mttr_target=260comment='hello Mickeyboxes'

System altered.

SQL > desc v$parameter

Name Null? Type-NUM NUMBER NAME VARCHAR2 (80) TYPE NUMBER VALUE VARCHAR2 (4000) DISPLAY_VALUE VARCHAR2 (4000) ISDEFAULT VARCHAR2 (9) ISSES_MODIFIABLE VARCHAR2 (5) ISSYS_MODIFIABLE VARCHAR2 (9) ISINSTANCE_MODIFIABLE VARCHAR2 (5) ISMODIFIED VARCHAR2 (10) ISADJUSTED VARCHAR2 (5) ISDEPRECATED VARCHAR2 (5) ISBASIC VARCHAR2 (5) DESCRIPTION VARCHAR2 (255) UPDATE_COMMENT VARCHAR2 (255) HASH NUMBER

SQL > select name, value,update_comment

2 from v$parameter 3 where name = 'fast_start_mttr_target' NAME VALUE UPDATE_COMMENT-- fast_start_mttr_target 260 hello Mickey!

[oracle@localhost dbs] $strings spfileorcl.ora | more

Orcl.__db_cache_size=96468992orcl.__java_pool_size=4194304*.dispatchers=' (PROTOCOL=TCP) (SERVICE=orclXDB)'* .fast _ start_mttr_target=260#hello Mickey notes # has been annotated * .memory _ target=421527552

Some parameters must be delayed, so you need to add the deferred option when you modify them. If marked as deferred in the ISINSTANCE_MODIFIABLE item in the parameter table v$parameter, you need to delay the effect.

SQL > col name format A20

SQL > col issys_modifiableformat A20

SQL > select name,issys_modifiable

2 from v$parameter 3 where issys_modifiable = 'DEFERRED' NAME ISSYS_MODIFIABLE---backup_tape_io_slaves DEFERREDrecyclebin DEFERREDaudit_file_dest DEFERREDobject_cache_optimal_size DEFERREDobject_cache_max_size_percent DEFERREDsort_area_size DEFERREDsort_area_retained_size DEFERREDolap_page_pool_size DEFERRED 8 rows selected.

For more details on the use of ALERT, you can check out the relevant chapters on ALTER SYSTEM in the online documentation reference.

If you do not want the modified value, you can use the reset command to revert to the default value

SQL > show parameter undo

NAME TYPE VALUE---undo_management string AUTOundo_retention Integer 900undo_tablespace string UNDOTBS1

SQL > alter system setundo_retention=800

System altered.

SQL > show parameter undo

NAME TYPE VALUE---undo_management string AUTOundo_retention Integer 800undo_tablespace string UNDOTBS1

SQL > alter system resetundo_retention sid='*'

System altered.

For more details about reset, you can consult the online documentation reference.

You can use spfile to create a pfile:

SQL > create pfile fromspfile

File created.

[oracle@localhost dbs] $ll

Total 36RW / RW / RMI. 1 oracle oinstall 1544 Aug 27 10:47 hc_orcl.dat-rw-r--r--. 1 oracle oinstall 2851 Aug 27 13:36 init.ora-rw-r--r--. 1 oracle oinstall 904 Aug 27 13:34 pfile file created by initorcl.ora# based on spfile-rw-r-. 1 oracle oinstall 24 Aug 27 10:45 lkORCLdrwxr-xr-x. 3 oracle oinstall 4096 Aug 27 10:20 old-rw-r-. 1 oracle oinstall 1536 Aug 27 10:45 orapworcldrwx-. 2 oracle oinstall 4096 Aug 27 10:47 peshm_orcl_0drwx-. 2 oracle oinstall 4096 Aug 27 09:31 peshm_ORCL_0-rw-r-. 1 oracle oinstall 2560 Aug 27 13:27spfileorcl.ora

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.