Scribd
Upload
116 views2 pages

Oracle Pfile and Spfile Guide

This document discusses pfiles and spfiles in Oracle. A pfile is a parameter text file that can be edited, while an spfile is a binary file that can only be changed with SQL commands. By default, Oracle uses the spfile at startup, but a pfile can be specified instead. The scope clause of alter system determines if a parameter change applies to memory, the spfile, or both. Dynamic parameters can be changed on the fly, while static parameters require a restart. The document provides examples of creating an spfile from a pfile, checking the current file, and altering parameter values.

Uploaded by

amruthamnaresh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
116 views2 pages

Oracle Pfile and Spfile Guide

This document discusses pfiles and spfiles in Oracle. A pfile is a parameter text file that can be edited, while an spfile is a binary file that can only be changed with SQL commands. By default, Oracle uses the spfile at startup, but a pfile can be specified instead. The scope clause of alter system determines if a parameter change applies to memory, the spfile, or both. Dynamic parameters can be changed on the fly, while static parameters require a restart. The document provides examples of creating an spfile from a pfile, checking the current file, and altering parameter values.

Uploaded by

amruthamnaresh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

Pfile and Spfile

What is pfile and spfile:


=========================

pfile is the parameter file .It is the text file that contains oracle instance parameters and it can be
changed by text editor such as vi or notepad++.

Spfile is the Server parameter file.It is the binary file and only can be change by using alter system
command.This feature introduced in 9i.When instance start up,it search for spfile by default,if no
spfile is found then it will use pfile.

Note:By default oracle will use spfile when instance starts,but you want to use the pfile instead of
spfile then you must specify like below.

startup pfile='<Path of the pfile>'

How to check instance is using pfile or spfile:


==============================================

SQL> show parameter spfile

NAME TYPE VALUE


------------------------------------ ----------- ------------------------------
spfile string +DATA_DG/RAMESH1/spfileramesh1.ora

Note:Value column will be empty if instance is using pfile instead of spfile.

How to create spfile from pfile:


-------------------------------

create spfile='+DATA_DG/RAMESH1/spfileramesh1.ora' from pfile;

How to create pfile from spfile:


--------------------------------

SQL> create pfile='/tmp/initramesh11.ora' from spfile;

File created.

SCOPE clause of alter system :


==============================

SCOPE = SPFILE // The change is applied in the server parameter file only and
No change is made to the current instance and change take place after instance got restarted.

SCOPE = MEMORY // The change is applied in memory only. Changes will gone when we restarted
the instance.

SCOPE = BOTH // The change is applied in both the server parameter file and memory . (current
and next startup)

Determine the parameter dynamic or static:


=========================================

example:
----------

SQL> set lines 300


col name for a60
select name, ISSYS_MODIFIABLE from v$parameter
where name='sga_max_size';SQL> SQL> 2

NAME ISSYS_MOD
------------------------------------------------------------ ---------
sga_max_size FALSE

SQL> select name, ISSYS_MODIFIABLE from v$parameter where name='sga_target';

NAME ISSYS_MOD
------------------------------------------------------------ ---------
sga_target IMMEDIATE

Change a parameter value:


========================

SQL> alter system set db_recovery_file_dest_size=7g scope=both sid='*';

System altered.

SQL> ALTER SYSTEM SET log_archive_dest_1='location=+FRA_DG/RAMESH1/db/archivelog'


SCOPE=memory sid='*';

System altered.

SQL> show parameter log_archive_dest_1

NAME TYPE VALUE


------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=+FRA_DG/RAMESH1/db/archivelog

You might also like