In this tutorial we learn what is pfile and what is spfile in oracle database.What is the difference between spfile and pfile in Oracle database .How to change these parameter file .
Both spfile and pfile are the parameter files in oracle database which is store the instance parameter value such as (SGA_MAX_size,Shared_pool,listener name ,db name ,db_unique name and so on ) and it is used and initialized defined parameter when startup the oracle instance.
What is PFILE
PFILE stand for parameter file .It is normally a text file we can edit this file using the normal editor.If we can use linux system then we can edit it vi editor. This is not recommended by the oracle .The default location is $ORACLE_HOME/dbs/init(SID).ora.we can not take any kind of backup of this PFILE using RMAN.
We can startup the oracle instance using the pfile using the below command.
SQL> STARTUP PFILE= init(sid).ora;
What is SPFILE
SPFILE stands for server parameter file. This is binary file.we can not edit this file using normal editor. If you edit this file using normal editor it will be corrupted. So we can change the parameter value usong “ALTER SYSTEM” only. Now a days it is recommended by oracle and we can take RMAN backup of SPFILE.The default location of the SPFILE is $ORACLE_HOME/dbs/spfile(sid).ora.
If we want to start oracle instance using spfile then we execute the below command.
SQL>STARTUP;
How to check server is using spfile or pfile
You can check server is using spfile or pfile using the below command.
SQL>SHOW PARAMETER SPFILE;
If this command gives the output then it will be confirm that server is running using spfile .If no rows comes after executing the above command then it will be confirm that server is not running with spfile.
How to create file from spfile and vice versa
We can create pfile from spfile using the below command.
SQL>CREATE PFILE = ‘$ORACLE_HOME/dbs/init(sid).ora’ from SPFILE;
and it is useful when mistakenly spfile is corrupted we can create spfile from this pfile also using the below command.
SQL>CREATE SPFILE FROM PFILE =’$ORACLE_HOME/dbs//init(sid).ora’
In this tutorial we have learned about pfile and spfile of Oracle.
0 comments:
Post a Comment