Oracle Architecture

Explain Oracle Architecture?
  Oracle server is divided into instance and database.

Again,

INSTANCE is divided into 
Memory structures 
Background processes.  

 Memory structures 
SGA  - Shared Global Area or System Global Area
PGA - Process Global Area or Program Global Area

Background processes 
DBWR - Database writer 
LGWR - Log writer 
SMON - System monitor
PMON - Process Monitor
ARCH - Archiver 
CKPT - Checkpoint

DATABASE is divided as 
Physical structures
Logical structures

Physically database contains
Datafiles
Online redo Logfiles 
Controlfile
Archived log files

Logically database is divided into
Tablespace
Segments
Extents
Blocks

Tablespace physically contains one or more datafiles,and logically group of segments.
Segments are group of Extents
Extents are group of blocks.

Below is the basic Diagram of Oracle Architecture.

Now lets discuss about each component.

1.
Who does instance recovery? How will you find  instance recovery has happened when you opeen the database?
SMON does it. You can find the information in alert log.

What will happen when you start the database? Explain about the stages.
If you give startup, oracle pass three stages.

NOMOUNT
First it checks the default location ($ORACLE_HOME/dbs in unix, and %ORACLE_HOME%/database in windows ) for spfile, if spfile not present then it will check for pfile.

  Reads the parameters from pfile/spfile and allocates instance (Memory structures and background process)

MOUNT
It get the control file path  from   pfile/spfile and reads the contents of controlfile.
Control file contains
- location on datafiles and logfiles
- checkpoint number
- dbname
-  backup information, if rman backup is configured.

OPEN
Here oracle checks the consistency of database. It checks whether same checkpoint number is present in all the datafile and logfile headers. If any mismatch , then oracle stops at mount stage and recovery is needed.

What is PGA?
PGA is Process Global Area or Program Global Area. It is mainly used for sorting purpose. It also holds private/session variables.

SQL commands that lead to sorting :
order by, group by, create index , union etc.

The PGA related parameters are 
in 8i
SORT_AREA_SIZE=size
HASH_AREA_SIZE=size
BITMAP_MERGE_AREA_SIZE=size
CREATE_BITMAP_AREA_SIZE =size
from 9i,
PGA_AGGREGATE_TARGET = size
WORKAREA_SIZE_POLICY = auto/manual

 If auto it uses 9i concept , if manual it uses 8i concept.

All the four parameters in oracle 8i are handled in 9i by using single parameter (PGA_AGGREGATE_TARGET). Each session will use one PGA for sorting. But it allocates only 10% of the PGA size for each session. 
For example, 
If we have PGA_AGGREGATE_TARGET = 1G, then each session can use only 100m of space. If a particular session want to use 1G , then we can user 8i parameter as shown below.

SQL > alter session set WORKAREA_SIZE_POLICY=MANUAL;
SQL > alter session set SORT_AREA_SIZE=1G;

 While sorting , if there is not enough space in PGA then oracle uses temporary tablespace.

What is the difference between shutdown immediate and shutdown transaction?

          When issuing shutdown immediate, oracle rollbacks any transaction that is  running and puts checkpoint and then brings down the database.

When issuing shutdown transaction, oracle waits till the transaction completes. Transaction will be completed when commit or rollback issued. Then it puts checkpoint and brings down the database.

How will you know 

How to create dba_waiters package in oracle?
Execute $ORACLE_HOME/rdbms/admin/utllockt.sql