Database News You Can Use
A monthly newsletter for Oracle users
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500
Ask DB Specialists: Benefits of a Server Parameter File
You’re invited to submit your Oracle-related questions to us at firstname.lastname@example.org. Include your name and telephone number in your email. If your question is answered in The Specialist next month, you will receive a free copy of our Oracle Wait Events reference poster. (US shipping addresses only).
This month’s question comes to us from Jody in Washington, DC: We have recently upgraded our database from Oracle 8i and now have a choice between a pfile and an spfile for storing initialization parameters. What are the differences and why would I want to use an spfile over a pfile?
Laurie Robbins of the Database Specialists team responds: In Oracle 9i, a new type of parameter file was introduced: the server parameter file or spfile. A pfile is a regular text file that you edit using your favorite editor. An spfile is a binary file that is modified through the ALTER SYSTEM command.
Many DBAs have found working with an spfile easier than a pfile. If you are using an spfile, you can use the ALTER SYSTEM command to accomplish several tasks simultaneously. When you execute ALTER SYSTEM the parameter change is validated immediately, which helps avoid errors associated with entering an incorrect parameter name or an invalid value. In addition, ALTER SYSTEM allows you to specify whether you want to update the parameter immediately (for those parameters that can be modified at the system and session level), update just the spfile, or both. All these features help avoid human error associated with manually updating a pfile.
The following example shows how to change the current value of TIMED_STATISTICS and store the value in the spfile so it will remain in effect the next time the instance is restarted:
SQL> ALTER SYSTEM SET TIMED_STATISTICS=true SCOPE=both;
You can find detailed information about the ALTER SYSTEM command in the Oracle SQL Reference manual. However, the features I use most can be summarized as:
ALTER SYSTEM SET parameter=value SCOPE=[memory/spfile/both] DEFERRED (specifies parameter change will take effect for future sessions) COMMENT=’text of your comment here’;
As with any new feature, it’s helpful to learn from the experience of others. A good practice to consider is to regularly create a backup copy of your spfile using the following command:
SQL> CREATE PFILE = ’/path/filename’ FROM SPFILE;
The backup pfile can be available in the event your spfile becomes corrupt, and it will also document parameter settings over time. It’s best to store the backup pfile in a non-default location. On instance startup, Oracle 9i and 10g will first look for an spfile and then a pfile if an spfile cannot be found. You may want more control over this process, so saving your backup pfile in a non-default location prevents it from being used automatically at instance startup. If you have any question about whether Oracle used an spfile or pfile on the last instance startup, you can issue SHOW PARAMETER SPFILE from SQL*Plus which will display a string in the value column if an spfile was used.
An spfile can be read using Unix commands like more or cat. However, I would avoid the temptation of viewing the contents of your spfile in this way. Although viewing the file won’t cause corruption, it does invite the possibility. If you want to view the contents of the current spfile, you can query the V$SPPARAMETER view. You can find specifics on this view in the Oracle Reference Guide, but you can get started with the following query:
set linesize 120 set pagesize 300 column name format a35 column value format a40 column update_comment format a40 word_wrapped SELECT name, value, update_comment FROM v$spparameter WHERE value IS NOT NULL ORDER BY name;
Another distinct advantage of an spfile over a pfile is its use as a single source for initialization parameters. If you want the ability to start your database instance remotely, an spfile is more efficient since it only needs to reside on the database server. When using a pfile, you need a copy on the remote or client system starting the database (such as when using Enterprise Manager). Using an spfile eliminates the need to keep multiple pfiles up to date.
So, in many ways, an spfile can help make managing initialization parameters easier. The ability to modify the spfile and set a parameter immediately using one statement can be a time saver and reduce human error. An spfile can also help those who like to start their database instance remotely by eliminating the need for a parameter file on the client system. However, if you are still wondering which type of parameter file is best for your environment, I found a relevant item on the popular site askTom (asktom.oracle.com). In Tom’s response to a question about how to remove an spfile so it doesn’t get used during startup, he seems to indicate his preference by the name he uses for the renamed spfile:
SQL> create pfile from spfile; SQL> shutdown SQL> !mv spfile$ORACLE_SID.ora spfile$ORACLE_SID.dont_use_the_best_feature SQL> startup
For more information on using an spfile, you can reference the following Metalink documents:
249664.1 – Pfile vs. SPfile
293698.1 – All You Wanted To Know About SPFILE and PFILE
166608.1 – ORA-03113: at STARTUP After Creating SPFILE
More and more companies are choosing to run their Oracle databases on the Linux platform, and the Oracle Database 10g feature that allows you to transport tablespaces across operating system platforms will likely add fuel to the Oracle on Linux fire. Recognizing the increase in popularity, we thought we would point out three resources specifically for DBAs running Oracle on Linux.
First there is Oracle-Base. We’ve mentioned this resource before, but now we’d like to point out resources on this site available for those running Oracle on Linux. At http://www.oraclebase.com/articles/Linux/ArticlesLinux.php, you’ll find dozens of relevant articles in each of the following sections:
- Oracle Database Installation Articles
- Oracle Application Server Installation Articles
- Other Oracle Resources
- Miscellaneous Articles
Secondly, Database Specialists has a popular and highly detailed installation guide called Installing and Configuring Oracle9i on the Linux Platform available at http://dbspecialists.wpengine.com/presentations.html#oracle920linux. We plan to update this guide when Oracle Database 10g Release 2 is released in the near future.
Lastly, the LinuxWorld Conference & Expo will take place August 8-11, 2005 in San Francisco. For more information, visit http://www.linuxworldexpo.com/live/12/events/12SFO05A.
Oracle DBAs typically find that a little operating system knowledge goes a long way. Most DBAs in Unix environments are comfortable with Unix basics. But if you need to do more with Unix than edit a file or schedule a job with cron, or if you know how to manipulate OS patches in HP-UX but need to apply a patch to a Solaris server, then we’ve found a reference that might interest you.
The site (at http://bhami.com/rosetta.html) features an at-a-glance listing of common Unix commands categorized by operating system. This reference will be useful to DBAs who already know the basics of Unix but need to be able to perform advanced Unix administration tasks such as configuring logical volumes or adjusting kernel parameters. The web page shows how to perform these advanced tasks on over a dozen Unix variants including Linux, Solaris, HP-UX, and AIX. By manipulating a drop-down list in the upper left corner of the page, you can limit the display to the specific Unix versions of interest to you. This web page can also be helpful for seeing similarities and differences between multiple variants of Unix.
For a great hands-on review of the importance of database design and communication among DBAs and developers, don’t miss Christopher Foot’s article in DBAZine. “One of the overlooked duties of an Oracle DBA is to inform, educate and assist the application development staff during the application development process,” Foot begins the article. It’s an easy read that covers a lot of ground, includes checklists, and outlines topics such as:
- Database design review meetings
- Logical data model review
- Designing for performance
- Setting up a successful test system in Oracle
- Monitoring performance during testing
- Performance design reviews
- Preparation for production turnover
- Post-production turnover
You’ll find the article at http://www.dbazine.com/oracle/or-articles/foot9.