Oracle Database 11g A Beginner's Guide | Beginner's Guide

Oracle Database 11g A Beginner's Guide

Authors: Ian Abramson, Michael Abbey and Michael Corey

Published: December 2008

eISBN: 9780071604604 007160460X | ISBN: 9780071604598

Contents

Acknowledgments

Introduction

1 Database Fundamentals

Critical Skill 1.1 Define a Database

Critical Skill 1.2 Learn the Oracle Database 11g Architecture

The Control Files

The Online Redo Logs

The System Tablespace

The Sysaux Tablespace

Default Temporary Tablespace

Undo Tablespace

The Server Parameter File

Background Processes

Project 1-1 Review the Oracle Database 11g Architecture

The Database Administrator

Critical Skill 1.3 Learn the Basic Oracle Database 11g Data Types

varchar2

number

date

timestamp

clob

blob

Critical Skill 1.4 Work with Tables

Tables Related to part_master

Critical Skill 1.5 Work with Stored Programmed Objects

Views

Triggers

Procedures

Functions

Packages

Critical Skill 1.6 Become Familiar with Other Important Items in Oracle Database 11g

Indexes

Users

Tablespace Quotas

Synonyms

Roles

Default User Environments

Critical Skill 1.7 Work with Object and System Privileges

Select

Insert

Update

Delete

System Privileges

Critical Skill 1.8 Introduce Yourself to the Grid

Critical Skill 1.9 Tie It All Together

Chapter 1 Mastery Check

2 Installing Oracle

Critical Skill 2.1 Research and Plan the Installation

Define System Requirements

Linux Installation

Critical Skill 2.2 Set Up the Operating System

Project 2-1 Configure Kernel Parameters

Critical Skill 2.3 Get Familiar with Linux

Critical Skill 2.4 Choose Components to Install

Critical Skill 2.5 Install the Oracle Software

Database Configuration Assistant

Verify the Installation

Chapter 2 Mastery Check

3 Connecting to Oracle

Critical Skill 3.1 Use Oracle Net Services

Network Protocols

Optimize Network Bandwidth

Connections

Maintain Connections

Define a Location

Critical Skill 3.2 Learn the Difference Between Dedicated and Shared Server Architectures

Dedicated Server

Shared Server

Set Dispatchers

Views to Monitor the Shared Server

Critical Skill 3.3 Define Connections

A Connect Descriptor

Define a Connect Descriptor

The Oracle Connection Manager

Session Multiplexing

Firewall Access Control

Critical Skill 3.4 Use the Oracle Net Listener

Password Authentication

Multiple Listeners

Connection Pooling

Critical Skill 3.5 Learn Naming Methods

Directory Naming Method

Directory Information Trees

Distinguished Names

How to Find the Directory Naming Information

Net Service Alias Entries

The Local Naming Method

The Easy Naming Method

The External Naming Method

Which Naming Method to Use

Critical Skill 3.6 Use Oracle Configuration Files

Critical Skill 3.7 Use Administration Tools

The Oracle Enterprise Manager/Grid Control

The Oracle Net Manager

The OEM Console

The OEM Components

The Oracle Net Configuration Assistant

The Oracle Internet Directory Configuration Assistant

Command-Line Utilities

The Oracle Advanced Security Option

Dispatchers

Project 3-1 Test a Connection

Critical Skill 3.8 Use Profiles

Critical Skill 3.9 Network in a Multi-tiered Environment

Critical Skill 3.10 Install the Oracle 11g Client Software

Chapter 3 Mastery Check

4 SQL: Structured Query Language

Critical Skill 4.1 Learn the SQL Statement Components

DDL

DML

Critical Skill 4.2 Use Basic Insert and Select Statements

Insert

Select

Critical Skill 4.3 Use Simple Where Clauses

A Where Clause with and/or

The Where Clause with NOT

The Where Clause with a Range Search

The Where Clause with a Search List

The Where Clause with a Pattern Search

The Where Clause: Common Operators

Critical Skill 4.4 Use Basic Update and Delete Statements

Update

Delete

Critical Skill 4.5 Order Data

Critical Skill 4.6 Employ Functions: String, Numeric, Aggregate (No Grouping)

String Functions

Numeric Functions

Aggregate Functions

Critical Skill 4.7 Use Dates and Data Functions (Formatting and Chronological)

Date Functions

Special Formats with the Date Data Type

Nested Functions

Critical Skill 4.8 Employ Joins (ANSI vs. Oracle): Inner, Outer, Self

Inner Joins

Outer Joins

Project 4-1 Join Data Using Inner and Outer Joins

Project 4-2 Join Data Using ANSI SQL Joins

Self-Joins

Critical Skill 4.9 Learn the Group By and Having Clauses

Group By

Having

Project 4-3 Group Data in Your Select Statements

Critical Skill 4.10 Learn Subqueries: Simple and Correlated Comparison with Joins

Simple Subquery

Correlated Subqueries with Joins

Critical Skill 4.11 Use Set Operators: Union, Intersect, Minus

Union

Union All

Intersect

Minus

Project 4-4 Use the Union Function in Your SQL

Critical Skill 4.12 Use Views

Critical Skill 4.13 Learn Sequences: Just Simple Stuff

Critical Skill 4.14 Employ Constraints: Linkage to Entity Models, Types, Deferred, Enforced, Gathering Exceptions

Linkage to Entity Models

Types

Deferred

Critical Skill 4.15 Format Your Output with SQL*Plus

Page and Line Size

Page Titles

Page Footers

Formatting Columns

Project 4-5 Format Your SQL Output

Writing SQL*Plus Output to a File

Chapter 4 Mastery Check

5 PL/SQL

Critical Skill 5.1 Define PL/SQL and Learn Why We Use It

Critical Skill 5.2 Describe the Basic PL/SQL Program Structure

Critical Skill 5.3 Define PL/SQL Data Types

Valid Characters

Arithmetic Operators

The varchar2 Data Type

The Number Data Type

The Date Data Type

The Boolean Data Type

Critical Skill 5.4 Write PL/SQL Programs in SQL*Plus

Project 5-1 Create a PL/SQL Program

SQL in Your PL/SQL Programs

PL/SQL Cursors

The Cursor FOR Loop

Critical Skill 5.5 Handle Error Conditions in PL/SQL

Error Handling Using Oracle-Supplied Variables

Critical Skill 5.6 Include Conditions in Your Programs

Program Control

Project 5-2 Use Conditions and Loops in PL/SQL

Critical Skill 5.7 Create Stored Procedures—How and Why

Critical Skill 5.8 Create and Use Functions

Project 5-3 Create and Use a Function

Critical Skill 5.9 Call PL/SQL Programs

Chapter 5 Mastery Check

6 The Database Administrator

Critical Skill 6.1 Learn What a DBA Does

Critical Skill 6.2 Perform Day-to-Day Operations

Architecture and Design

Capacity Planning

Backup and Recovery

Security

Performance and Tuning

Managing Database Objects

Storage Management

Change Management

Schedule Jobs

Network Management

Troubleshooting

Critical Skill 6.3 Understand the Oracle Database 11g Infrastructure

Schemas

Storage Structures

Critical Skill 6.4 Operate Modes of an Oracle Database 11g

Modes of Operation

Database and Instance Shutdown

Critical Skill 6.5 Get Started with Oracle Enterprise Manager

Instance Configuration

User Sessions

Resource Consumer Groups

Schema, Security, and Storage Management

Distributed Management

Warehouse Features

Other Tools

Critical Skill 6.6 Manage Database Objects

Control Files

Redo Logs

Undo Management

Schema Objects

Critical Skill 6.7 Manage Space

Archive Logs

Tablespaces and Data Files

Critical Skill 6.8 Manage Users

Create a User

Edit Users

Critical Skill 6.9 Manage Privileges for Database Users

Grant Authority

Roles

Profiles

Project 6-1 Create Essential Objects

Chapter 6 Mastery Check

7 Backup and Recovery

Critical Skill 7.1 Understand Oracle Backup and Recovery Fundamentals

Where Do I Start?

Backup Architecture

Oracle Binaries

Parameter Files

Control Files

Redo Logs

Undo Segments

Checkpoints

Archive Logs

Data Files, Tablespaces, Segments, Extents, and Blocks

Dump Files

Critical Skill 7.2 Learn about Oracle User-Managed Backup and Recovery

Types of User-Managed Backups

Cold Backups

Hot Backups

Recovery from a Cold Backup

Recovery from a Hot Backup

Seven Steps to Recovery

Recovery Using Backup Control Files

Critical Skill 7.3 Write a Database Backup

Critical Skill 7.4 Back Up Archived Redo Logs

Critical Skill 7.5 Get Started with Oracle Data Pump

Critical Skill 7.6 Use Oracle Data Pump Export

Critical Skill 7.7 Work with Oracle Data Pump Import

Critical Skill 7.8 Use Traditional Export and Import

Critical Skill 7.9 Get Started with Recovery Manager

RMAN Architecture

Set Up a Recovery Catalog and Target Database

Key RMAN Features

Backups

RMAN Using Enterprise Manager

Performing Backups

Restore and Recovery

Project 7-1 RMAN End to End

Chapter 7 Mastery Check

8 High Availability: RAC, ASM, and Data Guard

Critical Skill 8.1 Define High Availability

Critical Skill 8.2 Understand Real Application Clusters

Critical Skill 8.3 Install RAC

Critical Skill 8.4 Test RAC

Workload Manager

ASM

Critical Skill 8.5 Set Up the ASM Instance

Project 8-1 Install ASMLib

Critical Skill 8.6 Create ASM Disk Groups

Project 8-2 Create Disk Groups

Critical Skill 8.7 Use ASMCMD and ASMLIB

Critical Skill 8.8 Convert an Existing Database to ASM

Critical Skill 8.9 Understand Data Guard

Critical Skill 8.10 Explain Data Guard Protection Modes

Critical Skill 8.11 Create a Physical Standby Server

Project 8-3 Create a Physical Standby Server

Chapter 8 Mastery Check

9 Large Database Features

Critical Skill 9.1 Learn to Identify a Very Large Database

Critical Skill 9.2 Why and How to Use Data Partitioning

Why Use Data Partitioning

Implement Data Partitioning

Project 9-1 Create a Range-Partitioned Table and a Local-Partitioned Index

Critical Skill 9.3 Compress Your Data

Data Compression

Index Key Compression

Critical Skill 9.4 Use Parallel Processing to Improve Performance

Parallel Processing Database Components

Parallel Processing Configuration

Invoke Parallel Execution

Critical Skill 9.5 Use Materialized Views

Uses for Materialized Views

Query Rewrite

When to Create Materialized Views

Create Materialized Views

Critical Skill 9.6 Use SQL Aggregate and Analysis Functions

Aggregation Functions

Analysis Functions

Other Functions

Critical Skill 9.7 Create SQL Models

Project 9-2 Use Analytic SQL Functions and Models

Chapter 9 Mastery Check

A: Mastery Check Answers

Chapter 1: Database Fundamentals

Chapter 2: Installing Oracle

Chapter 3: Connecting to Oracle

Chapter 4: SQL: Structured Query Language

Chapter 5: PL/SQL

Chapter 6: The Database Administrator

Chapter 7: Backup and Recovery

Chapter 8: High Availability: RAC, ASM, and Data Guard

Chapter 9: Large Database Features

Index