Initialization Parameter File (Oracle Database) PFILE, SPFILE

Initialization Parameter File :

In order to start an instance and open the database, you must connect as SYSDBA and enter the STARTUP command. The Oracle Server will then read the Initialization Parameter file  and prepare the instance according to the Initialization Parameter contained within the file.

Some Specific Important Facts about Initialization Parameter file :

• Entries are specific to the Instance being Started
• Two types of Parameters :
• Explicit : Having an entry in the Initialization parameter file
• Implicit : NO Entry within the file, But Assuming the Oracle Default Value.
• Multiple Initialization parameter file can exists
• Changes to entries in the file take effect based on the type of initialization parameter file used :
• Static Parameter File, PFILE
• Persistent Server Parameter File, SPFILE

To Start an Instance the Oracle server reads the Initialization Parameter File. Two Types of Initialization Parameter files exists :

• Staitic Parameter File, PFILE, Commonly referred to as initSID.ora.
• Persistent Server Parameter File , SPFILE , commonly Referred to as spfileSID.ora

Initialization Parameter contents :

• A list of Instance Parameters
• The name of the Database the instance is associated with
• Allocation for memory structures of the System Global Area (SGA)
• What to do with Filled online redo Log Files.
• The names and  location of control files
•  Information About UNDO segments.

PFILE :  initSID.ora

The PFILE is a text file that can be Maintained using a standard operating system editor. The PFILE is read only during instance startup. If the file is modified , the instance must be shut down and restarted in order to make the new parameter values effective.

Characteristics of PFILE :

• Text File
  
• Modified With an operating system editor
• Modification made manually
• Changes take Effect on the next startup
• Only opened during Instance Start UP
• Default Location is $ORACLE_HOME/dbs

By default, The PFILE located in the $ORACLE_HOME/dbs directory and name initSID.ora.

Creating a PFILE :

How to Create PFILE ?

• PFILE created from a sample init.ora file
• Sample Installed by the Oracle Universal Installer
• Copy Sample using operating system copy command
• Uniquely Identified by Database SID

           cp init.ora $ORACLE_HOME/dbs/initdba01.ora

• Modify the initSID.ora
• Edit the Parameters
• Specific to Database needs

A sample init.ora file is created by the Universal Installer during installation. This Sample init.ora file can be used to create an instance specific initSID.ora. A text editor can be used to modify the parameter within the initSID.ora file.

PFILE Example : Here we will review the example of init.ora file that how it looks like and what parameter are added by default. Below is the my Test system default init.ora file.

cat init.ora

# Example INIT.ORA file
.
db_name='ORCL'
memory_target=1G
processes = 150
audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='<ORACLE_BASE>'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'

Now Let's see if Need to add a parameter manually in PFILE  then We need to review below set of rules that we have to fallow it.

• Specify the values in the fallowing format like Keyword = value .
• The server has a default value for each parameter. This Value may be operating system Dependent, depending on the parameter.
• Parameter can be specified in any order, although there are some exceptions
  
• Comment line begin with the # Symbol.
• Enclose parameters in double quotation marks to include character literals.
• Additional file can be included with the keyword IFILE.
• If case is the significant for the operating system , then it is also significant in filenames.
• Multiple values are enclosed in parentheses and separated by commas.

Note : The Best Practice is ,For Addition of Parameter in PFILE , First List the Parameter alphabetically or Group them by functionality and add it in PFILE.  The PFILE varies from Instance to Instance and does not necessarily look like the example given above.

SPFILE : spfileSID.ora :

An SPFILE  (" Introduce in Oracle 9i ") is a binary File. This file is not meant to be modified manually and must always reside on the server side. After the file is created it is maintained by the oracle server. If Modified manually, This makes SPFILE useless.  

The SPFILE provides the ability to make changes to database persistent across Shutdown and Startup. It also provide the ability to self-tune parameter values, which are recorded in the file.

RMAN Support for backing UP the Initialization Parameter file is possible because the SPFILE resides on server side. By Default,  the files is located in  " $ORACLE_HOME/dbs "  and has a default name in the format of spfileSID.ora.

Characteristics of SPFILE :

• Binary File
• Maintained by the Oracle Server
• Always resides on the server side
• Ability to make changes across shut down and start up
• Can Self-Tune parameter value
• Can have Recovery manager Support backing Up to the initialization parameter file.

Creating an SPFILE :

• Created from a PFILE file.
  

CREATE SPFILE = '$ORACLE_HOME/dbs/spfileORCL.ora'  FROM PFILE = '$ORACLE_HOME/dbs/initORCL.ora ';

Where

- SPFILE-NAME : SPFILE to be created

- PFILE- NAME :  PFILE creating the SPFILE

• Can be executed before OR after instance start up.

An SPFILE is created from PFILE using  " CREATE SPFILE " Command. This Command requires the SYSDBA previlege to execute. This command can be executed before OR after instance start UP.

Syntax : 

CREATE SPFILE = ' SPFILE - NAME ' FROM PFILE = 'PFILE-NAME';

Where

- SPFILE-NAME : Name of the SPFILE to be created

- PFILE-NAME :Name of the PFILE being used  to create the SPFILE. The PFILE Must be available on the server side

If the SPFILE-NAME  and PFILE-NAME are not included in the syntax, Oracle will use the default PFILE to generate an SPFILE with a system generated name.

SQL>  CREATE SPFILE FROM PFILE;

The contents of an SPFILE can be exported into a PFILE.

SQL> CREATE PFILE FROM SPFILE;

The PFILE is created as a text file on the server side. This command can be exeuted either before or after instance start up. This Provides an easy way to view the SPFILE and make modifications by :

• Exporting the SPFILE to a PFILE
• Editing the PFILE
• Re-creating the SPFILE form edited PFILE

Exporting an SPFILE to a PFILE can also serve as another alternative to creating a backup of the server parameter file.

Note : With Oracle9i Onwards Relase of oracle , RMAN can also back up server parameter file.

v$SPPARAMETER  :

By Using this VIEW, There are several options for Viewing the parameter settings within the SPFILE.  V$SPPARAMETER is another source for presenting and viewing Contents of the SPFILE.

Modifying Parameters In SPFILE :

• Changing Parameter values

ALTER SYSTEM SET undo_tablespace = UNDO2;

• Specifying temporary or persistent cahnges

ALTER SYSTEM SET undo_tablespace = UNDO2  SCOPE=BOTH

• Deleting or Resetting values

ALTER SYSTEM RESET undo_suppress_errors SCOPE=BOTH SID='*';

The ALTER SYSTEM SET Command used to change the value of instance Parameters.

ALTER SYSTEM SET  parameter_name = parameter_value [Comment 'text'] [SCOPE = MEMORY | SPFILE | BOTH] [SID = 'sid'|'*']

Where ,

Parameter_name : Name of the Parameter to be changed

Parameter_value : Value the parameter is being changed to

COMMENT : A comment to be added into the SPFILE next to the parameter being altered.

SCOPE : Determines if change should be made in memory, SPFILE , OR in both areas

MEMORY : Changes the parameter value only in currently running instance

SPFILE : Cahnges the parameter value in SPFILE only

BOTH : Changes the parameter value in the currently running Instance and the SPFILE

SID: Identifies the ORACLE_SID for the SPFILE being used

'sid' : Specific SID to be used in altering the SPFILE

'*' : Uses the Default SPFILE

The ALTER SYSTEM RESET Command is used to delete or Revert to the default value.

ALTER SYSTEM REST parameter_value [SCOPE = MEMORY | SPFILE | BOTH] [SID = 'sid' | '*']

Example :

SQL> ALTER SYSTEM RESET undo_suppress_errors SCOPE = BOTH  SID = 'ORCL';

There are several ways to remove a parameter from SPFILE :

• Set the parameter back to its default value to simulate deleting using ALTER SYSTEM SET .
  
• Re-Create the SPFILE using  CREATE SPFILE FROM PFILE.
  
• Use ALTER SYSTEM RESET to delete the parameter from the SPFILE.
  

Thank You !!

Oracle Database Logical Structure

Logical Structure :

 In Oracle Database Logical structure  gives the mandate or direction  like, how the physical space of database is used. Oracle Database Allocates logical space for all data in the database.

The Logical units of Database Space allocation are Data Blocks, Extents, Segments, Tablespaces.

A Logical Structure hierarchy exists as fallows :

• An Database contains at least one tablespace.
• A tablespace contains one or more  segments.
• A  Segment is made up of extents.
• An Extent is made up of logical Blocks.
• A Block is the smallest Unit for read and Write Operations.

The oracle database architecture includes logical and physical structures that make up the database.

• The Physical structure includes the control files, online redo log files, and Data files that make up the database.
• The Logical Structure includes  Tablespaces, Segments, Extents, and Data Blocks.

The Oracle Server enables fine- grained control of disk space use through tablespace  and logical storage structures, including segments, Extents and Data blocks.

Tablespaces:  

The data is an Oracle Database is stored in tablespaces.

• An Oracle database can be logically grouped into smaller logical areas of space known as tablespaces.
• A tablespace can belong to only one database at a time.
• Each tablespace consists of one or more operating system files, which are called data files.
• A tablespace may contain one or more segments.
• Tableapaces can be brought online while the database is running.
• Except for the SYSTEM tablesoace Or a tablespace with an active UNDO Segments, tablespace can be taken offline, Leaving the database running.
• Tablespaces can be switched between Read/Write and Read- Only Status.

Data Files (Not a Logical Structure ) :

• Each tablespace in an Oracle Database consist of one or more files called data files. These are Physical structures that Conforms with the operating system on which the Oracle Server is running.
• A datafile can belongs to only one tablespace.
• An Oracle Server Creates a datafile for a tablespace by allocating the specified amount of disk space plus a small amount of overhead.
• The Database Administrator can change the size of a datafile after its creation or can specify that a datafile should dynamically grow as objects in the tablespace Grow.

Segments :  

• A segment is space allocated for a specific Logical storage structure within a tablespace.
• A tablespace may consist of one or more segments
• A segment cannot span tablespaces, However, a segment can span multiple datafiles that belongs  to the same tablepsace.
• Each segment is made up of one or more Extents

Extents :

Space is Allocated to a segment by Extents.

• One or more Extents make up a segments.
• When a segment is created, it Consist of at least One Extent.
• As the segment Grows, Extent are added to the segment.
• The DBA can manually add extents to a segment
• An Extent is a set of Contiguous Oracle Blocks.
• An Extent cannot Span Data files, and therefore, It Must exists in one Datafils.

 

Data Blocks :

The Oracle Server manages the storage space in the oracle datafiles in units called Oracle Blocks Or Data Blocks.

• At the finest level of  granularity, The data in an Oracle Database is stored in data Blocks.
• Oracle Data Blocks are the smallest unit of storage that the Oracle Server can Allocates, read, Or Writes.
• One Data block Corresponds to one or more operating system blocks from an existing Data file.
• The Standard Data Block size for an Oracle Database is specified by the DB_BLOCK_SIZE  initialization Parameter when database  is created.
• The Data Block Size should be a multiple of the operating system block size to avoid Unnecessary I/O.
• The Maximum Data Block Size  is dependent on the Operating System. 

 

Thank you !!