An Oracle Server allows clients and administrators to connect to one or more instances. The RAM memory associated with the server is known as the Program Global Area (or PGA).
A Database instance is composed of the SGA (the System Global Area) and various background processes that Oracle uses to serve the database.
Each instance is identified by a service name (or
system identifier (or SID) which was used
in older Oracle versions).
The service name identifies the connection information needed (other than
the host name) for a client to use to connect to the instance.
When connecting to an instance the client supplies this name as the
The service names and their related information are stored in a file
Note many DBAs use the terms instance and database interchangeably. But strictly speaking in Oracle a database refers only to the data that is stored on a disk.
The SGA is the RAM managed by an Oracle instance, which contains per-process memory and shared (global) memory. The SGA is used to manage the interaction between the processes that make up an instance and the database itself. At any given moment the SGA may contain rollback segment blocks, SQL statements, cached data segment blocks, and multi-threaded server information.
The processes that make up an instance are:
JOB$table, by creating a
J#process for each job.
The SMON, PMON, DBWR, LGWR, and CKPT processes are required. The others may or may not be used on any given server, depending on the configuration.
To access a database, the instance is STARTed using the Oracle Server Manager or the Oracle Enterprise Manager (the OEM). Once the instance is STARTed (a.k.a. the nomount state), the database is then MOUNTed, and then it can be OPENed. Users can then CONNECT to the OPEN instance in order to access the data. (When the database instance is SHUTDOWN it enters a CLOSED state.)
When a user CONNECTs to an Oracle server, a user process is created at the user's host that handles the communication to the Oracle server. When a connection request is received, the server creates a server process. This is known as a dedicated server. For systems that support multi-threading efficiently an alternative is to use a single server process. This is known as a shared server. In this case it is a dispatcher process that assigns connections to server processes.
Normally there is one instance for each database. However when using a Real Application Cluster or RAC (formally known as an Oracle Parallel Server or OPS) a single database may be MOUNTed and OPENed on multiple instances.
When accessing an Oracle server a user generally only cares about the logical structure. However a DBA is concerned with both the logical views of the instance as well as the physical structure of the database.
|Logical Versus Physical Oracle Database Structures|
|Logical Structure||Physical Implementation|
An instance is composed of one or more tablespaces. Each tablespace holds and manages the memory for one or more objects such as tables. A tablespace is thus the logical view of a database as a collection of objects. Each tablespace is directly associated with one or more datafiles.
Tables are objects that represent a collection of similar data. Tables and other objects that represent data are composed of one or more extents, which are contiguous "chunks" of memory. As an object grows and shrinks (as data is added and removed), the number of extents can grow and shrink as well. (Not all extents are the same size. This is controlled by the DBA.) Tables are composed of rows (or records) and columns (or attributes).
An object in Oracle is any "thing" that can be owned by some user (i.e., belong to some collection of objects known as a schema). Examples include tables, indexes, views, sequences, ... A tablespace may be composed of objects from a number of different schemas.
Each object that relates directly to data in the database (e.g., tables and indexes) is stored in the datafiles for that database in a segment. Some objects (e.g., views, sequences, stored procedures, synonyms) are not stored in their own segments. However segment is sometimes used synonymously with object. In addition there are three special types of segments that do not contain objects:
Segments are composed of one or more extents. In turn extents are composed of a whole number of blocks, which is the smallest unit of memory managed by Oracle. Choosing correct block and extent (and SGA) sizes is an important part of performance tuning an Oracle server.
The physical structure of Oracle uses four types of files:
CONFIG.ORA, contain "static" configuration settings. These setting cannot be changed without restarting the instance. Although there are commands to change the settings dynamically, such settings are kept in RAM only and revert to the static setting when the instance is restarted.
alter systemcommands to update parameter values you can specify if you want the change to be retained permanently (in the SPFILE), apply to the current session only, or both.
Filenames in Oracle usually obey the following naming convention: "<prefix><number>.<extension>", where the common extensions and prefixes are:
.CTLfor control files
.ORAfor parameter files
.LOGfor redo log files
CONConfiguration parameter file
DATDatafile (for say tables)
IDXDatafile (for indexes)
SPFILEInitialization parameter file
LOGRedo log file
RBSRollback (or undo) segment datafile
TMPTemporary segment datafile
TOOLApplication tool information (configuration)
USRUser information datafile