Initialization parameters are configuration parameters that affect the operation of an instance. The instance reads these initialization parameters from either a text initialization parameter file(pfile/init.ora) or a server parameter file (spfile) at startup time. There are many different initialization parameters to optimize operation in various environments.
Normally when you want to start your oracle database you simply use the startup command. However in some cases you do want to start your database with some specific parameters set and you are stating them in your pfile. Meaning you initiate some of your database parameters from your pfile.
When starting your database with a pfile you can use the following command while connected via sqlplus as sysdba:
startup pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initDB11G.ora
this will result in something like below:
SQL> startup pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initDB11G.ora ORACLE instance started. Total System Global Area 2142715904 bytes Fixed Size 1337688 bytes Variable Size 671090344 bytes Database Buffers 1459617792 bytes Redo Buffers 10670080 bytes Database mounted. Database opened. SQL>
In some cases you might run into an error when trying to do so. Some of the common error messages (error message combinations are below)
LRM-00123: invalid character 0 found in the input file ORA-01078: failure in processing system parameters
The combination of a LRM-00123 and the ORA-01078 indicates most likely that you have specified the spfile in your startup command instead of your pfile.
LRM-00109: could not open parameter file '/u01/app/oracle/.........' ORA-01078: failure in processing system parameters
Combination of LRM-00109 and ORA-01078 indicates that you most likely have made a typo in the path to your pfile.
How can I know whether pfile or spfile is being used for the database instance?
There are 3 ways:
1. From V$SPPARAMETER View – V$SPPARAMETER view lists the contents of the SPFILE. The view returns NULL values if a PFILE was used to start up the instance.
2. Using SHOW PARAMETER:
SQL> show parameter spfile;
This query returns the spfile name if the database was started with spfile. It shows no value if the database is started with pfile.
3. Using the alert.log file:
– When a pfile is used, it shows something similar to:
Starting ORACLE instance (normal) ... Using parameter settings in server-side pfile [PATH]INITORCL.ORA
– When an spfile is used, it shows something similar to:
Starting ORACLE instance (normal) ... Using parameter settings in server-side spfile [PATH]SPFILEORCL.ORA