This article describes PFILE and SPFILE in Oracle Database in details from where you will know
Simply, PFILE and SPFILE are the initialization parameter file which contains some parameters like data_transfer_cache_size, Java_pool_size, sga_target, large_pool_size etc. When we start the machine, the initialization parameter file is read and the characteristics of the Instance are established by parameters specified within the initialization parameter file.
There are two types of initialization parameters files:
SPFILEs are available in Oracle 9i and above. All prior releases of Oracle are using PFILEs. By default, Oracle database initializes with spfile, if not exit spfile, then it initializes with PFILE.
Default file location is $ORACLE_HOME/dbs. You can see SPFILE location using commands:
SQL > SHOW parameter SPFILE;
This will return value as like:
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/19.0.0 /dbhome_1/dbs/spfileoradb.ora
if return value NUL, than, database is running with PFILE.
SPFILE is where
PFILE is where
The pfile and spfile are created using the SQL command CREATE SPFILE statement; CREATE SPFILE statement creates SPFILE and PFILE in default location. This requires connecting as SYSDBA.
Connect system/manager as sysdba;
To create SPFILE without specifying filenames:
SQL> CREATE SPFILE FROM PFILE;
To create PFILE without specifying filenames:
SQL> CREATE PFILE FROM SPFILE;
Because no names are specified for the files, an operating system-specific name is used for the initialization parameter file, and it is created from the operating system-specific default server parameter file.
If you want to create other location, than command will as like:
PFILE is specified
SQL> CREATE PFILE= '$ORACLE_HOME/dbs/test_init.ora' FROM SPFILE;
SPFILE is specified
SQL> CREATE PFILE FROM SPFILE='$ORACLE_HOME/dbs/test_spfile.ora';
When SPFILE and PFILE names are specified
SQL> CREATE SPFILE='$ORACLE_HOME/dbs/test_spfile.ora' FROM PFILE='$ORACLE_HOME/dbs/test_init.ora';
SQL> CREATE PFILE='$ORACLE_HOME/dbs/test_init.ora' FROM SPFILE='$ORACLE_HOME/dbs/test_spfile.ora';
Since PFILE is a text file, you can change PFILE using any text editor like vi. When you open this file, you will see as like bellows:
oradb.__data_transfer_cache_size=0 oradb.__db_cache_size=9395240960 oradb.__inmemory_ext_roarea=0 oradb.__inmemory_ext_rwarea=0 oradb.__java_pool_size=0 oradb.__large_pool_size=33554432 oradb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment oradb.__pga_aggregate_target=3724541952 oradb.__sga_target=11173625856 oradb.__shared_io_pool_size=134217728 oradb.__shared_pool_size=1577058304 oradb.__streams_pool_size=0 oradb.__unified_pga_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='19.0.0' *.control_files='/u01/app/oracle/oradata/ORCL/control01.ctl','/u01/app/oracle/oradata/ORCL/control02.ctl' *.db_block_size=8192 *.db_domain='mfi.com' *.db_name='orcl' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=oradbXDB)' *.enable_pluggable_database=true *.local_listener='LISTENER_ORADB' *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_target=3548m *.processes=320 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=10641m *.undo_tablespace='UNDOTBS1'
After changing, save and quit and it will be modified. So changing PFILE is very easy.
if you want to start database with PFILE, you need to shutdown database at first and startup database with PFILE
startup PFILE= '$ORACLE_HOME/dbs/test_init.ora'
But you need to change SPFILE, you need to execute ALTER SYSTEM command
ALTER SYSTEM SET MEMORY_TARGET = 5000M SCOPE=SPFILE;
Parameter Scope
The SCOPE value of the ALTER SYSTEM SET are SPFILE/MEMORY/BOTH where
A parameter value can be reset to the default using the following command.
ALTER SYSTEM RESET OPEN_CURSORS SID='*' SCOPE='SPFILE';
In a Real Application Cluster (RAC) environment node-specific parameters can be set using the SID parameter.
ALTER SYSTEM SET OPEN_CURSORS=500 SID='SID1' SCOPE='SPFILE';
To show parameter, you need execute SHOW PARAMETER command
show parameter process // it will show all process with value show parameter memory_target; // it will show memory target velue show parameter open_cursors; // it will show open_cursor value