Database vs Database Instance:

Database – Set of files residing on OS layer that store data, do not require database instance.

Database Instance – Set of memory structures that manage database files.  An instance includes shared memory area and set of background processes. Instances can exist independent of database files.

 

 

Basic vs RAC Configuration:

Basic – In Oracle 12c Basic configuration, you have one database instance per database.

RAC – In an Oracle 12c RAC configuration you have multiple instances per database which allows for improved scalability, performance and fault tolerance.

 

Primary Database vs Data Guard Database:

Primary Database – Is the database which performs all actual processing and storage of the RDBMS for active application processes.  A primary database can be related to zero or many standby databases.  Data Guard Database – Data Guard Databases are standby databases which draw changes from a primary database.  These standby databases can be: physical (byte for byte copies, kept in sync by applying primary database redo logs) or logical (where they are kept in sync by SQL statements by Oracle Streams).

 

 

Memory Structures of Oracle Instance:

System Global Area (SGA) – shared memory structures that contain data and control info for single instance.  SGA is shared by all server and background processes. Allocation and deallocation of space is performed in units call granules.   You can view components in view V$SGASTAT.  Examples include cached data blocks and shared SQL areas.

Program Global Area (PGA) – PGA is not shared memory.  It contains data and control information for single oracle process.   One PGA is created at start of server process and background process.  Total size of Instance PGA is total space of all PGA.

User Global Area (UGA) – Memory associated to just a single user session.

Software Code Areas – Memory area to store code being executed or can be executed.

 

 

Parts of SGA:

1. Database Buffer Cache – Contains data blocks read from data files. All users share the buffer cache and it optimizes physical I/O by keeping frequently accessed blocks in memory.  It uses Least Recently Used (LRU) to determine what blocks to keep.
2. Redo Log Buffer – Circular buffer that contains all changes by DML and DDL and allows for recovery. Redo use continuous space in buffer and LGWR process writes them to disk.
3. Shared Pool – Caches program data required by server to include SQL, PL/.SQL code, system parameters, data dictionary info. Every SQL executed requires access to shared pool.
4. Large Pool – optional memory area, used for large memory allocation which are too big for shared pool, such as UGA, Oracle XA interface, and RMAN.
5. Java Pool – Stores session specific Java code and data within JVM.
6. Steams Pool – used by Oracle Streams. Stores buffered queue messages and stream capture and apply process.  It begins with size of 0 and grows dynamically as needed.
7. Fixed SGA – Internal housekeeping area, contains general information about database and instance that other background processes need. Size of fixed SGA is set by Oracle database and can’t be changed.

 

 

 

Larry Catt

OCP