By David C. Kreines, Brian Laskey
Price: $39.95 USD
£28.50 GBP

Cover | Table of Contents | Colophon

Table of Contents

Chapter 1: Introduction

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Welcome to the world of Oracle database administration! This chapter provides an overview of Oracle database administration, and supplies some basic information required to understand the structure and characteristics of the Oracle database.

If one hundred Oracle database administrators were asked to define what a DBA does, the exercise would probably result in one hundred different descriptions. Nonetheless, there would be a high degree of commonality among these descriptions, and taken as a whole they would probably yield a fairly accurate job description for an Oracle DBA.

When Educational Testing Service (ETS) set out to develop a certification examination for Oracle DBAs a few years ago, the company's first step was to perform a job analysis, which is a formal way of evaluating and describing a job. The process ETS followed included these steps:

1. A group of experts was asked to assemble a laundry list of the tasks, skills, and abilities required of a DBA. A survey instrument was developed that listed these items along with a rating scale for the importance of each item.
2. The survey was administered to a large number of Oracle DBAs around the world.
3. The survey results were tabulated, yielding an average importance rating for each task, skill, and ability. Those falling below a predetermined cutoff level were eliminated.

The result was a detailed description of the tasks performed by a DBA, and of the specific knowledge and abilities required to perform those tasks. This DBA job analysis then became the blueprint for the Certified Oracle Database Administrator examination.

Here is a summary of the DBA job description that resulted from the ETS study. Prefix each of the following elements with "An Oracle DBA must be able to...."

Oracle architecture and options

• Demonstrate an understanding of the memory structures and processes that make up an Oracle instance
• Demonstrate an understanding of the logical and physical structures associated with an Oracle database
• Demonstrate an understanding of PL/SQL constructs (triggers, functions, packages, procedures) and their processing

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

Oracle Database Administration

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

If one hundred Oracle database administrators were asked to define what a DBA does, the exercise would probably result in one hundred different descriptions. Nonetheless, there would be a high degree of commonality among these descriptions, and taken as a whole they would probably yield a fairly accurate job description for an Oracle DBA.

When Educational Testing Service (ETS) set out to develop a certification examination for Oracle DBAs a few years ago, the company's first step was to perform a job analysis, which is a formal way of evaluating and describing a job. The process ETS followed included these steps:

1. A group of experts was asked to assemble a laundry list of the tasks, skills, and abilities required of a DBA. A survey instrument was developed that listed these items along with a rating scale for the importance of each item.
2. The survey was administered to a large number of Oracle DBAs around the world.
3. The survey results were tabulated, yielding an average importance rating for each task, skill, and ability. Those falling below a predetermined cutoff level were eliminated.

The result was a detailed description of the tasks performed by a DBA, and of the specific knowledge and abilities required to perform those tasks. This DBA job analysis then became the blueprint for the Certified Oracle Database Administrator examination.

Here is a summary of the DBA job description that resulted from the ETS study. Prefix each of the following elements with "An Oracle DBA must be able to...."

Oracle architecture and options

• Demonstrate an understanding of the memory structures and processes that make up an Oracle instance
• Demonstrate an understanding of the logical and physical structures associated with an Oracle database
• Demonstrate an understanding of PL/SQL constructs (triggers, functions, packages, procedures) and their processing
• Demonstrate an understanding of distributed architecture and client server
• Demonstrate an understanding of locking mechanisms

Security

• Create, alter, and drop database users
• Monitor and audit database access
• Develop and implement a strategy for managing security (roles, privileges, authentication)

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

Oracle Architecture

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This section gives a brief overview of the Oracle architecture. More detailed information is provided in Part II of the book, especially in Chapter 10, and Chapter 11.

For most Oracle users, the terms database and instance are used synonymously. However, there are differences that become especially important if you are using the Oracle Parallel Server (OPS) Option. The database is the data on disk, stored in operating system files (or possibly, under Unix, in raw files). The instance is composed of the System Global Area (SGA) memory and the background processes. The instance is STARTed using Oracle Server Manager or Oracle Enterprise Manager (OEM). The database is then MOUNTed on the instance and is finally OPENed. The users CONNECT to the instance in order to access the data in the database. Figure 1.1 shows the basic components of the Oracle database and instance.

Figure 1.1: The Oracle database and the Oracle instance

Except in an OPS environment, there is a one-to-one correspondence between instance and database. In the OPS world, the database can be MOUNTed on multiple instances.

The background processes take care of the database. Depending on the options configured and the values specified in the Oracle initialization file, usually known as the INIT.ORA file, there can be from 6 to 50 or more background processes. Each background process has a specific responsibility to perform. See Chapter 10, for more details on their functions. Oracle created the background processes as separate entities to allow them to run independently of each other, performing their functions as needed and, if possible, simultaneously.

The INIT.ORA parameter SINGLE_PROCESS forces the background processes to run as one process. This is primarily used for system debugging with Windows NT. All of the INIT.ORA parameters are described in Chapter 12.

The System Global Area (SGA) is the shared memory area used by the instance to store information that must be shared between the database and user processes. The main components of the SGA include the database buffer cache, shared pool, and redo log buffers. The

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

Software Options

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The Oracle relational database management system (RDBMS) is sold as a base product with options. Typically, new functionality is first introduced as an extra-cost option, and later bundled into the base product. The information provided here is current at the time of publication. Please contact your Oracle sales representative or the Oracle web site (www.oracle.com)for additional information.

The base product is available in two versions: Enterprise Server and Server. Originally, these two versions were referred to as the Server and the Workgroup Server. The Enterprise Server is the full-function version of the RDBMS. It contains all of the base features mentioned in Table 1.1, and it can support all of the Oracle options and data cartridges. The Server version is less feature-rich; it is designed to compete at the low end. The Server version does not support all the functionality of the Enterprise Server, it cannot be configured with all of the options and data cartridges, and, in some cases, it may be limited by the number of users or the size of the machine on which it can run. However, the Server version carries a lower price per user than the Enterprise Server, and may be attractive for that reason.

Table 1.1: Oracle Base Product Features

Feature	Oracle8 Server	Enterprise Server
Oracle Enterprise Manager (OEM)	Y	Y
Manageability Packs	N	Y
Fail safe for Oracle8 on NT	Y	Y
Client failover	N	Y
Server-managed backup and recovery

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

Configuration Planning

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Once your organization has decided which Oracle options to acquire, you need to start planning the implementation. Here are some of the areas you will have to consider.

One of the most important functions of the DBA is to provide for the continued availability of the information in the database. Prior to implementing the database, you need to specify how long you can afford to be down in the event of an emergency, as well as how long you can be down for normal maintenance. How you answer these questions determines what sort of redundancy you need to build into your configuration, and dictates how you will perform backups and standard system maintenance.

We recommend that the availability issue be discussed with the owners of the system and, if necessary, that their agreement be received in writing.

A number of Oracle facilities can help guarantee availability; we recommend that you consider the use of the replication, hot standby database, and Oracle Parallel Server facilities, described briefly in the following sections.

Section 1.4.1.1: Replication

Replication allows you to maintain separate databases, where the updates that are performed to one database are automatically propagated to the others. These redundant databases can be used both to maintain local copies of data (eliminating long network propagation delays) and to provide a second copy (which can continue in the event of failure of one of the databases). Once the failed database is brought back online, all updates that have occurred since the failure will be automatically applied to the database.

Replication does require that you completely duplicate the entire physical implementation, including multiple computer systems and storage. You will also have to implement a reasonably fast network communication link between the multiple computer systems. However, replication can be used to survive not only hardware or software problems, but also sitewide emergencies (assuming, of course, that the replicated database is running offsite).

Section 1.4.1.2: Hot standby database

With the hot standby database, you actually use only one database at a time. The second database is constantly in recovery mode. As an archived redo log file is generated, it is copied over to the remote site and applied to the database. In the event of a failure of the primary database, you complete the recovery process and bring up the standby 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!

Chapter 2: Installation

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

It probably goes without saying, but Oracle installation is a prerequisite to most of the administration topics mentioned in this book. Installation is probably one of the most important tasks a DBA will perform.

Oracle runs on a wide variety of hardware platforms, from personal computers to large mainframes, and there is a corresponding variety of installation methodologies. This chapter gives an overview of the Oracle installation process and the tools provided by Oracle to help with installation. We'll give you the critical information you need to complete an installation that will both function now and be maintainable in the future. However, this chapter cannot replace the appropriate Oracle Installation and User's Guide for your platform. Although we are experienced DBAs, we begin every installation with a careful review of the appropriate Oracle documentation, and we urge you to do the same.

To successfully complete an Oracle installation, you must perform the following tasks:

• Make pre-installation decisions.
• Obtain appropriate media.
• Install the Oracle software.
• Create a database.
• Configure the database.

We'll discuss each of these tasks in this chapter. See Figure 2.1 for an outline of the installation process.

Figure 2.1: The Oracle installation process

One of the first decisions you'll need to make before beginning an Oracle installation is the selection of an appropriate installation medium. For each platform on which Oracle runs, one or more types of installation media are available. These media include:

• CD-ROM
• DAT tape
• Cartridge tape (various types)

On some platforms, there may be no choice of media. For example, Oracle for Windows 98 is available only on CD-ROM. On other platforms, there may be a variety of media available. Oracle for Unix, for example, is available on CD-ROM, DAT tape, and cartridge tape. When ordering installation media, be certain that the media offered:

• Is supported by the hardware on your system
• Is the correct physical size
• Is the correct format
• Is intended for the operating system running on your hardware

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

Media Selection

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

One of the first decisions you'll need to make before beginning an Oracle installation is the selection of an appropriate installation medium. For each platform on which Oracle runs, one or more types of installation media are available. These media include:

• CD-ROM
• DAT tape
• Cartridge tape (various types)

On some platforms, there may be no choice of media. For example, Oracle for Windows 98 is available only on CD-ROM. On other platforms, there may be a variety of media available. Oracle for Unix, for example, is available on CD-ROM, DAT tape, and cartridge tape. When ordering installation media, be certain that the media offered:

• Is supported by the hardware on your system
• Is the correct physical size
• Is the correct format
• Is intended for the operating system running on your hardware

To obtain installation media or to arrange for replacement of faulty or incorrect media, contact Oracle Customer Relations by calling Oracle Support.

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

The Oracle Installer

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

No matter what hardware platform Oracle is to be installed on, installer software is provided as part of the installation media. Normally, this installer software is called orainst ; it may be called orainst, orainst.exe, orainst.com, and so on, depending on the operating system naming standards for executable files. Although the Oracle installer may look different or operate differently on different hardware platforms, its basic function is essentially the same. The Oracle installer will:

• Install Oracle software components
• Optionally create a starter database
• Perform operating system functions required to run Oracle

Depending on your operating system and hardware, the installer may be offered in one or more of the following modes:

Character mode

This variation of the installer is the most common and is intended to run on plain, character-based terminals like a VT-100. The character mode installer uses few graphics (other than line characters) and requires only a keyboard to operate.

Windows mode

On Windows-based machines (e.g., Intel machines running Windows 95, Windows 98, or Windows NT), a native Windows installation program is provided, which takes full advantage of Windows functionality, including a graphical user interface (GUI), which generally requires a keyboard and a mouse.

Motif mode

This type of installer is common on Unix implementations, and is also called X Windows. It is similar to a Windows implementation, and takes advantage of the Motif GUI. The Motif mode installer requires an X terminal (or X terminal emulation) with a keyboard and a mouse.

Strictly speaking, a mouse is not necessary to run the installer under Windows or Motif. The keyboard can be used to position the cursor, but this is very cumbersome and is not often done.

When multiple installers are available, the Oracle Installation and User's Guide for your platform will explain how to run each installer. In general, the mode chosen affects the look and feel of the installer, but the installer functionality is the same regardless of the mode you select.

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

Installable Components

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

When you begin your installation, you will need to indicate to the installer exactly which Oracle software components you wish to install. Typically, Oracle installation media include all components available for the platform. However, some of these components may require separate licensing, and some may either be unnecessary or inappropriate for your particular installation. For example, SQL*Net will be included on the installation media, including all available SQL*Net protocol adapters. In most cases, you will only be using one or two SQL*Net protocols, so it would not make sense to install any others.

The Oracle installer may be able to detect network protocols running on your machine and may attempt to install protocol adapters to support those found. Be sure that you want to install all the adapters that the installer selects. For example, the IPX protocol may be running on your machine, but you may not want SQL*Net to utilize that protocol, so in this case you would choose not to install the SPX protocol adapter.

There will be many database options and tools presented to you in the "available" list, and you should carefully choose those you wish installed. For example, in most Oracle installers, you must choose not only "Oracle Server (RDBMS)" but also "PL/SQL" if you want to use PL/SQL (and who doesn't need that?).

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

Dependencies

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Many Oracle components have dependencies—that is, one component requires one or more other components. The Oracle installer handles these dependencies automatically, so you can be assured that all required components will be installed. For example, if you choose to install the Oracle Server, the appropriate Required Support Files (RSF) will be recognized as a dependency and will be included automatically in your installation. At the end of the installation, you will see a list of installed components. Don't be surprised to see components listed there that you did not explicitly request—they are the result of dependency checking.

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

Pre-Installation Checklist

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Before beginning an Oracle installation session, make sure that:

• The Installation and User's Guide (IUG) has been read and is available.
• Any included Release Notes have been carefully reviewed. Don't forget the README file usually found on the installation media—this file often contains critical information.
• Appropriate installation media are available.
• Required operating system changes have been made (e.g., kernel parameters and patches).
• Sufficient disk space is available.
• Sufficient system memory is available.
• Desired Oracle components are identified.
• Root, system manager, or other appropriate passwords are available.
• The directory structure for Oracle files has been carefully planned.

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

Critical Decisions

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

While most decisions made during installation can be changed at a later time, some decisions are either very difficult or impossible to change, so it is important that you make these decisions correctly at the time of installation. They include:

• Creation of a starter database
• Location of Oracle software (ORACLE_HOME )
• Directory structure for Oracle datafiles
• Language to be used
• Database blocksize
• Number, size, and location of online log files
• Maximum number of datafiles to be allowed

We describe each of these decisions in the following sections.

On most Oracle platforms, when you install the Oracle software you are given an option to create an initial or starter database, which normally consists of:

• Default database name (usually ORCL)
• Default database blocksize (usually 2048)
• A SYSTEM tablespace
• A USERS tablespace
• Users SYS, SYSTEM, and SCOTT
• Three online redo log files
• A single control file
• A single non-SYSTEM rollback segment
• A standard initialization file (generally referred to as INIT.ORA in this book)

The sizes and locations of the files associated with this initial database vary from platform to platform. For all but the most simplistic database application, we strongly recommend that the starter database not be installed. Rather, you should carefully plan the database installation and create the database according to that plan. We recommend against using the starter database for the following reasons:

• The database should be meaningfully named.
• The default blocksize is usually too small.
• The operating system file locations do not follow the Optimal Flexible Architecture (OFA), described in the next section.
• The initial file sizes are usually inadequate.
• Users are created with inappropriate DEFAULT and TEMP tablespace assignments.
• The redo log files have not been sized and are not located properly.
• Multiple (mirrored) control files should always be used to protect against damage or loss.
• Multiple rollback segments should be defined in a non-SYSTEM tablespace.
• Initialization parameters should be planned and tuned for the database.

While it is certainly possible for the DBA to allow Oracle to create an initial database and then make appropriate modifications, it is far more efficient and less complex and time-consuming to create the appropriate database from scratch.

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

Post-Installation Tasks

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Once the Oracle software has been installed on the machine, there are a number of tasks you need to perform to create a functioning Oracle database. For example, on Unix systems, the root user must run a script called root.sh after installation.

We also recommend that you run the installer to check the list of components actually installed. In some cases, the Oracle Installer will install components that you did not request and that are not necessary. For example, the Oracle Web Server is automatically installed on some platforms, even though it was not requested and requires separate licensing. If you find an unnecessary component, it can be removed with the installer.

The first task you must perform after ensuring that the Oracle software has been correctly installed is creation of the database. This task consists, in turn, of several discrete subtasks, described in the following sections.

Section 2.7.1.1: Choosing a blocksize

While Oracle has defined a default blocksize for every operating system environment (often 2048), this blocksize is often not correct for the database being created. Few characteristics of the database are as important to overall performance as the database blocksize. The blocksize is specified in the INIT.ORA file with the DB_BLOCK_SIZE parameter, and once a database is created with a particular blocksize, it cannot be changed. It is, therefore, important to consider the options for database blocksize and define one that will provide good performance for your site. A few factors to consider are the following:

• The blocksize must be at least as big as a single block or disk sector on the host hardware.
• The blocksize should be a multiple of the host operating system and/or hardware blocksize. For example, many systems write 512-byte blocks to disk, so the blocksize should be a multiple of 512.
• The blocksize should not be bigger than the largest amount of data that the host operating system and/or hardware can read or write in one operation. For example, an operating system may be able to transfer 8192 bytes in a single operation to disk.
• Small blocksizes require less data to be transferred to and from disk, and may result in better I/O operation.

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

Chapter 3: Maximizing Oracle Performance

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

If you ask a room full of Oracle database administrators, "What is the single biggest part of your job?", chances are that the almost universal response will be "Configuration and tuning of the database." Oracle is a very complex and powerful product, and part of its power lies in its ability to get the best possible performance out of each individual database configuration. This chapter presents our approach to the configuration and tuning of an Oracle database and provides guidelines for implementing a high-performance database at your site.

The ongoing, day-to-day responsibility of most Oracle DBAs is to get the best possible performance from the Oracle database. A number of definitions may be offered for "performance," but we define performance as the objective, measurable amount of time required to perform a typical operation in the database in question. Yes, this is a simplistic definition that ignores other metrics like resource utilization, but let's face it: the database is expected to be as fast as possible, so this is a reasonable definition for this purpose.

Entire books have been written on the subject of Oracle performance (see the appendix, Resources for the DBA, for some we consider worthy of your attention), so we cannot hope to address all the intricacies of Oracle performance tuning in a single chapter. Rather, we hope to document a straightforward approach to performance tuning and to provide some practical guidelines that can be applied to a variety of installations.

It is important to realize that every Oracle installation is different in terms of its physical and logical database implementation, the types of transactions processed, and the performance requirements for those transactions. As a result, there cannot be an automatic tuning methodology, although several vendors, including Oracle, have attempted to provide one. Nor can a single set of rules provide a method for maximizing database performance. What can be provided, however, is a methodology that, when properly applied and combined with the knowledge and experience of a working DBA, will result in good performance for any given 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!

Configuration and Tuning—What's the Difference?

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Getting maximum performance from an Oracle database requires careful attention to both configuration and tuning of the database. These terms are often used interchangeably, but in reality, they are two different tasks—admittedly, with a bit of grey area between them.

Configuration is the process of setting up the physical and logical components of the database and its host systems, while tuning is the process of modifying the internal behavior of the database so that operations are performed in a particular manner. The entire process can become somewhat circular, since proper tuning often includes modifying the configuration, which then requires another look at tuning. Figure 3.1 shows the basic steps in the configuration and tuning process.

Figure 3.1: The configuration and tuning process

Some items that can be configured in an Oracle database are the following:

• Components of the database that affect the allocation of system processes, such as:
  SQL*Net
  Multi-Threaded Server (MTS)
  Parallel Query
  Parallel Server
  
• The layout and sizes of physical storage
• The sizing of database objects, such as:
  Tables
  Indexes
  Rollback segments
  Sort areas
  Temporary tablespaces
  Redo logs
  Partition tables
  Index-only tables
  
• The amount and allocation of memory, such as:
  Database buffers
  Redo log buffers
  Shared pool
  

Aspects of the Oracle database that can be tuned include the following:

• Memory utilization
• Disk utilization
• SQL statement execution

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

Achieving Maximum Performance

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Achieving maximum performance for your Oracle database doesn't just happen—it is usually the result of a lot of hard work, thought, and planning. The rewards, however, are well worth the effort expended: your database runs at peak efficiency, your users are happy, and you look good!

Our approach to maximizing performance is hierarchical in nature. Three distinct areas must be addressed, and they should be addressed in order. These areas are:

• Operating system configuration
• Oracle resource configuration
• Object creation and SQL execution

These areas are not necessarily independent; in fact, significant changes in one area are likely to require another look at the other areas. They are, however, sequence dependent. That is, you cannot hope to get good performance out of Oracle unless and until you have properly configured and tuned your operating system. Likewise, good query execution depends on a properly configured Oracle environment.

Every Oracle database is different, so we cannot tell you exactly how to accomplish your configuration and tuning goals, or even what those goals should be. What we can do is provide you with an approach we've found to be successful.

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

Configuring the Operating System

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This one is usually easy, since (in most cases) it's not your job! In most installations, there is a system administrator or manager who is responsible for addressing operating system and hardware issues. This system administrator is usually an expert on the hardware and operating system software, and most DBAs are not in a position to second-guess him or her. While deferring to the system administrator's expertise, here are a few points you might want to make sure are addressed:

• Physical memory should be fully utilized, but swapping (in environments that swap memory) should not occur. Swapping memory to disk is a very slow process, so if your system needs more memory, buy more memory! In particular, make certain that you do not create a System Global Area (SGA) that is too big for physical memory, since swapping of the SGA will severely degrade Oracle performance.
• The CPU should approach 100% utilization at peaks, but processes should not have to wait for the CPU.
• Disks and controllers should be running at or near their optimal capacities (usually 60 to 90% of maximum) with minimal I/O waits. As the DBA, you do have some control over this area as well, as we'll describe later in this chapter.
• Network throughput should not be a bottleneck. Consider backbone networks to link servers to each other, and separate client/server traffic from server/server traffic where possible.
• Try to keep your Oracle server machine a pure server, and move users to another machine.
• Be sure any operating system components (including patches) that may affect Oracle are installed.

Because Oracle is a major vendor in the database marketplace, most major hardware vendors have Oracle "experts" on staff who can provide advice on hardware and operating system issues that may affect Oracle's execution on their hardware. Take advantage of this expertise!

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

Configuring Oracle

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Oracle's overall performance is affected by the components that are installed, as well as by how those components are configured. A high-performance Oracle database is essential to obtaining maximum performance from transactions run against that database. This section provides general configuration guidelines and some specific recommendations for configuring SQL*Net/Net8, MTS, Parallel Query, and Parallel Server.

While every installation is different, there are some general configuration guidelines that can be applied to most databases, regardless of the components installed or the use of the particular database. These general guidelines are described in the following sections.

Section 3.4.1.1: Check the documentation

This one may seem obvious, but it needs to be said: read the documentation. Even experienced DBAs will benefit from a quick read of the pertinent documentation before beginning an Oracle installation. We recommend that you look at the following (at least):

• Hardware-specific Installation and User's Guide (IUG)
• Server Administrator's Guide
• Release Notes (usually packed with the media)
• The README file, which is usually found on the installation media and contains last-minute information that may not be in the printed documentation.

Section 3.4.1.2: Check resource requirements

Before beginning an installation, be certain that sufficient system resources are available. The IUG for your platform contains comprehensive information about disk storage and memory requirements. Remember that these requirements are minimums, and that the resources required may actually be higher, depending on other configuration decisions you make. For example, more memory will be required if you specify a larger SGA.

In particular, make sure there is enough disk space available on the device where you place the Oracle software (typically called ORACLE_HOME ) to load all software and ancillary files.

Section 3.4.1.3: Check system privileges

Most operating systems require the account that is performing the Oracle installation to have certain privileges. Be sure to check the

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

Sizing and Configuring Database Objects

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Proper sizing and configuration of database objects are critical to achieving maximum database performance. Proper object sizing is an ongoing task; as objects are created and modified, you must continue to examine their characteristics and make changes when necessary. Some sizing-related problems that negatively impact performance are:

Tablespace fragmentation

This problem, which leaves many unusable small extents scattered about a tablespace, can result when objects are created with inappropriate INITIAL or NEXT extent sizes.

Row chaining

This problem, which causes the data from a single row to reside in multiple Oracle blocks, typically occurs when an insufficient PCTFREE setting is specified and updates subsequently occur to the table.

Multiple extents

Multiple extents, which may cause data for a particular object to be spread across one or more datafiles, result when objects are created with improper INITIAL or NEXT extent sizes. This problem may become critical when the MAXEXTENTS parameter is permitted to assume the default value, since an attempt to allocate an extent beyond that number will result in a failure.

Log waits

Log waits, which cause a process to wait while log buffer records are written to a log file or while a log file switch is occurring, can add significant processing time. These are usually caused by a combination of an insufficient number of log files and log files that are too small.

Failure to extend a rollback segment

Such failures, which can cause a transaction to roll back, are caused when not enough rollback segments are allocated, or when the rollback segments allocated are not large enough.

The following sections contain specific guidelines and suggestions that may help prevent some of these performance problems.

Tables are the basic units of data storage in an Oracle database, so their configuration and resulting performance will have a large impact on overall database performance. Some guidelines for table configuration are as follows:

• Try to estimate how big a table will be and allocate a sufficiently large INITIAL extent to hold the entire table. However, if you are using Parallel Query, allocate the total space across as many extents in different datafiles as the degree of parallelism for the table.

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

Tuning Oracle

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Perhaps no single aspect of the DBA's job consumes as much time as tuning. Successful Oracle tuning requires a blend of knowledge and experience, and can be both challenging and frustrating—often at the same time! Entire volumes have been written on Oracle tuning (see the appendix, Resources for the DBA), and we cannot hope to cover all aspects of tuning in a single section. Instead, as we mentioned earlier, we will outline for you an approach to tuning that can be applied to a variety of situations.

Successful tuning of an Oracle database requires a careful, disciplined approach. Like overall system configuration, tuning must address the following:

• Hardware and operating system performance
• Oracle instance performance
• Individual transaction (SQL) performance

These should be addressed in sequence, since database performance tuning is not possible until the operating system and hardware have been well tuned, and an individual SQL statement cannot be properly tuned if Oracle is not running efficiently. When tuning any of these areas, there are three distinct steps in the process:

1. Measure current performance.
2. Make appropriate changes.
3. Assess the result.

Some changes to the Oracle instance may result in the need for changes to the operating system environment. For example, allocating additional database buffers may cause the operating system to start paging, which may require additional operating system tuning to eliminate.

The tuning process is almost always an iterative one. That is, after completing the three steps outlined above, the DBA must return to step 1 and repeat the process. This continues until no additional performance gains are possible.

Most performance improvement at the Oracle instance level will be achieved by tuning two areas: memory utilization and disk I/O.

Section 3.6.2.1: Memory utilization

It should come as no surprise that memory-based operations are much faster (sometimes by thousands of times) than disk operations. As a result, tremendous performance improvements may be achieved by replacing disk I/O with memory access of data. The three primary ways in which this can be done are described in the following list:

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

Chapter 4: Preventing Data Loss

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Once the database is operational, your most important responsibility is to ensure the availability of the data in that database. You need to develop a backup and recovery plan in advance. This chapter describes the various situations you may encounter and introduces the basic tools you can use to recover your data. Chapter 16, gives the detailed syntax for using the tools described in this section.

The time to plan for a recovery is before you need to recover data. There are several ways in which your data can be backed up, and each way has its limitations. No two installations will have the same requirements, nor will they necessarily back up data in the same way. In Table 4.1, we list the most common scenarios and show the appropriate backup strategies.

Table 4.1: Common Backup and Recovery Scenarios

If You Need to...	Use This Mechanism
Recover up to the point of failure	Hot backups Cold backups with archivelog mode
Recover individual tables	Exports Tablespace point-in-time recovery
Recover on a different operating system	Exports
Create a point-in-time view of the data for historical records	Exports Cold backups
Recover the database to a point prior to a failure	Hot backups Cold backups with or without archivelog mode Exports
Not have downtime for backups	Hot backups Exports

A physical backup of the database is one in which the datafiles are backed up. Most installations copy the datafiles to tape, but copies can be made to disk or to another computer system. Unless otherwise noted, we'll assume in our discussion that the datafiles have been copied to tape.

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

Types of Backups

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The time to plan for a recovery is before you need to recover data. There are several ways in which your data can be backed up, and each way has its limitations. No two installations will have the same requirements, nor will they necessarily back up data in the same way. In Table 4.1, we list the most common scenarios and show the appropriate backup strategies.

Table 4.1: Common Backup and Recovery Scenarios

If You Need to...	Use This Mechanism
Recover up to the point of failure	Hot backups Cold backups with archivelog mode
Recover individual tables	Exports Tablespace point-in-time recovery
Recover on a different operating system	Exports
Create a point-in-time view of the data for historical records	Exports Cold backups
Recover the database to a point prior to a failure	Hot backups Cold backups with or without archivelog mode Exports
Not have downtime for backups	Hot backups Exports

A physical backup of the database is one in which the datafiles are backed up. Most installations copy the datafiles to tape, but copies can be made to disk or to another computer system. Unless otherwise noted, we'll assume in our discussion that the datafiles have been copied to tape.

Section 4.1.1.1: Benefits

With physical backups, the recovery process is usually faster than with logical backups, since you only need to restore files from the tape backup.

Section 4.1.1.2: Limitations

You can usually only restore to a computer system that's running the same operating system as the one from which the backups were made. There may also be limitations on operating system and Oracle release levels.

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

Database Backup

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This section describes the available backup techniques you can use to back up your database.

A cold backup (also known as an offline backup) is a physical backup that is taken when the database is not operational—it is "cold." A complete cold backup of the database, including the control files and redo logs, can be used to recreate the database to the time of the cold backup.

Section 4.2.1.1: Benefits

There are two major benefits of performing cold backups:

• A cold backup is the easiest mechanism for performing backups. Any mechanism for creating a copy of the datafiles will work.
• A cold backup can be used with the database in either archivelog or noarchivelog mode.

Section 4.2.1.2: Limitations

For cold backups, the database must be down for the time it takes to back up the entire database. For sites that have an availability requirement, this may not be viable. Unless you are in archivelog mode, the entire database must be backed up at the same time and restored as a whole. Oracle refers to this operation as a full backup .

In our discussion, the assumption is generally that you want to recover the entire database. However, it is possible to recover a subset of the tablespaces. A procedure for doing this is provided in the following sections.

A hot backup (also known as an online backup) is a physical backup that is taken when the database is operational—that is, "hot." Oracle provides specific support for hot backups through archivelog mode and the ALTER TABLESPACE BEGIN/END BACKUP commands. Tablespaces are placed into backup mode, backed up, then taken out of backup mode. Oracle performs a checkpoint of all datafiles in a tablespace when a tablespace is placed into backup mode, and records the checkpoint number in the header of all datafiles.

Before you back up a datafile, its tablespace must be placed in backup mode with the command:

ALTER TABLESPACE BEGIN BACKUP

At this point, the database marks the datafile(s) as being in backup mode and starts writing information to the redo log at the block level, rather than at the byte level. You may then back up the datafile using any mechanism you choose. Once the datafile has been backed up, you issue the command:

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

Database Recovery

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The whole purpose of backups is to be able to recover the database. This section discusses the various reasons why you may need to perform a recovery and provides an overview of the new tablespace point-in-time recovery process.

The following sections briefly describe how you can recover from different types of failure.

Section 4.3.1.1: System failure

A system failure means that the entire system is unavailable. A failure of this kind may be the result of either a site outage (caused by fire or natural disaster) or a total failure of the server.

It is standard practice to have at least two copies of your backup tapes, with one copy stored at a different location. This way, you not only do not have a single point of failure if the tape containing your archive file breaks, but you also are able to get at your tapes in the event of a disaster. Even storing a single copy of your tapes in a fireproof vault will not work if you cannot cross a police line after an explosion.

In the case of a system failure, you have three options:

• Recover the entire database from a cold backup.
• Recover the entire database from hot or cold backups and apply as many archive files as you have available.
• Recover the entire database from exports using full, cumulative, and incremental exports.

Section 4.3.1.2: Disk failure

A disk failure is the most common form of database failure. With the cost of RAID solutions (including mirroring and parity solutions such as RAID-1 and RAID-5) coming down, most sites have moved to a hardware solution to prevent a single disk failure from impacting the database. If your site does not use these solutions, then at some point in your career you will have to perform a recovery from a disk failure.

We recommend multiple control files, separate disks for archive files, and mirroring redo logs either with redo log groups or operating system mirroring. With mirrored redo logs and multiple control files on separate disks, no single disk failure will cause a loss of the database. If you have only one control file or only one redo member per group, then losing the disk containing the control file or the active redo log would be catastrophic to the database. Placing archive files on a separate disk from the redo logs (if mirroring is not used) at least ensures that you can recover the database up to the point of the last archive file.

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

Automated Utilities

Content preview·