The Database
In Oracle, a database is used to describe the physical files used to store information. There are three types of physical files:
Data files store—you guessed it—all the data that caused us to get a database engine to begin with.
Control files store metadata about the rest of the database for use by the Oracle engine.
Redo-log files are used to record all changes made to the data for use in backup and recovery.
Regardless of how many files are used, they are all part of one database.
SQL Server uses the term database very differently. It's used to define a collection of objects such as tables. Each of these collections is stored in a separate set of files. One SQL Server installation typically contains many databases. In fact, the SQL Server installation process itself creates four databases.
Understanding how each vendor uses the term is critical to understanding the literature written about each of the products.
The Instance
Database files themselves are useless without the memory structures and processes to interact with the database. Oracle defines the term instance as the memory structure and the background processes used to access data from a database.
An instance has two major memory structures:
The System Global Area, also known as the Shared Global Area (SGA) stores information in memory that is shared by the various processes in Oracle.
The Program Global Area, also known as the Private Global Area (PGA) contains information that is private to a particular process.
The SGA contains, among other things, the database buffer cache that is used to cache information read from the data files, a data dictionary cache used to cache metadata information, and a library cache that caches recently used SQL and PL/SQL statements. The PGA is used to allocate memory for information such as sort space, 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. Some examples of these processes include the Database Writer, responsible for writing all changes to the database, and the Process Monitor, responsible for cleaning up after failed user processes.