Oracle Tutorial:Oracle Database and Oracle Instance
What is Oracle Database
What is Oracle Instance
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
System Global Area (SGA)
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.
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.
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.
archiver processes copy the redo log files to archival storage when
the log files are full or a log switch occurs
Oracle Instance startup
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