init.ora

The init.ora file stores the initialization parameters of Oracle. The values that are currently in effect can be viewed through v$parameter.

The init.ora file is read when an instance (for example using startup in SQL*Plus).

The default location of init.ora is $ORACLE_HOME/dbs> on unix and %ORACLE_HOME%\database on Windows. On Windows, the location can be changed by changing ORA_%ORACLE_SID%_PFILE.

The default name for the file is init$ORACLE_SID.ora (unix) or init%ORACLE_SID%.ora (windows)

However, it is possible to start the database with another init.ora file than the default one. In this case, there is no way to determine which init.ora was used when the database is running (at least up to Oracle 9i).

create pfile='/some/path/init.ora' from spfile;

In order to execute create pfile one needs the sysdba role.

When the database is created with DBCA (Database creation assistant), DBCA will create an init.ora file to create the database. Then it will create an spfile from that init.ora. Then it will rename the init.ora file into something like init.ora.55200519329 where 55200519329 is a timestamp of a sort. This init.ora.55200519329 will not be used by Oracle afterwards, so modifiying it will not change the init parameters.

Thanks to Marco de Booy and David Morris who both spotted an error on this page and notified me thereof.