SPFILE 、PFILE 的全面解读
?
這里先闡述一下數據庫的啟動過程：
1.??????啟動實例/例程（nomount狀態）時，讀取參數文件(文本文件PFILE 或服務器參數文件SPFILE)，分配SGA、啟動后臺進程、打開告警文件及后臺進程跟蹤文件；
2.??????裝載數據庫（mount 狀態）時，根據初始化參數control_file 的值定位并打開控制文件，從中得到數據文件及重做日志的信息。
3.??????打開數據庫（open狀態）時，打開所有數據文件和重做日志文件；至此，用戶可以正常訪問數據庫；
啟動數據庫語法：
startup?[nomount|mount|open|force]?[resrict]? [pfile=filename]
參數文件是實例啟動首先要用到的文件，所以此類文件相當關鍵，本文就結合官方文檔對參數文件進行一次全面的解讀；
Managing InitializationParameters Using a Server Parameter File
Initializationparameters for the Oracle Database have traditionally been stored in a textinitialization parameter file. For better manageability, you can choose tomaintain initialization parameters in a binary server parameter file that ispersistent across database startup and shutdown. This section introduces theserver parameter file, and explains how to manage initialization parametersusing either method of storing the parameters. The following topics arecontained in this section.
ORACLE DATABASE 初始化變量傳統上存放在一個“文本”初始化變量文件中；為了更好的管理，你也可以把初始化變量保存在一個二進制服務器變量文件中，此文件能夠永久保存這些變量；
·????????What Is a Server Parameter File?
·????????Migrating to a Server Parameter File
·????????Creating a Server Parameter File
·????????The SPFILE Initialization Parameter
·????????Changing Initialization Parameter Values
·????????Clearing Initialization Parameter Values
·????????Exporting the Server Parameter File
·????????Backing Up the Server Parameter File
·????????Recovering a Lost or Damaged Server Parameter File
·????????Viewing Parameter Settings
What Is a ServerParameter File?
A serverparameter file can be thought of as a repository for initialization parametersthat is maintained on the system running the Oracle Database server. It is, bydesign, a server-side initialization parameter file. Initialization parametersstored in a server parameter file are persistent, in that any changes made to the parameters whilean instance is running can persistacrossinstanceshutdown and startup.This arrangementeliminates the need to manually update initialization parameters to makepersistent any changes effected by?ALTERSYSTEM?statements. It alsoprovides a basis for self-tuning by the Oracle Database server.
A serverparameter file is initially built from a text initialization parameter fileusing the?CREATESPFILE?statement.(It can also be created directly by the Database Configuration Assistant.) Theserver parameter file is a binary file that cannot be edited using a texteditor.Oracle Database provides other interfaces for viewing and modifyingparameter settings in a server parameter file.
Caution:
Although youcan open the binary server parameter file with a text editor and view its text,?do not?manually edit it. Doing so willcorrupt the file. You will not be able to start your instance, and if theinstance is running, it could fail.
When youissue a?STARTUP?command with no?PFILE?clause, the Oracle instance searches an operatingsystem–specificdefault location for a server parameter filefrom which to read initialization parameter settings. If no serverparameter file is found, the instance searches for a text initializationparameter file. If a server parameter file exists but you want to override it withsettings in a text initialization parameter file, you must specify the?PFILE?clause when issuing theSTARTUP?command. Instructions for starting an instance using a serverparameter file are contained in?"Starting Up a Database".
服務器變量文件用createspfile語句從一個文本型的初始化變量文件中創建（也可以用數據庫配置助手直接創建），該文件是二進制類型，不能用文本編輯器修改；但是oracledatabase提供了其他查看和修改spfile中參數設置的接口；
注意：盡管可以用文本編輯器打開并瀏覽這個二進制服務器文件，不要去編輯，否則會破壞掉該文件，如果數據庫處于關閉狀態，那么將不能啟動，如果處在打開狀態，那么將不能運行；
執行數據庫啟動命令startup時，如果沒有指定 PFILE子句，首先實例會搜索相應的操作系統指定的服務器變量文件的默認位置（因為不同的操作系統默認位置可能不一樣）并從中讀取初始化參數設置。如果找不到，再搜索文本類型的初始化參數文件。假設服務器參數文件存在，但是你想用文本型的初始化參數文件來覆蓋它，那么在執行startup命令時必須指定PFILE子句；
Migrating to a Server Parameter File
If you arecurrently using a text initialization parameter file, then use the followingsteps to migrate to a server parameter file.
以下步驟用來由文本初始化參數文件遷移成為一個服務器參數
1.???If the initialization parameter file is located on a clientsystem, then transfer the file (for example, FTP) from the client system to theserver system.
Note:
If you are migrating toa server parameter file in an Oracle Real Application Clusters environment, youmust combine all of your instance-specific initialization parameter files intoa single initialization parameter file. Instructions for doing this and otheractions unique to using a server parameter file for instances that are part ofan Oracle Real Application Clusters installation are discussed in?Oracle Real Application Clusters Administration and Deployment Guide?and in your platform-specific OracleReal Application Clusters Installation Guide.
2.???Create a server parameter file in the default location using the?CREATE SPFILEFROM PFILE?statement.See?"Creating a Server Parameter File"?for instructions.
Thisstatement reads the text initialization parameter file to create a serverparameter file. The database does not have to be started to issue a?CREATE SPFILE?statement.
3.???Start up or restart the instance.
The instancefinds the new SPFILE in the default location and starts up with it.
Creating a ServerParameter File
You use the?CREATESPFILE?statement tocreate a server parameter file. You must have the?SYSDBA?or the?SYSOPER?system privilege to execute this statement.
可以使用 create? spfile?from? pfile語句創建服務器參數文件，這時你需要擁有SYSDBA或SYSOPER系統權限；
Note:
When you usethe Database Configuration Assistant to create a database, it automaticallycreates a server parameter file for you.
創建數據庫時如果用了DBCA，那么會自動創建一個spfile文件；
The?CREATE SPFILE?statement can be executed before or after instance startup.However, if the instance has been started using a server parameter file, anerror is raised if you attempt to re-create the same server parameter file thatis currently being used by the instance.
create spfile 語句可以在實例啟動前或啟動后創建；然而，如果實例已經以spfile啟動，如果你試圖重建當前正在使用的spfile文件，那么會出現錯誤(ORA-32002錯誤)；
例子：
SQL> show parameterspfile
NAME???????????????????????????????? TYPE??????? VALUE
----------------------------------------------- -------------? -----------------
spfile?????????????????????????????? string????? E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SPFILEORCL.ORA
SQL> create spfilefrom pfile;
create spfile frompfile
*
第 1 行出現錯誤:
ORA-32002: 無法創建已由實例使用的 SPFILE
官方對此的解釋及解決方案：
ORA-32002: cannot create SPFILE alreadybeing used by the instance
Cause:?A create spfile command is tryingto write to an SPFILE that was used to startup the instance.
Action:?Specify a different SPFILE name
?
You cancreate a server parameter file (SPFILE) from an existing text initialization parameterfile or from memory. Creating the SPFILE from memory means copying the currentvalues of initialization parameters in the running instance to the SPFILE.
你可以從已有的文本初始化參數文件或內存來創建一個spfile文件，從內存創建spfile意味著拷貝當前運行實例的初始參數的當前值到spfile中；
Thefollowing example creates a server parameter file from text initializationparameter file?/u01/oracle/dbs/init.ora. In thisexample no?SPFILE?name is specified, so the file is created with theplatform-specific default name and location shown in?Table 2-3.
使用默認的spfile 命名規則及默認路徑來創建spfile；
The nextexample illustrates creating a server parameter file and supplying a name andlocation.
使用自定義的spfile 命名方法及自定義的路徑創建spfile;
The nextexample illustrates creating a server parameter file in the default locationfrom the current values of the initialization parameters in memory.
在默認路徑下以默認的命名方式從內存創建spfile;
Whether youuse the default SPFILE name and default location or specify an SPFILE name andlocation, if an SPFILE of the same name already exists in the location, it isoverwritten without a warning message.
無論是默認位置還是你指定的位置，如果相同名字的spfile存在，在創建spfile的時候，會無告警情況下進行覆蓋；
When youcreate an SPFILE from a text initialization parameter file, comments specifiedon the same lines as a parameter setting in the initialization parameter fileare maintained in the SPFILE. All other comments are ignored.
Oraclerecommends that you allow the database to give the SPFILE the default name andstore it in the default location. This eases administration of your database.For example, the?STARTUP?command assumes this default location to read the SPFILE.
oracle 建議在默認的位置用默認的命名規則創建spfile，這將有利于管理數據庫，例如：在執行startup命令時會到默認的位置讀取spfile文件；
Table 2-3?showsthe default name and location for both the text initialization parameter file(PFILE) and server parameter file (SPFILE) for the UNIX, Linux, and Windowsplatforms, both with and without the presence of Oracle Automatic StorageManagement (Oracle ASM). The table assumes that the SPFILE is a file. If it isa raw device, the default name could be a logical volume name or partitiondevice name, and the default location could differ.
?
Table 2-3 PFILE and SPFILE Default Names and Locations on UNIX,LInux, and Windows
| Platform | PFILE Default Name | SPFILE Default Name | PFILE Default Location | SPFILE Default Location |
| UNIX and Linux | initORACLE_SID.ora | spfileORACLE_SID.ora | OH/dbs?or the same location as the datafilesFoot?1? | Without Oracle ASM: OH/dbs?or the same location as the datafilesFootref?1 When Oracle ASM is present: In the same disk group as the datafilesFoot?2? |
| Windows | initORACLE_SID.ora | spfileORACLE_SID.ora | OH\database | Without Oracle ASM: OH\database When Oracle ASM is present: In the same disk group as the datafilesFootref?2 |
?
Footnote?1?OH?representsthe Oracle home directory
Footnote?2?Assumesdatabase created with DBCA
Note:
Uponstartup, the instance first searches for an SPFILE named?spfileORACLE_SID.ora, and if notfound, searches forspfile.ora. Using?spfile.ora?enables all Real Application Cluster (Oracle RAC) instances touse the same server parameter file.
If neither SPFILE isfound, the instance searches for the text initialization parameter file?initORACLE_SID.ora.
If youcreate an SPFILE in a location other than the default location, you must createin the default PFILE location a "stub" PFILE that points to the serverparameter file. For more information, see?"Starting Up a Database" or? this :
啟動實例時，首先搜索以默認命名規則（類似spfileORACLE_SID.ora）的spfile，如果沒有，再找 spfile.ora 。用spfile.ora使所有的oracle RAC實例使用同一個服務器參數文件；如果spfile.ora也沒有找到，那么會搜索文本類型的參數文件initORACLE_SID.ora
如果你在非默認位置創建spfile ,那么你必須在默認pfile位置創建一個指向spfile的“stub”PFILE，看這：
?
{
NondefaultServer Parameter Files?A nondefault serverparameter file (SPFILE) is an?SPFILE?that is in a location other than the default location. It is notusually necessary to start an instance with a nondefault?SPFILE. However,should such a need arise, both SRVCTL (with Oracle Restart) and SQL*Plusprovide ways to do so. These are described later in this section.
Initialization Filesand Oracle Automatic Storage Management?A databasethat uses Oracle Automatic Storage Management (Oracle ASM) usually has anondefault?SPFILE. If you usethe Database Configuration Assistant (DBCA) to configure a database to useOracle ASM, DBCA creates an?SPFILE?for the database instance in an Oracle ASM disk group, and thencauses a text initialization parameter file (PFILE) to be created in thedefault location in the local file system to point to the?SPFILE, asexplained in the next section.
Starting Up withSQL*Plus with a Nondefault Server Parameter File
WithSQL*Plus you can use the?PFILE?clause to start an instance with a nondefault server parameterfile.
To start up with SQL*Plus with a nondefault server parameterfile:
1.???Create a one-line text initialization parameter file thatcontains only the?SPFILE?parameter. The value of the parameter is the nondefault serverparameter file location.
For example,create a text initialization parameter file?/u01/oracle/dbs/spf_init.ora?that contains only the following parameter:
SPFILE = /u01/oracle/dbs/test_spfile.oraNote:
You cannot use the?IFILE?initialization parameter within a text initialization parameterfile to point to a server parameter file. In this context, you must use the?SPFILE?initialization parameter.
2.???Start up the instance pointing to this initialization parameterfile.
3.? STARTUP PFILE = /u01/oracle/dbs/spf_init.oraThe?SPFILE?must reside on the database host computer. Therefore, thepreceding method also provides a means for a client system to start a databasethat uses an?SPFILE. It alsoeliminates the need for a client system to maintain a client-sideinitialization parameter file. When the client system reads the initializationparameter file containing the?SPFILE?parameter, it passes the value to the server where the specified?SPFILE?is read.
?
}
When youcreate the database with DBCA when Oracle ASM is present, DBCA places theSPFILE in an Oracle ASM disk group, and also causes this stub PFILE to becreated.
The SPFILE Initialization Parameter
The?SPFILE?initialization parameter contains the name of the current serverparameter file. When the default server parameter file is used by thedatabase—that is, you issue a?STARTUP?command and do not specify a?PFILE?parameter—the value of?SPFILE?is internally set by the server. TheSQL*Plus command?SHOWPARAMETERS SPFILE?(or anyother method of querying the value of a parameter) displays the name of theserver parameter file that is currently in use.
初始化參數spfile 包含了當前服務器參數文件的名稱，如果數據庫使用默認的服務器參數文件，那么在startup命令中沒有指定pfile參數，spfile 被服務器設置； sql* plus命令showparameter spfile會列出當前使用的服務器參數文件的名字及路徑；
Changing InitializationParameter Values
The?ALTERSYSTEM?statementenables you to set, change, or restore to default the values of initializationparameters. If you are using a text initialization parameter file, the?ALTER SYSTEM?statement changes the value of a parameter only for the currentinstance, because there is no mechanism for automatically updating textinitialization parameters on disk. You must update them manually to be passedto a future instance. Using a server parameter file overcomes this limitation.
alter? system?語句可以設置、修改、或恢復初始參數的默認值；如果正用文本型的初始參數文件，該命令僅僅修改當前實例的參數的值，因為沒有自動更新磁盤上初始參數的機制；你必須手動修改才可以；用服務器參數文件可以避免此限制；
There aretwo kinds of initialization parameters:
·????????Dynamic initializationparameters?can bechanged for the current Oracle Database instance. The changes take effectimmediately.
·????????Static initializationparameters?cannot bechanged for the current instance. You must change these parameters in the textinitialization file or server parameter file and then restart the databasebefore changes take effect.
有兩種初始化參數：
1.??? 動態初始化參數可以對當前實例修改，并立即生效；
2.???靜態初始化參數不能對當前實例修改，你必須在文本初始化文件中或服務器參數文件中修改后重啟數據庫才能生效；
Setting orChanging Initialization Parameter Values
Use the?SET?clause of the?ALTERSYSTEM?statement toset or change initialization parameter values. The optional?SCOPE?clause specifies the scope of a change as described in thefollowing table:
| SCOPE Clause | Description |
| SCOPE = SPFILE | The change is applied in the server parameter file only. The effect is as follows: 修改只應用到spfile中 ·???????? No change is made to the current instance. 不會更改當前實例 ·???????? For both dynamic and static parameters, the change is effective at the next startup and is persistent. 動態和靜態參數，改變都會在下次啟動后生效且具有永久性； This is the only?SCOPE?specification allowed for static parameters. 只有該語句能夠修改靜態參數 |
| SCOPE = MEMORY | The change is applied in memory only. The effect is as follows: 僅僅反映到memory中；對當前實例有效；重啟后失效；且不能修改靜態參數； ·???????? The change is made to the current instance and is effective immediately. ·???????? For dynamic parameters, the effect is immediate, but it is not persistent because the server parameter file is not updated. For static parameters, this specification is not allowed. |
| SCOPE = BOTH | The change is applied in both the server parameter file and memory. The effect is as follows: 上面兩種的結合；? 但是不能修改靜態參數； ·???????? The change is made to the current instance and is effective immediately. ·???????? For dynamic parameters, the effect is persistent because the server parameter file is updated. For static parameters, this specification is not allowed. |
?
It is anerror to specify?SCOPE=SPFILE?or?SCOPE=BOTH?if the instance did not start up with a server parameter file.The default is?SCOPE=BOTH?if a server parameter file was used to start up the instance,and?MEMORY?if a text initialization parameter file was used to start up theinstance.
啟動時如果沒有使用spfile，那么當修改是用了scopt=spfile或 scopt=both那么就會報錯（ora-32001）；
例子：
SQL> show parameterspfile
?
NAME???????????????????????????????? TYPE??????? VALUE
----------------------------------------------- ---------------- --------------
spfile??????????? ???????????????????string????? E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SPFILEORCL.ORA
SQL> show parameteruser_dump
NAME???????????????????????????????? TYPE??????? VALUE
----------------------------------------------- ----------------- -------------
user_dump_dest????????????????? ?????string?????E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP
SQL> create pfile='d:\pfile.ora'from spfile;
文件已創建。
SQL> shutdownimmediate;
數據庫已經關閉。
已經卸載數據庫。
ORACLE 例程已經關閉。
SQL> startuppfile='d:\pfile.ora';
ORACLE 例程已經啟動。
Total System GlobalArea? 838860800 bytes
Fixed Size????????????????? 1293384 bytes
Variable Size???????????? 734004152 bytes
Database Buffers?????????? 96468992 bytes
Redo Buffers??????????????? 7094272 bytes
數據庫裝載完畢。
數據庫已經打開。
SQL> alter systemset user_dump_dest='e:\oracle\product\10.2.0'scope=both;
alter system setuser_dump_dest='e:\oracle\product\10.2.0' scope=both
*
第 1 行出現錯誤:
ORA-32001: 已請求寫入 SPFILE,但是在啟動時未指定 SPFILE
SQL> alter systemset user_dump_dest='e:\oracle\product\10.2.0'scope=spfile;
alter system setuser_dump_dest='e:\oracle\product\10.2.0' scope=spfile
*
第 1 行出現錯誤:
ORA-32001: 已請求寫入 SPFILE,但是在啟動時未指定 SPFILE
SQL> alter systemset user_dump_dest='e:\oracle\product\10.2.0' scope=memory;
系統已更改。
SQL> show parameteruser_dump
NAME???????????????????????????????? TYPE??????? VALUE
----------------------------------------------- ----------------- -------------
user_dump_dest????????????????? ?????string?????E:\ORACLE\PRODUCT\10.2.0
SQL> create spfilefrom pfile;
create spfile frompfile
*
第 1 行出現錯誤:
ORA-01078: 處理系統參數失敗
LRM-00109: could notopen parameter file
'E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INITORCL.ORA'
SQL> create spfilefrom pfile='d:\pfile.ora';
文件已創建。
SQL> shutdownimmediate;
數據庫已經關閉。
已經卸載數據庫。
ORACLE 例程已經關閉。
SQL> startup
ORACLE 例程已經啟動。
Total System GlobalArea? 838860800 bytes
Fixed Size????????????????? 1293384 bytes
Variable Size???????????? 734004152 bytes
Database Buffers?????????? 96468992 bytes
Redo Buffers??????????????? 7094272 bytes
數據庫裝載完畢。
數據庫已經打開。
SQL> show parameteruser_dump
?
NAME???????????????????????????????? TYPE??????? VALUE
----------------------------------------------- ----------------- -------------
user_dump_dest????????????????? ?????string?????E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP
官方解釋及解決方案：
ORA-32001: write to SPFILE requested butno SPFILE is in use
Cause:?An ALTER SYSTEM command or aninternal self-tuning mechanism requested a write to the SPFILE but no SPFILEwas in use.
Action:?Re-start the instance using anSPFILE, or execute ALTER SYSTEM SET SPFILE.
?
For dynamicparameters, you can also specify the?DEFERRED?keyword. When specified, the change is effective only for futuresessions.
對于動態參數，可以指定deferred參數，只對接下來的會話有效；
When youspecify?SCOPE?as?SPFILE?or?BOTH, anoptional?COMMENT?clause lets you associate a text string with the parameterupdate. The comment is written to the server parameter file.
當scope指定spfile或both時，可以為修改的參數添加注釋；
Thefollowing statement changes the maximum number of failed login attempts beforethe connection is dropped. It includes a comment, and explicitly states thatthe change is to be made only in the server parameter file.
ALTER SYSTEM SET SEC_MAX_FAILED_LOGIN_ATTEMPTS=3 ???????????????? COMMENT='Reduce from 10 for tighter security.' ???????????????? SCOPE=SPFILE;The nextexample sets a complex initialization parameter that takes a list ofattributes. Specifically, the parameter value being set is the?LOG_ARCHIVE_DEST_ninitializationparameter. This statement could change an existing setting for this parameteror create a new archive destination.
ALTER SYSTEM ?????SET LOG_ARCHIVE_DEST_4='LOCATION=/u02/oracle/rbdb1/',MANDATORY,'REOPEN=2' ???????? COMMENT='Add new destination on Nov 29' ???????? SCOPE=SPFILE;When a valueconsists of a list of parameters, you cannot edit individual attributes by theposition or ordinal number. You must specify the complete list of values eachtime the parameter is updated, and the new list completely replaces the oldlist.
如果一個值由多個參數組成，那么修改的時候必須全部指定，有一樣的就再寫一次；
ClearingInitialization?Parameter Values
You can usethe?ALTER?SYSTEM?RESET?command to clear (remove) the setting of any initializationparameter in the SPFILE that was used to start the instance. Neither?SCOPE=MEMORY?nor?SCOPE=BOTH?are allowed. The?SCOPE?=?SPFILE?clause is not required, but can be included.
You may wantto clear a parameter in the SPFILE so that upon the next database startup adefault value is used.
See Also:
Oracle Database SQL Language Reference?for information about the?ALTER?SYSTEM?command
Exporting theServer Parameter File
You can usethe?CREATEPFILE?statement toexport a server parameter file (SPFILE) to a text initialization parameterfile. Doing so might be necessary for several reasons:
·????????For diagnostic purposes, listing all of the parameter valuescurrently used by an instance. This is analogous to the SQL*Plus?SHOWPARAMETERS?command orselecting from the?V$PARAMETER?or?V$PARAMETER2?views.
·????????To modify the &spfile;server parameter file by firstexporting it, editing the resulting text file, and then re-creating it usingthe?CREATESPFILE?statement
有幾個原因使你用create pfile語句導出spfile 文件到一個文本初始化參數文件中：診斷的目的，列出所有當前實例使用的參數值，類似于sql*plus的show parameter命令或從v$parameter或v$parameter2視圖中查詢語句；修改spfile文件，修改好之后可以通過create spfile語句重建服務器參數文件；
The exportedfile can also be used to start up an instance using the?PFILE?clause.
被導出的文本型參數文件也可以在startup的命令中以pfile子句指定，以用來啟動實例；
You musthave the?SYSDBA?or the?SYSOPER?system privilege to execute the?CREATE PFILE?statement. The exported file is created on the database serversystem. It contains any comments associated with the parameter in the same lineas the parameter setting.
Thefollowing example creates a text initialization parameter file from the SPFILE:
CREATE PFILE FROM SPFILE;Because nonames were specified for the files, the database creates an initializationparameter file with a platform-specific name, and it is created from theplatform-specific default server parameter file.
Thefollowing example creates a text initialization parameter file from a serverparameter file, but in this example the names of the files are specified:
CREATE PFILE='/u01/oracle/dbs/test_init.ora' ?????? FROM SPFILE='/u01/oracle/dbs/test_spfile.ora';Note:
Analternative is to create a PFILE from the current values of the initializationparameters in memory. The following is an example of the required command:
CREATE PFILE='/u01/oracle/dbs/test_init.ora' FROM MEMORY;Backing Up theServer Parameter File
You cancreate a backup of your server parameter file (SPFILE) by exporting it, asdescribed in?"Exporting the Server Parameter File".If the backup and recovery strategy for your database is implemented usingRecovery Manager (RMAN), then you can use RMAN to create a backup of theSPFILE. The SPFILE is backed up automatically by RMAN when you back up yourdatabase, but RMAN also enables you to specifically create a backup of thecurrently active SPFILE.
See Also:
Oracle Database Backup and Recovery User's Guide
Recovering a Lostor Damaged Server Parameter File
If yourserver parameter file (SPFILE) becomes lost or corrupted, the current instancemay fail, or the next attempt at starting the database instance may fail. Thereare several ways to recover the SPFILE:
如果初始化參數文件丟失或損壞，那么需要恢復，否則實例將失敗；下面是幾種恢復spfile的方法：
·????????If the instance is running, issue the following command tore-create the SPFILE from the current values of initialization parameters inmemory:
如果實例在運行，那么用下面的語句來重建；
This commandcreates the SPFILE with the default name and in the default location. You canalso create the SPFILE with a new name or in a specified location. See?"Creating a Server Parameter File"?for examples.
·????????If you have a valid text initialization parameter file (PFILE),re-create the SPFILE from the PFILE with the following command:
如果你有可用的pfile ,那么用下列語句來重建spfile;
This commandassumes that the PFILE is in the default location and has the default name. See?"Creating a Server Parameter File"?for the command syntax to use when thePFILE is not in the default location or has a nondefault name.
·????????Restore the SPFILE from backup.
從備份中恢復；
See?"Backing Up the Server Parameter File"?for more information.
·????????If none of the previous methods are possible in your situation,perform these steps:
1.???Create a text initialization parameter file (PFILE) from theparameter value listings in the alert log.
When aninstance starts up, the initialization parameters used for startup are writtento the alert log. You can copy and paste this section from the text version ofthe alert log (without XML tags) into a new PFILE.
See?"Viewing the Alert Log"?for more information.
2.???Create the SPFILE from the PFILE.
See?"Creating a Server Parameter File"?for instructions.
如果以上發放均不適合你的狀況，那么執行這些步驟來恢復吧：
1.???從alter log中列出的變量值創建一個pfile文件；（實例啟動時，這些啟動時使用的初始化參數會寫進alert log里，你可以拷貝過來用）
2.???再用createspfile from pfile創建spfile ;
Read/Write Errors During a Parameter Update
If an erroroccurs while reading or writing the server parameter file during a parameterupdate, the error is reported in the alert log and all subsequent parameterupdates to the server parameter file are ignored. At this point, you can takeone of the following actions:
·????????Shut down the instance, recover the server parameter file anddescribed earlier in this section, and then restart the instance.
·????????Continue to run the database if you do not care that subsequentparameter updates will not be persistent.
Viewing ParameterSettings
You can viewparameter settings in several ways, as shown in the following table.
| Method | Description |
| SHOW PARAMETERS | This SQL*Plus command displays the values of initialization parameters in effect for the current session. |
| SHOW SPPARAMETERS | This SQL*Plus command displays the values of initialization parameters in the server parameter file (SPFILE). |
| CREATE PFILE | This SQL statement creates a text initialization parameter file (PFILE) from the SPFILE or from the current in-memory settings. You can then view the PFILE with any text editor. |
| V$PARAMETER | This view displays the values of initialization parameters in effect for the current session. |
| V$PARAMETER2 | This view displays the values of initialization parameters in effect for the current session. It is easier to distinguish list parameter values in this view because each list parameter value appears in a separate row. |
| V$SYSTEM_PARAMETER | This view displays the values of initialization parameters in effect for the instance. A new session inherits parameter values from the instance-wide values. |
| V$SYSTEM_PARAMETER2 | This view displays the values of initialization parameters in effect for the instance. A new session inherits parameter values from the instance-wide values. It is easier to distinguish list parameter values in this view because each list parameter value appears in a separate row. |
| V$SPPARAMETER | This view displays the current contents of the SPFILE. The view returns?FALSE?values in the?ISSPECIFIEDcolumn if an SPFILE is not being used by the instance. |
?
?
?
?
?
轉載于:https://www.cnblogs.com/jiangu66/p/3177780.html
總結
以上是生活随笔為你收集整理的SPFILE 、PFILE 的全面解读的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 通过NAT转发实现私网对外发布信息
- 下一篇： windbg script ---- 禁