- 1 What is Oracle?
- 2 Oracle Version
- 3 Client/Server Architecture: Distributed Processing
- 4 Multitier Architecture: Application Servers
- 5 Distributed Databases
- 6 Replication
- 7 Heterogeneous Services
- 8 Advanced Queuing Overview
- 9 Advantages of Oracle Advanced Queuing
- 10 Data Warehousing Overview
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 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
Oracle Version 8, 1997:
Object Relation Extensions in the Database
From Client Server to Three tier Architecture
Oracle Version 8i, 1999:
Java in the database (JVM and SQLJ)
Data Warehousing Enhancements
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
Real Application Testing
Automatic Storage Management
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 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 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.
A multitier architecture has the following components:
This architecture enables use of an application server to:
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.
A 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 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 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.
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;
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.
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.
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.
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.
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 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:
Heterogeneous Services makes it possible for Oracle9i database server users to do the following:
Heterogeneous Services is generally applied in one of two ways:
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.
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 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.
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.
Oracle Advanced Queuing supports the following:
Using Oracle Advanced Queuing, you can:
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).
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: