File Location

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

db_name='ORCL'
memory_target=1G
processes = 150
audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='<ORACLE_BASE>/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='<ORACLE_BASE>'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300 
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'

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.

list all initialization parameters

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.

In SQL Plus

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