Oracle Parameter Files

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