Md Saidul Haque PFILE and SPFILE in Oracle Database and How can you create PFILE and SPFILE?
This article describes PFILE and SPFILE in Oracle Database in details from where you will know
- What is PFILE and SPFILE?
- File Location
- What is difference between PFILE and SPFILE?
- Advantages of SPFILEs
- How to create PFILE and SPFILE?
- How can we modify PFILE and SPFILE?
- How to SHOW Parameter in SPFILE?
What is PFILE and What is SPFILE?
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:
- Server Parameter File (SPFILE)
- Text Initialization Parameter File(PFILE)
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.
File Location
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.
What is difference between PFILE and SPFILE
SPFILE is where
- Binary file - It must not be edited manually. it can only be modified(read/write) with db server: "ALTER SYSTEM SET" command.
- Resides on server
- Default file name is spfile<SID>.ora
PFILE is where
- text file that must be updated with a standard text editor like "notepad" or "vi".
- Client side file and resides on local machine as well server
- Default file name is init<SID>.ora
Advanntage of SPFILE
- An SPFILE can be backed-up with RMAN (RMAN cannot backup PFILEs)
- Reduce human errors. The SPFILE is maintained by the server. Parameters are checked before changes are accepted.
- Eliminate configuration problems (no need to have a local PFILE if you want to start Oracle from a remote machine)
- Easy to find - stored in a central location
- Editing an SPFILE will corrupt it, and you will not be able to start your database anymore.
How to create PFILE and SPFILE?
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';
How can we modify PFILE and SPFILE
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
- BOTH - (Default) The parameter takes affect in the current instance and is stored in the SPFILE.
- SPFILE - The parameter is altered in the SPFILE only. It does not affect the current instance.
- MEMORY - The parameter takes affect in the current instance, but is not stored in the SPFILE.
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';
How to show Parameter in 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
