We will discuss the difference between the Oracle database and Oracle instance in this post
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 instances like in the 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.
Component of Oracle Instance
Oracle Instance consists of two components
1) Memory Components which mainly have two Parts System 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 the 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. A short description of them is available below
Buffer Cache
- Before any data stored in the database can be queried or modified, it must be read from the 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.
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 are 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
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.
Large Pool
- This is an optional area that is used for buffering large I/O requests for various server processes
Background processes
Background processes vary with Oracle database releases. I am listing here the basic database processes
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.
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 log writer process writes the redo log entries sequentially into an online redo log file.
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 data files and control files of the database to indicate the most recent checkpoint.
System Monitor (SMON)
The system monitor performs the crash recovery when a failed instance starts up again.
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 (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 below
(a) login to sqlplus as sysdba
(b) startup -> It starts the Oracle instance. It also mounts and opens the database
(c)startup mount->It starts the Oracle instance. It mounts the database only
(d) startup nomount->It starts the Oracle instance only. No mount happen
Hope you like this post on difference between the Oracle database and the Oracle instance
Related Articles
Oracle database recovery: Oracle database recovery, RMAN recovery, incomplete recovery, media recovery
How to Create the container database: How to Create the container database in Oracle 12c using DBCA or manually using sql scripts, with OMF, Important things to check before creating
Undo and redo : What is Undo and redo in Oracle database, what is undo, what is redo
https://docs.oracle.com/cd/B19306_01/server.102/b14220/intro.htm