1. Oracle Architecture

2. Database instance • When a database is started the current state of the database is given by the data files, a set of background (BG) processes and memory structures, which is called database instance (DBI) • The DBI is a set of memory structures and BG processes that access a set of data files (DFs) • Oracle’s physical file structures are collectively known as the Oracle server.

3. Init.ora Memory system global area LGWR Redo log buffer Shared library DB buffer cache Redo logs PMON SMON client Server processes ARCn DBWn Control files Archive redo logs CKPT Data files

4. Data files (DFs) • The database is contained in a number of DFs held on disk • When a DB is opened, these DFs and associated BG processes and memory structures are called a DBI. • DFs are read via the client process into the memory structure database buffer cache from where the data is processed • They are written back to the DFs only by the DBWR process

5. DBWn (DataBase Writers) • Except when the database is being recovered it is only the DBW which updates the data files • DBW writes blocks from the buffer cache that have been dirtied by various processes • DBW tries to make sure that there are always free blocks in the cache for the server process to read data into • A commit in a client process does NOT make the DBW write blocks • A check point makes the DBW write all current dirty blocks to speed any subsequent recovery

6. Re-do log buffer • Changes made to a DB are written to the redo log buffer • This is a cyclic buffer and is flushed to the redo log whenever a user process flags a commit or the buffer becomes more than a third full or every 3 seconds • Redo logs are fundamental to recovery. • Note: the DBW will not write dirty blocks to a data file until the redo log buffer containing the change has been written to the redo log

7. LGWR (Log Writer) • Writes redo log entries to the REDO LOG from the redo log buffer • Written sequentially (serially) into cyclic redo logs Written when log 4 is full the next redo log is log 1 Redo log 1 Redo log 2 Redo Log 3 Redo log 4

8. Redo Log • This records all the changes from the redo log buffer so that a DB can be recovered, it is written to by the LGWR process • It is used to recover a DB • Roll forward data files from a known point (backup) to any point covered by continuous redo logs • Min of 2 used cyclically • All redo logs must be kept since the last backup to enable a DB to be recovered • Can be queried using log miner.

9. Redo log archiving • The LGWR writes the contents of the redo log buffer to the current redo logs • When each redo log is full it is closed and the information written to the next log • The current redo log is cycled round • Redo information wil be lost unless it is written away to a permanent area before the redo log is cycled • If turned on process ARCH does this by copying them to the archived redo log area

10. Archive redo logs • Archive redo logs and the current redo logs contain the change vectors as far back as the archive logs go • If a redo log is missing the the db can only be recovered to the redo log that is missing • Hence all redo must be kept from the last successful backup to enable recovery

11. ARCn process • The arch process automates the copying of the online redo to the archive area • If archiving is turned on current when redo log is closed it is copied to archive area by ARC • If not copied by the time the un-archived current redo is cycled DB will STOP until archiving complete • Could be due to insufficient room

12. Control files • Contains information about the DB including • Db name • Files and locations • System control number of DB and data files • Archiving on or off • Used to detect and control recovery if SCN’s in control file do not match those on data files it knows if recovery needed. • Less than 500K in <8i but can be several MB in 9i

13. Initialisation parameters (init.ora) • Contains parameters that control the DB instance, read on start up • Can include parameters that • Name things (e.g. files) • Set limits • Affect capacity • Many parameters can be changed dynamically using alter system or alter session however they are not automatically propagated across on close of DB unless edited in the init.ora • SP (server parameter) file in 9i+ allows alter statements to dynamically change parameters.

14. Server parameter file (SP file) • These parameter files allow changes applied dynamically to be carried across shutting and opening the DB • On startup • If pfile is specified the init.ora file is used • If no pfile specified then default spfile is used if exists • If no pfile specified and no default spfile exists then default init.ora file is used

15. CKPT (Checkpoint) process • To minimise the time to recover a DB, Oracle periodically tells the DBW to write all current dirty blocks to the data files • This makes the DBW work harder by checkpoints help reduce the amount of work the db has to do during a recovery • When a checkpoint is complete all header blocks in the data and control files have to be updated and a check point entry has to be written to the redo log • CKPT updates the headers to reduce impact on DBW

16. PMON – process monitor • Monitors the processes running and performs various functions including • Performs process recovery when a user process fails • Cleans up the cache and frees the resources held by the processes • Checks dispatcher and server processors and restarts them if they have failed It is always running if the db is open

17. SGA (Shared Global Area) memory structures • Three main memory structures in the SGA area • DB buffer cache – a cache of db blocks oracle can not process data unless the block it belongs to is in the cache • Log buffers – a buffer of redo vectors being written to the redo logs • Shared pool – an area containing information on recently used statements, used to minimise reparsing of statements Very useful for optimisation

18. Have a look at … • Look at V$session to identify what processes are running • Use V$parameter to identify the value of sga_max_size on the data base (largest the SGA will be allowed to get to)