Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

create spfile from pfile tips

Oracle Tips by Burleson Consulting

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.

Tip! - Making a PFILE when Oracle will not start

SQL> connect system/manager as sysdba

SQL> create pfile='C:\ora\pfile\init.ora'

File Created.

Now edit the pfile in notepad to make the changes need to allow Oracle to start.

Finally, start the database using the pfile you just edited:

SQL startup pfile='C:\ora\pfile\init.ora';

SQL create spfile from pfile='C:\ora\pfile\init.ora'


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.

Also see this spfile example.

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.

Create spfile from pfile

The spfile is created using the CREATE SPFILE statement; this requires connecting as SYSDBA.

Connect system/manager as sysdba;

CREATE SPFILE FROM PFILE;

This command creates an spfile in a non-default location ($ORACLE_HOME/database).  However, you can fully-qualify the path name is the ?create spfile? statement:

CREATE SPFILE='/u01/admin/prod/pfile/file_mydb.ora'

FROM  

PFILE=/u01/admin/prod/pfile/initprod.ora';

Warning - After an spfile is created, when you bounce the database you may encounter an error. To get around this, you have to reconnect as SYSDBA and use the STARTUP command.

The addition of the spfile has changed the search path for the Oracle startup deck. Oracle now uses the following path:

  • Search for the spfile$ORACLE_SID.ora file in the default location,

  • Look for the spfile.ora; and

  • Search for the pfile by name init$ORACLE_SID.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> 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.CTL

You 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.

This is an excerpt from the bestselling "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle Certified Master).  It?s only $19.95 when you buy it directly from the publisher here.

 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

 


 

 

��
 
 
 
 

 
 
 

 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.