Tutorials
Oracle 10g
Oracle 10g Free Training: Oracle Concepts and Database Architecture
Oracle 10g Free Training: Oracle Concepts and Database Architecture - Page 2
In Oracle to access the data in the database, Oracle uses a set of Background processes that are shared by every user. Also, along with this there is some memory structure that stores the most recently queried data from the database.
A Database Instance (also save as Server) is set of memory structures and background processes that access a set of database files. It is possible for the single database to contain multiple instances, which is known as Real Application Cluster. The parameters that determine the size and composition of an oracle instance are either stored in an initialization file called init.ora or in spfile.ora. The initialization parameter file is read during instance start up and may be modified by the DBA. Any modification made will not be affected until the next start up.

Figure-1 Instance and database
Oracle uses an area of shared memory called the system global area (SGA) and a private memory area for each process called the program global area (PGA). The SGA consists of:
System global area:
The SGA is a shared memory region that contains data and control information for one oracle instance. Oracle allocates the SGA when an instance starts and de-allocates it when the instance shut downs. Every instance has the SGA .The entire SGA should be large as possible to increase the system performance and to reduce disk I/O.
The information stored in the SGA is divided into three memory structures,
1) Database buffers cache.
2) Redo log buffers.
3) Shared pool.
Database buffers cache: The database buffer stores the most recently used blocks of data. The set of database buffers in an instance is the database buffer cache. The buffer cache contains modified as well as unmodified blocks. Because the most recently and most frequently used data is kept in memory. It improves the performance of system by reducing the disk I/O operations.
Redo log buffers: The redo log buffer stores redo entries. This is a log of changes made to the database. The redo entries stored in the redo log buffers are written to an online redo log. An online redo log is a set of two or more files that record all the changes made to oracle data files and control files.
Shared pool: The shared pool caches various constructs that can be shared among users, for example SQL shared area. For example’s SQL statements are cached so that they can be reused. Stored procedures can be cached for faster access. Note that in previous versions “out of memory” error were occurs if the shared memory was full. In oracle 10g it does not happen. It provides automatic shared memory tuning.
PGA is a memory buffer that contains data and control information for a server process. A server process is a process that services a client’s requests. A PGA is created by oracle when a server process is started. The information in a PGA depends on the oracle configuration. The PGA area is a non-shared area of memory created by oracle when a server process is started.
The basic difference between SGA and PGA is that PGA cannot be shared between multiple processes in the sense that it is used only for requirements of a particular process whereas the SGA is used for the whole instance and it is shared.
The relationships between the databases’ physical and memory structures are maintained and enforced by the Background Process. These are database’s own background processes that may vary in the number depending in your database configuration. The Trace files are only created when there is any problem. Some of the Background Processes are:
SMON:
![]() |
First Page: Oracle 10g Free Training: Oracle Concepts and Database Architecture
| this information is not completed, plz give brief |

| :)PLEASE MORE DIAGRAMATIC PRESENTATION |
|
hi, This is a nice presentation of concepts. but i have a doubt, What is a backup and how we take backup? i Hope i will get the answer very soon. Thanks & Regards Tanmaya Pradhan. |
|
I found this information very use full to understand the basic architecture of oracle. Grate job done and also help full to start your jurnery for understanding the oracle. Regards, Mudit K. Sharma |
|
This information is very useful for the beginners to understand the oracle.This helps very much for the people who want to learn oracle. Regards, Madadi.Radha |
| if iam issued SELECT query to database, what process happen to SGA, which background proccess handle the query? |
| Wonderfull |
| very good explain of back ground process |
| very usefull for freshers |
| Its really usefull. Good sumerization of a big topic. |

|
Hi, Backup is the procedure to secure your data/Information in other storage media or other destination. Incase of any crash you can restore it back and save the information which can be lost. There is some method from which you can take the backup but before that you need to understand the different type of backup. One is Physical backup(Cold backup) and other is Logical Backup(HOT BAckup). Cold backup is where you have to shutdown the database and copy the Datafile, redologfile and controlfile to other location/media. HOT backup in that you dont need to shutdown you database. just apply some commands online and you will be able to take the backup of those files. Like say you want to take the backup of control file, apply command "Alter database backup controlfile to 'filename' or to Trace." To take the backup of datafile, "Alter tablespace begine backup;". Than copy all the datafiles pertaining to this tablespace in other location media and after copying all the datafile of that tablespace, apply another command which isa compulsory . "Alter tablespace < tablespace_name> end backup." for more information Tanmaya , just mail me on murtuza231@rediffmail.com. |

|
@mohammadmartak When you fire the SQL Query, Besically there are three steps involved in it. 1) Parse 2) Execution 3) Fetch In Step(1) : During the parse phase, the SQL statement is passed from user process to Oracle and a parsed representation of the SQL statement is loaded into a shared SQL area. Parsing is the process of 1) translating SQL statement, verifying it to be a valid statement 2) performing data distionary lookups to check table and column defination 3) acquiring parse locks on the required objects so that their defination do not change during the statement's parsing. 4) checking previleges to access referenced schema objects. 5) determining the optimal execution plan for the statement. 6) loading it into a shared SQL area. A SQL statement is parsed only if a shared SQL area for an identical SQL statement does not exist in the shared pool. In this case a new shared SQL area is allocated and the statement is parsed. Step(2) ...During execute phase, at this point Oracle has all necessary information and resources, so the statement is executed. Step(3) .. In the fetch phase, rows are selected and ordered(if requested by the query), and each successive fetch retrieves another row of the result, until the last row has been fetched. |
| Highly Appreciate the Work , the Language Used was very easy to understand and make others understand |
|
Highly Appreciate the Work ,Its really usefull. monaabdellatif; |
| really usefull |
| Its very useful for beginners. But a diagrammatic representation would have been good to explain the Oracle DB Architecture. |
|
Hi, this is very wonderful, even it a kind motivation and useful for beginners like us will know more and excel higher in the Database management with DBA itself. Please i have a problem with granting a previllages to user i have created. Also i am very interested in your programe, may i know please if there could be any posible way for me to enjoy these intensive free training your are making. Thank you all I will be expecting your kind answer in my 'inbox' of the e-mail i have provided. Thank you once again. |
|
Can any one pls come up with the answer for the following questions and i would really appreciate the effort. 1)when a recovery takes place for media failure the reco process recovers using archive file, does it use db cache to write(dbwr) the lost information to datafile.(b'coz no other process can write the datafile) and where to find information. 2)export utility uses which memory area. 3)what's the status of a cursor(implicit) in shared pool. thanks in advance. |
|
I want to learn to build a platform, what do I need to start with first? I want to manage data's. How can I backup files and directories? Thanks and better regards |
| Can anyone please tell me what type of Oracle 10g backup strategy we use in a real time scenario? |
| Hi, This is a nice documentation for a beginners who doesn't know ABCD in oracle. Each line has important information. Good Work. |