Differences between Oracle instances and databases

Those who are new to Oracle will be confused about instances and databases. What exactly does an instance represent? Why does this concept emerge? Oracle instance = memory used by process + process (SGA)
An instance is a temporary thing. You can also think that it represents the status of a database at a certain time point!
Database = redo file + control file + data file + temporary file
A database is a permanent collection of files.
Relationship between Oracle instances and databases
1.
Temporary and Permanent
2.
The instance can start startup nomount independently without data files, which is usually meaningless.
3.
An instance can only load (alter database mount) and open (alter database open) A database during its lifetime.
4.
A database can be loaded and opened by many instances at the same time (that is, RAC). The role of instances in the RAC environment can be fully reflected!

The following is a detailed explanation of instances and databases:
It is easy to confuse two words in the Oracle field, namely, "instance" and "Database ). As an oracle term, these two terms are defined as follows:
Q
Database: a collection of physical operating system files or disks. Use ORACLE 10 G The database may not be used as a separate file in the operating system, but its definition remains unchanged.
Q
Instance: A group of Oracle background processes/threads and a shared memory zone. These memories are shared by threads/processes running on the same computer. Here, you can maintain easy-to-lose and non-persistent content (some can be refreshed and output to the disk ). Database instances can exist even if there is no disk storage. Maybe the instance is not the most useful thing in the world, but you can think of it as the most useful thing, which helps draw a line between the instance and the database.
These two words are sometimes interchangeable, but they have different concepts. The relationship between the instance and the database is that the database can be loaded and opened by multiple instances, and the instance can load and open a database at any time point. In fact, to be precise, the instance can load and open a database at most throughout its life! This example will be introduced later.
Are you confused? We will give further explanations to help you understand these concepts. An instance is a group of operating system processes (or multi-threaded processes) and some memory. These processes can operate databases, while databases are just a collection of files (including data files, temporary files, redo log files, and control files ). At any time, an instance can only have a group of related files (associated with a database ). In most cases, the opposite is true: only one instance in a database operates on it. However, the real application cluster (RAC) of Oracle is an exception. This is an option provided by Oracle that allows operations on multiple computers in the cluster environment, in this way, multiple instances can simultaneously load and open a database (located on a group of shared physical disks ). Therefore, we can access the database from multiple computers at the same time. Oracle RAC supports highly available systems and can be used to build highly scalable solutions.
The following is a simple example. Suppose we have just installed Oracle 10 G 10.1.0.3. We execute a pure software installation, excluding the initial "start" database. Nothing except the software.
Through the PWD command, you can know the current working directory (this example uses a computer on a Linux platform ). Our current directory is DBS (if on Windows, it is the database directory ). Run the LS-l command to display the directory as "null ". There is no init. ora file or any stored parameter file (spfile). The stored parameter file will be discussed in chapter 3rd.

Using the PS (Process status) command, you can see all processes run by the user ora10g. Here we assume that ora10g is the owner of Oracle software. There are no oracle database processes.

Then run the IPCS command. This Unix command can be used to display communication devices between processes, such as shared memory and semaphores. Currently, no communication device is used in the system.

Then start SQL * Plus (Oracle command line interface) and connect as sysdba (the sysdba account can do anything in the database ). After the connection is successful, SQL * Plus reports that we are connected to an idle instance:

Our "instance" now only contains one Oracle server process, as shown in bold in the following output. No shared memory is allocated and no other processes exist.


Start the instance now:

The file prompted here is a file required to start the instance. We need a parameter file (a simple flat file, which will be described later ), or you must have a file that stores parameters. Now let's create a parameter file and put the minimum information required to start the database instance (more parameters are usually specified, such as the size of the database block, the location of the control file, and so on ).

Then return to SQL * Plus:

The nomount option is added to the startup command, because we do not want to "LOAD" the database yet (for more information about all the options for starting and disabling the database, see the SQL * Plus documentation ).
Note:
Run the startupcommand on Windows and use the oradim.exe utility to execute a service creation statement.

Now we have the so-called "instance ". All the background processes required to run the database are available, such as process monitor (pmon) and log writer (lgwr). These processes will be detailed in chapter 5th.

Using the IPCS command again, it will first report that shared memory and semaphores are used, which are two important inter-process communication devices on UNIX:

Note that we do not have a "Database" yet! At this time, only the name of the database (in the created parameter file), but not the database. If you try to "LOAD" the database, it will fail because the database does not exist at all. Create a database. Some people say that creating an Oracle database is cumbersome. Is that true? Let's take a look:

It is so easy to create a database here. However, in practice, you may need to use a slightly complex create database Command, because you may need to tell oracle where to put log files, data files, control files, and so on. However, we now have a fully operational database. You may also need to run $ ORACLE_HOME/rdbms/admin/catalog. SQL scripts and other catalog scripts are used to create the data dictionary we use every day (some views we have not used in this database, such as all_objects, the database already exists. You can simply query some Oracle v $ views (specifically v $ datafile, V $ logfile, and V $ controlfile) to list the files that constitute the database:

Oracle uses the default settings to put all the content together and create a database as a group of persistent files. If you close the database and try to open it again, you will find that the database cannot be opened:


A single instance can only load and open one database during its lifecycle. To open this (or other) database, you must discard the instance and create a new instance.
Reapply:
Q
An instance is a group of background processes and shared memory.
Q
A database is a collection of data stored on a disk.
Q
An instance can only load and open one database for a lifetime.
Q
Databases can be loaded and opened by one or more instances (using RAC.
As mentioned above, in most cases, there is a one-to-one relationship between the instance and the database. This may lead to confusion between the two. From the experience of most people, databases are instances and instances are databases.
However, this is not the case in many test environments. On my disk, there can be five different databases. Only one Oracle instance runs at any time on the test host, but the database it accesses may be different every day (or even every hour), depending on my needs. With different configuration files, I can load and open any of the databases. In this case, I only have one "instance" at any time, but there are multiple databases that can only access one of them at any time point.
So now you should know that if someone talks about an instance, it refers to the process and memory of oracle. When talking about databases, it refers to the physical file that saves data. You can access one database from multiple instances, but one instance can only access one database at a time.