Oracle Certification Notes: Part V

A continuation of my notes as I study for Oracle's Certified Associate credential:

Instance vs. Database

Traditionally a one-to-one relationship between instance and database

Oracle Real Application Clusters (RAC): Multiple instances on different servers pointing to a single database.

An instance may point to multiple databases through the use of database links.

Instance: Memory structure and processes. Temporary existence within memory and CPU.

Database: Physical files and disk usage. Infinite existence on disk storage.

DBA manages the relationship between the physical and logical layer of the Oracle Database architecture.

Instance: System Global Area

SGA described as part of the Oracle Instance.

3 required elements: shared pool, database buffer cache, log buffer

Getting a good balance as to the allocated size of SGA can have a significant impact on performance. Too much wasted memory can degrade performance.

SGA: Shared Pool

Shared Pool: Broken down further into segments, including library cache and data dictionary cache

Library Cache: Oracle parses developer code into executable language. Recently executed code is stored within the library cache for faster memory-access performance as opposed to re-parsing the developer code.

Data Dictionary Cache: Indexes, users, table metadata, etc. are stored in memory for faster access (as opposed to going to disk)

SGA: Database Buffer Cache and Log Buffer

Used for SQL execution. Users do not write updates directly to disk. Necessary data is copied from disk to the database buffer cache and then updates are made. Recently retrieved or changed data can now be referenced directly from memory and not disk, minimizing I/O.

The log buffer is used in conjunction with the database buffer cache as part of editing data and storing changes within memory.

Instance: 5 Major Background Processes

System Monitor (SMON): confirms the operation of the database

Process Monitor (PMON): confirms the operations of the user (e.g., computer reboots while in an Oracle session. Oracle gracefully exits user session)

Database Writer (DBW): May have up to 10 concurrent processes. Users always update data within memory. DBW responsible for writing data from memory onto physical disk. Goal: Minimize I/O usage for DBW.

Log Writer (LGWR): Data is written to memory not to disk, minimizing I/O; however, as data is changed, changes are also kept in the Online Redo Logs via the LGWR. Goal: Maximum I/O usage for LGWR.

Checkpoint (CKPT): The physical database is often not up-to-date as compared with data in memory (online redo logs are up-to-date, though). CKPT evaluates the appropriate time to bring physical disk up-to-date, utilizing DBW.

Database: Physical Files

Control File: Stores pointers to other physical files (e.g., online redo logs). Stores items that ensure database integrity (e.g., critical sequence information). Stores backup information. Be sure to specify multiple copies of the control file should one become corrupt. Oracle automatically synchronizes multiple copies.

Redo Log Files: Maintains information necessary to recreate database data. Fixed size files. Consist of a group of log files, each file called a member. Oracle requires at least 2 groups, with 1 member each. Multiple members within a group provides fault tolerance, as Oracle automatically synchronizes multiple members within a group.

Redo Log File Writing: Redo log files are written in a circular fashion (i.e., Log group 1 written to until full. Switch to Log group 2. Log group 2 then written to while Log file 1 is archived. Once Log group 2 is full, switch to Log group 1. Log group 1 is overwritten with new data as Log group 2 is archived, and so on.).

Datafiles: Store physical database data. At least 2 datafiles are required; however, several datafiles are typical. Datafile size only limited to hardware and operating system.

Posted via Woodland Blog (http://dramse01.blogspot.com)