about pfile and spfile

-

 

Oracle database : Basics about pfile and spfile

What is a parameter file

Parameter file is a text or binary to store the database initialization parameters. The oracle instance reads the parameter file during startup which are then used to control the behavior of database instance and many other aspects as well. Such as : memory allocation (SGA and PGA), startup of optional background processes, Setting of NLS parameters etc. There are 2 types of parameter files, namely :
 1. pfile (parameter file) – older way [ not recommended by oracle ]
2. spfile (server parameter file) – newer way [ recommended by oracle ]

spfile was introduced starting from oracle 9i, untill that time text based pfile was used to store database initialization parameters.

pfile V/s spfile

pfile spfile
Text file Binary file
Parameters in pfile can be edited using any text editor spfile can not be edited using a text editor. Instead it can only be altered using the “”ALTER SYSTEM”” command
Default location of pfile – $ORACLE_HOME/dbs/init[SID].ora where [SID] – is the name of the instance. Default location of spfile – $ORACLE_HOME/dbs/spfile[SID].ora where [SID] – is the name of the instance.
The RMAN utility can not take backup of a pfile The RMAN utility can take backup of a spfile.

How to check if SPFILE or PFILE is used

Run the below command against the database you want to check :

SQL> show parameter spfile

If the query returns no rows – pfile is used. If the query returns any value with a filename(with its path) then SPFILE is used by current running instance. For example: 

SQL> show parameter spfile 

NAME TYPE VALUE 
------------------------------------ ----------- ------------------------------ 
spfile string ?/dbs/spfile@.ora

The above example shows that spfile used by the running instance.

How to set parameters when using spfile

The basic syntax for altering the parameters using spfile is :

SQL> alter system set [parameter=value] sid='[sid|*]' scope=[memory|spfile|both];

The SCOPE clause specifies the scope of a change for static and dynamic parameters as described below:

SCOPE Allowed parameter changes Description
SPFILE static and dynamic changes are recorded in the spfile, to be given effect in the next restart
MEMORY dynamic changes are applied in memory only
BOTH dynamic changes are applied in both the server parameter file and memory

For dynamic parameters, we can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions.

 

Source: geekdairy

Comments

Post a Comment

Popular posts from this blog

Oracle Cloud Infrastructure(OCI) - Part2

-
Image
In my earlier post (OCI -Part1 ), we have gone through OCI basic details, let's dive deep more into it. Before that, we need to u nderstand the following concepts and terminology to help you get started with Oracle Cloud Infrastructure.  Note: OCI is re-branding of Bare Metal Cloud Service (BMCS) BARE METAL HOST Oracle Cloud Infrastructure provides you control of the physical host (“bare metal”) machine. Bare metal compute instances run directly on bare metal servers without a hypervisor. When you provision a bare metal compute instance, you maintain sole control of the physical CPU, memory, and network interface card (NIC). You can configure and utilize the full capabilities of each physical machine as if it were hardware running in your own data center. You do not share the physical machine with any other tenants. REGIONS AND AVAILABILITY DOMAINS  Oracle Cloud Infrastructure is physically hosted in regions and availability domains. A region is a localized geographic area, an...
Read more

Script to display the SQL text for a specific SQL_ID

-
--> Purpose:    Script to display the SQL text  for a specific SQL_ID. -->             It returns the SQL_TEXT. --> Parameters: sql_id --> To Run:     @sqltext.sql sql_id --> Example:    @sqltext.sql 27b3qfm5x89xn --> --> Copyright 2020@The Ultimate SQL Tuning Formula set echo off set define '&' set verify off define _sql_id=&1 set verify off set feedback off set linesize 200 set heading on set termout on col sqltext format a100 word_wrapped select  sql_text as sqltext from gv$sqltext where sql_id = '&_sql_id' order by piece; **************************************************************************************** --> Purpose:    Script to display the SQL text  for a specific SQL_ID. -->             It returns the SQL_TEXT. --> Caution:  ...
Read more

How to Change Your EnterpriseOne Environment Color

-
Image
Sometimes users start working and then forget which EnterpriseOne environment they are logged into. Can you tell which environment you are logged into? Luckily, there is a new UDC that allows you to change your EnterpriseOne environment color. You can now set up environments to be color-coded for when you need a clear visual distinction as to which environment you are logged into. This enables you to quickly identify the non-production environments by color. The new UDC is 98/CO and is available in EnterpriseOne Tools Release 9.2.4 with Applications Release 9.1 and 9.2. The image below shows the default EnterpriseOne environment color. However, you can easily change the color to reflect different environments. For example, DV could be red, PY could be green, and PD could be yellow, as shown below. To do so, just add a new row in the User Defined Codes section, enter the EnterpriseOne environment name and the HEX value of the color you want the environment to be, and ...
Read more