Oracle: pfile and spfile notes.

Oracle: pfile and spfile notes (Tested on Oracle 11g on Oracle Linux)
May 5, 2019

By default Oracle will start using the spfilesid.ora if there is one present in:
$ORACLE_HOME\dbs

If there is no spfile then it starts up using pfilesid.ora in:
$ORACLE_HOME\dbs

If we wanted to to startup oracle using pfile other that the default names then the full path command would be:
startup pfile=’/u01/app/oracle/product/11.2.0/db_1/dbs/XXXXXX.ora’;
‘XXXXX’ being one time use pfile name that we want to use.

To make a pfile from a working spfile:
create pfile=’/$ORACLE_HOME/dbs/xxxxxxx.ora’ from spfile=’/$ORACLE_HOME/dbs/xxxxxxxxx.ora’;

To make a spfile from a working pfile:
CREATE SPFILE=’$ORACLE_HOME/dbs/xxxxxxx.ora’ FROM PFILE=’$ORACLE_HOME/dbs/xxxxxxxx.ora’;

Changing an SPFILE Parameter While the DB is UP:

ALTER SYSTEM SET smtp_out_server=’mail.hallmarkbusiness.com’
                 COMMENT=’mail server change.’
                 SCOPE=BOTH;

examples:
create pfile=’/$ORACLE_HOME/dbs/initbsid.ora’ from spfile=’/$ORACLE_HOME/dbs/spfilebsid.ora’;
create SPFILE=’/$ORACLE_HOME/dbs/spfilebsid.ora’ from pfile=’/$ORACLE_HOME/dbs/initbsid.ora’;

STARTING DB WITH PFILE:
STARTUP PFILE = /$ORACLE_HOME/dbs/initbsid.ora

Prakash

Leave a Reply

Your email address will not be published. Required fields are marked *