Database Files -- Cont...

Long time but lets begin !!!


Control File:The Control file is another very Important file in the Database.Without Control file,the database will not get started.You can consider Control File as the Brain of the Database which contains Information about :

• Name of the Database
• The timestamp when Database was created
  
• Name and Location of the Data files and Redo Log files
• Tablespace Information
• Archived Log Info

In Short complete info about the Database.

Imp Points to Keep in Mind:

• Control File is a binary file.It is accessible only to Oracle and can be modified/written to by the same.No user/DBA can edit it.
• Several BG(background) Processes keep on updating/writing to the Control file.Hence Availability of the same is very Important.
• Each control file is associated with only one Oracle database.
• The Control file doesn't verify the existence of Datafiles and Online Redo Log files.
• The Control file plays a very important role in Database Recovery as it contains info about Datafiles created/Dropped,Checkpoint Info (CKPT--- will discuss later)
• It is always advisable to take a backup of your Control File when you are making physical changes to your Database i.e while using "Alter Database" commands.
  

Control File Multiplexing:Oracle Allows you to prevent Single point of failure by allowing you to maintain multiple copies of the same Control File on different disks.Thus mulitple Control File copies get written simultaneously for the Same Database.
You will be thinking what would happen if all the Control Files are lost or damaged?

Well in such a case the Instance will abort and we need to start Media Recovery.This is ofcourse a Tedious task . So make sure to preserve your Control Files.
By this you would have surmised the importance of Control File.

Well now Lets talk about our next file which is Redo Log file.

Online Redo Log files:Well have you heard of a Life Line? Yes...
Hmm Oracle too Provides you with a Life Line in case you mess up with your Database.
Online Redo Log files is for You.

This is the Most Imp Structure when it comes to Database Recovery and Instance Failover.

Redo Log files one or more in number form a redo log group.Thus there may be one or more Redo log files in a redo log group and two or more Redo Log groups.
Redo Log files record the details of Data block which is modified.For eg : Say we have a table emp_details in which you are modifying the salary of the emp, then the datablock corresponding to this value of the table will be modified.This info is stored in the Redo Log file.When required you can recontruct the changes made to the Database using redo log entries.
As the name suggests it stores changes made to the data so that you can "redo" those when needed.

Structure and Working :

Redo Log Groups are circular buffer in which data is written to in a circular fashion in the sense that once all files of one Redo Log group are exhausted, redo data is written to files of the next redo log group and then again back to the first one (in case there are 2 redo log groups).

This is called a Log Switch.Switching from one Log group to the other.

Each redo log group is identified by a Log Sequence Number that is overwritten when the log is reused.

Now you would have realised Why we need at least 2 log groups.So that when one redo log group is full and needs switch there is another to take over from it.

LGWR is the Process which writes data from Redo Log Buffer to the Redo Log files.

I will discuss the remaining files in my next post.Besides I will also discuss Background Processes one by one.

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.