9th December, 2008

Oracle Parameter Files

Filed under: database,oracle — admin @ 3:07 pm

This isn’t going to be news to any Oracle DBAs reading this. So if you are one, can I suggest you move along quickly to the next item in your feed reader.

Anyone left is hopefully curious like I was to figure out the difference between ‘traditional’ oracle initialisation files (init.ora) and the new fangled server parameter files (sp files). Well wonder no further fair reader for I can explain all.

Prior to Oracle 9.2 (a long time ago in Oracle database years) it was only possible to permanently effect the configuration parameters of your database by changing the init.ora file and restarting your database instance. You could make changes whilst the database was running by using the ‘ALTER SYSTEM SET’ command but these would be lost the next time your database was restarted because the startup process would use the values in your init.ora file. This meant that any instance tuning would require some informed ‘twiddling’ by the DBA to settle on a suitable value for an instance parameter and that would then be followed by a restart, causing anyone using the database to be mightily inconvenienced.

With the introduction of server parameter files in 9.2 and above changes made interactively (using ‘ALTER SYSTEM SET’) are automatically persisted. This is because whenever a change is made this way it is automagically written to your spfile.

This article has a much more comprehensive description of the differences between the two different types of files. One of the useful things it mentions is the resolution order the database server uses to select an initialisation file on startup. This is;

  • spfile<SID>.ora
  • spfile.ora
  • init<SID>.ora

Tick another thing off the list of ‘stuff that has been bothering me and I need to figure out’.

2 Comments

  1. A useful trick for spfiles. Using ALTER SYSTEM SET … SCOPE=spfile without care can result in an instance that won’t open, making further ALTER SYSTEM statements to update the spfile impossible. Catch 22.

    A pfile can be generated from an spfile, manually. Copy spfile.ora to init.ora. Then open it in a text editor and remove non-ascii characters from the start and end of the file. Edit the erroneous value(s). Start the instance manually, with STARTUP PFILE=’path/to/init.ora’. You can then issue CREATE SPFILE FROM PFILE=’path/to/init.ora’ and restart the instance normally.

    Alex

    Comment by Alex Willmer — 10/12/2008 @ 6:45 am

  2. A useful trick for spfiles. Using ALTER SYSTEM SET … SCOPE=spfile without care can result in an instance that won’t open, making further ALTER SYSTEM statements to update the spfile impossible. Catch 22.

    A pfile can be generated from an spfile, manually. Copy spfile.ora to init.ora. Then open it in a text editor and remove non-ascii characters from the start and end of the file. Edit the erroneous value(s). Start the instance manually, with STARTUP PFILE=’path/to/init.ora’. You can then issue CREATE SPFILE FROM PFILE=’path/to/init.ora’ and restart the instance normally.

    Alex

    Comment by Alex Willmer — 10/12/2008 @ 6:45 am

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Powered by WordPress