Oracle Datafiles

It's not at all easy to learn a technology completely, but we will try to learn oracle as we can. Let us see oracle datafiles, their structure, how to manage them and how they work.

Datafiles are physical structure of oracle database. Oracle stores your important data in these datafiles. These are located on hard disk in directory $ORACLE_HOME/oradata/SID along with others important files like redo log files, controlfiles etc.

One or more physical datafiles make a tablespace. It means a datafile can have only one tablespace, but a tablespace can have more than one datafiles. Oracle creates a datafile by allocating sufficient amount of disk space plus the overhead required for the file header.

When the datafile is created, the associated disk space is formatted, but does not contain any user data. Oracle reserve this space to use it in future.

When you create database with DBCA, oracle allocates first datafile to system tablespace as it is most important tablespace in database. We can explicitly allocate datafiles to tablespace as and hen we want. Have a look on tablespace and datafiles structure in oracle:


The data related to related schema in tablespace is physically stored in datafiles. Schema object does not corresponds to a specific datafile, but a datafile is repository for the schema objects. A datafile can be allocated to multiple schema, but it is not necessary that a schema object (say table) will reside only in that datafile as shown in above example.

Datafiles contain first block as it's header, which contains information about space allocated, space available, blocks present etc. in datafile. It also contain other important information such as block size, tablespace, file size, timestamps etc. When database opens, oracle checks whether information in datafile header matches with that of control file. When user request some data, oracle checks it first in buffer cache in SGA and if it is not present there then oracle reads it from appropriate datafile. CKPT process writes in datafile header the current SCN and log sequence number.

You can check information about datafiles by using v$datafile. You can take datafiles offline any time for you purpose. For this purpose execute following command:

sql> alter database datafile 'data/file/path.dbf' offline;

and online by executing following command

sql> alter database datafile 'data/file/path.dbf' online;

but remember, when taking datafile online it may ask for media recovery, then you can do

sql>alter database recover datafile 'data/file/path.dbf';

That's all what we can discuss about datafiles now. I am sure as we go on, we ill definitly get to know more about them.

enjoy...

SGA Memory Structures

We are learning oracle SGA since last couple of posts, and now we are very much into it. Let's have discussion on memory structures of oracle SGA(System Global Area).

As we discussed that oracle SGA is consist of several memory structures and background processes. We discussed background processes in last post, let's now have look at memory structures of SGA.

SGA is consist of following memory structures:
1. Database buffer cache
2. Shared pool
3. Redo log buffer cache
4. Java pool
5. Large pool
6. Streams pool




1. Database Buffer Cache:

The database buffer cache is that region of SGA which holds the copy of data blocks read from datafiles. The buffer cache is shared among all connected user of database. When user try to retrieve data from database, server process read it first from datafiles and place a copy of recently used data blocks in database buffer cache. It is managed by least recently used (LRU) algorithm.Data updation is done in this area. There are three types of buffer in buffer cache:

A. Dirty buffer:- Dirty buffer are those which are changed or modified, and need to be written to the disk. This task is done by DBWR, and these dirty buffer are flushed out to datafiles.

B. Free buffers:- Free buffers are those which do not contain any data and ready to use. When server process fetch any data from datafiles, it si stored in free buffers.

C. Pinned buffers:- Pinned buffer are those which are currently being used or explicitly retained for future used.

2. Shared Pool:

The shared pool contains most recently used SQL and PL/SQL statements and data definition, data dictionary, character set information. It is further divided into:

A. Library cache: The library cache includes the shared SQL areas, private SQL areas,PL/SQL procedures and packages, and control structures such as locks and library cache handles. The shared SQL area contains recently executed SQL commands and their execution plans and parse tree where private SQL area contains values for bind variables and run time buffers.

PL/SQL program units are processed the same way as SQL statements by Oracle. When a PL/SQL program unit is executed, the code is moved to the shared PL/SQL area while the individual SQL commands within the program unit are moved to the shared SQL area. Again, the shared program units are maintained in memory with an LRU algorithm.

B. Data Dictionary Cache:

The data dictionary cache is collection of database tables and views containing metadata about database, its structure, its privileges and its users. Oracle access the data dictionary during the parsing of SQL statements. The data dictionary cache is also known as the row cache because it holds data as rows instead of buffers(which hold entire blocks of data).

3. Redo Log Buffer Cache:

The redo log buffer cache is circular buffer in SGA which holds information about changes made to database. It contains the redo entries which contains the information to redo the changes made to database in case of failure. Changes are made to the database through any of these commands:INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP.

4. Java Pool:

The java pool services parsing requirements for java commands. It is required if installing and using java.

5. Large Pool:

The Large pool is a voluntary area in the SGA that can be configured by the database administrator to provide large memory allocations for specific database operations such as an Oracle backup or restore. The large pool allows Oracle to request large memory allocations from a separate pool to prevent contention from other applications for the same memory. The large pool does not have an LRU list.

6. Streams Pool:

The streams pool is used when you are using oracle streams. It is used on capture activity. In prior releases of oracle it is assigned 10% from shared pool, which often faces overflow.

Other than this some memory structures are still there, they are as follows:-

1. Program Global Area:

This is another memory structure of Oracle.

(PGA) is the area in the memory that contains the data and process information for one process. This area is non-shared memory. The PGA size is fixed and is dependent on the operating system. The contents of the PGA vary depending on server configuration.

The memory allocated to hold variables, arrays, and other information that belongs to the session is called stake space.PGA is allocated when the process is completed. Unlike the SGA that is shared by several processes, the PGA provides sort space, session information, stack space, and cursor information for a single server process.

2.Sort Area Size:

The memory area that Oracle uses to sort data is known as the sort area, which uses memory from the PGA for a dedicated server connection. For multi threaded server configurations, the sort area is allocated from the SGA. Sort area size can grow depending on the need. The parameter SORT_AREA_RETAINED_SIZE determines the size to which the sort area is reduced after the sort operation. The memory released from the sort area is kept with the server process; it is not released to the operating system.

Oracle Background Processes

Hi folks, actually i was bit busy since last couple of days, so was unable to post on blog.

As post name suggest, we are going to discuss background process that drives oracle for its optimum performance. As the name suggest, these are processes which runs behind the scene, but are the main operating part of oracle database.

Let's have look at them in detail.

As we have discuss, when instance is allocated at RAM (physical memory) means SGA is created. This SGA has some memory structures and background processes. Although memory processes and background processes are interdependent, they holds their own importance too. Some of important background processes and their functions are described in this post.

First we will discuss all the basic BG processes, which are known and most important.

1. PMON:

The process monitor recovers failed process resources. It checks if any user process fails, it cleans up all resources that user process has acquired,and roll back uncommitted transaction and releasing locks. In shared server environment, PMON restarts any failed dispatcher or server process.

2. SMON:

The system monitor recovers after instance crash. It performs crash recovery when crashed instance is start again. It monitors and clean temporary extents. It also merges contiguous areas of free space in datafile, this process is called as coalescing. In RAC in non-failed instance, SMON performs instance recovery of other failed instance.

3. DBWR:

The database writer process writes dirty blocks from database buffer cache to datafiles. Dirty blocks are those blocks which are modified by server process, and need to be flushed out to make room for new blocks in cache.DBWR never ever modify database buffer cache, it is done by server process. DBWR writes to datafiles when space is needed in cache, on commit and when forceful checkpoint is occurred. You can configure more than one DBWR process up to 10.

4. LGWR:

The log writer process writes redo log buffer entries (redo entries) into online redo log files on disk. This is done by LGWR when transaction is committed, every 3 seconds and when DBWR signals LGWR to do so.

5. CKPT:

The checkpoint process regularly initiates a checkpoint, and updates controlfile and datafile header. This process flushes the redo entries by means of LGWR. Uses DBWR to write all dirty buffer to datafiles and updates datafile header.

6. ARCH:

The archive process is optional process. If your database is in archive log mode and automating archiving is enabled, then this process is active. This process writes filled redo log files to archive log location specified by you. It is required while recovering database. LGWR is responsible for starting multiple ARCH process if workload increase. There can be up to 10 archiver processes.

7. MMAN:

The MMAN dynamically adjust the size of SGA components. It is new process added to oracle 10g a a part of "Automatic Shared Memory Management".

These are the basic background processes in oracle SGA. Other than this some background processes are:-

CQJ0,FMON,Pnnn etc.

We will see them in detail later.

Oracle Physical Structure

hi friends,

In last topic we discussed about basic oracle architecture. In this discussion we will discuss physical structure of oracle database. But before that, we have to learn the logical architecture which is involved in physical structure.

confused....???? let's see.

Oracle physical structure which include logical structure is as follows:-

Logical Structures

Oracle database is divided into smaller logical units to manage, store, and retrieve data efficiently. The logical units are tablespace, segment, extent, and data block. The following figure will explain it more clearly. Have a look:



Tablespace

Tablespace is a logical structure which holds other logical structure of database. Tablespace is further broken into segments. segment is further broken into extents. This way tablespace holds the segments , extents and datafiles for a particular purpose. Like we have system tablespace, users tablespace, index tablespace and etc., All tablespace segments and extents for that particular purpose. Tablespace can be named, as we seen in above example.

Segments

A Tablespace is further broken into segments. A segment is used to stores same type of objects. That is, every table in the database will store into a specific segment (named Data Segment) and every index in the database will also store in its own segment (named Index Segment). The other segment types are Temporary Segment and Rollback Segment.

Extents

A segment is further broken into extents. An extent consists of one or more data block. When the database object is enlarged, an extent will be allocated. Unlike a tablespace or a segment, an extent cannot be named.

Data Block

A data block is the smallest unit of storage in the Oracle database. The data block size is a specific number of bytes within tablespace and it has the same number of bytes. the database block consist of multiple operating system blocks.

Now lets have a look at actual physical structure of oracle database. It is a structure which holds your actual data. It consist of some files, which resides on disk. These files are datafiles, control files, redo log files and password file.

Datafiles

Datafiles holds actual data of oracle database.A datafile is a file that correspondens with a tablespace. One datafile can be used by one tablespace, but one tablespace can has more than one datafiles. When ever user request data, oracle retrieve that data from these datafiles with the help of some processes in the SGA (System Global Area). And also manipulated data is written back to these datafiles, so that changed data is available to all users.

Redo Log files

Redo log files are the files that store the redo entries generated by DML statements. It can be used for recovery processes. These files also stores data, but contain only changes made to database. These changes are useful when recovering database. These file can be archived, that is can be copied offline for recovery purpose. this process is called archiving. These files belongs to certain group. We will discuss redo log files in more details later.

Control Files

Control files are used to store information about physical structure of database, such as datafiles size and location, redo log files location, etc. Control files are very crucial for database operation.
An Oracle database cannot be started without at least one control file.It contains (but is not limited to) the following types of information:

* Database information (RESETLOGS SCN and their time stamp)
* Archive log history
* Tablespace and datafile records (filenames, datafile checkpoints, read/write status, offline or not)
* Redo threads (current online redo log)
* Database's creation date
* database name
* current archive log mode
* Log records (sequence numbers, SCN range in each log)
* RMAN catalog
* Database block corruption information
* Database ID, which is unique to each DB

Hope you have got a bit of information about from these blogs. We will definitely discuss all structures in details in our future topics.

Oracle Basic Architecture

Hi all,

As an oracle DBA you must have the very sound knowledge of oracle instance and its architecture.you should be able to differentiate between logical and physical layer of database.

In this article we will discuss the basic architecture of oracle database, what is an instance, what are physical components and logical components of oracle database.

oracle database is consist of two main components
1. Oracle Instance (virtual)
2. Oracle files (physical)

Oracle Instance:- It is an virtual component that is mounted on your system's physical memory (RAM) when oracle services starts. It occupies some space on your RAM and it is the component of the database which deals with the physical layer of oracle database. The following figure shows all virtual components of Instance, lets see them and discuss about them:-


As we have discussed that Instance is virtual and is allocated on our system's physical memory, one may ask 'what is instance made up of?', the answer to this question is, instance is made up of 'System Global Area (SGA)' and 'Background Processes', which communicate with physical files of database to serve request made by user.

It is recommended by oracle that 50%-70% memory should be given to SGA. We will discuss how to allocate size to SGA.Now SGA is consist of following components:

1. Shared Pool:-This is area of SGA which is divided into two parts, one is library cache which stores information about commonly used SQL and PL/SQL statements. means again if the same statement is issued by another user then oracle would not require to access it from datafile (discussed later) and can prevent lot of hard work. It is maintained by Least Recently Used algorithm, means the statement which are not used frequently are thrown out of library cache.

The other part is called as dictionary cache which holds information about definition of objects in database, indexes, users and their privileges, authentication information so that no invalid user can access database etc.

2.Database Buffer Cache:-Buffer cache is used to stores the copies of data block that retrieved from datafiles. That is, when user retrieves data from database, the data will be stored in buffer cache. Its size can be manipulated via DB_CACHE_SIZE parameter in init.ora initialization parameter file.

Just don't think about other buffer pool components specified in the figure, we will discus them later.

3. Redo Log Buffer Cache:-Each DML statement (insert, update, and delete) executed by users will generates the redo entry. What is a redo entry? It is an information about all data changes made by users. That redo entry is stored in redo log buffer before it is written into the database physical structure. To manipulate the size of redo log buffer, you can use the LOG_BUFFER parameter in init.ora initialization parameter file.

you will get complete idea about redo log file in our next discussion 'Oracle Physical Components'.

4. Large Pool:-Large pool is an optional area of memory in the SGA. It is used to relieves the burden place on the shared pool. It is also used for I/O processes. The large pool size can be set by LARGE_POOL_SIZE parameter in init.ora initialization parameter file.


5. Java Pool:-As its name, Java pool is used to services parsing of the Java commands. Its size can be set by JAVA_POOL_SIZE parameter in init.ora initialization parameter file.

Now as we have discussed the SGA components, let us now switch Background process which work behind the scene in instance, in cooperation with SGA:-

1. DBWn:-Database writer (DBWn) process is used to write data from buffer cache into the datafiles (in next discussion). Historically, the database writer is named DBWR. But since some of Oracle version allows us to have more than one database writer, the name is changed to DBWn, where n value is a number 0 to 9.

2. LGWR:- Log writer (LGWR) process is similar to DBWn. It writes the redo entries from redo log buffer into the redo log files.

3. CKPT:- Checkpoint (CKPT) is a process to give a signal to DBWn to writes data in the buffer cache into datafiles. It will also updates datafiles and control files header when log file switch occurs.

4. SMON:- System Monitor (SMON) process is used to recover the system crach or instance failure by applying the entries in the redo log files to the datafiles.

5. PMON:- Process Monitor (PMON) process is used to clean up work after failed processes by rolling back the transactions and releasing other resources.

So, in this article we discussed the basic virtual (logical) components of oracle database and instance. Physical structure of database is some thing which is easy to understand and main component . So let us see it in next discussion.

Your comments are always welcome.

Few Things To Remember For Oracle Students

Hi all,

In this article i am going to discuss my own experience and what i was taught when i was an oracle student.

Many of us always confuse on 'How to study oracle?', 'How to prepare for oracle online exams?' and lot more question about oracle. Many people on oracle forum ask how oracle can be studied?

Look oracle is a very interesting technology and practical too. If you have joined oracle training program then they will teach you all the theory part, how oracle works, what are the components of oracle and let you practice on their system. But is it sufficient? practicing a topic of oracle one in a day may make you perfect in that topic, but is it sufficient to be a good DBA? answers might vary.

But according to me, along with practicing oracle topic with theory, you should practice previous or old topic as well. This will keep you connected with your total progress. Following are some tips which are discussed by experts like Aman sharma, Pavan kumar, Satish kandi, and many more on oracle forum for oracle beginners. Lets have a look:-

1. As an oracle student you should have enough knowledge about RDBMS and oracle corporation and its product. Its not in your OCP curriculum, but its a good gesture to have knowledge about the company, whose product you are going to use.

2. First of all if you have a system at your home, install oracle database on it and start practicing. If you don't have one, then you have to spend some extra hours at your institute.

3. Try to learn how oracle services start and stop on windows. You should have a bit knowledge about operating system process for this. But best suggestion is try to learn oracle on Linux platform. I heard that all institute are providing training on Linux platform now a days, which is a good thing.

4. Don't hesitate to ask, if you stuck on something. Please remember there is always a senior/expert to ask in IT sector.

5. Oracle has some components which are virtual, so be sure to identify them clearly. You should have very good understanding of oracle instance in order to understand oracle internals.

6. Read a lot of documents as you can. I am very bad at this. But still it is recommended by experts to keep reading. Everything is documented in oracle, you jut have to Google it.

7. Last but not the least, DBA is a very responsible job to do. Do not expect to get job as you complete you certification. Keep practicing on you machine, do what worst can happen to your database and try to recover it. Treat your home PC as a production database and you will find you are getting real world experience.

hope this helps you to be a good oracle student.

Oracle Certifications

Many students who are interested in making career in oracle, ask me ' how we can get certified?' ,'what is importance of certification?', 'what are the steps to get certified?'

Look, learning oracle means to learn how oracle works, its internals, its component, how we can get optimum from our system, how to resolve user queries and many more...

Lets have a detailed look on above topics,

1. Oracle corporation offers oracle certification. if you clear these exams then you can be appointed as oracle DBA, database analyst, backup admin etc.,

2. Very popular certification which is also a minimum requirement for perusing career in oracle and to get job as 'Database Administrator' is 'Oracle Certified Professional (OCP)'. OCP's have a deep knowledge of oracle working.

3. To get certified you must complete as hands on course for oracle certification examination. Find for prometric centers in your city on oracle website.

4. There are 4 papers which you have to complete to acquire OCP certification.
A) Introduction to oracle
B) Fundamental I

After completing these two papers you are called as 'Oracle Certified Associate (OCA). In this duration you learn how to use SQL language to retrieve, manipulate, delete and insert data in oracle database. In second paper you learn the architecture of oracle database, what are the components of oracle database, how they are work, etc.,

C) Fundamentals II
D) Performance Tuning

During these season you will learn backup and recovery strategies for oracle database (we will discus them in coming topics). and in last paper you will learn how to tun your database for best performance, means how to size database components for their optimum use.

there are other certifications too, which hold very high level in oracle community like Oracle Certified Master (OCM), RAC administrator etc. you can find information about all of them on following site:

http://education.oracle.com/

Hey, don't get scared....oracle is not a devil, by this time you have a very little idea what is oracle and there are some component that runs it.

As we discover oracle in our future topic you will become more clear. and i know you are little confuse also by this time. don't worry you can post comments and let me know what you want to know, i will try to post it as soon as possible.

what Is Oracle?

I always wonder when some people as me 'what is oracle?' not necessarily these people are from It field, but oracle is definitely not an UFO for all of us.

Besides having dictionary meaning ' a shrine where an oracular god is consulted' and 'an authoritative person who divines the future' oracle holds lot of meaning associated with it. For us oracle is a RDBMS (Relational Database Management System). Oracle is an information company. Oracle provides database solutions. rather, explaining it in simple words, 'oracle is a database which holds all your data, tables and if you want images too'.

Oracle database is #1 database with many features. Larry Ellison and his friends started Software Development Laboratories (SDL) in 1977 and rename their company as oracle, based on their CIA-funded project having code name 'Oracle'.

Oracle is a database system which holds your data as you want. like an organization may use it to save their employee information, Id's, salary and lots more information. A vendor may use it for storing information about his dealers.

Like any other excel sheet oracle uses tables and columns to store data, and unlike any other sheet it allows you to see only a particular row about particular thing like employee, dealer etc.,

Oracle database use SQL as its manipulation language. you can edit, insert, update and modify your data with this command language.

For large scale operations oracle is considered as most scalable, consistent and firm database than any other database in market.

this is what we called very basic about oracle, we can discuss here, as name of this blog is basic oracle. in upcoming post we see how oracle database works, its architecture and its component.