When
an Oracle Instance is started, the characteristics of the Instance are
established by parameters specified within the initialization parameter
file. These initialization parameters are either stored in a PFILE or
SPFILE. SPFILEs are available in Oracle 9i and above. All prior
releases of Oracle are using PFILEs.
SPFILEs provide the following advantages over PFILEs:
- An SPFILE can be backed-up with RMAN (RMAN cannot backup PFILEs)
- Reduce human errors. The SPFILE is maintained by the server. Parameters are checked before changes are accepted.
- Eliminate configuration problems (no need to have a local PFILE if you want to start Oracle from a remote machine)
- Easy to find - stored in a central location
What is the difference between a PFILE and SPFILE:
A PFILE is a static, client-side text file that must be updated with
a standard text editor like "notepad" or "vi". This file normally
reside on the server, however, you need a local copy if you want to
start Oracle from a remote machine. DBA's commonly refer to this file
as the INIT.ORA file.
An SPFILE (Server Parameter File), on the other hand, is a
persistent server-side binary file that can only be modified with the
"ALTER SYSTEM SET" command. This means you no longer need a local copy
of the pfile to start the database from a remote machine. Editing an
SPFILE will corrupt it, and you will not be able to start your database
anymore.
How will I know if my database is using a PFILE or SPFILE:
Execute the following query to see if your database was started with a PFILE or SPFILE:
SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile';
You can also use the V$SPPARAMETER view to check if you are using a
PFILE or not: if the "value" column is NULL for all parameters, you are
using a PFILE.
Viewing Parameters Settings:
One can view parameter values using one of the following methods (regardless if they were set via PFILE or SPFILE):
- The "SHOW PARAMETERS" command from SQL*Plus (i.e.: SHOW PARAMETERS timed_statistics)
- V$PARAMETER view - display the currently in effect parameter values
- V$PARAMETER2 view - display the currently in effect parameter values, but "List Values" are shown in multiple rows
- V$SPPARAMETER view - display the current contents of the server parameter file.
Starting a database with a PFILE or SPFILE:
Oracle searches for a suitable initialization parameter file in the following order:
- Try to use the spfile${ORACLE_SID}.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)
- Try to use the spfile.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)
- Try to use the init${ORACLE_SID}.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)
One can override the default location by specifying the PFILE parameter at database startup:
SQL> STARTUP PFILE='/oradata/spfileORCL.ora'
Note that there is not an equivalent "STARTUP SPFILE=" command. One
can only use the above option with SPFILE's if the PFILE you point to
(in the example above), contains a single 'SPFILE=' parameter pointing
to the SPFILE that should be used. Example:
SPFILE=/path/to/spfile
Changing SPFILE parameter values:
While a PFILE can be edited with any text editor, the SPFILE is a
binary file. The "ALTER SYSTEM SET" and "ALTER SYSTEM RESET" commands
can be used to change parameter values in an SPFILE. Look at these
examples:
SQL> ALTER SYSTEM SET open_cursors=300 SCOPE=SPFILE;
SQL> ALTER SYSTEM SET timed_statistics=TRUE
COMMENT='Changed by Frank on 1 June 2003'
SCOPE=BOTH
SID='*';
The SCOPE parameter can be set to SPFILE, MEMORY or BOTH:
- MEMORY: Set for the current instance only. This is the default behaviour if a PFILE was used at STARTUP.
- SPFILE: update the SPFILE, the parameter will take effect with next database startup
- BOTH: affect the current instance and persist to the SPFILE. This is the default behaviour if an SPFILE was used at STARTUP.
The COMMENT parameter (optional) specifies a user remark.
The SID parameter (optional; only used with RAC) indicates the instance
for which the parameter applies (Default is *: all Instances).
Use the following syntax to set parameters that take multiple (a list of) values:
SQL> ALTER SYSTEM SET utl_file_dir='/tmp/','/oradata','/home/' SCOPE=SPFILE;
Use this syntax to set unsupported initialization parameters (obviously only when Oracle Support instructs you to set it):
SQL> ALTER SYSTEM SET "_allow_read_only_corruption"=TRUE SCOPE=SPFILE;
Execute one of the following command to remove a parameter from the SPFILE:
SQL> ALTER SYSTEM RESET timed_statistics SCOPE=SPFILE SID=‘*’;
SQL> ALTER SYSTEM SET timed_statistics = '' SCOPE=SPFILE;
Converting between PFILES and SPFILES:
One can easily migrate from a PFILE to SPFILE or vice versa. Execute
the following commands from a user with SYSDBA or SYSOPER privileges:
SQL> CREATE PFILE FROM SPFILE;
SQL> CREATE SPFILE FROM PFILE;
One can also specify a non-default location for either (or both) the PFILE and SPFILE parameters. Look at this example:
SQL> CREATE SPFILE='/oradata/spfileORCL.ora' from PFILE='/oradata/initORCL.ora';
Here is an alternative procedure for changing SPFILE parameter values using the above method:
- Export the SPFILE with: CREATE PFILE=‘pfilename’ FROM SPFILE = ‘spfilename’;
- Edit the resulting PFILE with a text editor
- Shutdown and startup the database with the PFILE option: STARTUP PFILE=filename
- Recreate the SPFILE with: CREATE SPFILE=‘spfilename’ FROM PFILE=‘pfilename’;
- On the next startup, use STARTUP without the PFILE parameter and the new SPFILE will be used.
Parameter File Backups:
RMAN (Oracle's Recovery Manager) will backup the SPFILE with the
database control file if setting "CONFIGURE CONTROLFILE AUTOBACKUP" is
ON (the default is OFF). PFILEs cannot be backed-up with RMAN. Look at
this example:
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
Use the following RMAN command to restore an SPFILE:
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
References:
- Oracle9i Database Administrator's Guide Release 2 (9.2)
Chapter 2: Creating an Oracle Database
- Oracle9i Recovery Manager User's Guide Release 2 (9.2)
Chapter 5: "RMAN Concepts I: Channels, Backups, and Copies"
- Oracle9i SQL Reference Release 2 (9.2)
相关推荐
Net: Board Net Initialization Failed No ethernet found.解决方案,如实际开发中有遇到,仅供参考 1. 网卡没有插好或者网卡损坏。 2. 网卡的驱动程序没有正确加载。 3. 网线没有接好或者网线损坏。 4. 网络设备...
// TODO: Add extra initialization here CDialog::OnInitDialog(); CFile PEfile,PEfile2; WORD NumofSection; DWORD n,j,ImpRVA,ImpRaw,NameRaw; char cBuff[1024]; IMAGE_IMPORT_DESCRIPTOR ImpDescriptor...
信息: Initialization processed in 984 ms 2010-8-11 18:24:13 org.apache.catalina.core.StandardService start 信息: Starting service Catalina 2010-8-11 18:24:13 org.apache.catalina.core.StandardEngine ...
Set the wrapping functions as the delegate for handling scheduler initialization to RxJava: RxJava 2.x: RxJavaPlugins.setInitComputationSchedulerHandler( Rx2Idler.create("RxJava 2.x Computation ...
Pointers vs. Multi-dimensional Arrays Command-line Arguments Pointers to Functions Complicated Declarations Chapter 6: Structures Basics of Structures Structures and Functions Arrays of ...
Junit出现InitializationError,原因是少了两个jar包,下载下来导入即可
信息: The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: C:\Program Files\Java\jdk1.6.0_10\bin;C:\Program ...
资源名称:mysql启动失败的解决方法资源截图: 资源太大,传百度网盘了,链接在附件中,有需要的同学自取。
2017-11-15 19:23:46 1404 [Note] InnoDB: Completed initialization of buffer pool 2017-11-15 19:23:46 1404 [Note] InnoDB: Highest supported file format is Barracuda. 2017-11-15 19:23:46 1404 [Note] ...
14443-3(Initialization+and+anticollision).pdf。
1: Initialization FileLogUtils.init (Context); 2: Use FileLogUtils.write (your Log information); Log File Path: If the SD card or external memory exists, the SD Android data package name files Log ...
nopCommerce_4.4功能实现详解
Chapter 11:Files ..................................................................................................... 163 Chapter 12: Serialization ...................................................
2.4.1 Initialization........ 39 2.5 The Traditional Conversions...... . 40 2.6 Enumeration Types........ 43 2.6.1 typedef Declarations...... . 44 2.7 Expressions.......... . 44 2.7.1 Precedence and ...
2014-10-14 10:10:21.409 WARN 3435 --- [ main] o.s.b.a.jdbc.DataSourceInitializer : Could not send event to complete DataSource initialization (ApplicationEventMulticaster not initialized - call ' ...
play-services-ads-lite-18.3.0是2019-11-23为止,最新的安卓admob sdk,用于原生安卓介入 加入工程后: ...import com.google.android.gms.ads.initialization.OnInitializationCompleteListener; 即可
we build on the initialization method proposed by Martinelli [1] and extended by Kaiser et al. [2], modifying it to be more general and efficient. We improve accuracy with several rounds of visual-...
Puzzle 85: Lazy Initialization Chapter 10. Advanced Puzzlers Puzzle 86: Poison-Paren Litter Puzzle 87: Strained Relations Puzzle 88: Raw Deal Puzzle 89: Generic Drugs Puzzle 90: It's Absurd, It's a ...
Pointers to Pointers Multi-dimensional Arrays Initialization of Pointer Arrays Pointers vs. Multi-dimensional Arrays Command-line Arguments Pointers to Functions Complicated ...
randomapi-ruby 从访问数据的 API#安装捆绑器: gem install randomapi#Initialization 如果您只使用 1 个 API 密钥和 API id,则在初始化程序中传递它们是有意义的: RandomApi . configure do | config | config ....