Oracle Relational Database

In this section we will study ORACLE in detail and understand how it works. It covers the ORACLE architecture, and describes how to start and end the ORACLE server.

What is Oracle?

Oracle is the most widely used Relational Database Management System (RDBMS) in the world. Oracle corporation offers this RDBMS as its core product in its product line, which also includes a fourthgeneration application development tool set, reporting tools, and utilities.

The powerful relational model has evolved from the hierarchical and network data models. The most widely accepted and used data model is the relational model. A relational model has three major aspects to it:

Structures Defined objects that contain data and are accessible to the users
Operations Defined actions that manipulate data or schema objects
Rules Laws that govern how data can be manipulated and by whom

Why use Oracle?

The Oracle database was designed using the relational data model and uses Structured Query Language (SQL) as its query language. Using the Oracle database provides these major advantages.

Oracle is an "open" system and adheres to the industry accepted standards for data access language (SQL)
Oracle supports databases of all sizes, from several bytes to gigabytes in size.
Oracle supports a large number of concurrent users. It minimizes data contentions and guarantees data concurrence.
Oracle supports a true client/server environment. It enables processing to be split between the database server and the client application programs.
Oracle provides fail safe security features to limit and monitor data access and usage.
Oracle behaves the same on all platforms (Windows, UNIX, Macintosh, and Mainframes). This is because more than 80 percent of the internal code of Oracle is the same across all platforms and operating systems.

The relational data model offers the following advantages over other traditional data models:

A data model that is easily understood and visualized
Integrity laws that are easy to develop and understand to protect data and data structures
reduced data storage and redundancy
Independence of physical data storage and logical database structure

How to use the Oracle RDBMS

The Oracle RDBMS can reside on a variety of hardware platforms and operating systems, including personal computers. This session gives an overview of the Oracle server architecture and basic concepts for operating and using the Oracle database server.

Oracle system Architecture

Oracle processing capability comes from its memory structure and processes. The mechanisms of ORACLE execute by using memory structures and processes. All memory structures exist in the main memory of the computers that constitute the database system. Processes are jobs or tasks that work in the memory of these computers.

Memory Structures

ORACLE creates and uses memory structures to complete several jobs. For example, memory is used to store program code being executed and data that is shared among users.

1. System Global Area (SGA): This is a shared memory region allocated by ORACLE that contains

data and control information for one ORACLE instance. An SGA and the ORACLE background processes constitute an ORACLE instance. The SGA is allocated when an instance Starts and deallocated when the instance shuts down. Each instance that is started has its own SGA.

Database Buffer Cache: Database buffers of the SGA store the most recently used blocks of the database data; the set of the database buffers in any instance is the database buffer cache. These buffers can contain modified data that has not yet been permanently written to disk. The performance is increased as most recently used data is kept in memory , thus leading to less disk 1/0.

Redo Log Buffer: The redo log buffer of the SGA stores redo entries a log of changes made to the database. The redo entries stored in the redo log buffers are written to an on line redo log file) which is used if database recovery is necessary. Its size is static. The relational data model offers the following advantages over other traditional data models: A data model that is easily understood and visualized Integrity laws that are easy to develop and understand to protect data and data structures reduced data storage and redundancy Independence of physical data storage and logical database structure

How to use the Oracle RDBMS

The Oracle RDBMS can reside on a variety of hardware platforms and operating systems, including personal computers. This session gives an overview of the Oracle server architecture and basic concepts for operating and using the Oracle database server. Oracle system Architecture

Oracle processing capability comes from its memory structure and processes. The mechanisms of ORACLE execute by using memory structures and processes. All memory structures exist in the main memory of the computers that constitute the database system. Processes are jobs or tasks that work in the memory of these computers.

Memory Structures

ORACLE creates and uses memory structures to complete several jobs. For example, memory is used to store program code being executed and data that is shared among users.

1. System Global Area (SGA):This is a shared memory region allocated by ORACLE that contains data and control information for one ORACLE instance. An SGA and the ORACLE background processes constitute an ORACLE instance. The SGA is allocated when an instance Starts and deallocated when the instance shuts down. Each instance that is started has its own SGA.

Database Buffer Cache: Database buffers of the SGA store the most recently used blocks of the database data; the set of the database buffers in any instance is the database buffer cache. These buffers can contain modified data that has not yet been permanently written to disk. The performance is increased as most recently used data is kept in memory , thus leading to less disk 1/0.

Redo Log Buffer: The redo log buffer of the SGA stores redo entries a log of changes made to the database. The redo entries stored in the redo log buffers are written to an on line redo log file) which is used if database recovery is necessary. Its size is static. An SGA and the ORACLE background processes constitute an ORACLE instance. Each ORACLE instance may use several background processes. These processes are DBWR, LGWR, CKPT, SMON, PMON, ARCH, RECO, Dnnn, and LCKn.

a. Database Writer(DBWR): The Database writer writes modified blocks from the database buffer cache to the data files. This write is done only when more data needs to be read into the SGA and too few database buffers free. The least recently used data is written to the data file first.

b. Log Writter(LGWR): The log writer writes redo log entries to disk. Redo log data is generated in the redo log buffer of the SGA. As transactions commit and log buffer fills, LGWR writes redo log entries into an on line redo log file.

C. Checkpoint(CKPT): At specific times, all modified database buffers in the SGA are written to the data files by DBWR; this event is called checkpoint. The checkpoint process is responsible for signaling DBWR at checkpoints and updating all the data files and control files of the database to indicate the most recent checkpoint. If CKPT is not present, LGWR assumes its responsibilities.

d. System Monitor(SMON): The purpose of the System Monitor is to perform instance recovery. It is used at instance startup and to perform appropriate recovery for a failed CPU a shared disk system. SMON is also responsible for cleaning up temporary segments that are no longer in use and for cleaning up dead transactions skipped during crash and instance recovery because of file read or off line errors. These transactions are eventually recovered by SMON when the tablespace or file is brought back online.

C. Process Monitor(PMON): The purpose of the Process Monitor is to perform process recovery when a user process fails. PMON is responsible for cleaning up the cache and for freeing resources that the process was using. For example, it resets the status of the active transaction table, releases locks and removes the process ID from the list of active ORACLE processes. PMON also checks on despatcher and server processes and restarts them if they have failed.

f. Archiver(ARCH): The Archiver process copies the on line redo log files to tape when they are full. ARCH is active only when archiving is enabled.

g. Recoverer(RECO): The recoverer is used to resolve distributed transactions that are pending due to a network or system failure in a distributed database. At timed intervals, the local RECC attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions.

h. Dispatcher(Dnnn): These are optical background processes, present only when a multi threaded server configuration is used. Atleast one dispatcher process is created for every communication protocol in use(DOOO,...,Dnnn).

I. Lock(LCKn): Up to ten lock processes(LCK0,......,LCK9) are used for inter instance locking when the ORACLE parallel server is used.

Operating the Oracle Database

An ORACLE database is not necessarily always available to all users. The database administrator can startup a database so that it is open. When a database is open, users can access the information that it contains. If a database is open, the database administrator can shut down the database so that it is closed. When a database is closed, user cannot access the information that it contains. Security for database startup and shutdown is controlled via connections to ORACLE as INTERNAL.

Database Startup

There are three steps to starting a database and making it available for system wide use:

1. Start an instance
2. Mount the database
3. Open the database

Starting an Instance

The process of starting an instance includes the allocation of an SGA, a shared area of memory used for database information, and creation of the background processes. Instance startup is performed in anticipation of a database being mounted by the instance. If an instance has been only started, no database is associated with these memory structures and processes.

Mounting a Database

Mounting a database is the process of associating a database with a previously started instance. After a database is mounted by an instance, it remains closed and is accessible only to database administrators. The database administrator might want to start an instance and only mount the database to complete specific maintenance operations. When a database is mounted, the instance finds the control files and opens them. Once the database's control files are opened, ORACLE reads them to get the names of the database's files and redo log files, and then confirms that these files exist as specified. Opening a mounted database is the process of making the database available for normal database operations. Any valid user can connect the database and access its information once it is open. In most cases the database administrator opens the database to make it available for general use.

When the database is opened, the online data files and online redo log files are opened. If a tablespace was off line when the database was previously shut down, the tablespace and its corresponding data file will still be off line when the database is reopened.

Database and Instance Shutdown

There are three steps to shutting down an instance and the database to which it is connected.

1. Close the database.
2. Dismount the database.
3. Shut down the instance.
All three steps are automatically performed when an

Closing a Database

The first step of database shutdown is closing the database. When a database is closed, all database data and recovery data in the SGA are written to the data files and redo log files, respectively. After this operations, all on line data files and on line redo log files are closed.

Dismounting a Database

The second step accomplished during database shutdown is dismounting or disassociating the database from an instance. After a database is dismounted, only an instance remains in the memory of your computer. After a database is dismounted, the control files of the database are closed.

Shutting Down an Instance

The final step in database shutdown is shutting down an instance. When an instance is shut down, the SGA is removed from memory and the background processes are terminated from the computer that executes ORACLE.

Oracle Database Access

An Oracle instance that is started and has open database can be accessed by users. An Oracle user is anyone who has a user account established in the Oracle server and has been granted the appropriate privileges for data access. The methods of database access can be through the following applications:

User application in a client/server environment via SQL*NET
SQL*Plus, one of the most popular access methods
Oracle tools, such as Developer 2000

The following list illustrates an Oracle RDBMS being accessed by user in a client/server environment. This illustration describes only the most basic level of operation that Oracle performs. An Oracle server has an instance that is started, and a database that is mounted and open to users.

2. The user application attempts to establish a connection to the Oracle server.

3. The server recognizes the client's request for connection and creates a process on behalf of the user.

The user's application receives an acknowledgement of a successful connection and sends SQL statements to access and update the database. The server's process receives the SQL statements and checks the user's access privileges. If the SQL transaction is successful,. the server process sends a message to the client application. If it is not successful, the server sends the appropriate error message to the client. During this transaction, the server prevents data contention with other transactions requesting the same data. The server also manages other transactions and watches out for conditions that require intervention.

The Data Dictionary

Each ORACLE database has a data dictionary. The data dictionary of an ORACLE database is a set of tables and views that are used as a read only reference about the database. For example, a data dictionary stores information about the logical and physical structure of the database. A data dictionary also stores such information as:
the valid users of an ORACLE database
information about integrity constraints for tables in the database

how much space is allocated for a schema object and how much of it is being used

A data dictionary is created when the database is created. To accurately reflect the status of the database at all times; the data dictionary is automatically updated by ORACLE in response to specific actions such as when the structure of the database is altered. The data dictionary is critical to the operation of the database, which relies on the data dictionary to record, verify, and conduct ongoing work. For example, during database operation, ORACLE reads the data dictionary to verify that schema objects exist and that users have proper access to them, Thus the data dictionary is logically a set of tables and views. These may be accessed by user queries to obtain information about the data base. The data dictionary consists of a set of views that are accessed through the SQL language.

Domain Name Search