P ROPUESTAS DE MEJORA

parameter files for export, national language support (NLS) considerations, exporting to a point in time using Oracle9iFlashback Query, maximizing export performance using direct path, identifying limitations and security considera- tions for direct path, controlling the size of export files using UNIX file compres- sion, using transportable tablespaces to speed up restore, and running import for maximum performance including an import benchmark.

Part Five covers high-availability solutions:

Chapter 20: VERITAS High Availability (HA) for Oracle. Topics covered include Oracle Disk Manager, and an overview of the following VERITAS prod- ucts and features with respect to Oracle HA—VERITAS File System (VxFS) and Volume Manager, Storage Rollback, VERITAS Cluster Server (VCS), FlashSnap, and VERITAS Volume Replication Facility (VVRF).

Chapter 21: Oracle Replication. Topics covered include a comparison of multi- master replication and standby database for disaster recovery, synchronous and asynchronous replication, conflict resolution and notification, issues associated with sequences in a replicated environment, prerequisites for a multimaster con- figuration, and the setup and execution of a multimaster configuration using Oracle Enterprise Manager (OEM).

Chapter 22: Real Application Clusters (RAC). Topics covered include the func- tionality gaps in Oracle Parallel Server (OPS) that RAC solves, the components of a RAC configuration, a description of cache fusion improvements, installation improvements and manageability enhancements, interconnect configuration, parameter differences between RAC and single instance configuration, a com- plete set of initialization parameters for a two-node cluster, the client network configuration for load balancing and failover, considerations for choosing your Oracle operating system, and choosing to run RAC on Linux.

Chapter 23: Protecting Data Using Standby Databases. Topics covered include creating and running a physical standby database in Oracle8i, creating and run- ning a physical standby database using Oracle9iData Guard, creating and run- ning a logical standby database using Oracle9iData Guard, and improving standby management using Oracle9iData Guard Manager and broker. Part Six shows you how to maintain your Oracle system:

Chapter 24: Guidelines for Health Checks and Monitoring. Topics covered include health checks for physical layout, health checks for tablespace and roll- back segment definitions, defining monitoring requirements, implementing monitoring using database jobs, sending alerts from within the database using UNIX sendmail, implementing monitoring using OEM and Oracle Intelligent Agent (OIA), creating customized monitoring scripts using Tool Control Lan- guage (Tcl), and performing fixit jobs.

Chapter 25: Auditing Techniques. Topics covered include enabling the database audit trail, relocating the audit trail and changing ownership, understanding the audit session identifier, choosing audit options, identifying suspicious activity, using the audit trail to track input/output (I/O) trends, using triggers to audit data content changes, and using fine-grained access control to audit SELECT statements in Oracle9iusing the DBMS_FGA package.

Chapter 26: Migration and Upgrade. Topics covered include a migration prereq- uisites checklist, using Oracle Data Migration Assistant (ODMA) to perform migration, and post-migration tasks.

Chapter 27: Working Effectively with Oracle Support. Topics covered include benefits and drawbacks of problem reporting via the Web, the Oracle Support Services (OSS) view of the customer’s role in the support process, the escalation process, the use of Oracle STATSPACK and Remote Diagnostic Agent to stan- dardize support, and the choice of your Oracle product set for supportability. Chapter 28: Troubleshooting Oracle DBMS Problems. Topics covered include

understanding the UNIX system log, identifying Oracle shared memory, using UNIX kernel tracing, using Oracle and operating system network tracing, using Oracle event tracing, and utilizing operating system performance diagnostics.

Who Should Read This Book?

One theme that emerges strongly from the book is that both DBAs and developers have a strong influence on whether systems meet performance and availability goals. Organizations where developers and DBAs have a rigid view of their respective roles and responsibilities often deliver production applications that don’t meet end-user requirements.

At different times, I’ve been both a professional Oracle DBA and a product devel- oper at Oracle Corporation, so I’ve seen the situation from both sides. The best approach is one where an organization employs Oracle professionals with a mixture of skills, where some have an emphasis on development and others have an emphasis on production DBA support. A secondary goal of this book is to try to break down the tra- ditional walls between developers and DBAs by giving visibility to the kind of tasks that each performs. From my experience, a poor relationship between DBA and devel- opment groups often results from a poor understanding of what the other group does. Therefore, this book is intended for both DBAs and developers of all levels. In many ways, the term advancedin the Oracle world is something of a misnomer. The scope of what Oracle provides is very extensive: There’s a lot to know, but most of it is not espe- cially complex. Anyone who understands Oracle fundamentals can understand and benefit from the contents of this book and Oracle’s own Concepts Guide is a great place to start. The challenge comes in deploying systems based on techniques that work from the many techniques available. This book sets out to provide a fast track to deploying systems with performance and availability built in.

Tools You Will Need

This book contains many SQL code examples, and you need a suitable tool to execute them. DbCool (www.dbcool.com) is a tool you might consider using as a companion to this book. It was used to run all SQL and generate all SQL output referenced in the text. One advantage of DbCool is that all SQL output column widths are automatically sized to fit the width of the data in output. Another is that output results can be grouped and sorted on the client without re-executing the original SQL. There is no

obligation to use DbCool, which is free and runs on Windows platforms. SQL*Plus and iSQL*Plus work just as well in many cases, except in situations where line-mode out- put is incompatible with the requirement, such as stepping through a SQL execution plan. All SQL examples in the text contain the statement terminator required when you submit SQL or PL/SQL from SQL*Plus.

Some examples are based on C code, and you will need a C compiler to build the shared libraries from source code. All code compiles with the free Gnu C compiler (gcc). It’s assumed that you have access to an Oracle database running the Oracle Enter- prise Edition, as this is required for some of the more advanced Oracle features. It is noted in the text when the Enterprise Edition is required.

What’s on the Companion Web Site?

The companion Web site for this book (www.wiley.com/compbooks/ingram) includes updates on techniques found in the book, links to useful resources, and full source code for several examples referenced in this book, including the following:

dbcool_perl.tar. A UNIX tar file containing all the scripts required to implement the Perl-based Oracle management scripts covered in Chapter 4.

dbcool_mon.sql. A PL/SQL package for the collection of Oracle events and sta- tistics metrics

dbcool_tkprof.sql and dbcool_tkprof.c. A PL/SQL package and external proce- dure to enable TKPROF to be executed from a client-side application

dbcool_audit.pl. A Perl script to provide a basic database security audit

dbcool_rman_gen.pl and dbcool_legato_gen.pl. Perl scripts to generate a selec- tion of different backup types for Oracle RMAN backups and Legato Networker for Oracle backups.

dbcool_space.sql and dbcool_space.xls. A PL/SQL package for collecting data- base space growth statistics and an Excel spreadsheet for producing JPEG images charting growth based on the collected statistics

dbcool_utl.sql and dbcool_utl.c. A PL/SQL package and external C code for performing UNIX-style pattern matching in SQL, and presenting database- server file system space information. Full details are available in a related paper on the companion web site.

dbcool_gen_standby.pl. A Perl script to help generate physical and logical standby databases

dbcool_arch_to_standby.pl. A Perl script to copy archived redo logs from a pri- mary server to a standby server

dbcool_ora_healthcheck.pl. A Perl script to check database conformance with layout standards and other configuration issues

dbcool_2pc_pending.pl. A Perl script to notify OEM of a critical severity problem

High-Performance Oracle

Proven Methods for Achieving

Optimum Performance and Availability

Fundamentals of an

Oracle Configuration

One

3 What could be simpler than installing Oracle on UNIX from the installation media? Place the CD-ROM in the mounted drive, follow the instructions in the Installation and User Guide, and you’re done. It’s as simple as that, isn’t it? Long-time Oracle users know that rarely are things that simple. Even before you unpack the CD-ROM, you must read and digest the installation documentation for your platform. If you have an active support contract, check Oracle’s Metalink Web site (metalink.oracle.com) for late-breaking news on installation issues.

As a database administrator (DBA) or developer, you should begin with a set of requirements that will help you build a system to meet those requirements. The fol- lowing are the requirements for your Oracle installation:

■■ First, all Oracle software in your organization should be installed the same way on all machines. That way, DBAs know how their installed environment looks and will feel comfortable with it, no matter what machine they log onto. They will be able to do their job faster and with fewer mistakes, which is especially important during an emergency callout or after a hardware failure that requires a reinstallation of the Oracle software.

■■ Second, you want to install the software right the first time. Then your Oracle software and database instances will be available to your business users in the shortest possible time.

■■ Third, you need to adopt a service-based approach, founded on standards. By defining clearly the role of the DBA group, you can set expectations for the peo- ple you provide the service to.

Installing Oracle

Before any of this happens, though, the Oracle DBA requires the services of the UNIX system administrator (SA) for some basic system configuration to support Ora- cle. To enable this service-based approach, you need to document and publish what services you provide and the services you require from other groups. But if you’re in the DBA group, be prepared to standardize first, and not wait for others.

This chapter covers the following topics: ■■ UNIX configuration for Oracle

■■ Installation via the Network File System (NFS) ■■ How to perform a silent installation

■■ Using Microsoft Systems Management Server for client installation 4 Chapter 1

USING REMOTE COPY COMMANDS, REMOTE SHELLS, AND .RHOSTS

Several examples in this book use UNIX remote copy (rcp) and shell (rsh) commands to copy files between your systems and run remote commands between them. In some cases, this is an Oracle requirement. For example, Oracle Data Guard Broker (covered in Chapter 23) enables you to configure a standby database from a primary database by using the rcp command to copy the primary database files to the standby site. Using these commands requires the configuration of a .rhosts file to authenticate the local machine running the command to the remote server that runs the rsh command (or is the target of an rcp).

To run either an rsh or rcp command against a remote server from a local server, log onto the remote server and create a file named .rhosts in the remote $HOME directory. Insert the following line into this file to enable remote operations from the server named local and account oracle:

local oracle

If you aren’t concerned about security (although you should be!), you can instead add the line:

+ oracle

This allows remote connections onto the remote server from all other servers, provided that the remote account is named oracle. The security concern here is that anyone who can create a local oracle account on any UNIX server on your network, or log onto such an account, can then access the remote server as the Oracle DBA account without providing a password. After you’ve configured authentication in the .rhosts file, the rsh command provides a fast way to check the configuration. For example, to test the connection to a machine named remote, run:

$ rsh remote

TEAM

FLY

The Oracle DBA Group Cluster

Until now, you’ve probably used a local CD-ROM drive to install Oracle software, or requested that an SA remote mount the drive onto your machine. This book proposes that you unload the software from the CD-ROM once, and that the DBA group build a minimum of two UNIX servers to provide redundant Oracle services, including:

■■ Oracle Names address resolution services ■■ Oracle DBA group repository and warehouse ■■ DBA group Web site

■■ Oracle Recovery Manager (RMAN) backup catalog ■■ Oracle software releases

By providing each of the listed services on two different servers, all the services can remain available if a single server is down. Ideally, these two servers should be on dif- ferent sites to provide a disaster recovery (DR) capability as well as redundancy against scheduled site outages. It’s best to dedicate the two servers solely for the pro- vision of Oracle services: If you allow other groups to co-host with you, you run the risk that their downtime requirements might impact your database services. Through- out the rest of this chapter, we’ll refer to this pair of machines as the DBA Cluster, and the two servers in it as ora1.uk.dbcool.com and ora2.uk.dbcool.com. The following sec- tions are based on this configuration.

UNIX System Requirements

This section covers the Oracle standard build for your UNIX servers. It’s based on Sun Solaris, which is the most popular platform used to run commercial Oracle systems. However, the operating-system-specific details are as critical as covering the same areas for your organization’s strategic platform.

You should document your standard configuration and publish it on the DBA group’s Web site on the DBA Cluster so that any UNIX SA knows where to find your standard build information. With this approach, the DBA is not even involved in a sys- tem build. Don’t email the details because over time the requirements might change,

If you connect without being prompted for a password, the rsh and rcp commands are working as required. If you are prompted for a password, you might need to specify the host name in the remote .rhosts file using a fully qualified host name, such as

remote.uk.dbcool.com. Finally, check that the remote .rhosts file is owned by the login account, and that only the login account has write access to the file. If this is not the case, UNIX denies access for security reasons. If a password prompt still appears, consult your SA group. They might have disabled all rsh services for security reasons.

and you will have to resend them. Instead, refer people to the DBA Group Web site for the latest version. Developing this standard build requires a liaison with your SA team. It might be difficult, as DBAs and SAs sometimes regard each other with suspicion, but it will save countless hours in the future.

TIP

Oracle UNIX Account Details

The goal of the following sections is to provide you with sufficient information to cre- ate a standard build document for the UNIX SA group that configures your servers. You should begin by configuring the UNIX account that owns the Oracle software, as shown in Table 1.1.

Usually, you can simply use the default options for choosing an account name and group. The Korn shell (ksh) is chosen because it’s very similar to the Bourne shell (sh) (which Oracle uses to develop its own scripts) with some extra facilities such as a com- mand history. No DBA should be expected to work in a shell without a command his- tory. Standardizing the shell makes it possible to standardize a set of aliases for common commands. If you ever wondered why Oracle doesn’t use the Korn shell, it’s because Oracle was shipping UNIX systems before ksh existed. On some non-Sun systems (such as Linux) where ksh is not available, the Bash shell provides similar capabilities.

The Oracle account UNIX home directory should be set to the value of $ORACLE_ BASE, where $ORACLE_BASE is traditionally set to /u01/app/oracle as defined by the Oracle Optimal Flexible Architecture (OFA) standard. The OFA standard should be followed religiously for all your Oracle software and database layouts. This book assumes that you are familiar with it. It’s well documented at www.oracle.com.

A local password file should be used to authenticate the account rather than a net- work account, and you should consider giving the Oracle account a different password on each server. This means more effort for the DBA because a repository is required to 6 Chapter 1

Table 1.1 Oracle UNIX Account Details

SETTING VALUE

Oracle UNIX account name oracle

Oracle UNIX group dba

Login shell ksh

Oracle configuration files directory /var/opt/oracle or /etc (owner oracle, group dba) Oracle $HOME $ORACLE_BASE (owner oracle, group dba) Authentication local password file

track all the passwords. But if you use a single network logon and someone cracks the password, they have DBA access to all your databases. Your security audit group might want to talk to you about that. But security isn’t necessarily about making life easy for the DBA; it’s about protecting your systems.

Oracle UNIX Memory Requirements

Ensure that at least 400MB are free in /tmp because the Oracle Installer uses temporary space. Ensure that the system has an absolute minimum of 256MB of RAM. Determin- ing your memory requirements is discussed later in the book. Make sure that at least double the amount of physical memory is available in swap space. To avoid excessive paging, which severely degrades performance, you don’t want your memory require- ments to exceed physical available memory by much. Chapter 28 explains how to detect when excessive paging is taking place.

Oracle UNIX Kernel Requirements

Set the following UNIX kernel parameters as below, and ensure that the system is