Recovery File Structures   «Prev  Next»
Lesson 2 Oracle database configuration file
Objective What is the init.ora file.

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.

Purpose and Function of init.ora file

Here's a breakdown of the purpose and function of the init.ora file in Oracle databases:
  • 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.
  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.

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

Switch from using initialization parameter file (init.ora) to (SPfile) in Oracle

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:
  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

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

-- Start the database with the SPfile

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

This is a partial image of my initorc1.ora file.
1) This is a partial image of my initorc1.ora file. There are a few parameters displayed in this image that you'll want to pay attention to, the first of which is db_name=GL1. Since you can have multiple databases on your system, this parameter indicates which database is controlled by this init.ora file. orc1 will also be part of the name for many files associated with this database.

2) Another parameter you'll want to take note of is control_files=c:\oracle8\database\ctl1orc1.ora. This parameter indicates where the control file(s) for this database exist.

3) The last parameter I wanted to point out here is log_checkpoint_interval=8000. This parameter indicates the checkpoint interval for this instance.

4) This image is from the Oracle Instance Manager screen. The information being displayed relates to redo log archiving. In this case the log mode is NOARCHIVELOG which indicates we are not archiving our redo log files. We'll discuss this topic in a later module.

5) This image is from Oracle Instance Manager and displays parameter information including default values. Note that the initorc1.ora file is used to modify values and will not indicate what the default values are for a specific parameter. You can also change parameters using Instance Manager.

6) This image displays information from Server Manager, a line mode tool. You can see that I entered three commands and displayed information that may have been included in the initorc1.ora file or could have been accesses via Instance Manager.

These tools are the three primary ways of viewing parameters. Notice that the information is presented a little differently with each tool. The next lesson is about the database control file.
SEMrush Software