Oracle initialization parameter files

You can start Oracle instances using either of two initialization files: an ASCII text file or a binary file. The binary file is called the server parameter file (SPFILE). The server parameter file is stored on the database server; changes applied to the instance parameters are persistent across all startups and shutdowns. The default server parameter file is named spfileSID.ora and is located in the following directory: Windows systems: ORACLE_HOME\database Linux systems: ORACLE_HOME/dbs The default ASCII text file is named initSID.ora and is located in the following directory: Windows systems: ORACLE_BASE/admin/ORACLE_SID/pfile Linux systems: ORACLE_BASE\admin\ORACLE_SID\pfile You can also start Oracle instances using a specified ASCII text file or server parameter file, rather than the default file, or without specifying a file. Example: specifying no initialization file The following example starts an Oracle instance without specifying a file: Copy sqlplus /nolog SQL> connect / as sysdba SQL> startup Oracle first searches for the spfileSID.ora file. If it does not exist, Oracle searches for the spfile.ora file. If neither exists, Oracle uses the initSID.ora file. If none of these files exist, Oracle displays messages similar to the following example: Copy SQL> startup ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file 'D:\ORA101\DATABASE\INITORA101.ORA' Example: specifying ASCII text file The following example starts an Oracle instance using the initSID.ora file: Copy SQL> startup pfile=d:\ora101\database\initORA101.ora ORACLE instance started. Total System Global Area 118255568 bytes Fixed Size 282576 bytes Variable Size 83886080 bytes Database Buffers 33554432 bytes Redo Buffers 532480 bytes Database mounted. Database opened. This option is not available if you are using a server parameter file. If you try to start an Oracle instance using this option and specifying a server parameter file, Oracle displays the following error message: Copy SQL> startup spfile=d:\ora101\database\spfileORA101.ora SP2-0714: invalid combination of STARTUP options If you start the database by specifying an ASCII text file, the spfile parameter is displayed as empty: Copy SQL> show parameter spfile NAME TYPE VALUE --------------------------------- ----------- ----------- spfile string Example: specifying server parameter file To start an Oracle instance using a server parameter file, you must use an initSID.ora file in which you specify only the spfile parameter: Windows systems: Copy spfile=d:/ora101/database/spfiletest.ora SQL> startup pfile=d:/ora101/database/inittest.ora Linux systems: Copy spfile=d:\ora101\database\spfiletest.ora SQL> startup pfile=d:\ora101\database\inittest.ora Copy ORACLE instance started. Total System Global Area 122449892 bytes Fixed Size 282596 bytes Variable Size 88080384 bytes Database Buffers 33554432 bytes Redo Buffers 532480 bytes Database mounted. Database opened. To determine whether you used the server parameter file, enter the following command in SQL*Plus: Copy SQL> show parameter spfile NAME TYPE VALUE ------------------------------ ----------- --------------------------------- spfile string d:\ora101\database\spfiletest.ora Changing a parameter in the server parameter file depends on whether the parameter is static or dynamic. Changes to static parameters do not take effect until the database is restarted. Dynamic parameters can be changed while the database is running and do not require restarting the database. To change a dynamic parameter: Copy SQL> alter system set open_cursors=400; System altered. To change a static parameter, qualify the command with scope=spfile: Copy SQL> alter system set processes=200 scope=spfile; System altered.