Initialization and Configuration Files

Oracle determines the physical configuration of an instance by accessing the control files. However, there are a number of configuration parameters related to tuning that you do not want to reset manually each time you start an instance. To make this easier, Oracle provides a series of initialization files that are similar to the autoexec.bat and config.sys of Microsoft DOS. On multiuser Oracle systems, there are two primary initialization files that are used:

•  init.ora. This is the main file. The actual filename contains the Oracle instance’s SID (system ID), so that an instance named blue would have an initialization file called initblue.ora.

•  config.ora. Typically, config.ora files contain the locations for the control, archive, and dump files. They also contain the database name and the database block size parameters. Oracle intended these files to contain values that have parameters that are the same between instances; init.ora files contain parameters that are tuned and vary between instances. As with init.ora files, the config.ora files contain the SID in their name (for example, configblue.ora). The config.ora file is called by placing a line in the init.ora file that says to use a second initialization file (ifile = filename).

The initialization files are simple text files that you can edit with standard operating system editors, such as the wonderful vi editor in UNIX or Notepad under Microsoft Windows. The format is simple; you set a variable equal to a value. I have two gripes about the setup of these files. First, I hate to have to keep two files edited when one can do the job (personal preference). Second, the data is so scattered in these files with so many options commented out that it is difficult to see what you are setting and you have no history of what you were using in the past. Therefore, I like to redo my initialization file to contain the following sections (a sample init.ora file for UNIX systems is contained on the disk):

•  A header that contains the name of the file, its purpose, and a history of all parameter changes (so that if a parameter change does not work out, you know where to set it).

•  A grouping of all parameters that affect the size of the SGA. I do not keep commented-out parameters, only live ones.

•  A grouping of all parameters that affect this size of the PGA.

•  A grouping of all parameters that deal with logging.

•  A grouping of the parameters associated with national language support (NLS), which is Oracle’s tool to support databases that use multiple languages for the data and such things as date formats.

•  A grouping of all parameters associated with the multithreaded server, if that option is used.

•  A general category that contains the other miscellaneous parameters, such as whether multiple database writer processes are used.

•  Finally, in certain cases Oracle will suggest you add some undocumented parameter to your system to make it run better. This is often the case when you are working with a large data warehouse. I like to keep these parameters in a separate section so that I know where they originated.

Before moving on to the next section on log and trace files, you need a few more notes. In multiuser Oracle systems, you can find the init.ora files in the dbs subdirectory under the ORACLE_HOME directory (the directory under which you locate all your Oracle software and administrative files. If this does not contain the actual file, it will contain at least a link that points to the init.ora file. The config.ora files are located in the pfile subdirectory under the ORACLE_BASE/admin/instance_name directory. In Personal Oracle, there is only an init.ora file, located under the /orawin/rdbms directory. This is probably enough to get you started in the world of Oracle initialization files. If you have a test Oracle instance available to you, try altering the configuration files and tuning parameters in this instance before you work on the instance that your company is depending on for its daily operations. If you do not have this luxury, be careful, make a copy of the original initialization files with another name (for example, initblue.ora.1jan98), and then try adjusting one parameter at a time until you build up your confidence.