cover.eps

Oracle® 11g For Dummies®

Table of Contents

Introduction

About This Book

Who Are You?

What’s in This Book

Part I: You Don’t Have to Go to Delphi to Know Oracle

Part II: Implementing Oracle on Your Own

Part III: Caring for an Oracle Database

Part IV: Inspecting Advanced Oracle Technologies

Part V: The Part of Tens

Icons in This Book

Where to Go from Here

Part I: You Don’t Have to Go to Delphi to Know Oracle

Chapter 1: A Pragmatic Introduction to Oracle

Introducing a New Kind of Database Management

Pooling Resources with Grid Computing

Anticipating Technology and Development Trends

Meeting Oracle in the Real World

Making the Oracle Decision

Chapter 2: Understanding Oracle Database Architecture

Defining Databases and Instances

Deconstructing the Oracle Architecture

Walking Down Oracle Memory Structures

Trotting around the System Global Area

Shared pool

Database buffer cache

Redo log buffer

Large pool

Java pool

Program Global Area

Managing Memory

Managing memory automatically

Following the Oracle Processes

Background processes

User and server processes

Getting into Physical Structures

Getting Physical with Files

Data files: Where the data meets the disk

Control files

Redo log files

Moving to the archives

Server and initialization parameter files

Applying Some Logical Structures

Tablespaces

Segments

Extents

Oracle blocks

Chapter 3: Preparing to Implement Oracle in the Real World

Understanding Oracle Database Deployment Methodology

Client-server applications

Muli-tier applications

Component configurations

Checking on the Requirements

User and directory requirements

Hardware requirements

Software requirements

Storage requirements

Other requirements

Part II: Implementing Oracle on Your Own

Chapter 4: Creating an Oracle Database

Feeling at Home in Your Environment

Finding the Oracle software owner

Oracle versions

Getting to home base

ORACLE_BASE

ORACLE_HOME

ORACLE_SID

PATH

Sticking with the Oracle Universal Installer and oraenv

Configuring an Instance

Using PFILE and SPFILES

Setting parameters in the pfile and spfile

Creating Your Oracle Database

Bossing the Database Configuration Assistant (DBCA)

Taking database control

Taking the DBCA steps

Sharing (a) memory

Feeling the Post-Configuration Glow

Chapter 5: Connecting to and Using an Oracle Database

Starting and Stopping the Database

Environmental requirements

Starting the database

Stopping the database

Connecting to the Database Instance

Local versus remote connections

Communication flow

Setting up tnsnames.ora

Configuring the database listener with listener.ora

Starting and stopping the database listener

Testing the connection

Oracle Net Configuration Assistant

Sidestepping Connection Gotchas

Chapter 6: Speaking the SQL Language

Using SQL in Oracle

Sharpening the SQL*Plus Tool

Using SQL Connection Commands

SQL*Plus Profile Scripts glogin.sql and login.sql

SQL*Plus buffer and commands

SQL*Plus commands

Getting Help from SQL*Plus

SQL language elements

Using the Oracle Data Dictionary

Getting a PL/SQL Overview

Blocking PL/SQL

Chapter 7: Populating the Database

Creating Tablespaces

Creating Users and Schemas

Creating Database Objects

Object types

Object creation methods

Part III: Caring for an Oracle Database

Chapter 8: Protecting Your Oracle Database

Assessing Database Threats

Instance failure

Oracle code tree

Dropped objects

Media failure

Corruption

User error

Getting Your Oracle Recovery Manager

Starting RMAN

Configuring RMAN

RMAN catalog

Putting It in the Archives

Turning archiving on and off

Archive logs

Enabling archiving

Enabling the Flash Recovery Area

Backup File Types with RMAN

Backing up with backup sets

Making copies

Maintaining the Archives

Viewing Backup Information

Recovering Your Oracle Database

Verifying the problem

Complete recovery

Incomplete recovery

Recovering your database with copies

Chapter 9: Protecting Your Oracle Data

Authentication

User authentication

Password authentication

Operating system authentication

Granting the Privileged Few

System privileges

Object privileges

Role Playing

Oracle-supplied roles

The SYSDBA role

Virtual Private Database Concept

Auditing Oracle’s Big Brother

Getting ready to audit

Enabling and disabling audits

Auditing system privileges

Auditing objects

Verifying an audit

Viewing audit information

Turning off audits

Encrypting a Database

Chapter 10: Keeping the Database Running

Doing Your Chores

Making way, checking space

Monitoring space in your segments

Growing and shrinking: Tricky tables

Checking users

Audit records

System logs

Automating Jobs with the Oracle Scheduler

Scheduler objects

Creating your first scheduler job

Using Oracle Data Pump

Data Pump Export

Data Pump Import

Creating Oracle Directories

Using Data Pump with a Parameter File

Chapter 11: Tuning an Oracle Database

Evaluating Tuning Problems

Tuning Your Database

Gathering Performance Information with Automatic Workload Repository

SQL*Plus method

Database Control method

Using the Automatic Database Diagnostic Monitor (ADDM)

Improving Queries with SQL Tuning

Explain plan

Active Session History (ASH)

SQL Access Advisor

SQL Tuning Advisor

SQL Profiling and Plan Management

10046 trace event

Chapter 12: Troubleshooting an Oracle Database

Troubleshooting with System Methodology

Identifying the real problem

Performing basic system checks

Performing basic database checks

Analyzing error messages

Developing and applying a solution

Troubleshooting Using Oracle Database Logs

Database log infrastructure

Database alert log

Trace and dump files

Listener log

Benefiting from Other Diagnostic Utilities

Oracle Enterprise Manager

Remote Diagnostic Agent

Diagnostic database scripts

Chapter 13: Monitoring and Managing with Enterprise Manager

Tasting Oracle Enterprise Manager Flavors

OEM Java Console

OEM Database Control

OEM Grid Control

Configuring Enterprise Manager with the DBCA

Creating and Managing Database Control Users

Working with Metrics and Policies

Setting Up Notifications

Setting Up User Notifications

Navigating Database Control’s Main Page

Inspecting the Database Control main page

Accessing other targets

Part IV: Inspecting Advanced Oracle Technologies

Chapter 14: Flashing Back and Replaying: Advanced Features

Rolling Back with Flashback Database

Configuring and enabling a flash back

Using restore points

Flashing back your database

Using Flashback Data Archive

Oracle Database Replay

Using database replay

Replaying the workload

Chapter 15: Using High-Availability Options

Gathering Real Application Clusters

Exploring RAC Architecture

Hardware considerations for RAC

Software considerations for RAC

Preparing for a RAC Install

Tools for managing a RAC installation

Oracle RAC application for high availability

Defending Oracle Data Guard

Data Guard architecture

Physical standby database

Logical standby database

Performing switchover and failover operations

Part V: The Part of Tens

Chapter 16: Top Ten Oracle Installation Do’s

Read the Documentation

Observe the Optimal Flexible Architecture

Configure Your Profile

Running the Wrong Bit

Set umask

Become Oracle

Stage It

Patch It

Mind the User and Group IDs

Back It Up

Chapter 17: Top Ten Database Design Do’s

Using Oracle’s Built-In Constraints

Spreading Out Your IO

Knowing Data Normalization

Using Naming Conventions

Setting Up Roles and Privileges Properly

Keeping Ad-Hoc Queries to a Minimum

Enforcing Password Security

Limiting the Number of DBAs

Storing Code in the Database

Testing Your Recovery Strategy

Appendix A: Quick Start Install of Oracle 11g on Linux

Checking your operating system version

Checking your kernel version

Checking your OS packages

Creating Linux operating system groups and users

Creating the ORACLE_BASE directory

Configuring the Oracle user’s environment

Installing the Oracle 11g database software

End User License Agreement

Oracle® 11g For Dummies®

by Chris Zeis, Chris Ruel, and Michael Wessler

WileyTitlePageLogo.eps

About the Authors

Michael Wessler received his bachelor’s degree in computer technology from Purdue University in West Lafayette, Indiana. He is an Oracle Certified Database Administrator for Oracle 8 and 8i, an Oracle Certified Web Administrator for 9iAS, and a 10g Database Technician. Michael also holds a CISSP security certification. He has administered Oracle databases on NT and various flavors of UNIX and Linux, including clustered Oracle Parallel Server (OPS) environments. He also performs database and SQL/PLSQL tuning for applications. Michael has worked in many IT shops ranging from small dot-com start-ups to large government agencies and corporations. Currently, Michael is a technical manager at Perpetual Technologies Inc., consulting for the U.S. government.

In addition to Oracle DBA consulting, Michael has worked extensively as an Oracle 10gAS Web Application Server Administrator. He manages multiple web applications for the Department of Defense and consults at various government agencies and in the private sector. Michael also frequently lectures on 10gAS and teaches Oracle Performance Tuning classes. Michael is the author of Oracle DBA on UNIX and Linux and coauthor of Oracle Application Server 10g: J2EE Deployment and Administration, Oracle Unleashed, Second Edition, UNIX Primer Plus, Third Edition, COBOL Unleashed, UNIX Unleashed, Fourth Edition, and High Availablity: SuccessfulImplementation for the Data-Driven Enterprise.

Chris Ruel lives in Indianapolis, Indiana. He graduated from Wabash College in 1997 and has been working with Oracle ever since. Currently, he is a consultant for Perpetual Technologies, Inc., a technology consulting firm with a focus on database management in the federal and commercial sectors. His clients range from Fortune 500 companies to Department of Defense contracts. He also serves as the vice president of the Indiana Oracle User’s Group and speaks at many local events (Oracle Tech Days) hosted by his company. He served as an Oracle University Instructor from 2000–2004, traveling the country teaching Oracle’s DBA curriculum. Chris is certified in Oracle 8i–10g. He recently received his 10g RAC Certified Expert qualification and is studying for his 11g OCP upgrade exam.

When not working on Oracle, Chris enjoys racing and restoring radio control cars. He is also a big fan of Formula 1 auto racing, grilling out with his friends, and watching the Indianapolis Colts play football.

Chris Zeis is the Chief Technology Officer and a partner at Perpetual Technologies, Inc. in Indianapolis, Indiana. Chris is the author or coauthor of four books on database technologies.

Dedication

Michael Wessler: For my Mom, Barb Wessler. A truly unique woman and loving mother; you’re the best! Love always, Mike.

Chris Ruel: I dedicate this book to my parents who raised me well. If it wasn’t for them, I don’t know what I would be doing today. Well, my dad always said that I would be digging ditches when he scolded me for not applying myself.

Authors' Acknowledgments

Michael Wessler: I would like to thank all my family and friends for being so understanding of the time and commitment it takes when writing a book. It takes countless hours to write one of these and it’s those closest to the authors who lose out during that time. In particular, I’d like to thank Angla Imel for understanding “Sorry, I have to write this weekend.” I’d also like to thank my fellow authors, Chris Zeis and Chris Ruel, for bringing me into this exciting project. Finally, I’d like to thank Tony and Sue Amico not just for getting me into technical writing, but for being such wonderful family friends.

Chris Ruel: I appreciate the opportunity to be able to cooperate with the other authors of this book, Chris and Mike. A special thanks to the Wiley team (Kyle, Kelly, Tonya, and the mysterious “DB”) for putting up with a newbie author who had a lot of questions and a writing style that had to be wrangled into submission.

Lastly, I want thank my lovely girlfriend, Angie, who put up with lots of lost weekends. Also, I apologize to my grilling buddies who wondered where I was half of the summer weekends!

Chris Zeis: I would like to personally thank my cowriters, Chris and Mike, for their support. I would also like to acknowledge the people and resources that helped me through this: my business partners Ron and Ryan at Perpetual Technologies, Inc. (PTI), my good friends and leaders at the Defense Finance and Accounting Serivces, The National Guard of Indiana PFO team (CW4 Ferguson and crew), the great folks at Wiley Publishing, and my Limey.

Publisher’s Acknowledgments

We’re proud of this book; please send us your comments through our online registration form located at www.dummies.com/register/.

Some of the people who helped bring this book to market include the following:

Acquisitions, Editorial, and Media Development

Project Editors: Tonya Maddox Cupp, Kelly Ewing

Acquisitions Editor: Kyle Looper

Copy Editor: Rebecca Whitney

Technical Editor: Damir Bersinic

Editorial Manager: Jodi Jensen

Media Development Assistant Producers: Angela Denny, Josh Frank, Shawn Patrick, Kit Malone

Editorial Assistant: Amanda Foxworth

Sr. Editorial Assistant: Cherie Case

Cartoons: Rich Tennant (www.the5thwave.com)

Cover Photo: ©Shutterstock/djgis

Composition Services

Project Coordinator: Patrick Redmond

Layout and Graphics: Shawn Frazier, Christine Williams

Proofreaders: Laura Albert, John Greenough, Christine Sabooni

Indexer: Christine Spina Karpeles

Introduction

If you are reading this text, chances are you’re considering throwing yourself into the wonderful world of Oracle database administration. The good news is, you have come to right place. The bad news? Well, it’s not really bad news . . . but you have quite an adventure ahead of you. Luckily, the authors of this book, your guides in this adventure, can help smooth out any bumpy roads. With over 35 years of combined Oracle experience, we hope to make understanding the Oracle database a fun, enlightening experience.

Oracle is large company with a diverse portfolio of software. It’s constantly growing too. It seems like every other week Oracle releases some slick new product or acquires another company. Don’t let the overwhelming nature of the big picture discourage you. This book imparts a fundamental knowledge of the basics of database administration. An Oracle career is a constant learning process. Establishing a solid understanding of the building blocks behind the database engine will vault you into a successful Oracle career.

You might be interested to know that the rock behind all of Oracle’s products is almost always the database. Take comfort in knowing that in the database world, Oracle is the best. Learning the database is the first step to opening an awful lot of doors for you. Starting at this level is key. After reading this book, you will be well on your way to an interesting career filled with challenges and plenty of opportunity.

There will always be a need for managing information. Every year we see companies grow and accumulate data at a staggering rate. Databases are not a passing fad like some other areas of information technology. The concept of a relational database has been in circulation for almost 40 years and won’t be going away anytime soon.

About This Book

Despite this book being titled Oracle 11g For Dummies, we focus on the tenets of Oracle database administration. Not only do we cover many of the features released with the 11g version of the database, but also cover the fundamental building blocks. Many of these concepts and techniques apply to past versions of the Oracle and almost certainly future releases.

Tip.eps Sometimes in the book we refer to directories and file locations on both Linux/UNIX and Windows. Essentially the two can be interchanged with a couple of things in mind. For example, here is an ORACLE_BASE value that you might come across on Linux/UNIX:

$ORACLE_BASE: /u01/app/oracle

In Windows, /u01 is much like a drive letter. They call it a mount point in Linux/UNIX.

Also, variables in Linux/UNIX are frequently prefixed with a dollar sign. Furthermore, the slashes are in opposite directions for each operating system. On Linux/UNIX you call / a forward slash. In Windows, you use a back slash \. Lastly, Windows encapsulates the variable in percent signs. The same previous setting might look like this in Windows:

%ORACLE_BASE%: C:\app\oracle

We try to give examples of both environments throughout this book.

Who Are You?

People who find themselves needing or wanting a skill set for Oracle databases come from all backgrounds. You might be an application developer, a system administrator, or even a complete newbie. Many of the folks that we come across in this industry became a database administrator (DBA) by accident. One day, your company finds itself without a DBA, and the next thing you know, that’s you! One trick is to be ready. Above all else, learn on your own and think rather than just react.

What’s in This Book

Oracle 11g has six different parts with six different major topics.

Part I: You Don’t Have to Go to Delphi to Know Oracle

Part I helps prepare you for implementation by discussing why you’d choose Oracle in the first place, what’s included in the architecture and how the elements work together, and what you need to do before starting Oracle database creation.

Part II: Implementing Oracle on Your Own

Part II gets you into the nitty gritty of Oracle database creation by discussing the tools and actual steps you’ll take. The Database Creation Assistant (DBCA) is detailed here, as well as the SQL language. You can use either tool; automatic or manual setup is your choice. Finally, you read what to populate your Oracle database with.

Part III: Caring for an Oracle Database

You can’t just create and populate an Oracle database: You have to protect it. Part III has the tools and tips you need to secure both the database and the data within. The less glamorous but no less crucial maintenance chores are detailed in this part, along with basic troubleshooting, should you need to do some. Enterprise Manager makes a star appearance here as well; keeping an eye on your database can keep you from having to troubleshoot in the long run.

Part IV: Inspecting Advanced Oracle Technologies

Part IV reveals some rare Oracle goods, including the flashback database, flashback data archive, and database replay. Rolling back for data recovery is detailed here, and high-availability options Real Application Clusters (RAC) and Data Guard are explained as well.

Part V: The Part of Tens

Avoid installation mistakes by reading Part V. Ten simple things to avoid (and another 10 to make sure you do) add up to 20 problem solvers. Head off trouble before it starts.

Icons in This Book

You see these icons throughout this book. They’re a heads-up for different situations.

Warningbomb.eps Warnings, if not heeded, will cause you to lose data. And maybe your job.

Remember.eps Remembering these bits of information can help you in the long run. And even the short run. Even on a brief walk.

Tip.eps Tips can save you time or energy or manpower or resources. We realize all these items are in short supply.

TechnicalStuff.eps Technical Stuff icons indicate things we think are interesting and want to share with you, but can be skipped if you’d rather get straight to the nitty-gritty.

Where to Go from Here

Jump on in! Keep an open mind and try not to get overwhelmed. Like any skilled profession, it isn’t always easy but you can do it and we think you’ll find it rewarding. This book is written so you can avoid the “too-much- information” reaction. Look at each section as a piece of a big puzzle, and you will soon see how everything starts to take shape.

Part I

You Don’t Have to Go to Delphi to Know Oracle

277652-pp0101.eps

In this part . . .

Need to create a database? Considering Oracle? Already administering an Oracle database? Chapter 1 helps you with the first two by touting Oracle’s advantages. Chapter 2 explains how Oracle database architecture works and Chapter 3 prepares you for actually implementing the Oracle database.

Chapter 1

A Pragmatic Introduction to Oracle

In This Chapter

Getting familiar with Oracle

Implementing grid computing

Incorporating Oracle into everyday life

Oracle 11g is by far the most robust database software on the market today. It’s also the leading database software used and sold all over the world. It has become an enterprise architecture standard for managing data, regardless of the data’s size or complexity.

This chapter highlights the reasons to use Oracle 11g.

Introducing a New Kind of Database Management

Oracle is software that efficiently organizes data in a relational manner. Before Oracle, other database software ran on mainframes and used a hierarchical data model where data is stored in a tree-like structure as flat files — those crazy COBOL programmers!

The relational model is a concept where data is logically stored. These design elements are in the form of tables. Tables have columns, and the columns have attributes (character or number, for example). The tables are organized to store specific data. The tables relate to one another through primary keys.

For more clarity, Oracle, the company, was founded on the database software that transformed the industry into what it is today. Oracle, the company, owns many software products and applications that it has written or acquired, but the database software is still Oracle’s core product.

Tip.eps This book focuses more on database administration rather than Oracle applications administration.

technicalstuff.eps

Pooling Resources with Grid Computing

Grid computing offers a pool of distributed resources for computing services. It’s simply described as computing as a service, similar to a utility-type model.

Oracle supports grid computing with its Real Application Clusters (RAC) capability and its Oracle Enterprise Manager (OEM):

RAC uses Oracle’s clustering software to manage a highly available environment. If you need additional hardware resources (such as memory or CPU), or experience hardware failure, you simply add another node (server) to the grid. (Truthfully, it’s more complicated than that, but you get the point.)

EM manages the databases and hosts, which are also called targets. It has a web interface that gives you a comprehensive view of each target’s state. It handles all the monitoring requirements and provides other web-based tools to interact or perform maintenance with.

Together, RAC and EM make up the components to support true grid computing. RAC is a complex architecture that requires a fair amount of systems and database administrator knowledge, which is unfortunately beyond the scope of this book.

Tip.eps Chapter 13 covers the capabilities and configuration for EM and its lighter single database version, DB Console. You can find additional information about Oracle RAC at www.oracle.com/database/rac_home.html.

Anticipating Technology and Development Trends

Oracle’s success is partially due to anticipating, adapting, and establishing database technology trends. You can choose from numerous designing tools and Integrated Development Environment (IDE) technologies, such as Service Oriented Architecture (SOA), Java, and Extensible Markup Language (XML).

These technologies are portable, which reduces hardware or software dependencies and suits standard business-to-business (B2B) processing and communication:

SOA is a style of IT architecture that utilizes a build-once/deploy-many concept. Its root definition includes webcentric services that work together to sustain business processes. SOA separates the application function from the underlying software and hardware to allow better use (or reuse) of application processing. These functions or service units are written to be flexible by design and capable of service-to-service communication.

SOA concepts eliminate hard coding and stove piping of applications for better use with other applications. Generally, SOA is engineered for large enterprise architectures that require a scalable, cost-effective approach to application development and maintenance.

Java is a free programming language that standardizes applications across hardware platforms. This write-once/run-anywhere programming language supports object-oriented programming (oop) methodologies. Java is widely used for enterprise-level applications on the web and is very popular because it can run on any operating system without much tweaking. Oracle supported Java shortly after its creation.

XML is an all-purpose language that helps share data across systems via the Internet. It standardizes the programming methods or calls, which allow for B2B communication. XML supports the SOA framework as well.

Meeting Oracle in the Real World

The Oracle 11g database can support any requirement you have for using and storing data. From financial institutions, such as banks, to human resources or manufacturing applications, Oracle can handle it. Its strengths lie in its vast number of software components and its ability to recover to any point in time.

General Oracle use supports a variety of applications that are labeled by type. The following list outlines the majority of database types:

Online Transactional Processing (OLTP): Used for transaction-oriented applications where the response is immediate and records are modified or inserted regularly.

Decision Support System (DSS): Used for processing data and making judgments on data for making decisions. A DSS database usually involves many ad hoc queries.

Online Analytical Processing (OLAP): Used for analyzing data. Typically, OLAP is used for business intelligence or data mining, such as budgeting or forecasting.

Hybrid: Acts as a multifunctional database. Most hybrid databases contain transactional, processing ad hoc querying, and batch processing. Larger databases that have service-level requirements are generally isolated to their own databases for performance and manageability reasons.

Uses for Oracle center around data and information. Industries leaders are particularly interested in information. Have you heard the motto “Information Drives Performance”? That motto basically suggests that the performance of a company is relative to the information it has and uses. This information assists in making more competitive and educated decisions.

A good example of this process is how Amazon and eBay use their information. They track user interaction on their Web sites to help define a user’s shopping tendencies and interests. They then make programmatic recommendations based on that information to promote purchases, which in turn creates revenue. Information usage in this manner is known as Business Intelligence (BI) and is a common practice among many businesses today. Instead of saying, “Build it and they will come,” Oracle can say, “Get their information and build them something they can’t refuse.”

Making the Oracle Decision

The decision to use Oracle over other technologies or database software can be a difficult one. Several things can influence your decision:

Cost

Available expertise

Project scope

Scale

Most of our clients decided to use Oracle based on available expertise because pricing is fairly competitive across database companies. In one case, Microsoft SQL Server was almost chosen because the developers had ASP/VB.NET experience. If the developers were Java eccentric, the database software would have never been discussed. Management, however, realized that it could use the pre-existing Oracle database infrastructure and still develop with Microsoft products.

Microsoft Access and even Microsoft Excel have their place, but if you want functionality, scalability, recoverability, and security, Oracle is the best choice. Linux gurus also use MySQL or PostgreSQL. Both are free for public use. The difficulty in using or managing MySQL or PostgreSQL is finding qualified expertise. You also need to consider the software support capability of the product. Oracle support provides a deep, mature group and a knowledge base for issues, such as bugs or general guidance.

In comparison to other database software products, Oracle has a similar level of complexity in installing, configuring, and maintaining it. Senior expert-level professionals are sometimes necessary for particular issues, but most novices to Oracle can achieve success without much training or guidance. We’ve trained many DBAs in our day, and they all had very little knowledge of Oracle but were eager to get their hands dirty. A good understanding of information technology and computers in general definitely helps with the learning curve.

Remember.eps Oracle runs on all the common and latest operating system versions of Linux, UNIX, Microsoft Windows, Mainframes, and Mac. It provides the same functionality and utilities regardless of the operating system or hardware. It also supports 64-bit architecture to add additional memory space for large applications. You can purchase licensing per CPU or per named user.

Tip.eps Additionally, Oracle provides lower-cost licensing for its standard editions. Oracle licensing information is available at www.oracle.com/corporate/pricing/technology-price-list.pdf.