The legacy init.ora file is a very simple file in terms of its construction. It is a series of variable key/value pairs. A sample init.ora file might look like this:
control_files=’/opt/oracle/oradata/CDB/control01.ctl’
db_block_size=8192
db_name=’CDB’
In fact, this is pretty close to the most basic init.ora file you could get away with in real life, though if the block size I was using was the default on my platform (the default block size varies by platform), I could remove that parameter.
The parameter file is used at the very least to get the name of the database and the location of the control files. The control files tell Oracle the location of every other file, so they are very important to the “bootstrap” process that starts the instance.
Now that you know what these legacy database parameter files are and where to get more details about the valid parameters you can set, you also need to know where to find them on disk. The naming convention for this file by default is
init$ORACLE_SID.ora (UNIX/Linux environment variable)
init%ORACLE_SID%.ora (Windows environment variable)
and by default it will be found in
$ORACLE_HOME/dbs (UNIX/Linux)
%ORACLE_HOME%\DATABASE (Windows)
It is interesting to note that, in many cases, you’ll find the entire contents of this parameter file to be something like
IFILE= /some/path/to/somewhere/init.ora’
The IFILE directive works in a similar fashion to an #include file in C.
It includes in the current file the contents of the named file. Here, this directive includes an init.ora file from a nondefault location.
It should be noted that the parameter file does not have to be in any particular location. When starting an instance, you can use the pfile=filename option to the startup command. This is most useful when you’d like to try out different init.ora parameters on your database to see the effects of different settings.
Legacy parameter files can be maintained by using any text editor. For example, on UNIX/Linux, I’d use vi; on the many Windows operating system versions, I’d use Notepad; and on a mainframe, I would perhaps use Xedit. It is important to note that you are fully responsible for editing and maintaining this file. There are no commands within the Oracle database itself that you can use to maintain the values in the init.ora file.
For example, when you use the init.ora parameter file, issuing an ALTER SYSTEM command to change the size of an SGA component would not be reflected as a permanent change in that file. If you want that change to be made permanent—in other words, if you’d like it to be the default for subsequent restarts of the database—it’s up to you to make sure all init.ora parameter files that might be used to start this database are manually updated.
The last interesting point of note is that the legacy parameter file is not necessarily located on the database server. One of the reasons the server parameter file (that we’ll discuss shortly) was introduced was to remedy this situation.
The legacy parameter file must be present on the client machine attempting to start the database, meaning that if you run a UNIX/Linux server but administer it using SQL*Plus installed on your Windows desktop machine over the network, then you need the parameter file for the database on your desktop.
I still remember how I made the painful discovery that the parameter files are not stored on the server. This goes back many years to when a brand-new (now retired) tool called SQL*DBA was introduced. This tool allowed us to perform remote operations, specifically, remote administrative operations.
From my server (running SunOS at the time), I was able to connect remotely to a mainframe database server. I was also able to issue the shutdown command. However, it was at that point I realized I was in a bit of a jam—when I tried to start up the instance, SQL*DBA would complain about not being able to find the parameter file. I learned that these parameter files—the init. ora plain text files—were located on the machine with the client; they had to exist on the client machine—not on the server. SQL*DBA was looking for a parameter file on my local system to start the mainframe database.
Not only did I not have any such file, I had no idea what to put into one to get the system started up again! I didn’t know the db_name or control file locations (even just getting the correct naming convention for the mainframe files would have been a bit of a stretch), and I didn’t have access to log in to the mainframe system itself. I’ve not made that same mistake since; it was a painful lesson to learn.
When DBAs realized that the init.ora parameter file had to reside on the client’s machine that starts the database, it led to a proliferation of these files. Every DBA wanted to run the administrative tools from their desktop, so every DBA needed a copy of the parameter file on their desktop machine.
Tools such as Oracle Enterprise Manager (OEM) would add yet another parameter file to the mix. These tools would attempt to centralize the administration of all databases in an enterprise on a single machine, sometimes referred to as a management server.
This single machine would run software that would be used by all DBAs to start up, shut down, back up, and otherwise administer a database. That sounds like a perfect solution: centralize all parameter files in one location and use the GUI tools to perform all operations.
But the reality is that sometimes it’s much more convenient to issue the administrative startup command from within SQL*Plus on the database server machine itself during the course of some administrative task, so we ended up with multiple parameter files again: one on the management server and one on the database server.
These parameter files would then get out of sync with each other, and people would wonder why the parameter change they made last month might “disappear,” then reappear in seemingly randomly manner.
Enter the server parameter file (SPFILE), which can now be a single source of truth for the database.
