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!!!!

SGA--- Contd..

2)DB Buffer Cache:When we fire a query for retrieving data or updating the same,the data has to be retrieved from Data file and updated in the same.However this would result in huge overhead in case of frequently accessed data.Hence to avoid this and to have faster retrieval and update of data,we have memory cache component known as "Database Buffer Cache"in the SGA.When a query is fired for the first time,the block is read from the Data file and a copy of the same is stored in the DB Buffer Cache.The next request for the same data is satisfied from the DB Buffer Cache.This saves a lot of time and makes execution faster.

• DB Cache follows LRU algorithm to remove the aged out/least recently used DB blocks.
• The size of each Buffer in the DB Cache is equal to the size of Oracle Block(Which may consist of multiple OS Blocks) and is defined by Parameter : "DB_BLOCK_SIZE".

3)Redo Log Buffer Cache :What happens when you modify the Data Blocks?What would happen when You realise after multiple updates to a table that actually those updates were meant for a different table.You have deleted certain entries and inserted some new.What to do in such a scenario?Well the Developers of Oracle DB Server knew that we were humans and bound to make mistakes.Hence they provided us with Redo Log Buffer Cache.This cache stores all the modifications made to the Data Blocks for us to have an option to redo all the changes made if need be and return to the Original State.

• The Redo Log Buffer Cache is actually a circular buffer with each entry known as a "Redo Entry".The entries get inserted in a circular fashion.
• Sized by Parameter : LOG_BUFFER.

You may be thinking what happens when the Buffer is full?
Well we will discuss this when we talk about "LGWR" Background Process.

4)Large Pool:The Large pool acts as a supplement to the Shared Pool.It actually relieves the Shared Pool of the burden by volunteering to store several important Information.It is used for storing Shared server session Information, allocates memory for IO,backup and recovery Processes.

• Large Pool is an optional Memory area
• Large Pool is used when parameter "PARALLEL_AUTOMATIC_TUNING" is set to true otherwise Shared Pool gets this memory.
• Sized by Parameter "LARGE_POOL_SIZE".

5)Java Pool :This again is an Optional Memory area and required when we are installing and using Java.

• Java Pool is used for Parsing of Java Commands.
• Sized by parameter "JAVA_POOL_SIZE".

I will discuss the Different Database files and their importance in my next post.

SGA --- Shared Pool

SGA is dynamic memory area allocated during Instance Startup.
Parameter for SGA sizing : SGA_MAX_SIZE

Note :You will be more familiar with the Parameters when I will discuss Parameter File and type of Parameter File.Till then dont worry if you Stumped by these Parameters.Take them to be variables defining some values used by Oracle DB Server.

Now let us discuss the importance and functionality of each of the memory sub components of SGA:

1) Shared Pool :
Parameter for Shared Pool sizing : SHARED_POOL_SIZE
The Shared Pool basically consists of two sub memory areas :

i)Library Cache: This Cache area contains the most recently used SQL(Structure Query Language) and PL/SQL(Programming Language SQL) statements.As such we have faster retrieval of query results from the cache than finding data from the Database.The Lib Cache basically stores the statements and their corresponding Excution Paths(i.e sequence of actions server process follows to execute a particular query).Hence if the Query is found in the Cache there is no need to again generate the Execution path and this saves time.However if the entry is not found in the Lib Cache, Execution Path needs to be created fresh for that statement.

• As the Cache size is limited, it purges/removes the entries (sql,pl/sql stmts) based on LRU (Least Recently Used) Algorithm.
• The sizing of Lib Cache depends on Shared Pool sizing.Memory is allocated when a statement is parsed.
  
• The Lib Cache internally consists of Shared SQL area(For Parsing and creating Execution path for SQL statements) and Shared PL/SQL area(For Parsing and creating Execution path for PL/SQL statements).
  

ii)Data Dictionary Cache :DD Cache contains cached information about the Database.
As a DBA , you need to frequently access tables and other database objects.For this, the access to these objects should be defined.What files you can access and what you cannot should be defined?What privileges you have on a particular table?What are the Datafiles in the Database?What are the segment names, extent locations?

All this information is stored in Data Dictionary(Meta Data) Tables.While processing a request,this information is required and for this the Data Dictionary Tables are read and the info is stored in the DD Cache.

The sizing of the DD Cache depends on the Shared Pool size.

I will discuss on Database Buffer Cache,Redo Log Buffer Cache,Java Pool,Large Pool in my next post.

Oracle Database Server

The Oracle Database Server Consists of :
1)Oracle Instance
2)Physical Database

Let us discuss these components of Oracle Database Server:

1)Oracle Instance : The Instance consists of a Memory Area known as SGA(System Global Area) and the Background Processes.When a DB is started, it is the Instance which gets loaded into the RAM.Hence the SGA is provided as part of RAM.We will be discussing the SGA components and various Background processes in detail.

2)Physical Database : The Physical Database consists of OS level files termed as :

1. Data Files
2. Control File
3. Redo Log Files

Besides these, there are other files like Password File,Parameter File and Archive Log Files.

Pictorial Representation of the same is as below :




I will discuss each of these Memory Structures and Background Processes in detail in my next post.