Oracle tutorial: Oracle Database and Oracle Instance

Last updated on August 15th, 2015 at 05:24 pm

Oracle Tutorial:Oracle Database and Oracle Instance

What is Oracle Database

Oracle database is comprised of a set of operating system files containing data entered by users or applications and structural information about the database itself called database metadata. Information is stored persistently in these files.A database  refers to the physical and logical components of a specific database, and its operation

What is Oracle Instance

An Oracle instance consists of the shared memory structures and background
processes that run the Oracle database. You can have an instance without a
database (for example, when you have not yet created a database), and if a
database exists, it can be open or not.You can have multiple instance like in case of Real Application clusters(RAC) to access the same Oracle database

It is basically the architecture to view or update the data contained in the database. Therefore before the database can be used, the database instance must be started. When the database
instance is not available, your data is safe in the database but it cannot be accessed by
any user or application.
The properties of a database instance are specified using instance initialization
parameters. When the instance is started, an initialization parameter file is read and
the instance is configured accordingly.

Oracle Instance consists of two components

1) Memory Components which mainly has two Parts Sytem Global Area(SGA )  and Process Global Area (PGA)

System Global Area (SGA)

The SGA is a shared memory area that contains data and control information for the
instance. Multiple users can share data within this memory area (controlled by Oracle)
and information stored in the SGA can avoid repeated access from physical disk, a
time consuming operation.
For optimal performance, the SGA should be large enough to avoid frequent disk
reads and writes.
The SGA has several subcomponents .The short description of them is available belowa) Buffer Cache Before any data stored in the database can be queried or modified, it must
be read from disk and stored in memory. The buffer cache is the component of the SGA that acts as the buffer to store any data being queried or modified. All user processes connected to the database share access to the buffer cache.
b) Shared Pool The shared pool caches information that can be shared among users.
Some examples:

i)SQL statements are cached so that they can be reused
ii)Information from the data dictionary such as user account data, table and index descriptions, and privileges is cached for quick access and reusability
iii) Stored procedures, which are executable code that is stored in the database, can be cached for faster access
c) Redo Log Buffer This buffer improves performance by caching redo information (used for
instance recovery) until it can be written at once and at a more opportune time to the physical redo log files that are stored on disk.
d) Large Pool This is an optional area that is used for buffering large I/O requests for
various server processes2) Background processes.

Background processes varies with Oracle database release. I am listing here the basic database processes

a) Database Writer (DBWn) The database writer writes modified blocks from the database
buffer cache to the files on disk. Oracle allows a maximum of 20 database writer processes.
b) Log Writer (LGWR) The log writer process writes redo log entries to disk. Redo log
entries are generated in the redo log buffer of the SGA and the logwriter process writes the redo log entries sequentially into an online redo log file.
c) Checkpoint At specific times, all modified database buffers in the SGA are written to the datafiles by a database writer process (DBWn). This event is called a checkpoint. The checkpoint process is responsible for signalling DBWn at checkpoints and updating all of the datafiles and control files of the database to indicate the most recent checkpoint.
d) System Monitor (SMON) The system monitor performs crash recovery when a failed instance
starts up again.
e) Process Monitor (PMON) The process monitor performs process recovery when a user
process fails. It is responsible for cleaning up the cache and freeing resources that the failed process was using.

f) Archiver (ARCn) When the database is running in archive log mode, one or more
archiver processes copy the redo log files to archival storage when
the log files are full or a log switch occurs

Oracle Instance startup

Oracle instance are startup as belowa) login to sqlplus as sysdba
b)  startup  -> It starts the Oracle instance  .It also mount and open the database
startup mount->It starts the Oracle instance  .It mounts the database only
startup nomount->It starts the Oracle instance  only. No mount happen

Leave a Reply