Pro Oracle Database 11g Administration (8 page)

■ INTRODUCTION

Chapters 6, 7, 8, 9, and 10
discuss configuring users and database objects such as tables, constraints, indexes, views, synonyms, sequences, and so forth.

Chapters 11 and 12
detail how to create and maintain large database objects and partitioned tables and indexes.

Chapters 13, 14, and 15
show how DBAs use tools such as Data Pump, external tables, and materialized views to mange and distribute large amounts of data.

Chapters 16, 17, 18, 19, and 20
take a deep dive into backup and recovery concepts. Both user managed backups and RMAN backup and recovery are discussed in detail.

Chapters 21 and 22
focus on techniques used to automate database jobs and how to troubleshoot typical problems that DBAs encounter.

Conventions Used in This Book

The following typographical conventions are used in this book:

• $ is used to denote Linux/Unix commands that can be run by the operating system owner of the Oracle binaries (usually named oracle).

• # is used to denote Linux/Unix commands that should be run as the root operating system user.

• SQL> is used to denote one line SQL*Plus statements.

• Monospaced font is used for code examples, utility names, file names, URLs, and directory paths.

•
Italic
is used to highlight a new concept or word.

• UPPERCASE indicates names of database objects like views, tables, and corresponding column names.

• < > is used where you need to provide input, such as a filename or password.

Comments

I've tried to keep this book as error free as possible. However, mistakes happen or inadvertently get overlooked. If you find any type of error in this book, whether it be a typo or an erroneous command, please let me now about it. You can submit any issues by going to the main Apress web page at: http://www.apress.com. Search for this book and then use the errata page to submit corrections.

Contacting the Author

If you have any questions regarding the book, feel free to contact me directly at the following email address: [email protected].

xxxiii

CHAPTER 1 ■ INSTALLING THE ORACLE BINARIES

Oracle Inventory Directory

The Oracle inventory directory stores the inventory of Oracle software installed on the server. This directory is required and is shared among all installations of Oracle software on a server. When you first install Oracle, the installer checks to see whether there is an existing OFA-compliant directory structure in the format of /u[01–09]/app. If such a directory exists, then the installer creates an Oracle inventory directory such as

/u01/app/oraInventory

If you have the ORACLE_BASE variable defined for the oracle operating system user, then the installer creates a directory as follows for the location of Oracle inventory:

ORACLE_BASE/../oraInventory

For example, if ORACLE_BASE is defined to be /ora01/app/oracle, then the installer defines the location of Oracle inventory to be

/ora01/app/oraInventory

If the installer doesn’t find a recognizable OFA-compliant directory structure or an ORACLE_BASE

variable, then the location of Oracle inventory is created under the HOME directory of the oracle user. For example, if the HOME directory is /home/oracle, then the location of Oracle inventory is

/home/oracle/oraInventory

Oracle Base Directory

The Oracle base directory is the topmost directory for Oracle software installations. You can install one or more versions of the Oracle software beneath this directory. The OFA standard for the Oracle base directory is as follows:

//app/

The mount point used is typically named something like /u01, /ora01, /oracle, or /oracle01. You can name this mount point according to whatever your standard is for your environment. I prefer to use a mount point name like /ora01. It’s short and, when I look at the mount points on a database server, I can immediately tell which mount points are used for the Oracle database. Also, a short mount-point name is easier to use when you’re querying the data dictionary to report on the physical aspects of your database. Additionally, a shorter mount-point name makes for less typing when you’re navigating through directories via operating system commands.

The software owner is typically named oracle. This is the operating system user that you use to install the Oracle software (binaries). A fully formed Oracle base directory path is something like

/ora01/app/oracle

Oracle Home Directory

The Oracle home directory defines the installation location of software for a particular product such as Oracle Database 11
g
, Oracle Database 10
g
, and so on. You must install different products or different releases of a product in separate Oracle homes. The recommended OFA-compliant Oracle home directory is as follows:

ORACLE_BASE/product//

3

CHAPTER 1 ■ INSTALLING THE ORACLE BINARIES

In the previous line of code, the version is something like 11.2.0.1 or 10.2.0.1. The install_name value is something like db_1, devdb1, test2, or prod1. Here is an example Oracle home name for an 11.2

database:

/ora01/app/oracle/product/11.2.0.1/db_1

■
Note
Some DBAs dislike the db_1 string on the end of the ORACLE_HOME directory and see no need for it. The reason for the db_1 is that you may have two separate installations of binaries: a development installation and a test installation. If you don’t require that configuration in your environment, feel free to drop the extra string on the end.

Oracle Network Files Directory

Some Oracle utilities use the value TNS_ADMIN to locate network configuration files. This directory is defined to be ORACLE_HOME/network/admin. This directory typically contains the tnsnames.ora and listener.ora Oracle Net files.

Automatic Diagnostic Repository

Starting with Oracle Database 11
g
, the ADR_HOME directory specifies the location of the diagnostic files related to Oracle. These files are crucial to troubleshooting problems with the Oracle database. This directory is defined to be ORACLE_BASE/diag/rdbms/dbname/instname, where dbname is your database name and instname is your instance name. In single-instance environments, the database name and instance name are the same, with the exception that the database name is in lowercase and the instance name is in uppercase. For example, in the next line, the database name is specified by o11r2, whereas the instance name is specified as O11R2:

/ora01/app/oracle/diag/rdbms/o11r2/O11R2

Now that you understand the OFA standard, you see how it’s used when installing the Oracle binaries. For example, you need to specify directory values for the ORACLE_BASE and ORACLE_HOME

directories when running the Oracle installer.

Installing Oracle

Suppose you’re new on the job, and your manager asks you how long it will take to install a new set of Oracle Database 11
g
binaries on a server. You reply that it will take less than an hour. Your boss is incredulous and states that previous DBAs always estimated at least a day to install the Oracle binaries (software) on a new server. You reply, “Actually, it’s not that complicated, but DBAs do tend to overestimate installations because it’s hard to predict everything that might go wrong.”

When you’re handed a new server and are given the task of installing the Oracle binaries, this usually refers to the process of downloading and installing the software required before you can create an Oracle database. This process involves several steps:

Create the operating system dba group and the operating system oracle user.

4

CHAPTER 1 ■ INSTALLING THE ORACLE BINARIES

Ensure that the operating system is configured adequately for an Oracle database.

Obtain the database installation software from Oracle.

Unzip the database installation software.

Configure the response file, and run the Oracle silent installer.

Troubleshoot any issues.

These steps are detailed in the following subsections.

■
Note
Any version of the database that Oracle designates as a base release (10.1.0.2, 10.2.0.1, 11.1.0.6, and 11.2.0.1) can be freely downloaded from Oracle’s technology network web site (http://otn.oracle.com).

However, be aware that any subsequent patch downloads require a purchased license. In other words, downloading base software requires an Oracle Technology Network (OTN) logon (free), whereas downloading a patchset requires a My Oracle Support account (paid support account).

Step 1. Create the Operating System Group and User

If you work in a shop with a good system administrator (SA), then steps 1 and 2 usually are performed by the SA. If you don’t have an SA, then you have to perform these steps yourself (this is often the case in small shops where you’re required to perform many different job functions). You need root access to accomplish these steps.

As root, use the groupadd command to add operating system groups. In Linux/Unix environments, Oracle recommends that you create three operating system groups: oinstall, dba, and oper. The oinstall group has privileges to manipulate the installation files and perform installations and upgrades. Operating system users assigned to the dba group can connect to the database with sysdba database privileges. Operating system users assigned to the oper group can connect to the database with sysoper database privileges.

The idea behind the three separate groups is to be able to allow separate operating system users to perform various database tasks. For example, you may have one user who typically installs the Oracle software and is assigned the oinstall group, and other users who require sysdba or sysoper privileges who are assigned the dba and oper groups, respectively.

Having said that, many shops create just one operating system group, dba, and use that group to install software and perform all sysdba and sysoper functions. I typically only use one group (dba).

If you have root access, you can run the groupadd command as shown here: