Explain Oracle Architecture?
Oracle server is divided into instance and database.
INSTANCE is divided into
SGA - Shared Global Area or System Global Area
PGA - Process Global Area or Program Global Area
DBWR - Database writer
LGWR - Log writer
SMON - System monitor
PMON - Process Monitor
ARCH - Archiver
CKPT - Checkpoint
DATABASE is divided as
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.
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
- 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
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.
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?