Oak,
Yours are all excellent questions. I'll be glad to offer some insight into the definitions and distinctions for the terms SID, Instance, and Database insofar as they relate to the Oracle World.
First of all, Oracle uses the term, "Database", very differently from how other database vendors use the term.
An easy way to distinguish an Oracle
database from an Oracle Instance is that an Oracle database is what you have left when someone turns off the power to the computer. Specifically, under power-off condition, you have
files left that Oracle divides into three major groups:[ul][li]
Control files, which record the physical file names of all other database files and high-level characteristics that define the database.[/li][li]
Database data files, which provide the storage space for Oracle's tablespaces, which, in turn, hold tables, indexes, et cetera.[/li][li]
On-line Redo Log files, which store a sequential list of all changes to the database. These redo log files are what we use when we must recover a physically or logically damaged database.[/li][/ul]An
Oracle Instance [which always has a
System
IDentifier (SID)] exists only when the computer is running. The Instance is made up of memory structures and executing programs that read and change the contents of the database. The main
memory structures of an Oracle instance is the System Global Area (SGA). The SGA is divided into three main sub-sections:[ul][li]
Shared Pool. This memory structure contains the SQL Area in which Oracle stores both human-readable copies of each SQL statement and binary, executable interpretations of the SQL statements. This memory structure also houses the program/work areas for each connection to the Oracle database.[/li][li]
Database Buffer Cache. This chunk of memory holds database data that we read from and update to the database data files. When one SELECTs data from a table, if it is not already in the database buffer cache, the
server that is handling our SQL requests must go out to the database data files and read approriate blocks of data into open blocks in the database buffer cache that satisfy our SELECT. If we INSERT, UPDATE, or DELETE rows, those changes take place in database buffer cache blocks, then, at appropriate times, a program called the Database Write (DBWR) physically writes blocks from the database buffer cache back out to the database data files on disk.
Oracle also writes (sequentially) the Data Manipulation Language (DML) changes (that result from INSERT, UPDATE, and DELETE statements) to the next Instance memory structure, the
Log Buffer.
[/li][li]
Log Buffer. This memory structure is an area that Oracle sets aside to gather up changes as they occur against the database. At appropriate times, a program called the
Log Writer (LGWR) copies all of the information that is in the Log Buffer out to the On-Line Redo Log files. This process writes the changes sequentially. The log-buffer entries exist mainly for "reconstructing" the database in the event of a power failure, a SHUTDOWN ABORT, or some sort of damage or disruption to a database data file.[/li][/ul]In addition to the
memory structures (SGA) of an Oracle Instance, there are the background processes that do the actual work of the database. There are many categories of background processes. Oracle adds more background processes as Oracle versions become more sophisticated. Among the background processes that exist in Oracle instances (at minimum) all the way back to Oracle Version 7 are (including those I have already mentioned):[ul][li]Database Writer (DBWR)[/li][li]Log Writer (LGWR)[/li][li]Archiver (ARCH)[/li][li]Checkpoint (CKPT)[/li][li]System Monitor (SMON)[/li][li]Process Monitor (PMON)[/li][/ul]So, the
memory structures and
background processes compose an Oracle Instance.
Now, the name of an Oracle Instance and the name of a/the database that the instance manages can be two entirely different names. But since the names can be the same for those two components, most organizations name the Instance and the Database by the same name.
Persons who are authorized to connect to, and manipulate, databases connect to an Oracle Instance with a
Oracle User Name, which a Database Administrator creates using the "CREATE USER..." SQL command. Once an Oracle User exists, the User can create (and thus
own) tables, indexes, and many other types of database objects. Other names by which you may hear reference to an Oracle USER are: "SCHEMA" and "LOGIN".
All of the Oracle USERs and the objects that they own (e.g., tables, indexes, et cetera) that are managed by a single Oracle
Instance compose one Oracle
database. (Other database servers, e.g., SQL Server, MySQL, EnterpriseDB, DB2, Informix, et cetera, refer to each
schema as a separate
database. So each installation would contain, in their terminology, many
databases. This is an important terminological distinction when speaking with Oracle professionals.)
Now, to bring all of these concepts together: When an Oracle Database Administrator does a "startup" command, there are three "levels" (statuses) to which the administrator can bring the database:[ul][li]
STARTUP NOMOUNT. This means that Oracle has created the
memory structures and started the
background processes of the instance only. The database itself is not yet accessible.[/li][li]
STARTUP MOUNT. All of the work of STARTUP NOMOUNT has occurred, with the addition that the database's CONTROL FILES have been opened, but the database is still not yet accessible.[/li][li]
STARTUP OPEN. All of the work of the previous two steps has occurred, but now Oracle has read the contents of the CONTROL FILES and has opened the files to which the CONTROL FILES point. If Oracle has been able to successfully open all of the database data files and on-line redo log files that appear in the CONTROL FILES, then the database is now OPEN and ready to read from and update.[/li][/ul]It is absolutely reasonable to have multiple Oracle Instances and their associated databases on a single *nix or Windows environment. In fact, on the *nix boxes that I manage, there are usually from 1 to 5 Oracle Instances and Databases that reside on a single machine.
I hope that all of this is understandable. If you have follow-up questions, we welcome them.
![[santa] [santa] [santa]](/data/assets/smilies/santa.gif)
Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services:
www.dasages.com]