Database vs Instance
Oracle is a RDBMS (object-relational database management system) that widely used in enterprises. It is developed by the Oracle Corporation. An Oracle system is made up of at least one Instance and a database. Instance is a collection of processes that communicate with the data storage. Database is the actual storage, which holds the collection of files. However, the term Oracle database is used to refer to the whole Oracle database system (instances and databases). Because of this, there is always some confusion for the beginners between the terms database and instance.
What is Instance?
Instance is a collection of processes running on top of the operating system and the related memory that interacts with the data storage. The instance is the interface between the user and the database. Processes capable of communicating with the client and accessing database are provided by the instance. These processes are background processes and they are not enough to maintain the ACID (Atomicity, Consistency, Isolation, and Durability) principle in the database. So, an instance also uses few other components such as memory cache and buffers. More specifically, an Instance is composed of three parts. They are SGA (System Global Area), PGA (Program Global Area) and background processes. SGA is a temporary shared memory structure, which has a life span of the instance startup to its shutdown.
Database
The Oracle database refers to the actual storage of the Oracle RDBMS. It is made up of three main components. They are control files, redo files and data files. Optionally there could be password files in the database. The control files keep track of all the data files and redo files. It also helps keep the database integrity intact by keeping track of the System Change Number (SCN), timestamps and other critical information such as backup/recovery information. Data files keep the actual data. At the time of database creation, at least two data files are created. These files are physically seen by the DBA (Database Administrator). File operations such as renaming, resizing, adding, moving or dropping can be carried out on data files. Redo log files (also known as online redo logs), keep the information regarding the changes to the database with the chronological information. This information is needed in case the user needs to redo all or some of the modifications on the database. In order for an instance to manipulate the data of the database, it should open it first. An instance could open only one database. However, a database can be opened by multiple instances.
What is the difference between Database and Instance?
The terms instance and database in Oracle RDBMS are highly related, but they refer to the two different components within the system. The database refers to the actual storage of the RDBMS, while Instance is a collection of processes running on top of the operating system and the related memory that interacts with the data storage. Instance needs to open the database before manipulating data. Multiple instances can open a single database, but an instance cannot open multiple databases.