Difference between Database and Instance in Oracle

DATABASE:

Database is the term used to describe the physical files that store information.

There are three types of physical files:

• Data files:-These store all the data that is needed by a database engine.
• Control files:-These store metadata(ie data about data) about the rest of the database for use by the Oracle engine.
• Redo-log files:-These files record all changes made to the data and serves as backup and helps in recovery.

INSTANCE:

Database files needs specific memory structures and processes to interact with data in the database.Without them,there is no use with having only the database files.

So to put in a nutshell,A memory structure and the background process that is needed to access data in database is what is called an instance.

An instance has two major memory structures:

The System Global Area or Shared Global Area (SGA):-It stores information in memory that is shared by the various processes in Oracle.

The Program Global Area or Private Global Area (PGA):-It contains information that is private to a particular process.

SGA contains:

Database buffer cache that is used to cache information read from the data files.
Data dictionary cache used to cache metadata information.
Library cache that caches recently used SQL and PL/SQL statements.

PGA allocates memory for information such as variables,arrays,and cursor information that is private to each process.

The instance also contains numerous background processes that cooperate to fulfill all the various functions needed like Database Writer that writes all changes to the database,and Process Monitor that cleans up after failed user processes.

For an SQL server,A database comprises of a collection of objects such as Tables.An SQL server installation may contain many databases.