Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Oracle Architecture

Explain Oracle Architecture?
  Oracle server is divided into instance and database.
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
Online redo Logfiles 
Archived log files
Logically database is divided into
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.

oracle architecture
Now lets discuss about each component.
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.
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)
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.

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
from 9i,
 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 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


Popular posts from this blog

rman interview and scenario based questions and answer and also rman(backup and recovery) discussions panel

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

PostgreSQL pgBadger