Database Arch 11g Diag  

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)

 (I)System Global Area

  • Once The Instance is started it allocated memory  to SGA
  • It is a basic component of oracle instance its size depends on RAM
  • The oracle 10g parameter of SGA and PGA  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 details

(1 )Shared Pool:

  - It's parameter is shared_pool_size

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

  I) Library Cache:

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

 - Here it checks some of the following

     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

  II)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 its  read only

(2)Database Buffer Cache:

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

     - It's parameter  is

   show parameter db_block_size =8kb is default size,
   show parameter db_cache_size

(3)Redo log Buffer Cache or Recovery Mechanism:

     - It's 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 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    

 (II)Program Global Area 

     - 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

 Process Structure :  


      - A program that request interaction with oracle server

     - It's must first  establish a connection

     - It does not  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

It has some of components  are






Let us we can see each components are


    - 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


    - 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


   - Monitoring the system is called system monitor

   - Instance recovery

   - Rolls forward changes into redologs

   - Open database for user access

   - Rolls back uncommitted transactions


   - Taking Care of All background Process

   - Cleaned up after failed process

   - Rolling Back


   - 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

  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.