Database Concepts and Architecture

What is an Oracle Database?

A database is a collection of data. An Oracle database holds its data in a coordinated set of files stored on disk, including following types:

• Parameter Files - The Oracle Parameter File (PFILE or SPFILE) holds initialization parameters which indicate where the control files are, how memory is allocated, how logging is performed, and other characteristics of the Oracle instance.
• Control Files - The Control File stores information needed to verify that the database is consistent, including a list of all database files making up the instance and other important data.
• Redo Log Files - The Redo Log files store a sequence of entries describing all actions taken against the database. This data is used to recover a database in the event of instance failure.
• Data Files - The Data Files contain blocks of data which store database objects (e.g., tables, indexes, materialized views, etc.) in the database.
• Temp Files - The Temp files contains data used temporarily including intermediate results, sort results and so forth.

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

---

What is an Instance?

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

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

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 - Memory Manager process which will help in automatic memory management when use sga_target,memory_target
• LSP0 - Logical standby coordinator process (controls Data Guard log-application)
• MRP - Media-recovery process (detached recovery-server process)
• MMON - This is the process which will write to AWR base tables ie WR$ tables
• 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.

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 by many instances.

Tablespaces

Disk space needs to be allocated for certain database objects (like tables and indexes). 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;

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;

Schema Objects

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

• Table (heap, IOT, temporary, etc.)
• Index
• View
• Materialized View (snapshot)
• Sequence
• Synonym
• Cluster
• Trigger
• Procedure
• Function
• Package (containing procedures and functions)

Etc.