Oracle Concepts - pfile and spfile
Introducing the PFILE and SPFILE
Oracle provides two different types of mutually exclusive parameter files that you can use, PFILE and SPFILE. Let’s look at the PFILE and the SPFILE in a bit more detail.
The Oracle PFILE
As we said, the parameters are stored in either a PFILE or an SPFILE. The PFILE is a text-based file, and the “init.ora” file has been around for over a decade. Inside the PFILE are a number of database settings called parameters. These parameters help the Oracle programs know how to start. The parameters tell the Oracle programs how much memory to allocate, where to put files related to the database and where certain database files already exist.
As the PFILE is text based, one can edit it in an editor like vi on UNIX or Notepad on Windows. When you have changed it, you need to make sure you save your changes to disk before you exit the editor. Also, make sure you save it as a plain text file, since some editors (like Microsoft Word) can save documents in special formats that Oracle would not be able to read.
Depending on which operating system you are running on, your PFILE is located by default in the ORACLE_HOME\database (usually the case on Windows) or ORACLE_HOME\dbs directory for most other platforms (we talked about where ORACLE_HOME was earlier in this book).
If you are using a PFILE, it takes on the form. of initSID.ora, meaning the file will use the ORACLE_SID you defined when you created the database. If your SID is called testdb, the resulting PFILE should be called inittestdb.ora
The Oracle SPFILE
The SPFILE is different from the PFILE in that it can not be directly edited. This is because it has a header and footer that contains binary values. Since you can not change a SPFILE directly, Oracle allows you to manage the SPFILE via the alter system command.
That might sound a bit more complex, but it really is no harder than manually changing a PFILE. For using an SPFILE, you can reap great benefits. It can be backed up by RMAN (Oracle’s backup and recovery software) every time a change is made or when the database is backed up, which means it’s easier to recover (we will talk about RMAN a great deal in a later chapter!). Also, SPFILES allow you to make dynamic changes to parameters that are persistent. For example, remember that we said this database parameter change was not persistent if we were using PFILES:
Alter system set db_recovery_file_dest_size=10g;
If we were using SPFILES the parameter would keep the same value, even after a database restart. This means you only have to change the parameter value in one place, and that you can forget having to change it in the PFILE of the database.
One of the most important benefits of the SPFILE is that Oracle has introduced many automatic tuning features into the core of the database. Without an SPFILE, Oracle can not autotune your database.
An SPFILE uses the same formatting for its file name as the PFILE, except the word spfile replaces init. For instance, if your ORACLE_SID is testdb, the resulting spfile would be called spfiletestdb.ora.
Administering the PFILE and SPFILE
As a DBA the main thing you need to worry about with the SPFILE and PFILES are backing them up. You can use RMAN to backup an SPFILE, or back them up yourself.
Remember that a PFILE is simply a text based file, which means you can copy it to another directory without affecting the Oracle instance. This is the easiest way to backup a PFILE.
To back up an SPFILE, you will first want to convert it to a PFILE. You can do this with the following syntax.
SQL> create pfile from spfile;
This will create a PFILE named initSID.ora in your $ORACLE_HOME/database (Windows) or $ORACLE_HOME/dbs (Linux/Unix) directory.
Note that the SID in initSID.ora will be replaced with the SID of your database as defined during creation.
In addition, you can back up the file directly to the preferred location with the command:
SQL> create pfile=/path/to/backup.ora from spfile;
If the time comes that you must put the SPFILE back into place, you can do so with this command:
SQL> create spfile from pfile=/path/to/backup.ora
If your database is currently running using the SPFILE, be sure to shut down first so Oracle can replace the file. As your SPFILE is in use the entire time your database is running, you should never overwrite it during normal operations
You can use the V$PARAMETER dynamic view to see the current setting of the different database parameters. In this example, we use the DESC SQL*Plus command to describe the V$PARAMETER view, and we then query the V$PARAMETER view to see the value of the control_file parameter setting:
SQL> desc v$parameter Name Null? Type ----------------------------------------- -------- ------------- NUM NUMBER NAME VARCHAR2(80) TYPE NUMBER VALUE VARCHAR2(512) DISPLAY_VALUE VARCHAR2(512) ISDEFAULT VARCHAR2(9) ISSES_MODIFIABLE VARCHAR2(5) ISSYS_MODIFIABLE VARCHAR2(9) ISINSTANCE_MODIFIABLE VARCHAR2(5) ISMODIFIED VARCHAR2(10) ISADJUSTED VARCHAR2(5) ISDEPRECATED VARCHAR2(5) DESCRIPTION VARCHAR2(255) UPDATE_COMMENT VARCHAR2(255) HASH NUMBER SQL> select name, value from v$parameter where name = 'control_files'; NAME VALUE -------------------- ----------------------------------------------- control_files C:\ORACLE\ORADATA\BOOKTST\BOOKTST\CONTROL01.CTL, C:\ORACLE \ORADATA\BOOKTST\BOOKTST\CONTROL02.CTL, C:\ORACLE\ORADATA\ BOOKTST\BOOKTST\CONTROL03.CTLYou may also use the shortcut “show parameter” command. For instance:
SQL> show parameter control_files;
The Parameter File at Startup Time
Oracle prefers the use of an SPFILE to a PFILE. When you startup your Oracle database, Oracle will scan the contents of your parameter directory ($ORACLE_HOME/database on Windows or the Linux directory name $ORACLE_HOME/dbs), searching in the following order:
* spfileSID.ora
* spfile.ora
* initSID.ora
* init.ora
If the directory contains none of the above, then the startup will fail.
Alternatively, you can tell Oracle where to find a PFILE if you store it in a different location.
SQL> startup pfile=/path/to/pfile/inittestdb.ora
Furthermore, you can create a PFILE that contains nothing but the following line:
SPFILE=/path/to/spfiletestdb.ora
By doing so, we are able to startup using a PFILE in any location we want, but continue to use an SPFILE that can also be in a different location. This can be very beneficial for those that wish to store their SPFILE in a centralized location, such as a SAN. Now, let’s take a quick look at redo log administration.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23895263/viewspace-687747/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle Spfile and pfileOracle
- Oracle的spfile和pfileOracle
- Oracle中的spfile和 pfileOracle
- Oracle中的pfile和spfileOracle
- oracle spfile和pfile小結Oracle
- oracle引數檔案pfile,spfileOracle
- Oracle 引數檔案(spfile和pfile)Oracle
- 探索Oracle pfile和spfile的祕密Oracle
- 【原創】oracle spfile和pfile小結Oracle
- oracle實驗記錄 (PFILE 啟動SPFILE)Oracle
- 水煮Oracle——《Oracle pfile/spfile引數檔案詳解Oracle
- Oracle基礎 07 引數檔案 pfile/spfileOracle
- 如何在pfile中引用spfile來啟動oracleOracle
- 判斷ORACLE啟動時使用spfile還是pfileOracle
- 檢視ORACLE啟動時使用spfile還是pfileOracle
- oracle pfile spfile啟動順序 優先順序 spfilesid 〉 spfile 〉 initsidOracle
- Create spfile from pfile命令要注意
- (轉)Oracle中的啟動引數檔案:spfile和pfileOracle
- 檢視 Oracle 是用spfile 啟動還是 pfile 啟動Oracle
- 關於pfile,spfile的一些命令
- orcle pfile和spfile的區別和聯絡
- Rac中使用create pfile from spfile後的解決方法
- oracel體系結構梳理----控制檔案以及pfile和spfile
- Oracle啟動找不到spfile或者pfile提示ORA-01078,利用init.ora手工建立Oracle
- nomount的時候是必須讀取pfile或者spfile檔案的。
- 11.2版本alert日誌中記錄了pfile或者spfile的全路徑
- Oracle 19c Concepts(00):Changes in This Release for Oracle Database ConceptsOracleDatabase
- Oracle ASM spfile in a disk groupOracleASM
- Oracle修改spfile的位置Oracle
- Oracle 19c Concepts(19):Concepts for Database DevelopersOracleDatabaseDeveloper
- Oracle ASM儲存Spfile解析OracleASM
- 【問題處理】恢復因誤生成PFILE 導致RAC的SPFILE無效的問題
- Oracle 19c Concepts(18):Concepts for Database AdministratorsOracleDatabase
- oracle中spfile的預設位置Oracle
- oracle rman之丟失spfile恢復Oracle
- Oracle 19c Concepts(10):TransactionsOracle
- Oracle 19c Concepts(07):SQLOracleSQL
- Oracle 19c Concepts(13):Oracle Database InstanceOracleDatabase