The Database Files

I shall introduce the Several Database Files now before I take you through the Different Background Processes.

These Files include :
1)Parameter File
2)Control File
3)Data File

Besides these files we will discuss other related Database files like:
4)Redo Log Files
5)Archive Log File
6)Password File

These all are physical OS level files.
I will discuss the Parameter File in detail in this post and continue with the rest of the files in the next.So dont worry, we will go Step by Step.
So all Set.Lets Begin!!!

1)Parameter File : When you start your Oracle Instance, this is the first file to be read.The Parameter file as the name suggests contains the Database Parameters which get initialized when the Instance is first started.For an analogy, take for eg. When you boot your OS, there are boot parameters and other OS parameters with respect to memory allocation and process initiation that are set.On the same lines, when Oracle Instance is started, parameters with respect to your

• Memory allocation(I mentioned some Parameter names when discussing SGA)
• The name of the Database the Instance connects to
• The Instance related Parameters
• The location of Control Files(Very Important)
• Besides Information about Online Redo Log Files and Undo Segment(will discuss later) are set
  

There are 2 types of Parameter Files in the Oracle Database:
i)PFILE(Parameter File):This is the text file which can be viewed and modified by any appropriate OS Editor (For eg : Notepad in Windows, Visual or Vi editor in Unix)
It is named as initSID.ora (where SID stands for System Identifier and is your Instance Name).
The Default location for Parameter files is $ORACLE_HOME/dbs directory.

Imp Note: When you make changes in the PFILE,you need to Bounce the Database i.e, shutdown the Instance and Start it up.Without this the Changes will not get reflected.
While Installation of Oracle Software, the Oracle Universal Installer creates template Files and depending on your need, you can configure and make changes to the Parameter File to create your customized Parameter File.

ii)SPFILE(Server Parameter File): Now that PFILE has a limitation that when you need to bounce Instance for the Changes to take place and you may not want this as your Clients are currently connected and want the Instance to be up and running but making changes to the Parameter File is also important to boost the Performance.
Hmm well dont worry.... SPFILE comes to your Rescue.
The Changes made to the SPFILE get reflected on the run.No need to bounce the Instance.
Its a Binary file and so cant be viewed or edited as in case of PFILE.
Named as : spfileSID.ora
Location is : $ORACLE_HOME/dbs.
Now that I said its a Binary File, you would say that then how do we make changes and view it.
Well there are a couple of methods for that.Choice is yours:

• Create a PFILE from SPFILE.Modify PFILE as per your requirements.Create SPFILE from that PFILE.Looks a bit Tedious!!! But is shadowed by its Benefits...

On sqlplus connect "/as sysdba"
create PFILE='File Name with Path' from SPFILE='File Name with Path';
Now PFILE can be modified
create SPFILE='File Name with Path' from PFILe='File Name with Path';

And you are done!!!!

• Parameters can be viewed by querying the view V$SPPARAMETER( only for Viewing)

NOTE:Now you would be thinking, if we have both SPFILE and PFILE and I told that Instance uses Parameter file during Starup, then which file it will use ?????

This is the sequence the Server looks for Parameter Files during Startup:

1)spfileSID.ora
2)spfile.ora
3)initSID.ora
4)init.ora

IMP:By Default the Instance Startup uses a SPFILE.If you want to use a specific PFILE specify it during startup as follows:

SQL>startup PFILE='File Name with Path'

I think now Parameter File will be Crystal Clear.Open it and view the contents now.

In my next post, I will discuss the remaining Files.
Happy Learning!!!!