Oracle Essentials: Oracle9i, Oracle8i and Oracle8

Content preview·Buy reprint rights for this chapter

Where do we start? One of the problems in comprehending a massive product such as the Oracle database is the difficulty of getting a good sense of how the product works without getting lost in the details of implementing specific solutions. This book aims to solve this problem by giving you a thorough grounding in the concepts and technologies that form the foundation of Oracle's technology products: the Database Server (currently Oracle9i) and the Application Server (Oracle9iAS). Oracle is also a provider of business applications, popularly named the "E-Business Suite"; discussion of those applications is outside the scope of this book, although we do touch on how technology components are being leveraged in linking Oracle's applications to non-Oracle applications and in building data warehouses.

We've tried to write a book for a wide range of Oracle users, from the novice to the experienced user. To address this range of users, we've focused on the concepts and technology behind the Oracle database. Once you fully understand these facets of the product, you'll be able to handle the particulars of virtually any type of Oracle database. Without this understanding, you may feel overburdened as you try to connect the dots of Oracle's voluminous feature set and documentation.

This first chapter lays the groundwork for the rest of the discussions in this book. Of all the chapters, it covers the broadest range of topics; most of these are discussed further later in the book, but some of the basics—for example, the brief history of Oracle and the contents of the different "flavors" of the Oracle database products—are unique to this chapter.

Oracle has grown from its humble beginnings as one of a number of databases available in the 1970s to the overwhelming market leader of today. In its early days, Oracle Corporation was known as an aggressive sales and promotion organization. Over the years, the Oracle database has grown in depth and quality, and its technical capabilities now match its early hype. With the Oracle8, Oracle8

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The concept of the relational database was first described around 1970 by Dr. Edgar F. Codd in an IBM research publication entitled "System R4 Relational." Initially, it was unclear whether any system based on these concepts could achieve commercial success. Nevertheless, Relational Software, Incorporated (RSI) was founded in 1979 and released Oracle V.2 as the world's first relational database. By 1985, Oracle could claim more than 1,000 relational database customer sites. IBM itself would not embrace relational technology in a commercial product until the Query Management Facility in 1983.

Why has relational database technology grown to become the de facto database technology since that time? A look back at previous database technology may help to explain this phenomenon.

Database management systems were first defined in the 1960s to provide a common organizational framework for what had been data stored in independent files. In 1964, Charles Bachman of General Electric proposed a network model with data records linked together, forming intersecting sets of data, as shown on the left in Figure 1-1. This work formed the basis of the Codasyl Data Base Task Group. Meanwhile, the North American Aviation's Space Division and IBM developed a second approach based on a hierarchical model in 1965. In this model, data is represented as tree structures in a hierarchy of records, as shown on the right in Figure 1-1. IBM's product based on this model was brought to market in 1969 as the Information Management System (IMS). As recently as 1980, almost all database implementations used either the network or hierarchical approach. Although several competitors utilized these technologies, only IMS remains.

Figure 1-1: Network model and hierarchical model

The relational database uses the concept of linked two-dimensional tables consisting of rows and columns, as shown in Figure 1-2. Unlike the hierarchical approach, no predetermined relationship exists between distinct tables. This means that the data needed to link together the different areas of the network or hierarchical model need not be defined. And because relational users don't need to understand the representation of data in storage to retrieve it (many such users created ad hoc queries against the data), ease of use helped popularize the relational model.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Oracle9i Database Server describes the most recent major version of the Oracle Relational Database Management System (RDBMS) family of products that share common source code. Leveraging predecessors including the Oracle8 release that surfaced in 1997, the family includes:

Personal Oracle, a database for single users that's often used to develop code for implementation on other Oracle multiuser databases
Oracle Standard Edition, which was named Workgroup Server in its first iteration as part of the Oracle7 family and is often simply referred to as Oracle Server
Oracle Enterprise Edition, which includes additional functionality

In 1998, Oracle announced Oracle8i, which is sometimes referred to as Version 8.1 of the Oracle8 database. The "i " was added to denote added functionality supporting Internet deployment in the new version. Oracle9i followed, with Application Server available in 2000 and Database Server in 2001. The terms "Oracle," "Oracle8," "Oracle8i," and "Oracle9i" may appear to be used somewhat interchangeably in this book, since Oracle9i includes all the features of previous versions. When we describe a new feature that was first made available specifically for Oracle8i or Oracle9i we've tried to note that fact to avoid confusion, recognizing that many of you may have old releases of Oracle. We typically use the simple term "Oracle" when describing features that are common to all these releases.

Oracle has focused development around a single source code model since 1983. While each database implementation includes some operating system-specific source code, most of the code is common across the various implementations. The interfaces that users, developers, and administrators deal with for each version are consistent. Features are consistent across platforms for implementations of Oracle Standard Edition and OracleEnterprise Edition. As a result, companies have been able to migrate Oracle applications easily to various hardware vendors and operating systems while leveraging their investments in Oracle technology. From the company's perspective, Oracle has been able to focus on implementing new features only once in its product set, instead of having to add functionality at different times to different implementations.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The Oracle database is a broad and powerful product. The remainder of this book examines different aspects of Oracle—for example, data structures, performance, and parallel processing. But before you can understand each of the different areas of Oracle in depth, you must familiarize yourself with the range of features in the Oracle database.

The rest of this chapter gives you a high-level overview of the basic areas of functionality in the Oracle product family. By the end of this chapter, you will at least have some orientation points to guide you in exploring the topics in the rest of this book.

To give some structure to the broad spectrum of the Oracle database, we've organized the features into the following sections:

Database application development features
Database connection features
Distributed database features
Data movement features
Performance features
Database management features

At the end of each of the following sections describing database features we've included a subsection called "Availability," which indicates the availability of each feature in specific Oracle products. You should be aware that as this feature list grows and Oracle implements packaging changes in new versions, the availability of these features in the version you implement may vary slightly.

In this chapter, we've included a lot of terminology and rather abbreviated descriptions of features. Oracle is a huge system. Our goal here is to quickly familiarize you with the full range of features in the system. Subsequent chapters will provide additional details. Obviously, though, whole books can be (and have been!) written about each of the features summarized here.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Applications are often built on the foundation of the Oracle database system. The features of the Oracle database and related products described in this section are used to create applications. We've divided the discussion in this section into two categories: database programming and database extensibility options. Later in this chapter, we describe the Internet Developer Suite, a set of optional tools used in Oracle9i Database Server and Oracle9i Application Server development.

All flavors of the Oracle database include different languages and interfaces that allow programmers to access and manipulate the data in the database. Database programming features usually interest two groups: developers building Oracle-based applications that will be sold commercially, and IT organizations within companies that custom-develop applications unique to their businesses. The following sections describe the languages and interfaces supported by Oracle.

Section 1.4.1.1: SQL

The ANSI standard Structured Query Language (SQL) provides basic functions for data manipulation, transaction control, and record retrieval from the database. However, most end users interact with Oracle through applications that provide an interface that hides the underlying SQL and its complexity.

Section 1.4.1.2: PL/SQL

Oracle's PL/SQL, a procedural language extension to SQL, is commonly used to implement program logic modules for applications. PL/SQL can be used to build stored procedures and triggers, looping controls, conditional statements, and error handling. You can compile and store PL/SQL procedures in the database. You can also execute PL/SQL blocks via SQL*Plus, an interactive tool provided with all versions of Oracle.

Section 1.4.1.3: Java features and options

Oracle8i introduced the use of Java as a procedural language with a Java Virtual Machine ( JVM) in the database (originally called JServer). JVM includes support for Java stored procedures, methods, triggers, Enterprise JavaBeans™ (EJBs), CORBA, IIOP, and HTTP. The Accelerator is used for project generation, translation, and compilation. As of Oracle Version 8.1.7, it can also be used to deploy/install shared libraries.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The connection between the client and the database server is a key component of the overall architecture of a computing system. The database connection is responsible for supporting all communications between an application and the data it uses. Oracle includes a number of features that establish and tune your database connections.

The following features relate to the way the Oracle database handles the connection between the client and server machines in a database interaction. We've divided the discussion in this section into two categories: database networking and Oracle9i Application Server.

Database users connect to the database by establishing a network connection. You can also link database servers via network connections. Oracle provides a number of features to establish connections between users and the database and/or between database servers, as described in the following sections.

Section 1.5.1.1: Oracle Net/Net8

Oracle's network interface, Net8, was formerly known as SQL*Net when used with Oracle7 and previous versions of Oracle. You can use Net8 over a wide variety of network protocols, although TCP/IP is by far the most common protocol today.

In Oracle9i, the name of Net8 has been changed to Oracle Net, and the features associated with Net8, such as shared servers, are referred to as Oracle Net Services.

Section 1.5.1.2: Oracle Names

Oracle Names allows clients to connect to an Oracle server without requiring a configuration file on each client. Using Oracle Names can reduce maintenance efforts, since a change in the topology of your network will not require a corresponding change in configuration files on every client machine.

Section 1.5.1.3: Oracle Internet Directory

The Oracle Internet Directory (OID) was introduced with Oracle8i. OID serves the same function as Oracle Names in that it gives users a way to connect to an Oracle Server without having a client-side configuration file. However, OID differs from Oracle Names in that it is an LDAP (Lightweight Directory Access Protocol) directory; it does not merely support the Oracle-only Oracle Net/Net8 protocol.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

One of the strongest features of the Oracle database is its ability to scale up to handle extremely large volumes of data and users. Oracle scales not only by running on more and more powerful platforms, but also by running in a distributed configuration. Oracle databases on separate platforms can be combined to act as a single logical distributed database.

This section describes some of the basic ways that Oracle handles database interactions in a distributed database system.

Data within an organization is often spread among multiple databases for reasons of both capacity and organizational responsibility. Users may want to query this distributed data or update it as if it existed within a single database.

Oracle first introduced distributed databases in response to the requirements for accessing data on multiple platforms in the early 1980s. Distributed queries can retrieve data from multiple databases. Distributed transactions can insert, update, or delete data on distributed databases. Oracle's two-phase commit mechanism, which is described in detail in Chapter 12, guarantees that all the database servers that are part of a transaction will either commit or roll back the transaction. Distributed transactions that may be interrupted by a system failure are monitored by a recovery background process. Once the failed system comes back online, the same process will complete the distributed transactions to maintain consistency across the databases.

You can also implement distributed transactions in Oracle by popular transaction monitors (TPs) that interact with Oracle via XA, an industry standard (X/Open) interface. Oracle8i also added native transaction coordination with the Microsoft Transaction Server (MTS), so you can implement a distributed transaction initiated under the control of MTS through an Oracle database.

Heterogeneous Services allow non-Oracle data and services to be accessed from an Oracle database through tools such as Oracle Transparent Gateways. For example, Transparent Gateways allow users to submit Oracle SQL statements to a non-Oracle distributed database source and have them automatically translated into the SQL dialect of the non-Oracle source system, which remains transparent to the user. In addition to providing underlying SQL services, Heterogeneous Services provide transaction services utilizing Oracle's two-phase commit with non-Oracle databases and procedural services that call third-generation language routines on non-Oracle systems. Users interact with the Oracle database as if all objects are stored in the Oracle database, and Heterogeneous Services handle the transparent interaction with the foreign database on the user's behalf.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Moving data from one Oracle database to another is often a requirement when using distributed databases, or when a user wants to implement multiple copies of the same database in multiple locations to reduce network traffic or increase data availability. You can export data and data dictionaries (metadata) from one database and import them into another. Oracle also offers many other advanced features in this category, including replication, transportable tablespaces, and Advanced Queuing.

This section describes the technology used to move data from one Oracle database to another automatically.

You can use basic replication to move recently added and updated data from an Oracle "master" database to databases on which duplicate sets of data reside. In basic replication, only the single master is updated. You can manage replication through the Oracle Enterprise Manager (OEM prior to Oracle9i, EM in Oracle9i).

You can use advanced replication in multimaster systems in which any of the databases involved can be updated and conflict-resolution features are needed to resolve inconsistencies in the data. Because there is more than one master database, the same data may be updated on multiple systems at the same time. Conflict resolution is necessary to determine the "true" version of the data. Oracle's advanced replication includes a number of conflict-resolution scenarios and also allows programmers to write their own. We cover replication in more detail in Chapter 12.

Transportable tablespaces were introduced in Oracle8i. Instead of using the export/import process, which dumps data and the structures that contain it into an intermediate file for loading, you simply put the tablespaces in read-only mode, move or copy them from one database to another, and mount them. You must export the data dictionary (metadata) for the tablespace from the source and import it at the target. This feature can save a lot of time during maintenance, because it simplifies the process.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Oracle includes several features specifically designed to boost performance in certain situations. We've divided the discussion in this section into two categories: database parallelization and data warehousing.

Database tasks implemented in parallel speed up querying, tuning, and maintenance of the database. By breaking up a single task into smaller tasks and assigning each subtask to an independent process, you can dramatically improve the performance of certain types of database operations.

Parallel query features became a standard part of Enterprise Edition beginning with Oracle 7.3. Examples of query features implemented in parallel include:

Table scans
Nested loops
Sort merge joins
GROUP BYs
NOT IN subqueries (anti-joins)
User-defined functions
Index scans
Select distinct UNION and UNION ALL
Hash joins
ORDER BY and aggregation
Bitmap star joins
Partition-wise joins
Stored procedures (PL/SQL, Java, external routines)

When you're using Oracle, by default the degree of parallelism for any operation is set to twice the number of CPUs. You can adjust this degree automatically for each subsequent query based on the system load. You can also generate statistics for the cost-based optimizer in parallel.

Section 1.8.1.1: Availability

You can perform maintenance functions such as loading (via SQL*Loader), backups, and index builds in parallel in Oracle Enterprise Edition. Oracle Partitioning for the Enterprise Edition enables additional parallel Data Manipulation Language (DML) inserts, updates, and deletes as well as index scans.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Oracle includes many features that make the database easier to manage. We've divided the discussion in this section into four categories: Oracle Enterprise Manager, add-on packs, backup and recovery, and database availability.

As part of every Database Server, Oracle provides the Oracle Enterprise Manager (EM), a database management tool framework with a graphical interface used to manage database users, instances, and features (such as replication) that can provide additional information about the Oracle environment. EM can also manage Oracle9iAS and Oracle iFS, Internet Directory, and Express.

Prior to the Oracle8i database, the EM software had to be installed on Windows 95/98 or NT-based systems and each repository could be accessed by only a single database manager at a time. Now you can use EM from a browser or load it onto Windows 95/98/2000 or NT-based systems. Multiple database administrators can access the EM repository at the same time. In the EM release for Oracle9i, the super administrator can define services that should be displayed on other administrators' consoles, and management regions can be set up.

Several optional add-on packs are available for Oracle, as described in the following sections and in more detail in Chapter 5. In addition to these database-management packs, management packs are available for Oracle Applications and for SAP R/3.

Section 1.9.2.1: Standard Management Pack

The Standard Management Pack for Oracle provides tools for the management of small Oracle databases (e.g., Oracle Server/Standard Edition). Features include support for performance monitoring of database contention, I/O, load, memory use and instance metrics, session analysis, index tuning, and change investigation and tracking.

Section 1.9.2.2: Diagnostics Pack

You can use the Diagnostics Pack to monitor, diagnose, and maintain the health of Enterprise Edition databases, operating systems, and applications. With both historical and real-time analysis, you can automatically avoid problems before they occur. The pack also provides capacity planning features that help you plan and track future system-resource requirements.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Many Oracle tools are available to developers to help them present data and build more sophisticated Oracle database applications. Although this book focuses on the Oracle database, this section briefly describes the main Oracle tools for application development: Oracle Forms Developer, Oracle Reports Developer, Oracle Designer, Oracle JDeveloper, Oracle Discoverer Administrative Edition, and Oracle Portal.

Oracle Forms Developer provides a powerful tool for building forms-based applications and charts for deployment as traditional client/server applications or as three-tier browser-based applications via Oracle9i Application Server. Developer is a fourth-generation language (4GL). With a 4GL, you define applications by defining values for properties, rather than by writing procedural code. Developer supports a wide variety of clients, including traditional client/server PCs and Java-based clients. Version 6 of Developer adds more options for creating easier-to-use applications, including support for animated controls in user dialogs and enhanced user controls. The Forms Builder in Version 6 includes a built-in JVM for previewing web applications.

Oracle Reports Developer provides a development and deployment environment for rapidly building and publishing web-based reports via Reports for Oracle9i Application Server. Data can be formatted in tables, matrices, group reports, graphs, and combinations. High-quality presentation is possible using the HTML extension Cascading Style Sheets (CSS).

Oracle JDeveloper was introduced by Oracle in 1998 to develop basic Java applications without writing code. JDeveloper includes a Data Form wizard, a BeansExpress wizard for creating JavaBeans and BeanInfo classes, and a Deployment wizard. JDeveloper includes database development features such as various Oracle drivers, a Connection Editor to hide the JDBC API complexity, database components to bind visual controls, and a SQLJ precompiler for embedding SQL in Java code, which you can then use with Oracle. You can also deploy applications developed with JDeveloper using the Oracle9

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Oracle Lite is Oracle's suite of products for enabling mobile use of database-centric applications. Key components of Oracle Lite include the Oracle Lite database and iConnect, which consists of Advanced Replication, Oracle Mobile Agents (OMA), Oracle Lite Consolidator for Palm, and Oracle AQ Lite.

Although the OracleLite database engine can operate with much less memory than other Oracle implementations (it requires less than 1 MB of memory to run on a laptop), Oracle SQL, C and C++, and Java-based applications can run against the database. Java support includes support of Java stored procedures, JDBC, and SQLJ. The database is self-tuning and self-administering. In addition to Windows-based laptops, OracleLite is also supported on handheld devices running WindowsCE and Palm OS.

A variety of replication possibilities exist between Oracle and Oracle Lite, including the following:

Connection-based replication via Oracle Net, Net8, or SQL*Net synchronous connections
Wireless replication through the use of Advanced Queuing Lite, which provides a messaging service compatible with Oracle Advanced Queuing (and replaces the Oracle Mobile Agents capability available in previous versions of Oracle Lite)
File-based replication via standards such as FTP and MAPI
Internet replication via HTTP or MIME

You can define replication of subsets of data via SQL statements. Because data distributed to multiple locations can lead to conflicts—such as which location now has the "true" version of the data—multiple conflict and resolution algorithms are provided. Alternatively, you can write your own algorithm.

In typical usage of Oracle Lite, the user will link her handheld or mobile device running Oracle Lite to an Oracle Database Server. Data and applications will be synchronized between the two systems. The user will then remove the link and work in disconnected mode. After she has performed her tasks, she'll relink and resynchronize the data with the Oracle Database Server.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

This chapter focuses on the concepts and structures at the core of the Oracle database. When you understand the architecture of the Oracle server, you'll have a context for analyzing the rest of thefeatures of Oracle.

Many Oracle practitioners use the terms "instance" and "database" interchangeably. In fact, an instance and a database are different (but related) entities. This distinction is important because it provides insight into Oracle's architecture.

In Oracle, the term database refers to the physical storage of information, and the term instance refers to the software executing on the server that provides access to the information in the database. The instance runs on the computer or server; the database is stored on the disks attached to the server. Figure 2-1 illustrates this relationship.

Figure 2-1: An instance and a database

The database is physical: it consists of files stored on disks. The instance is logical: it consists of in-memory structures and processes on the server. An instance can connect to one and only one database. Instances are temporal, but databases, with proper maintenance, last forever.

Users do not directly access the information in an Oracle database. Instead, they pass requests for information to an Oracle instance.

The real world provides a useful analogy for instances and databases. An instance can be thought of as a bridge to the database, which can be thought of as an island. Traffic flows on and off the island via the bridge. If the bridge is closed, the island exists but no traffic flow is possible. In Oracle terms, if the instance is up, data can flow in and out of the database. The physical state of the database is changing. If the instance is down, users cannot access the database even though it still exists physically. The database is static: no changes can occur to it. When the instance comes back into service, the data will be there waiting for it.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Many Oracle practitioners use the terms "instance" and "database" interchangeably. In fact, an instance and a database are different (but related) entities. This distinction is important because it provides insight into Oracle's architecture.

In Oracle, the term database refers to the physical storage of information, and the term instance refers to the software executing on the server that provides access to the information in the database. The instance runs on the computer or server; the database is stored on the disks attached to the server. Figure 2-1 illustrates this relationship.

Figure 2-1: An instance and a database

The database is physical: it consists of files stored on disks. The instance is logical: it consists of in-memory structures and processes on the server. An instance can connect to one and only one database. Instances are temporal, but databases, with proper maintenance, last forever.

Users do not directly access the information in an Oracle database. Instead, they pass requests for information to an Oracle instance.

The real world provides a useful analogy for instances and databases. An instance can be thought of as a bridge to the database, which can be thought of as an island. Traffic flows on and off the island via the bridge. If the bridge is closed, the island exists but no traffic flow is possible. In Oracle terms, if the instance is up, data can flow in and out of the database. The physical state of the database is changing. If the instance is down, users cannot access the database even though it still exists physically. The database is static: no changes can occur to it. When the instance comes back into service, the data will be there waiting for it.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

When you create a database, you assign a specific name to it. You cannot change the database name once you have created it, although you can change the name of the instance that accesses the database.

This section covers the different types of files and other components that make up a complete database.

Before you examine the physical files of the actual database, you need to understand a key logical structure within a database, the tablespace. All the data stored in a database must reside in a tablespace.

A tablespace is a logical structure; you cannot look at the operating system and see a tablespace. Each tablespace is composed of physical structures called datafiles; each tablespace must consist of one or more datafiles, and each datafile can belong to only one tablespace. When you create a table, you can specify which tablespace in which to create it. Oracle will then find space for it in one of the datafiles that make up the tablespace.

Figure 2-2 shows the relationship of tablespaces to datafiles for a database.

Figure 2-2: Tablespaces and datafiles

This figure shows two tablespaces within an Oracle database. When you create a new table in this Oracle database, you may place it in the DATA1 tablespace or the DATA2 tablespace. It will physically reside in one of the datafiles that make up the specified tablespace.

A tablespace is a logical view of the physical storage of information in an Oracle database. There are actually three fundamental types of physical files that make up an Oracle database:

Control files
Datafiles
Redo log files

Other files are used within a database environment, such as password files and instance initialization files, but the three fundamental types just listed represent the physical database itself. Figure 2-3 illustrates the three types of files and their interrelationships.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

An Oracle instance can be defined as an area of shared memory and a collection of background processes. The area of shared memory for an instance is called the System Global Area, or SGA. The SGA is not really one large undifferentiated section of memory—it's made up of various components that we'll examine in the next section, Section 2.3.1. All the processes of an instance—system processes and user processes—share the SGA.

Prior to Oracle9i, the size of the SGA was set when the Oracle instance was started. The only way to change the size of the SGA or any of its components was to change the initialization parameter and then stop and restart the instance. With Oracle9i, you can change the size of the SGA or its components while the Oracle instance is still running. Oracle9i introduces the concept of the granule, which is the smallest amount of memory that you can add to or subtract from the SGA.

The background processes interact with the operating system and each other to manage the memory structures for the instance. These processes also manage the actual database on disk and perform general housekeeping for the instance.

There are other physical files that you can consider as part of the instance as well:

The instance initialization file: The initialization file contains a variety of parameters that configure how the instance will operate: how much memory it will use, how many users it will allow to connect, to which database the instance actually provides access, and so on. You can alter many of these parameters dynamically at either the systemwide or session-specific level. Up until Oracle9i, the only initialization file was calledINIT.ORA . Oracle9i introduces a file named SPFILE that performs the same function as the INIT.ORA file but can also persistently store changes to initialization parameters that have been made while Oracle9i is running.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Each Oracle database includes a set of what is called metadata, or data that describes the structure of the data contained by the database. The tables and views that hold this metadata are referred to as the Oracle Data Dictionary. All the components discussed in this chapter have corresponding system tables and views in the data dictionary that fully describe the characteristics of the component. You can query these tables and views using standard SQL statements. Table 2-1 shows how you can find information about each of the components in the data dictionary.

Data dictionary tables that are preceded by the V$ or GV$ prefixes are dynamic tables, which are continually updated to reflect the current state of the Oracle database. Static data dictionary tables can have a prefix such as DBA_, ALL_, or USER_ to indicate the scope of the objects listed in the view.

Table 2-1: Database Components and Their Related Data Dictionary Views
Component	Data Dictionary Tables and Views
Database	V$DATABASE
Tablespaces	DBA_TABLESPACES, DBA_DATA_FILES, DBA_FREE_SPACE
Control files	V$CONTROLFILE, V$PARAMETER, V$CONTROLFILE_RECORD_SECTION
Datafiles	V$DATAFILE, V$DATAFILE_HEADER, V$FILESTAT, DBA_DATA_FILES
Segments	DBA_SEGMENTS
Extents	DBA_EXTENTS

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

If you've been reading this book sequentially, you should understand the basics of the Oracle database architecture by now. This chapter begins with a description of how to install a database and get it up and running. (If you've already installed your Oracle database software, you can skim through this first section.) We'll describe how to create an actual database and how to configure the network software needed to run Oracle. Finally, we'll examine how to manage databases and discuss how users access databases.

Prior to Oracle8i, the Oracle installer came in both character and GUI versions for Unix. The Unix GUI ran in Motif using the X Windows system. Windows NT came with a GUI version only. From Oracle8i on, the installer has been completely rewritten in Java. The Oracle installer is one of the first places in which you can see the benefits of the portability of Java; the installer looks and functions the same way across all operating systems. Installing Oracle is now quite simple, requiring only a few mouse clicks and answers to some questions about options and features.

The installer allows you to install Oracle software, uninstall software, or simply check which software you have installed. The launch screen for the installer is shown in Figure 3-1.

Figure 3-1: Oracle Universal Installer

Although the installation process is now the same for all platforms, there are still particulars about the installation of Oracle that relate to specific platforms. Each release of the Oracle Database Server software is shipped with several pieces of documentation. Included in each release are an installation guide, release notes (which include installation information added after the installation guide was published), and a "getting started" book. You should read all of these documents prior to starting the installation process, since each of them contains invaluable information about the specifics of the installation. You will need to consider details such as where to establish the Oracle Home directory and where database files will reside. These issues are covered in detail in the documentation. In addition to the hardcopy documentation, online documentation is shipped on the database server CD-ROM, which provides additional information regarding the database and related products.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Prior to Oracle8i, the Oracle installer came in both character and GUI versions for Unix. The Unix GUI ran in Motif using the X Windows system. Windows NT came with a GUI version only. From Oracle8i on, the installer has been completely rewritten in Java. The Oracle installer is one of the first places in which you can see the benefits of the portability of Java; the installer looks and functions the same way across all operating systems. Installing Oracle is now quite simple, requiring only a few mouse clicks and answers to some questions about options and features.

The installer allows you to install Oracle software, uninstall software, or simply check which software you have installed. The launch screen for the installer is shown in Figure 3-1.

Figure 3-1: Oracle Universal Installer

Although the installation process is now the same for all platforms, there are still particulars about the installation of Oracle that relate to specific platforms. Each release of the Oracle Database Server software is shipped with several pieces of documentation. Included in each release are an installation guide, release notes (which include installation information added after the installation guide was published), and a "getting started" book. You should read all of these documents prior to starting the installation process, since each of them contains invaluable information about the specifics of the installation. You will need to consider details such as where to establish the Oracle Home directory and where database files will reside. These issues are covered in detail in the documentation. In addition to the hardcopy documentation, online documentation is shipped on the database server CD-ROM, which provides additional information regarding the database and related products.

You'll typically find the installation guide in the server software CD case. The installation guide includes system requirements (memory and disk), preinstallation tasks, directions for running the installation, and notes regarding migration of earlier Oracle databases to the current release. You should remember that complete installation of the software includes not only loading the software, but also configuring and starting key services.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

As we discussed in Chapter 2, an Oracle installation can have many different databases. You should take a two-step approach for any new databases you create. First, understand the purpose of the database, and then create the database with the appropriate parameters.

As with installing the Oracle software, you should spend some time learning the purpose of an Oracle database before you create the database itself. Consider what the database will be used for and how much data it will contain. You should understand the underlying hardware that you'll use—the number and type of CPUs, the amount of memory, the number of disks, the controllers for the disks, and so on. Because the database is stored on the disks, many tuning problems can be avoided with proper capacity and I/O subsystem planning.

Planning your database and the supporting hardware requires insights into the scale or size of the workload and the type of work the system will perform. Some of the considerations that will affect your database design and hardware configuration include the following:

How many users will the database have?: How many users will connect simultaneously and how many will concurrently perform transactions or execute queries?
Is the database supporting OLTP applications or data warehousing?: This distinction leads to different types and volumes of activity on the database server. For example, online transaction processing (OLTP) systems usually have a larger number of users performing smaller transactions, while data warehouses usually have a smaller number of users performing larger queries.
What are the expected size and number of database objects?: How large will these objects be initially and what growth rates do you expect?

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Oracle Net (known as Net8 for Oracle8 and Oracle8i and SQL*Net prior to Oracle8) is a layer of software that allows different physical machines to communicate for the purpose of accessing an Oracle database.

The name Net8 has been changed to Oracle Net in Oracle9i, and we will generally use "Oracle Net" in this chapter as a neutral term to apply to all versions of Oracle networking. The term "Oracle Net services" in Oracle9i refers to all the components of Oracle Net, including dispatchers, listeners, and shared servers; these are explained later in this chapter.

A version of Oracle Net runs on the client machine and on the database server, and allows clients and servers to communicate over a network using virtually any popular network protocol (e.g., TCP/IP, LU6.2, DECNET, and SPX/IPX). Oracle Net can also perform network protocol interchanges. For example, it allows clients that are speaking SPX/IPX to interact with database servers that are speaking TCP/IP.

Oracle Net also provides location transparency—that is, the client application does not need to know the server's physical location. The Oracle Net layer handles the communications, which means that you can move the database to another machine and simply update the Oracle Net configuration details accordingly. The client applications will still be able to reach the database, and no application changes will be required.

Oracle Net introduces the notion of service names, or aliases. Clients provide a service name or Oracle Net alias to specify which database they want to reach without having to identify the actual machine or instance for the database. Oracle Net looks up the actual machine and the Oracle instance, using the provided service name, and transparently routes the client to the appropriate database.

The following Oracle Net configuration options resolve the service name the client specifies into the host and instance names needed to reach an Oracle database:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Starting a database is quite simple—on Windows NT you simply start the Oracle services, and on Unix you issue the STARTUP command from Server Manager or SQL*Plus (in Oracle8i). While starting a database appears to be a single action, it involves an instance and a database and occurs in several distinct phases. When you start a database, the following actions are automatically executed:

Starting the instance. Oracle reads the instance initialization parameters from the SPFILE or INIT.ORA file on the server. Oracle then allocates memory for the System Global Area and starts the background processes of the instance. At this point, none of the physical files in the database have been opened, and the instance is in the NOMOUNT state.

There are problems that can prevent an instance from starting. For example, there may be errors in the initialization file, or the operating system may not be able to allocate the requested amount of shared memory for the SGA. You also need the special privilege SYSOPER or SYSDBA, granted through either the operating system or a password file, to start an instance.
Mounting the database. The instance opens the database's control files. The initialization parameter CONTROL_FILES tells the instance where to find these control files. At this point only the control files are open. This is called the MOUNT state, and at this time, the database is accessible only to the database administrator. In this state, the DBA can perform only certain types of database administration. For example, the DBA may have moved or renamed one of the database files. The datafiles are listed in the control file but aren't open in the MOUNT state. The DBA can issue a command (ALTER DATABASE) to rename a datafile. This command will update the control file with the new datafile name.
Opening the database

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Logically enough, the process of shutting down a database or making it inaccessible involves steps that reverse those discussed in the previous section:

Closing the database . Oracle flushes any modified database blocks that haven't yet been written to the disk from the SGA cache to the datafiles. Oracle also writes out any relevant redo information remaining in the redo log buffer. Oracle then checkpoints the datafiles, marking the datafile headers as "current" as of the time the database was closed, and closes the datafiles and redo log files. At this point, users can no longer access the database.
Dismounting the database . The Oracle instance dismounts the database. Oracle updates the relevant entries in the control files to record a clean shutdown and then closes them. At this point, the entire database is closed; only the instance remains.
Shutting down the instance . The Oracle software stops the background processes of the instance and frees, or deallocates, the shared memory used for the SGA.

In some cases (e.g., if there is a machine failure or the DBA aborts the instance), the database may not be closed cleanly. If this happens, Oracle doesn't have a chance to write the modified database blocks from the SGA to the datafiles. When Oracle is started again, the instance will detect that a crash occurred and will use the redo logs to automatically perform what is called crash recovery. Crash recovery guarantees that the changes for all committed transactions are done and that all uncommitted or in-flight transactions will be cleaned up. The uncommitted transactions are determined after the redo log is applied and automatically rolled back.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The previous sections described the process of starting up and shutting down a database. But the database is only part of a complete system—you also need a client process to access the database, even if that process is on the same physical machine as the database.

To access a database, a user connects to the instance that provides access to the desired database. A program that accesses a database is really composed of two distinct pieces—a client program and a server process—that connect to the Oracle instance. For example, running the Oracle character-mode utility SQL*Plus involves two processes:

The SQL*Plus process itself, acting as the client
The Oracle server process, sometimes referred to as a shadow process, that provides the connection to the Oracle instance

Section 3.6.1.1: Server process

The Oracle server process always runs on the computer on which the instance is running. The server process attaches to the shared memory used for the SGA and can read from it and write to it.

As the name implies, the server process works for the client process—it reads and passes back the requested data, accepts and makes changes on behalf of the client, and so on. For example, when a client wants to read a row of data stored in a particular database block, the server process identifies the desired block and either retrieves it from the database buffer cache or reads it from the correct datafile and loads it into the database buffer cache. Then, if the user requests changes, the server process modifies the block in the cache and generates and stores the necessary redo information in the redo log buffer in the SGA. The server process, however, does not write the redo information from the log buffer to the redo log files, and it does not write the modified database block from the buffer cache to the datafile. These actions are performed by the Log Writer (LGWR) and Database Writer (DBWR) processes, respectively.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

To truly understand how all the disparate pieces of the Oracle database work together, in this section we'll walk through an example of the steps taken by the Oracle database to respond to a user request. For this example, we'll look at a user who is adding new information to the database (in other words, executing a transaction).

A transaction, in this case, is a work request from a client to insert, update, or delete data. The statements that change data are a subset of the SQL language called Data Manipulation Language (DML). Transactions must be handled in a way that guarantees their integrity. Although Chapter 7 delves into transactions more deeply, we must visit a few basic concepts relating to transactions now in order to understand the example in this section:

Transactions are logical and complete: In database terms, a transaction is a logical unit of work composed of one or more data changes. A transaction may consist of multiple INSERT, UPDATE, and/or DELETE statements affecting data in multiple tables. The entire set of changes must succeed or fail as a complete unit of work. A transaction starts with the first DML statement and ends with either a commit or a rollback.

Commit or rollback

Table of Contents

Section 1.4.1.1: SQL

Section 1.4.1.2: PL/SQL

Section 1.4.1.3: Java features and options

Section 1.5.1.1: Oracle Net/Net8

Section 1.5.1.2: Oracle Names

Section 1.5.1.3: Oracle Internet Directory

Section 1.8.1.1: Availability

Section 1.9.2.1: Standard Management Pack

Section 1.9.2.2: Diagnostics Pack

Section 3.6.1.1: Server process