Oracle Essentials Oracle Database 11g (13 page)

V$SGA, V$SGASTAT, V$SGAINFO, V$SGA_DYNAMIC_COMPONENTS, V$SGA_DYNAMIC_FREE_

MEMORY, V$SGA_RESIZE_OPS, V$SGA_RESIZE_CURRENT_OPS, V$MEMORY_TARGET_

ADVICE, V$SGA_TARGET_ADVICE, V$PGA_TARGET_ADVICE

Work area memory

V$PGASTAT, V$SYSSTAT

Processes

V$PROCESS, V$BGPROCESS, V$SESSION

Alerting

DBA_THRESHOLDS, DBA_OUTSTANDING_ALERTS, DBA_ALERT_HISTORY, V$ALERT_TYPES, V$METRIC

Performance monitoring

V$LOCK, DBA_LOCK, V$SESSION_WAIT, V$SQLAREA, V$LATCH

RMAN recovery

V$RECOVER_FILE

User passwords

V$PWFILE_USERS

Tables

DBA_TABLES, ALL_TABLES, USER_TABLES

Indexes

DBA_INDEXES, ALL_INDEXES, USER_INDEXES

Data dictionary

DBA_OBJECTS, ALL_OBJECTS, USER_OBJECTS

The Data Dictionary

|

55

Chapter 3

CHAPTER 3

Installing and Running Oracle

3

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, youcan 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 discuss how users access databases and begin a discussion of how to manage databases—a topic that will be continued in subsequent chapters.

Installing Oracle

Prior to Oracle8
i
, 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. Since Oracle8
i
, the installer has been Java-based.

The Oracle installer is one of the first places in which youcan see the benefits of the portability of Java; the installer looks and functions the same way across all operating systems. For some time now, installing Oracle has been quite simple, requiring only a few mouse clicks and answers to some questions about options and features.

Oracle made great strides in further simplifying installation with Oracle Database 10
g
. Both that install and the installation of Oracle Database 11
g
can be accomplished in less than 20 minutes.
Figure 3-1
shows a version of the launch screen of the installer for Oracle Database 10
g
.

The current version of the Oracle Universal Installer begins the process by checking the target environment to make sure there are enough resources for the Oracle database. If the target is a bit light, youwill be informed with a warning and given the option to continue.

56

Figure 3-1. Oracle Universal Installer

As part of the installation process, the Installer also runs the Net Configuration Assistant and the Database Configuration Assistant so that you will end up with a working Oracle instance when the process is complete.

If, for some reason, the installation fails, the commands that did not succeed are listed in a log file, which helps youunderstand where the problem may lie and gives you a handy set of commands you can run yourself once the problem is fixed.

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 its own set 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. Youwill 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 media, and this provides additional information regarding the database and related products.

Installing Oracle

|

57

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.

One of the more important decisions youneeded to make before actually installing Oracle in older releases concerned the directory structure and naming conventions you would follow for the files that make up a database. Clear, consistent, and well-planned conventions were crucial for minimizing human errors in system and database administration. Today, this naming is largely automated during the installation process.

Some of the more important database naming that takes place includes the following:

• Disk or mount point names

• Directory structures for Oracle software and database files

• Database filenames: control files, database files, and redo log files The Optimal Flexible Architecture (OFA), described in the next section, became the basis for naming conventions for all of these files.

Optimal Flexible Architecture

Oracle consultants working at large Oracle sites created (out of necessity) a comprehensive set of standards for database directory structures and filenames prior to Oracle’s introduction of more automated installation procedures. This set of standards is called
An Optimal Flexible Architecture for a Growing Oracle Database
or, as it is lovingly known in the Oracle community, the OFA. For example, the OFA provides a clear set of standards for handling multiple databases and multiple versions of Oracle if deployed on the same machine. It includes recommendations for mount points, directory structures, filenames, and scripting techniques. Anyone who knows the OFA can navigate an Oracle environment to quickly find the software and files used for the database and the instance. This standardization increased productivity and avoided errors.

Since Oracle7 releases, the OFA standards are embedded in the Oracle installer. System administrators and database administrators working with Oracle will find understanding the OFA worthwhile, even if your Oracle system is already installed.

OFA documentation is included in the Oracle installation guide.

Supporting Multiple Oracle Versions on a Machine

You can install and run multiple versions of Oracle on a single-server machine. All Oracle products use a directory referred to by the environment or system variable ORACLE_HOME to find the base directory for the software they will use. Because of
58

|

Chapter 3: Installing and Running Oracle

this, you can run multiple versions of Oracle software on the same server, each with a different ORACLE_HOME variable defined. Whenever a piece of software accesses a particular version of Oracle, the software simply uses the proper setting for the ORACLE_HOME environment variable.

Oracle supports multiple ORACLE_HOME variables on Unix and Windows systems by using different directories. The OFA provides clear and excellent standards for this type of implementation.

Upgrading an Oracle Database

Oracle Database 10
g
added two additional features that apply to upgrading an existing Oracle database: the Database Upgrade Assistant and support for rolling upgrades.

If youwant to upgrade a single instance, youcan use the Database Upgrade Assistant, which can be started from the Oracle Universal Installer. As of Oracle Database 11
g
, youcan upgrade from the free version of Oracle, Oracle XE, to a single instance with the Database Upgrade Assistant.

One of the longstanding problems with upgrades has been the requirement to bring down the database, upgrade the database software, and then restart the database.

This necessary downtime can impinge on your operational requirements. If you are using a Real Application Clusters implementation since Oracle Database 10
g
, you can perform a
rolling upgrade
. A rolling upgrade allows you to bring down some of the nodes of the cluster, upgrade their software, and then bring them back online as part of the cluster. You can then repeat this procedure with the other nodes. The end result is that you can achieve a complete upgrade of your Oracle database software without having to bring down the database.

Creating a Database

As we noted in
Chapter 2,
Oracle might be installed for a variety of workloads. 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.

Planning the Database

As with installing the Oracle software, you should spend some time learning the purpose of an Oracle database before youcreate 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.

Creating a Database

|

59

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?

What are the access patterns for the various database objects?

Some objects will be more popular than others. Understanding the volume and type of activity in the database is critical to planning and tuning your database.

Some people employ a so-called
CRUD matrix
that contains Create, Read, Update, and Delete indicators, or even estimates for how many operations will be performed for each key object used by a business transaction. These estimates may be per minute, per hour, per day, or for whatever time period makes sense in the context of your system. For example, the CRUD matrix for a simple employee update transaction might be as shown in
Table 3-1,
with the check-marks indicating that each transaction performs the operation against the object shown.

Table 3-1. Access patterns for database objects

Object

Create

Read

Update

Delete

EMP

DEPT

SALARY

How much hardware do I have now, and how much will I add as the database grows?

Disk drives tend to get cheaper and cheaper. Suppose you’re planning a database of 100 GB that youexpect to grow to 300 GB over the next two years. You may have all the disk space available to plan for the 300 GB target, but it’s more likely that you’ll buy a smaller amount to get started and add disks as the database grows. It’s important that youplan the initial layout with the expected growth in mind.

Prior to Oracle9
i
, running out of tablespace in the middle of a batch operation meant that the entire operation had to be rolled back. Oracle9
i
introduced the
60

|

Chapter 3: Installing and Running Oracle

concept of
resumable space allocation
. When an operation encounters an out-of-space condition, if the resumable statement option has been enabled for the session, the operation is suspended for a specific length of time, which allows the operator to correct the out-of-space condition. You even have the option to create an AFTER SUSPEND trigger to fire when an operation has been suspended.

With Automatic Storage Management (ASM), introduced in Oracle Database 10
g
, you can add additional disk space or take away disks without interrupting database service. Although you should still carefully estimate storage requirements, the penalty for an incorrect judgment, in terms of database downtime, is significantly reduced with ASM.