24 Oct Architecture of Oracle Database Management System
The Oracle Database Management System provides an open, comprehensive, and integrated approach to information management. An Oracle server consists of an Oracle database and an Oracle instance.
However in Oracle RAC, there may be more than one instance accessing the same database. An instance and a database may have a many-to-one relationship when using RAC, and a one-to-one in case of single-instance non-RAC Oracle deployments.
The following steps describe a basic Oracle configuration where the user and associated server process are on separate machines connected via a network:
- An instance is running on the computer that is executing Oracle, often called the host or database server.
- A computer used to run an application (a local machine or client workstation) runs the client as a user process. The client application attempts to establish a connection to the server using the appropriate SQL*Net/Oracle network driver.
- The server is running the proper SQL*Net/Oracle network driver. By default, the server detects the connection request from the application and creates a (dedicated) server process request on behalf of the user. Other types of database connections are possible, such as “shared server” connections as well as connections coming from application servers by proxy through connection pools.
- The user executes a SQL statement and commits a transaction. For example, the user changes a name in a field or row of a table.
- The server process receives the statement and checks the shared pool for a SQL statement resident in the shared SQL area that contains an identical SQL statement. If a shared SQL statement is found, the server process checks the user’s access privileges to the requested data and the previously existing shared SQL statement is used to process the new statement; if not, a new shared SQL area is allocated for the statement so that it can be parsed and processed.
- The server process retrieves any necessary data from the actual data file (tables) after checking for the respective data blocks in the buffer cache.
- The server process may modify data in the buffer cache, which is a primary component of the system global area (SGA). Once the transaction is committed, the log writer (LGWR) process immediately records copies of the transcription of the transaction from the log buffer to the online redo log file. At this point, the database writer (DBWR) process writes modified data blocks permanently to the data files on disk when doing so is efficient.
- If the transaction is successful and disk acknowledgment is received, the server process sends a message across the network to the application. If it is not successful, an appropriate error message is transmitted.
Throughout this entire procedure, the other background processes run, watching for conditions that require intervention. In addition, the database server manages other user transactions and attempts to minimize contention such as locking, deadlock conditions, and bottlenecks on processing resources.