Oracle Database - server parameter file (spfile)

By default, the preconfigured database uses a parameter file (spfile) named spfilesid.ora, which is stored in the $ORACLE_HOME/dbs directory (dbs stands for database) .

However, if you choose Automatic Storage Management for the database, Database Configuration Assistant typically uses the same storage mechanism for the server parameter file.

If the server parameter file is not located in the $ORACLE_HOME/dbs directory, the database uses the SPFILE parameter in an initialization parameter file to locate it. The default initialization parameter file is $ORACLE_HOME/dbs/initsid.ora.

You can use the Oracle Enterprise Manager Database Control to view the location of the server parameter file and list all of the initialization parameters, as follows:

• Log in to the Database Control.
• Click Server.
• In the Database Configuration section of the Server page, click Initialization Parameters.

Enterprise Manager displays a table listing the current value of each initialization parameter.

• Select the SPFile tab.

Enterprise Manager displays a table listing the value of each initialization parameter specified in the server parameter file. The location of the server parameter file is displayed on top of the page.

The following command determine whether the system uses:

• an initialization parameter file (initsid.ora)
• or a server parameter file (spfiledbname.ora)

SQL> show parameter pfile;

NAME      TYPE        VALUE
--------- ----------- -------------------------------------------------------------
spfile    string      C:\APP\GERARD\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEORCL.ORA