RDBMS Software, Database and Instance

This was a quick note I wrote for a friend and colleague a few weeks ago :
==========================================================================

Many a times we hear or read statements like "I installed the Oracle Database" OR "I started (or shutdown) the Database".

I'd like to explain the 3 terms "Software" , "Database" and "Instance" and a few additional terms.


1. Software :
1a. What is *installed* is the Oracle RDBMS Software (which may also be called the Oracle Database Software). Alternatively it is called "Oracle Binaries" or "Oracle Executables". This is the software (binary files, library files, make files, default configuration/parameter files) that are installed using the Oracle Universal Installer. The OUI extracts or copies files from the Installation Media (CD/DVD/DownloadedImage) and may relink them before placing them under the ORACLE_HOME.

1b. Here, I will also refer to ORACLE_HOME . This is an important concept. The ORACLE_HOME is where the Software is Installed. Database Instance Parameter files may also be placed here, although they are generally placed under their own admin/ directory under ORACLE_BASE. The OFA "standard" (where "OFA" is variously called "Oracle Flexible Architecture" or , as in "Oracle OFA" "Optimal Flexible Architecture") in earlier versions has never been mandatory but a good guideline. ORACLE_BASE is the "parent" directory which can hold 1 or more ORACLE_HOMEs. Each ORACLE_HOME holds a different Version/Release/PatchSetLevel of the Oracle Database Software. For each database, the admin directories would be under ORACLE_BASE, not under ORACLE_HOME.
However, the OFA has been documented differently in the 10gR2 Installation Guides. See, for example, the appendix on OFA in the Installation Guide for Windows or the appendix on OFA in the Installation Guide for Linux
This provides for multiple installations of the Release (e.g. 10.2.0) under one directory but as separate "db_n" subdirectories. Thus, the ORACLE_BASE is now what used to be the ORACLE_HOME in earlier versions, and the ORACLE_HOME is a sub-directory of that. Personally, I prefer the older "architecture".

1c. Parameter Files. An installation of the Oracle Software also includes default parameter files that can be used as templates for the init.ora and the listener.ora and tnsnames.ora and other configuration files. If, in the process of installing the Software, you also choose to run the NetConfigurationAssistant, it will configure the listener.ora and tnsnames.ora files with default parameters (or the parameters you specify when running the assistant). If you had also chosen to "Create the Database", the DatabaseConfigurationAssistant (called "dbca"), this tool would create the instance parameter file (init.ora and/or spfile.ora).

When someone says "I installed the Oracle Database" ask him if he
a. Installed the Software
b. and also Created a Database (configuring the Database Instance Parameter files through the "dbca" at the same time)
c. and also Configured the Network

He may have actually done only "a" (which is what I would recommend as a standalone exercise) OR also done "b" and "c" with "a", in which he case did more than "install". He also did "create" and "configure".


2. Database :
The Database is the set of files which actually hold the data. This consists of the Control Files, the Database Files (corresponding to the Tablespaces) and the Redo Log Files (and, optionally, ArchiveLogs of the Redo Logs). A Database is *created*, not installed.

When someone says "Oracle was already installed, so I just installed the database", he should actually be saying "Using the existing installed Software, I created the Database" (and he may, or may not have, started the Database Instance yet !).
When someone says "I shutdown the database", he should actually be saying "I shutdown the Database Instance".


3. Instance (or Database Instance) :
The Database Instance is the set of *processes* (a single process, multi-threaded, in Windows) that are "running" on the Operating System. These include the Background Processes and the Server Processes. The processes share a common "SGA" ("System Global Area") and communicate with each other through IPC. Thus, Oracle on Unix and Linux is a Multi-Processing architecture (while on Windows it is Mulit-Threaded). Each client that connects to the Database Instance gets a Server Process forked for it (unless the Database Instance is configured to run Shared Servers and the Client requests a Shared Server connection [Shared Servers were also known as "MultiThreaded Servers" in earlier vresions].

When someone says "I am unable to connect to the database" , he should actually be saying "I [or 'My Client program [SQLPlus ?]'] is unable to connect to the Database Instance".

When the DBA says "the Database has crashed", he should actually be saying "The Database Instance has crashed [or terminated with error]".

When the IT Manager says "Startup the Database on the second node of the RAC Cluster", he should actually be saying "Startup the [second] Database Instance, to run on the second node of the Oracle RAC [Cluster]".


4. Multiple Database Instances, Multiple Databases :
On a particular storage set and on a particular server, you can also have more than 1 Database. You can have 2 or 3 or 4 Databases, whereby Instances are started from the same ORACLE_HOME or from separate ORACLE_HOMEs (which separate ORACLE_HOMEs may or may not be the same Version, Release and Patchset of the Oracle [RDBMS] Software.


Oracle OPS (Oracle Parallel Server) and RAC (Real Application Cluster) consists of two or more Database Instances on two more nodes [although there are ways to have RAC Instances running on the same node] accessing the same Database [I prefer "the same database" instead of "a common database" or a "shared database", although I would be comfortable with "on shared disks" ]. Similarly, the shared storage can host more than 1 Database, of which none or some or all are RAC Databases.

In an OS Cluster , you can have one more Databases configured to run in
a. Failover :- the surviving node takes over (owns) the IP address of a failed node and starts the Database Instance
b. RAC : both (or all nodes) run Database Instances accessing the same Database(s).


5. Connectivity :
Client Software not installed on the same Host (server) as the Database Server software can connect to the Database Instance by first sending a connection request to the Listener. This configuration definition is in the tnsnames.ora file on the client which identifies the Host, the Listener Port and the Database Instance SID or Service Name, and in the listener.ora file on the server which identifies the ORACLE_HOME, the Instance SID or Service Name. The Listener does no Authentication (other than verifying the requested SID or Service Name) and "forks" a Server Process and hands over the Client connection to the Server Process. It is the Server process that then does the Database Authentication. After that, the Listener has no hand in the communication between the Server and Client. The Listener can be shutdown without affecting existing connections (although, of course, this will prevent new connection requests from succeeding). In a Shared Server architecture, the Listener does the handover to the Dispatcher which then redirects the Client to an available Shared Server process.
Note that it should be clear that clients (e.g. 'sqlplus') running from the same ORACLE_HOME installation can connect to a running Database Instance even when the Listener has been shut down.


6. Backup and Recovery :
Transactions are captured in the Online Redo Logs (which are archived as ArchiveLogs). Thus, Backup and Recovery consists of the Database and the ArchiveLogs (and, additional, configuration files). In an RAC environment, each Instance has it's own Redo Thread. Backup and Recovery must capture and restore *all* the Redo threads of the Databases, else the database is not recoverable.