Database Concepts and Architecture

From Oracle FAQ

Jump to: navigation, search

Contents

[edit]

What is an Oracle Database?

A database is a set of files stored on disk. A database consists of the following types of files:

  • Parameter Files - Oracle Parameter Files (PFILE or SPFILE)
  • Control Files - the most important file. Stores pointers to all database files and other important data to keep the database consistent.
  • Redo Log Files - Stores the already commited data blocks (after image) used for instance recovery.
  • Data Files - The actual data segments.
  • Temp Files - temporary data - like sort segments.

Data and Temp files are grouped into tablespaces (see definition below).

[edit]

What is an Instance?

An instance is a collection of Oracle background processes and shared memory structures.

[edit]

Memory Areas

  • SGA - System Global Area
The SGA consists of the following four (five if MTS) parts:
  • Fixed Portion
  • Variable Portion
  • Shared pool
  • java pool
  • PGA - Process Global Area
  • UGA - User Global Area
[edit]

Processes

Oracle uses many small (focused) processes to manage and control the Oracle instance. This allows for optimum execution on multi-processor systems using multi-core and multi-threaded technology. Some of these processes include:

  • PMON - Process Monitor
  • SMON - System Monitor
  • ARCn - Redo Log Archiver
  • LGWR - Redo Log Writer
  • DBWn - Database Writer
  • CKPT - Checkpoint process
  • RECO - Recoverer
  • CJQn - Job Queue Coordinator
  • QMNn - Queue-monitor processes
  • Dnnn - Dispatcher Processes (multiplex server-processes on behalf of users)
  • Snnn - Shared server processes (serve client-requests)
  • MMAN - Internal process (used for internal database tasks)
  • LSP0 - Logical standby coordinator process (controls Data Guard log-application)
  • MRP - Media-recovery process (detached recovery-server process)
  • MMON - Memory-monitor process
  • MMNL - Memory monitor light (gathers and stores AWR statistics)
  • PSP0 - Process-spawner (spawns Oracle processes)
  • RFS - Remote file server process (archive to a remote site)
  • DBRM - DB resource manager (new in 11g)
  • DIAGn - Diagnosability process (new in 11g)
  • FBDA - Flashback data archiver process (new in 11g)
  • VKTM - Virtual Timekeeper (new in 11g)
  • Wnnn - Space Management Co-ordination process (new in 11g)
  • SMCn - Space Manager process (new in 11g)

An instance can mount and open one and only one database.

A database can normally only be mounted and opened by one instance. However, when using Real Application Clusters (RAC) a database can be mounted and opened by many instances.

[edit]

What's the relationship between database and instance?

  • An instance can mount and open one and only one database.
  • Normally a database is mounted and opened by one instance.
  • When using RAC, a database may be mounted and opened many instances.
[edit]

Tablespaces

Disk space needs to be allocated for certain database objects (like tables and indexs). In Oracle, disk space from the operating system is allocated to tablespaces. Database objects are then created within a tablespace.

To list all tablespaces: 

SELECT tablespace_name FROM dba_tablespaces;

Commands used to create new tablespaces: 

CREATE TABLESPACE ts1 DATAFILE '/u01/oradata/orcl_ts1_01.dbf' SIZE 100M;

CREATE UNDO TABLESPACE undots1 DATAFILE '/u01/oradata/orcl_undots1_01.dbf' SIZE 100M;

CREATE TEMPORARY TABLESPACE temp1 TEMPFILE '/u01/oradata/orcl_temp1_01.dbf' SIZE 100M;
[edit]

Database Users

A database consists of multiple users that one can connect to. Each user has its own namespaces - objects within it cannot share the same name.

To list all the database users: 

SELECT username FROM dba_users;

To create a new user: 

CREATE USER scott IDENTIFIED BY tiger;
[edit]

Schema Objects

Schema objects are created within a schema (Oracle user). Here are some of the object types that can be created:

Etc.

Retrieved from "http://www.orafaq.com/wiki/Database_Concepts_and_Architecture"
Personal tools