Try it here
Oracle Architecture

Oracle Database 11g Architecture


Oracle Database Memory Structures

Oracle Database Memory Structures

Oracle Server:

  • A server is a collection of database units and it provides comprehensive integrated approach to info management
  • It consists of an "Instance & Database"

Oracle Instance:

  • It means to access an oracle database
  • It always open one & only one database

It consists of two types :

- Memory Structure
- Back Ground Process

Memory Structure:

  • System Global Area (SGA)
  • Program Global Area (PGA)
  • User Global Area (UGA)
  • Software code areas
  1. System Global Area

    The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas.

    • Once the Instance is started it allocates memory to SGA
    • It is a basic component of oracle instance its size depends on RAM
    • The oracle 10g parameter of SGA and PGA are sga_target , sga_max_size , pga_aggregate_target

    It consists of

    - Shared Pool

    - Database Buffer Cache

    - Redolog Buffer Cache

    - Large pool

    - Stream pool

    - Java pool

    here we can go to see each components in detail

    1. Shared Pool:

      - It's parameter is shared_pool_size

      - It's consists of Library cache and Data Dictionary Cache

      1. Library Cache:

        - It stores information about recently used sql and Pl-sql Statements

        - Here it checks some of the followings

        1)Semantic checking - it checks the privilege issued commands by user

        2)Syntax checking - it checks the syntax of user issued commands

        3)Soft parse - Already Executed Sql statements command

        4)Hard parse - New Sql Statements

      2. Data Dictionary Cache:

        - It stores the collection of most recently used definitions in the databases includes dbfiles,tables,indexes ,columns etc.

        - It has the information about database and it is read only.

    2. Database Buffer Cache:

      - It stores copies of data block that have been retrieved from the database datafiles.

      - Its parameters are

      show parameter db_block_size =8kb is default size,
      show parameter db_cache_size
    3. Redo log Buffer Cache or Recovery Mechanism:

      - It maintains records of modification database blocks.

      - Primary purpose is recovery.

      Show parameter log_file
    4. Large Pool:

      - Parallel execution allocates buffers out of the large pool only when sga_traget

      - It works to release the burden of the shared pool

      show parameter parallel_automatic_tuning
    5. Java Pool:

      - Parsing requirement of java commands

      - Requires installation of java based projects

        Show parameter java_pool_size
    6. Stream Pool:

      - It's Cache "Oracle Stream" Objects

      - Oracle Stream means to allow data multiplication between on oracle databases or oracle and non-oracle databases,It can be used for Replication,Message Queuing,Loading data into a Data Warehouse,Event Notification,Data Protection Automatic Shared Memory Management (ASMM ) was introduced in Oracle 10g.

      its taking care by oracle and allocates SGA components size ASMM taking care of

      1)Shared pool

      2)Library cache

      3)Database buffer cache

      4)Large pool

      5)Java Pool

      6)Stream Pool

  2. Program Global Area

    A PGA is a nonshared memory region that contains data and control information exclusively for use by an Oracle process. The PGA is created by Oracle Database when an Oracle process is started.

    One PGA exists for each server process and background process. The collection of individual PGAs is the total instance PGA, or instance PGA. Database initialization parameters set the size of the instance PGA, not individual PGAs.

    - It reserved memory for each user process connecting to an oracle database

    - Allocates memory when a process is created

    - De-allocates memory when a process is terminated

  3. User Global Area

    The UGA is memory associated with a user session.

    The UGA is session memory, which is memory allocated for session variables, such as logon information, and other information required by a database session. Essentially, the UGA stores the session state.

  4. Software code areas

    Software code areas are portions of memory used to store code that is being run or can be run. Oracle Database code is stored in a software area that is typically at a different location from user programs , a more exclusive or protected location.

Process Structure :


    - A program that request interaction with oracle server

    - It must first establish a connection

    - It doesn't interact directly with oracle server


    - It directly interacts with oracle server

    - It can be a dedicated or shared server

    - It always responds to user requests


    - It enforces the relationship between memory structure and database

    - To view all background process

     !ps -ef | grep databasename

    Its some of components are






    Each components are

    1. DBWR:

      - Time Out Error

      - Tablespace offline

      - Tablespace Read only

      - Tablespace Drop or Truncate in above situations, Data 'll be flushed from database buffer cache into data files

    2. LGWR:

      - At commit

      - Every 3 sec

      - When there is full 1MB reached

      - Redolog Buffer reached one-third full

      - Before DBWR writes In above situations, redolog writes through LGWR from redo log buffer

    3. SMON:

      - Monitoring the system is called system monitor

      - Instance recovery

      - Rolls forward changes into redologs

      - Open database for user access

      - Rolls back uncommitted transactions

    4. PMON:

      - Taking Care of All background Process

      - Cleaned up after failed process

      - Rolling Back

    5. CHKPT:

      - Updating the control file with checkpoint information.

      - It's a process of writing by DBWR ,all modified buffers in SGA cache into Data files

        Alter system checkpoint;

Database :

The Database is a collection of data which contains data files ,control files ,redolog files and Archive log

  1. Data file:

    - It is a portion of an oracle database ,it stores the data which includes user data and undo data

    - It's extension ".dbf"

    - The default location is " $ORACLE_BASE/oradata"

    - To view the location in database use this command

       Select name from V$datafile;
  2. Control file:

    - It's heart of the database

    - It holds the information of data file ,redo log file locations and backup information starting time and ending time

    - It's extension ".ctl"

     Show parameter control_files

    - By default oracle has copied the control files into flash_recovery_area

  3. Redo log File:

    - It's part of an oracle database

    - It's the main purpose is to recover the database

    - It's extension ".log"

    - When transaction is committed that details in redo log buffer are written to a redo log file

    select * from V$log; or Select * from V$logfile ;
  4. Archive log:

    - It's a group of redo log files to one or more offline destinations, known collectively as the archived redo log

    - Its Default location is Flash_recovery_area

    - Must enable archive log mode in the database then only ll be saved on archive log folder other wise the log buffer overwrites on redo log files through Lgwr.

Writer profile pic

Uk01 on Apr 20, 2015 at 12:04 am

If you like and would like to contribute, you can write your article here or mail your article to . See your article appearing on the main page and help others to learn.

Post Comment

Comments( 0)


Forgot Password

Please enter your email address below and we will send you information to change your password.