Overview Of Oracle

What is Oracle?

Prepared By
Wipro Technologies
Oracle Database Administrator

ORACLE is a boon to the world of relational database management system which emerged can reliably manage a large amount of data in a multi-user environment so that many users can concurrently access the same data. Also Oracle delivers high performance to the users of the database. Oracle databases are secure from unauthorized access and provide efficient solutions for failure recovery. The ORACLE Server provides efficient and effective solutions for the major database features.

ORACLE consists of many tools that allow you to create an application with ease and flexibility. You must determine how to implement your requirements using the features available in ORACLE, along with its tools. The features and tools that you choose to use to implement your application can significantly affect the performance of your application.  

Oracle Version

Oracle Version 1, 1978

Written in Assembly Language

Ran under PDP-11 under RSX in 128K of Memory

Implementation separates oracle code and user code. 

Oracle Version 2, 1979: 

The first SQL Relational Database Management system that was commercially released.

Written in PDP-11 Assembly Language

Ran on VAX/VMS in Compatibility mode 

Oracle Version 3.1980: 

Written in C, Portable Source Code

Retained Split Architecture

Introduced the concept of Atomic SQL Execution and transactions (Commit & Rollback) 

Oracle Version 4, 1984: 

Introduced Read Consistency.

Portable to many platforms

Interoperability between PC and Server 

Oracle Version 5, 1986: 

True Client Server ???Distributed Processing

VAX ???Cluster Support

Version 5.1- Distributed Queries 

Oracle Version 6, 1989: 

Major Kernel Rewritten

OLTP Performance enhancements (save points)

Online Backup and Recovery

Row level Locking

PL\SQL in the database

Parallel Servers (VAX Clusters, nCube) 

Oracle Version 7, 1993: 

Stored Procedures and Triggers

Shared SQL, Parallel Execution

Declarative Referential Integrity

Advanced Replication 

Oracle Version 8, 1997: 

Object Relation Extensions in the Database

From Client Server to Three tier Architecture

Partitioning Option 

Oracle Version 8i, 1999: 

Java in the database (JVM and SQLJ)

Partitioning Enhancements

Data Warehousing Enhancements

XML Support

Summary Management

Oracle Internet Directory (LDAP)

Ported to Linux 

Oracle Version 9i, 2001: 

Real Application Clusters, with cache fusion??? Scalability on inexpensive clustered hardware

Automatic segment-space management

Internet security enhancements

Integrated business intelligence functionality

Data Guard (standby databases)

Oracle managed files

Globalization support (Unicode, time zones, locales) 

Oracle Version 10g, 2003: 

Primary goal: Build a self-managing database that requires minimal human intervention.

Reduction in administration cost without

Compromising high availability, scalability, and security.

Minimal performance impact

Effective for all configurations and workloads 

Oracle Version 11g, 2007: 

Flashback data Archive

Advanced Compression

Real Application Testing

Automatic Storage Management 

Client/Server Architecture: Distributed Processing

Distributed processing uses more than one processor to divide the processing for a set of related jobs. Distributed processing reduces the processing load on a single processor by allowing different processors to concentrate on a subset of related tasks, thus improving the performance and capabilities of the system as a whole.

An Oracle database system can easily take advantage of distributed processing by using its client/server architecture. In this architecture, the database system is divided into two parts: a front-end or a client and a back-end or a server.

The Client

The client is the front-end database application and is accessed by a user through the keyboard, display, and pointing device such as a mouse. The client has no data access responsibilities. It concentrates on requesting, processing, and presenting data managed by the server. The client workstation can be optimized for its job. For example, it may not need large disk capacity or it may benefit from graphic capabilities.

The Server

The server runs Oracle software and handles the functions required for concurrent, shared data access. The server receives and processes the SQL and PL/SQL statements that originate from client applications. The computer that manages the server can be optimized for its duties. For example, it can have large disk capacity and fast processors.

Multitier Architecture: Application Servers

multitier architecture has the following components:

  • A client or initiator process that starts an operation
  • One or more application servers that perform parts of the operation. Anapplication server provides access to the data for the client and performs some of the query processing, thus removing some of the load from the database server. It can serve as an interface between clients and multiple database servers, including providing an additional level of security.
  • An end or database server that stores most of the data used in the operation

This architecture enables use of an application server to:

  • Validate the credentials of a client, such as a web browser
  • Connect to an Oracle database server
  • Perform the requested operation on behalf of the client

The identity of the client is maintained throughout all tiers of the connection. The Oracle database server audits operations that the application server performs on behalf of the client separately from operations that the application server performs on its own behalf (such as a request for a connection to the database server). The application server's privileges are limited to prevent it from performing unneeded and unwanted operations during a client operation.

Distributed Databases

distributed database is a network of databases managed by multiple database servers that appears to a user as a single logical database. The data of all databases in the distributed database can be simultaneously accessed and modified. The primary benefit of a distributed database is that the data of physically separate databases can be logically combined and potentially made accessible to all users on a network.

Each computer that manages a database in the distributed database is called a node. The database to which a user is directly connected is called the local database. Any additional databases accessed by this user are called remote databases. When a local database accesses a remote database for information, the local database is a client of the remote server. This is an example of client/server architecture.

While a distributed database enables increased access to a large amount of data across a network, it must also hide the location of the data and the complexity of accessing it across the network. The distributed database management system must also preserve the advantages of administrating each local database as though it were not distributed.

Location Transparency

Location transparency occurs when the physical location of data is transparent to the applications and users of a database system. Several Oracle features, such as views, procedures, and synonyms, can provide location transparency. For example, a view that joins table data from several databases provides location transparency because the user of the view does not need to know from where the data originates.

Site Autonomy

Site autonomy means that each database participating in a distributed database is administered separately and independently from the other databases, as though each database were a non-networked database. Although each database can work with others, they are distinct, separate systems that are cared for individually.

Distributed Data Manipulation

The Oracle distributed database architecture supports all DML operations, including queries, inserts, updates, and deletes of remote table data. To access remote data, you make reference to the remote object's global object name. No coding or complex syntax is required to access remote data.

For example, to query a table named EMP in the remote database named SALES,reference the table's global object name:

SELECT * FROM emp@sales;

Two-Phase Commit

Oracle provides the same assurance of data consistency in a distributed environment as in a nondistributed environment. Oracle provides this assurance using the transaction model and a two-phase commit mechanism.

As in nondistributed systems, transactions should be carefully planned to include a logical set of SQL statements that should all succeed or fail as a unit. Oracle's two-phase commit mechanism guarantees that no matter what type of system or network failure occurs, a distributed transaction either commits on all involved nodes or rolls back on all involved nodes to maintain data consistency across the global distributed database.



Replication is the process of copying and maintaining database objects, such as tables, in multiple databases that make up a distributed database system. Changes applied at one site are captured and stored locally before being forwarded and applied at each of the remote locations. Oracle replication is a fully integrated feature of the Oracle server. It is not a separate server.

Replication uses distributed database technology to share data between multiple sites, but a replicated database and a distributed database are not the same. In a distributed database, data is available at many locations, but a particular table resides at only one location. For example, the EMP table can reside at only the db1 database in a distributed database system that also includes the db2 and db3 databases. Replication means that the same data is available at multiple locations. For example, the EMP table may be available at db1, db2, and db3.  


Table Replication

Distributed database systems often locally replicate remote tables that are frequently queried by local users. By having copies of heavily accessed data on several nodes, the distributed database does not need to send information across a network repeatedly, thus helping to maximize the performance of the database application.

Data can be replicated using materialized views.

Multitier Materialized Views

Oracle9i, Release 1 (9.0.1), supports materialized views that are hierarchical and updatable. Multitier replication provides increased flexibility of design for a distributed application. Using multitier materialized views, applications can manage multilevel data subsets where there is no direct connection between levels.

An updatable materialized view lets you insert, update, and delete rows in the materialized view and propagate the changes to the target master table. Synchronous and asynchronous replication is supported.

Conflict Resolution

In Oracle9i, Release 1 (9.0.1), conflict resolution routines are defined at the topmost level, the master site, and are pulled into the updatable materialized view site when needed. This makes it possible to have multitier materialized views. Existing system-defined conflict resolution methods are supported.

In addition, users can write their own conflict resolution routines. A user-defined conflict resolution method is a PL/SQL function that returns either TRUE or FALSE. TRUE indicates that the method was able to successfully resolve all conflicting modifications for a column group.

See Also:

Oracle9i Replication and Oracle9i SQL Reference for more information about creating and managing multitier materialized views 


Oracle and Oracle Net Services

Oracle Net Services is Oracle's mechanism for interfacing with the communication protocols used by the networks that facilitate distributed processing and distributed databases. Communication protocols define the way that data is transmitted and received on a network. In a networked environment, an Oracle database server communicates with client workstations and other Oracle database servers using Oracle Oracle Net Services software.

Oracle Net Services supports communications on all major network protocols, ranging from those supported by PC LANs to those used by the largest of mainframe computer systems.

Using Oracle Net Services, the application developer does not have to be concerned with supporting network communications in a database application. If a new protocol is used, the database administrator makes some minor changes, while the application requires no modifications and continues to function.

Heterogeneous Services

Heterogeneous Services is a component within the Oracle9i database server that is necessary for accessing a non-Oracle database system.

The term "non-Oracle database system" refers to the following:

  • Any system accessed by PL/SQL procedures written in C (that is, by external procedures)
  • Any system accessed through SQL (that is, by Oracle Transparent Gateways and Generic Connectivity)
  • Any system accessed procedurally (that is, by procedural gateways)

Heterogeneous Services makes it possible for Oracle9i database server users to do the following:

  • Use Oracle SQL statements to retrieve data stored in non-Oracle systems.
  • Use Oracle procedure calls to access non-Oracle systems, services, or application programming interfaces (APIs) from within an Oracle distributed environment.

Heterogeneous Services is generally applied in one of two ways:

  • Users use an Oracle Transparent Gateway in conjunction with Heterogeneous Services to access a particular, vendor-specific, non-Oracle system for which an Oracle Transparent Gateways is designed. For example, you would use the Oracle Transparent Gateway for Sybase on Solaris to access a Sybase database system that was operating on a Sun Solaris platform.
  • Users use Heterogeneous Services' generic connectivity to access non-Oracle databases through ODBC or OLE DB interfaces.

Advanced Queuing Overview

Oracle Advanced Queuing provides an infrastructure for distributed applications to communicate asynchronously using messages. Oracle Advanced Queuing stores messages in queues for deferred retrieval and processing by the Oracle server. This provides a reliable and efficient queuing system without additional software such as transaction processing monitors or message-oriented middleware.

Uses for Message Queuing

Consider a typical online sales business. It includes an order entry application, an order processing application, a billing application, and a customer service application. Physical shipping departments are located at various regional warehouses. The billing department and customer service department can also be located in different places.

This scenario requires communication between multiple clients in a distributed computing environment. Messages pass between clients and servers as well as between processes on different servers. An effective messaging system implements content-based routing, content-based subscription, and content-based querying.

A messaging system can be classified into one of two types:

Synchronous Communication

Synchronous communication is based on the request/reply paradigm--a program sends a request to another program and waits until the reply arrives.

This model of communication (also called online or connected) is suitable for programs that need to get the reply before they can proceed with their work. Traditional client/server architectures are based on this model.

The major drawback of the synchronous model of communication is that the programs to whom the request is sent must be available and running for the calling application to work.

Asynchronous Communication

In the disconnected or deferred model, programs communicate asynchronously, placing requests in a queue and then proceeding with their work.

For example, an application might require entry of data or execution of an operation after specific conditions are met. The recipient program retrieves the request from the queue and acts on it. This model is suitable for applications that can continue with their work after placing a request in the queue -- they are not blocked waiting for a reply.

For deferred execution to work correctly even in the presence of network, machine and application failures, the requests must be stored persistently, and processed exactly once. This can be achieved by combining persistent queuing with transaction protection.

Processing each client/server request exactly once is often important to preserve both the integrity and flow of a transaction. For example, if the request is an order for a number of shares of stock at a particular price, then execution of the request zero or two times is unacceptable even if a network or system failure occurs during transmission, receipt, or execution of the request.

Advantages of Oracle Advanced Queuing

Oracle Advanced Queuing supports the following:

  • Point-to-point and publish-subscribe models of communication
  • Structured payload that can be queried using SQL
  • Recipient and subscription lists
  • Persistent and nonpersistent queues
  • Queue-level access control
  • Asynchronous notification
  • HTTP and SMTP access
  • Nonrepudiation service

Using Oracle Advanced Queuing, you can:

  • Propagate messages to other databases, local or remote.
  • Remove multiple messages from the queue as a bundle, specify multiple recipients, and wait for messages on multiple queues.
  • Prioritize messages and specify a window of execution for each message.
  • Perform AQ operations through HTTP/SMTP, using XML formatting.
  • Retain messages and message history for analysis.
  • Keep statistics on messages stored in queues and propagated to other queues.
  • Integrate transactions to simplify application development and management
  • Use Oracle9i Real Application Clusters to achieve higher performance

Because Oracle Advanced Queuing queues are implemented in database tables, all the operational benefits of high availability, scalability, and reliability apply to queue data. In addition, database development and management tools can be used with queues.

Applications can access the queuing functionality through the native interface for Oracle Advanced Queuing (defined in PL/SQL, C/C++, Java, and Visual Basic) or through the Java Messaging Service interface for Oracle Advanced Queuing (a Java API based on the Java Messaging Service standard).

See Also:

Oracle9i Application Developer's Guide - Advanced Queuing for detailed information about Oracle Advanced Queuing 


Data Warehousing Overview

Oracle9i, Release 1 (9.0.1), provides many features that support the needs of data warehousing and business intelligence. These features are typically useful for other activities such as Online Transaction Processing (OLTP). Therefore, most features described in this manual are presented in general terms, rather than emphasizing their data warehousing use.

These are some of the Oracle features that are designed to support data warehousing and business intelligence:

  • Parallelism in queries, data loading, indexing, and other data manipulation
  • Cost-based query optimization for efficient data access
  • Indexing techniques specifically tailored to data warehousing needs
  • Advanced SQL join algorithms
  • Data partitioning to simplify management and enhance performance
  • Analytic functions for high performance queries and simpler programming
  • Materialized view for maximum performance
?? Wiziontech Solutions | Created by Thiyagaraaj