Oracle documentation

Most Oracle documentation and those of us who write about Oracle make a very common assumption about the init.ora file. We assume that when we refer to init.ora it is understood that this file may be named something else. I performed a default installation of Oracle8 on my NT platform. I took the default instance name of orc1 during the install. Oracle installed a file called initseed.ora on my disk and created a file called initorc1.ora that is specific to the instance orc1. Both files are located in my c:\oracel8\database subdirectory. For convenience, we will refer to init.ora as a general name rather than an instance specific name.

Here's a breakdown of the purpose and function of the init.ora file in Oracle databases:
Purpose

• Configuration Blueprint: The init.ora file serves as the primary configuration file for an Oracle database instance. It stores a list of initialization parameters and their values. These parameters dictate all sorts of behaviors at startup.
• Performance Tuning: Many init.ora parameters directly influence how the database utilizes system resources (like memory and CPU), impacting overall database performance.
• Customization: The init.ora file enables database administrators (DBAs) to tailor the database instance to suit specific requirements and workloads.

Functions

1. Startup Configuration: When an Oracle database instance starts, it first reads the init.ora file. The parameters defined in this file determine:
   • Memory Allocation: Parameters such as `DB_BLOCK_BUFFERS`, `SGA_TARGET`, and `PGA_AGGREGATE_TARGET` control how much memory the database instance allocates for various structures, such as cache for data or program code.
   • System Resources: Parameters like `PROCESSES` and `OPEN_CURSORS` specify how many processes and open cursors the database can handle simultaneously.
   • File Locations: The init.ora usually tells the instance where to find crucial files like control files, redo logs, and diagnostic trace files.
   • Database Behavior: Parameters can influence logging behavior, auditing, character sets, and more.
2. Persistent Settings: Unlike dynamically alterable parameters, changes made to the init.ora file require a database restart for them to take effect. This ensures the database always starts with consistent settings.

Location

• The standard location of the init.ora file is within the `ORACLE_BASE/admin<ORACLE_SID>/pfile` directory, where `<ORACLE_SID>` is the system identifier of the Oracle database instance.
• The file's actual name typically follows the format `init<ORACLE_SID>.ora`. For example, for an instance with the SID "PROD," the initialization parameter file would likely be named `initPROD.ora`.

Important Notes:

• Oracle also supports the use of a server parameter file (SPFILE), which is a binary file that serves the same purpose as init.ora. SPFILEs offer benefits like dynamic modification of parameters without a restart.
• Experienced DBAs carefully adjust parameters in the init.ora (or SPFILE) to optimize the database instance's performance and behavior.

To switch from using an initialization parameter file (init.ora) to a server parameter file (SPfile) in Oracle, you can use the `CREATE SPFILE` command in SQL*Plus or another Oracle command interface. Here are the steps to follow:

1. Start SQL*Plus and Connect to the Database: You need to connect as a user with the necessary privileges (typically as SYSDBA).
2. Check the Current Configuration: Before creating an SPfile, it's good practice to check the current initialization parameters and whether an SPfile is already in use. You can do this by querying the `V$PARAMETER` view.
3. Create the SPfile from the Existing init.ora File: Use the following command to create an SPfile from an existing init.ora file:
   

      CREATE SPFILE FROM PFILE='path_to_init.ora';
      

   
   Replace `path_to_init.ora` with the actual path to your init.ora file.
4. Restart the Database: After creating the SPfile, you need to restart the database to use the new SPfile. This can be done using the following commands:

      SHUTDOWN IMMEDIATE;
      STARTUP;
      

5. Verify the SPfile is in Use: After restarting, check that the database is now using the SPfile. This can be done by querying the `V$SPPARAMETER` view or checking the database properties.

Here is a detailed command sequence example assuming you have the necessary administrative rights:

-- Connect as SYSDBA
CONNECT / AS SYSDBA

-- Check if SPfile is already in use
SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM v$parameter WHERE name = 'spfile';

-- Create SPfile from init.ora
CREATE SPFILE FROM PFILE='/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initORCL.ora';

-- Shutdown the database
SHUTDOWN IMMEDIATE;

-- Start the database with the SPfile
STARTUP;

-- Verify the SPfile is in use
SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM v$parameter WHERE name = 'spfile';

This will switch your Oracle database from using an init.ora file to using an SPfile, which allows for dynamic changes to the database parameters without needing to restart the database.

The init.ora file contains initialization information about your database and instance. There are over 130 parameters that can be set in your init.ora file by using Oracle Instance Manager, Oracle Server Manager, or any text editor. All modifications specified in the init.ora file will take effect the next time you start up your instance. Most parameters are set with default values by the instance and do not concern our backup and recovery plan, but there are some parameters that you may want to set manually. When you consider the number of database options (Java, objects, parallel server, and so on) and potential platforms (Unix, NT, Netware, LINUX, MVS, and so on) it is easy to understand why there are more than 130 parameters. This module introduces certain parameters that relate to backup and recovery. In the wrap up lesson, a sidebar will accumulate the parameters that were covered. While it isn't the only place to find information about your database, the init.ora file should be one of the first places you look to determine how your database is set up. View the slide show below to see parameter information using three different tools, init.ora, Server manager, and the Oracle Enterprise Manager tools.