Database Initialization File in Oracle

As you learned in earlier in this course, the initialization file is a text file containing a number of parameter settings that are used when you start an instance. These parameters control such things as the amount of memory used by the instance, the database block size, the maximum number of processes, and a large number of other settings that control the way in which the instance operates.

When you create a database, one of the first things you need to do is to create the initialization file for that database. That is what we are going to do in the remainder of this module. During this course, those of you who are running Oracle will be building a project database.Since our project database will be about coins, let's name the database COIN. The OFA guidelines specify init.oraSample, where Sample is the database name, as the naming convention to use for initialization files. We will follow that guideline, and name our initialization file initCOIN.ora. If you are running Oracle, go ahead and create a blank initCOIN.ora file. You can use Notepad to do this. When you open Notepad you will get a blank file. Use the

File->Save As

menu option to save this blank file as initCOIN.ora. Place it in whichever of the following locations is appropriate, depending on the operating system and the version of the Oracle database software that you are using:

For UNIX:
$ORACLE_BASE/admin/COIN/pfile
For Windows NT:
c:\oracle\admin\COIN\pfile

You will need to create the directory for the COIN database. It won't already exist. You will also need to create the pfile directory, underneath the COIN directory. Do this using the standard commands for whatever operating system that you are using. For example, on UNIX, you might issue these commands:

mkdir $ORACLE_BASE/admin/COIN
mkdir $ORACLE_BASE/admin/COIN/pfile

The same command works on NT, but there is no $ORACLE_BASE environment variable, and backward-slashes are used instead of forward ones. For example:

mkdir c:\oracle\admin\COIN
mkdir c:\oracle\admin\COIN\pfile

If you are running UNIX, you may find that Oracle has separated initialization parameters into two groups.

The default location of the pfile (parameter file) for an Oracle instance depends on your operating system and whether you're using a single instance or a RAC (Real Application Clusters) configuration. Here's a breakdown:
Default Locations: Single Instance (All Operating Systems): The default location for the pfile in a single instance setup is:

$ORACLE_HOME/dbs/init<SID>.ora

• `<SID>` is the system identifier for your Oracle instance.
• `$ORACLE_HOME` is an environment variable pointing to the directory where your Oracle software is installed.

RAC (Real Application Clusters):
In a RAC environment, the pfile might not reside on a single, shared location. It can be stored on individual cluster nodes, potentially with some variations depending on your specific configuration. However, it's still typically found within the `$ORACLE_HOME/dbs` directory on each node.
Finding the Pfile Location: Here are a couple of ways to determine the exact location of the pfile for your specific instance:

1. Check the Server Parameter File: The server parameter file (`spfile`) might hold information about the pfile location. You can query the `v$parameter` view to find the value of the `initialization_file` parameter. This parameter often points to the path of the pfile used by the instance.
   

   SELECT name, value
   FROM v$parameter
   WHERE name = 'initialization_file';
   

2. Look for Symbolic Links: In some cases, there might be a symbolic link in the `$ORACLE_HOME/dbs` directory named `init.ora` pointing to the actual location of the pfile. Following this symbolic link will reveal the pfile's path.

Important Notes:

• While the default location is `$ORACLE_HOME/dbs/init.ora`, it's possible for the pfile to be placed in a non-standard location during instance creation. If the methods above don't reveal the pfile location, consult your database administrator or refer to your specific instance's configuration documentation.
• It's generally recommended to keep the pfile in a secure location with appropriate access controls.

On many UNIX installations, if you look in the pfile directory for an Oracle instance you will find both an init.ora file and a config.ora file.
Why is this done?
Answer: This is a way that Oracle is using to separate initialization parameters into two groups. One group consists of parameters containing the name of a specific database. These are placed into the config.ora file. Here is an example of a config.ora file from one of the databases I manage:

$ cat configSEED.ora
control_files = (/orcl1/oradata/SEED/control01.ctl,
                /orcl2/oradata/SEED/control02.ctl,
                /orcl03/oradata/SEED/control03.ctl)
background_dump_dest = /ORACLE_BASE/admin/SEED/bdump
user_dump_dest = /ORACLE_BASE/admin/SEED/udump
db_name = SEED

When a config.ora file is used like this, the init.ora file will contain a line such as the following:

ifile = /ORACLE_BASE/admin/SEED/pfile/configSEED.ora

When you start an instance like this, Oracle will start by reading the init file. The ifile directive tells it to include the contents of the specified file, which in this example is the config file. You do not need to do anything differently for this situation; this is just an explanation of why you may notice two different files being used on some systems.

There are a few points to clarify regarding Oracle, OFA (Optimal Flexible Architecture), and initialization parameters:

1. OFA and Software Installation: OFA itself isn't software that you install. It's a set of guidelines for organizing your Oracle software installation on the file system. So, when you install Oracle Database on Windows, you're not specifically installing OFA software.
2. Initialization Parameters: The initialization parameters for your Oracle instance are stored in a file called the spfile (server parameter file) by default. This file is not directly located within the init.ora file.

Here's a breakdown of how it works on Windows with OFA:

• Default Location (OFA): Following OFA recommendations, the spfile is typically placed in the `$ORACLE_HOME/dbs` directory on Windows. `$ORACLE_HOME` is an environment variable pointing to your Oracle installation directory.
• init.ora (Optional): The `init.ora` file can exist in the same `$ORACLE_HOME/dbs` directory, but it plays a different role. It can act as a template or a fallback for the spfile. Here are the typical scenarios for init.ora:

• Template: During instance creation, the Oracle Universal Installer (OUI) might copy `init.ora` to create the spfile, potentially applying customizations during the process.
• Fallback: If the spfile is missing or corrupt, Oracle might attempt to use `init.ora` as a fallback for startup parameters. However, this behavior can be disabled in Oracle configurations.

In summary:

• OFA is a guideline for file system organization, not software you install.
• Initialization parameters reside in the spfile (server parameter file), typically located in `$ORACLE_HOME/dbs` on Windows with OFA.
• The init.ora file might exist as a template or fallback for the spfile.

For managing initialization parameters, it's recommended to modify the spfile directly using appropriate tools like `SRVCTL` or SQL*Plus with the `ALTER SYSTEM` command. You can consult the Oracle documentation for detailed instructions on managing spfiles and initialization parameters.