ORACLE DATABASE ARCHITECTURE

ORACLE 11G DATABASE ARCHITECTURE:


































































ORACLE ARCHITECTURE :
---------------------

--> ORACLE SERVER INCLUDES

-- INSTANCE
-- ORACLE DATABASE
INSTANCE :
----------

-->INSTANCE INLUDES
  -- SGA
-- PGA
-- BACKGROUND PROCESS
-- SERVER PROCESS
SGA(SHARED GLOBAL AREA) :
-------------------------
--> IT INCLUDES
-- SHARED POOL
-- DATABASE BUFFER CACHE
-- REDO LOG BUFFER
-- FIXED SGA
-- LARGE POOL
-- STREAM POOL
-- JAVA POOL

SHARED POOL :
-------------

--> THE SHARED POOL INCLUDES

   
    i) LIBRARY CACHE :
      ---------------

--> STORES EXECUTABLE SQL AND PL/SQL CODE

--> IT INCLUDES

-- SHARED SQL AREA
-- PARESED SQL STATEMENTS
-- SQL EXECUTION PLANS
-- PARSED & COMPILIED PL/SQL PROGRAM UNITS.

-- PRIVATE SQL AREA(ONLY FOR SHARED SERVER)

     ii) DATA DICTIONARY CACHE :
-----------------------

--> HOLDS INFORMATION ABOUT DATABASE OBJECTS

--> ALSO CALLED AS ROW CACHE BECAUSE IT HOLDS DATA AS ROWS INSTEAD OF
               BUFFERS.


     iii) SERVER RESULT CACHE :
 ---------------------

  --> HOLDS RESULT SETS AND NOT DATA BLOCKS.

  --> IT CONTAINS

 -- SQL QUERY RESULT CACHE

-- THE DATABASE CAN STORE THE RESULTS OF QUERIES & QUERY
                             FRAGMENTS IN IT.

 -- PL/SQL FUNCTION RESULT CACHE

-- STORES FUNCTION RESULT SET.
   
     iv) RESERVED POOL :
----------------

   --> IS A MEMORY AREA IN THE SHARED POOL THAT ORACLE DATABASE CAN
                  USE TO ALLOCATE LARGE  CONTIGUOUS CHUNKS OF MEMORY.



DATABASE BUFFER CACHE :
-----------------------


--> STORES COPIES OF DATA BLOCKS READ FROM DATA FILES

--> BUFFER STATES
-- UNUSED
-- CLEAN
-- DIRTY

--> BUFFER MODES
-- CURRENT MODE
-- CONSISTENT MODE

--> SERVER PROCESS IS RESPONSIBLE FOR READING DATA FROM DATA FILES.

--> BUFFER POOLS
-- DEFSULT POOL
-- KEEP POOL
-- RECYCLE POOL


REDOLOG BUFFER :
----------------

--> STORES REDO ENTRIES DESCRIBING CHANGES MADE TO THE DATABASE.

--> USED FOR DATABASE RECOVERY.


--> PARAMETERS

        LOG_BUFFER



Large Pool
----------
   ---> it includes UGA, request queue, response queue, i/0 buffer area and free memory

    -->The large pool is an optional memory area intended for memory allocations that are
larger than is appropriate for the shared pool. The large pool can provide large
memory allocations for the following:

--> UGA for the shared server and the Oracle XA interface (used where transactions
interact with multiple databases)

--> Message buffers used in the parallel execution of statements

--> Buffers for Recovery Manager (RMAN) I/O slaves

                --> By allocating session memory from the large pool for shared SQL, the database      
                      avoids performance overhead caused by shrinking the shared SQL cache. By allocating
                      memory in large buffers for RMAN operations, I/O server processes, and parallel buffers, the
                      large pool can satisfy large memory requests better than the shared pool.

               --> The large pool is different from reserved space in the shared pool, which uses  
                      the same LRU list as other memory allocated from the shared pool. The large  pool does not
                      have an LRU list. Pieces of memory are allocated and cannot be  freed until they are done
                      being used. As soon as a chunk of memory is freed, other processes can use it.


Fixed SGA
----------
     The fixed SGA is an internal housekeeping area. For example, the fixed SGA contains:


--> General information about the state of the database and the instance, which the
             background processes need to access

--> Information communicated between processes, such as information about locks

        --> The size of the fixed SGA is set by Oracle Database and cannot be altered manually.
 
--> The fixed SGA size can change from release to release.



JAVA POOL :
-----------

--> STORES ALL SESSION SPECIFIC JAVA CODE AND WITH IN THE JVM.

--> THE JAVA POOL ADVISOR IS INTERNALLY TURNED ON WHEN STATISTICS_LEVEL IS SET TO TYPICAL OR           HIGHER


Streams Pool
------------

  --> The Streams pool stores buffered queue messages and provides memory for Oracle
      Streams capture processes and apply processes.

  --> The Streams pool is used exclusivelyby Oracle Streams.

  --> Unless you specifically configure it, the size of the Streams pool starts at zero.

  --> The pool size grows dynamically as required by Oracle Streams.



TYPES OS PROCESSES :
--------------------


--> CLIENT PROCESSES
-- RUNS ON APPLICATION OR ORACLE TOOL CODE

--> ORALCE PROCESSES

-- BACKGROUND PROCESSES
-- START WITH THE DATABASE INSTANCE AND PERFORM MAINTENANCE TASKS

-- SERVER PROCESSES
-- PERFORM WORK BASED ON CLIENT REQUEST

-- SLAVE PROCESSES
-- PERFORM ADDITIONAL TASKS FOR A BACKGROUND OR SERVER PROCESSES.


background processes:
---------------------

--> A MULTIPLE ORACLE DATABASE USES SOME ADDITIONAL PROCESSES CALLED BACKGROUND PROCESSES.

--> THE FOLLOWING QUERY LISTS THE BACKGROUND PROCESSES

SQL> SELECT PNAME FROM V$PROCESS WHERE PNAME IS NOT NULL ORDER BY PNAME;

--> THE FOLLOWING ARE THE MANDATORY BACKGROUND PROCESSES

-- PMON
    -- CLEANS USER, SERVER & OTHER BACKGROUND PROCESSES
    -- REGISTER SERVICES WITH LISTENER

-- SMON
    -- CRASH RECOVERY
    -- CLEAN TEMP SEGMENTS
    -- COLLATES FREE SPACE

  -- DBWR
    -- WRITES CONTENTS OD DATABASE BUFFERS TO DATAFILES
    -- DBWN PERIODICALLY WRITES BUFFERS TO ADVANCE THE CHECK POINT  
-- LGWR
    -- WRITES ONE CONTIGUOUS PORTION OF THE BUFFER TO THE ONLINE
                   REDOLOG.
    -- IT WRITES
-- A USER COMMITS A TRANSACTION
-- AN ONLINE RED LOG SWITCH OCCURS
-- THREE SECONDS HAVE PASSED SICE LGWR LASR WROTE
-- THE REDO LOG BUFFER IS 1/3RD IS FULL OR CONTAINS 1MB OF
                              BUFFERED DATA
-- DBWN MUST WRITE MODIFIED BUFFERS TO DISK

-- CKPT
    -- UPDATES THE CONTROLFILE AND DATAFILE HEADERS WITH CHECKPOINT
                  INFORMATION AND DBWN  TO WRITE BLOCKS TO DISK


-- RECO
    -- THE DISTRIBUTED TRANSACTION RECOVERY PROCESS FINDS PENDING
                  DISTRIBUTED TRANSACTIONS AND RESOLVES THEM

-- MMON
    -- CAPTURES STASTICS FOR SQL OBJECTS WHICH HAVE BEEN RECENTLY
                  MODIFIED.
    -- MMON WRITES A METRIC VIOLATES ITS THRESHOLD VALUE

        -- MMNL
    -- WRITES STATISTICS  FROM THE ACTIVE SESSION HISTORY(ASH) BUFFER IN
                  THE SGA TO DISK


--> THE FOLLOWING ARE THE OPTIONAL BACKGROUND PROCESSES

-- ARCH
    -- COPIES AN ONLINE REDOLOG FILE TO ANOTHER LOCATION WHEN THE REDLOG FILE IS FILLED UP OR THERE IS A LOG SWITCH

-- MMAN
    -- DYNAMICALLY ADJUSTS THE SIZE OF SGA COMPONENTS.

-- CJQN
    -- JOB QUEUE PROCESSES
    -- SELECT JOBS THAT NEED TO BE RUN
 




server processes
-----------------        
               
----> for handling clients                                                          

 type :
 ------
   i) shared server process

  ii) dedicated server process


 shared server process:
 ----------------------

  --> can handle multiple clients through dispatcher
  --> UGA store in SGA


 dedicated server process:
 -------------------------

  --> can handle only one user process
  --> uGA store in PGA
                                       
                   
 UGA ---> User global area
          it contais session variables like logon information\ and OLAP pool also.

                                 
PGA:
----

   PGA --> Program global Area

                                   
                                     
   --> The pga includes the following
           
          --> sql work areas
          --> session memory
          --> private sql area                                    
                                         
   --> in shared server the sql private area is in shared pool

   --> The UGA is in PGA when server process is in dedicated otherwise it is in large pool

   i)SQL Work Area:
    ---------------
       --> it inludes sort area,hash area and bitmap merge area.
                     
               sort area --> sort operator uses sort area
           
               hash area --> hash join operator uses hash area
       
               bitmap merge area --> bitmap merge uses the bitmap merge area to merge data    retrieves from scans of multiple bitmap indexes.
 
             
  ii) Session memory:
    -----------------
       --> INFORMATIN ABOUT SESSION


  iii) private sql area:
     -------------------
        --> it includes Run time area and Persistent area.


           Run-time area -->This area contains query execution state information.  
              Oracle Database creates the run-time area as the first step of an                                  execute request.
                     For DML statements, the run-time area is freed when the SQL statement      
                               is closed.

          persistent area --> This area contains bind variable values.
               A bind variable value is supplied to a SQL statement at run time  when the
                                       statement is executed. The persistent area is freed only when the cursor is closed.
   
                                       


---> Differences in Memory Allocation Between Dedicated and Shared Servers
     ---------------------------------------------------------------------

  Memory Area                                                            Dedicated Server         Shared Server
  -----------                                                                  -----------------          -------------

  Nature of session memory                                                   Private                   Shared

  Location of the persistent area                                              PGA SGA

  Location of the run-time area for DML/DDL statements          PGA PGA
                                                 
                                   

---> Background processes also allocates their own PGA's.                              
                                           
                               
--> ORACLE DATABASE STORAGE STRUCTURES
    -----------------------------------


--> IT INCLUDES

-- PHYSICAL STORAGE STRUCTURES

-- LOGICAL STORAGE STRUCTURES                                        


PHYSICAL STORAGE STRUCUTRES :
-----------------------------

--> IT INCLUDES

-- DATA FILES AND TEMP FILES
-- A DATA FILE IS A FILE ON DISK THAT WAS CREATED BY ORACLE DATABASE
-- CONTAINS DATA STRUCTURES SUCH AS TABLES AND INDEXES
--

-- CONTROL FILES
-- IS A ROOT FILE THAT TRACKS THE PHYSICAL COMPONENTS OF THE DATABASE
-- IT CONTAINS
-- DB NAME AND DBID
-- TIME STAMP OF DB CREATION
-- INFORMATION ABOUT TABLESPACES
-- INFORMATION ABOUT DATA FILES,RED LOG FILES AND ARCHIVED REDO LOG FILES
-- RMAN BACKUPS

-- ONLINE REDOLOG FILES
-- IS A SET SET OF FILES CONTAINING RECORDS OF CHANGES MADE TO DATA.

-- PARAMETER FILE

-- SERVER PARAMETER FILE

-- PASSWORD FILE
-- STORES THE PASSWORD OF SYSDAB PREVILAGE USER

-- ALERT LOG FILE
-- CONTAINING CHRONOLOGICAL LOG OF DATABASE MESSAGES
-- WHICH IS AN XML IFLE
-- IT INCLUDES INTERNAL ERRORS,SEVERAL MESSAGES ETC.

-- TRACE FILE
-- IS AN ADMINISTRATIVE FILE THAT CONTAINS DIAGNOSTIC DATA USED TO
                    INVESTIGATE   PROBLEMS
-- CAN PROVIDE GUIDANCE TUNNING APPLICATIONS OR AN INSTANCE
                                         
                                   

LOGICAL STORAGE STRUCTURES :
----------------------------

--> THE LOGICAL STORAGE STRUCTURES ARE


-- TABLESPACES
-- LOGICAL STOARAGE CONTAINER FOR SEGMENTS
-- AT THE PHYSICAL LEVEL, A TABLESPACE STORES DATA IN ONE OR MORE DATA OR TEMP   FILES
-- SEGMENTS
-- IS A SET OF EXTENTS THAT CONTAINS ALL THE DATA FOR A LOGICAL STORAGE WITHIN A   TABLESPACE

-- EXTENTS
-- IS A LOGICAL UNIT OF DATABASE STORAGE SPACE ALLOCATION MADE UP OF CONTIGUOUS   DATA BLOCK

-- ORACLE DATABLOCK
  -- A DATA BLOCK IS THE MINIMUM UNIT OF DATABASE I/O
-- PARAMETER FOR BLOCK SIZE IS DB-BLOCK_SIZE

ORACLE DATABASE STARTUP AND SHUTDOWN :


Database start :


----------------


 for database start includes 3 steps
   1)nomount(instance start without mounting database)
   2)database mount
   3)database open


   1)nomunt:
   ---------
    -> it reads the parameter file(init.ora)
    -> in nomount only instance started allocates memory for SGA and PGA.
    -> it starts all processes.
                     
   2)mount:
   --------
    -> control file is palced in memory.
    -> reads the control file.


   3)open :
   --------
    -> repair and dictionary is placed in dictionary cache.
    -> it open the all the data files
    -> the order of oepning datafiles are system.dbf,undo.dbf,sysaux.dbf,user datafiles,redlogfiles.  
    -> it cant open the database when any one of data files is not found.




                      system.dbf
                        |
                       undo.dbf
                       |                                              
                       sysaux.dbf                        
                          |                        
                       user datafiles
                          |
                       redo log files
                 
                                                     
                                                   
                                                     
Database shutdown:
------------------


   it includes the following
   
      1) database closed
      2) database unmounted
      3) database instance shutdown
   
                                                 
     1)database closed:
     ------------------
       -> data written to datafiles in disk.
       -> the data files and redolog files are closed but the database is            mounted.
       
     2)database unmounted:
     ---------------------
       -> close the database
       -> database unmount database to disassociate it from instance.


     3)database instance shutdown:
     -----------------------------
       -> shutdown the instance
       -> SGA is removed from memory and background processes are terminated.


   
   shutdown types:
   ---------------
       1)normal
       2)immediate
       3)transactional
       4)abort
             
     Database Behavior       ABORT   IMMEDIATE   TRANSACTIONAL     NORMAL
     -----------------               -----     ---------         -------------             -------
      Permits new user
      connections                   No          No                   No                 No
   
      Waits until current
      sessions end                 No          No                   No                 Yes


      Waits until current
      transactions end            No          No                   Yes                Yes


      Performs a checkpoint
      and closes open files      No         Yes                   Yes                Yes                






commands:
--------------

--> for normal startup  database


SQL > startup


--> for restart the database 


SQL > startup  force


--> for starting a database to nomount state


SQL > startup  nomount


--> for starting a database to mount state


SQL > alter database mount;


--> for open a database


SQL > alter database open;


--> for closing a database


SQL > alter database close;


--> for dismount the database


SQL > alter database dismount;


--> for shutdown the database


SQL > shutdown normal/abort/transactional/immediate;