You are previewing Oracle Database 11g: A Beginner’s Guide.
O'Reilly logo
Oracle Database 11g: A Beginner’s Guide

Book Description

Get a Solid Foundation in Oracle Database Technology

Master Oracle Database 11g fundamentals quickly and easily. Using self-paced tutorials, this book covers core database essentials, the role of the administrator, high availability, and large database features. Oracle Database 11g: A Beginner's Guide walks you, step by step, through database setup, administration, programming, backup, and recovery. In-depth introductions to SQL and PL/SQL are included. Designed for easy learning, this exclusive Oracle Press guide offers:

• Core Concepts--Oracle Database 11g topics presented in logically organized chapters

• Critical Skills--Lists of specific skills covered in each chapter

• Projects--Practical exercises that show how to apply the critical skills learned in each chapter

• Progress Checks--Quick self-assessment sections to check your progress

• Notes--Extra information related to the topic being covered

• Mastery Checks--Chapter-ending quizzes to test your knowledge

Table of Contents

  1. Cover Page
  2. Oracle Database 11g: A Beginner’s Guide
  3. Copyright Page
  4. Contents
  5. ACKNOWLEDGMENTS
  6. INTRODUCTION
  7. 1 Database Fundamentals
    1. Critical Skill 1.1 Define a Database
    2. Critical Skill 1.2 Learn the Oracle Database 11g Architecture
      1. The Control Files
      2. The Online Redo Logs
      3. The System Tablespace
      4. The Sysaux Tablespace
      5. Default Temporary Tablespace
      6. Undo Tablespace
      7. The Server Parameter File
      8. Background Processes
      9. Project 1-1 Review the Oracle Database 11g Architecture
        1. The Database Administrator
    3. Critical Skill 1.3 Learn the Basic Oracle Database 11g Data Types
      1. varchar2
      2. number
      3. date
      4. timestamp
      5. clob
      6. blob
    4. Critical Skill 1.4 Work with Tables
      1. Tables Related to part_master
    5. Critical Skill 1.5 Work with Stored Programmed Objects
      1. Views
      2. Triggers
      3. Procedures
      4. Functions
      5. Packages
    6. Critical Skill 1.6 Become Familiar with Other Important Items in Oracle Database 11g
      1. Indexes
      2. Users
      3. Tablespace Quotas
      4. Synonyms
      5. Roles
      6. Default User Environments
    7. Critical Skill 1.7 Work with Object and System Privileges
      1. Select
      2. Insert
      3. Update
      4. Delete
      5. System Privileges
    8. Critical Skill 1.8 Introduce Yourself to the Grid
    9. Critical Skill 1.9 Tie It All Together
    10. Chapter 1 Mastery Check
  8. 2 Installing Oracle
    1. Critical Skill 2.1 Research and Plan the Installation
      1. Define System Requirements
      2. Linux Installation
    2. Critical Skill 2.2 Set Up the Operating System
      1. Project 2-1 Configure Kernel Parameters
    3. Critical Skill 2.3 Get Familiar with Linux
    4. Critical Skill 2.4 Choose Components to Install
    5. Critical Skill 2.5 Install the Oracle Software
      1. Database Configuration Assistant
      2. Verify the Installation
    6. Chapter 2 Mastery Check
  9. 3 Connecting to Oracle
    1. Critical Skill 3.1 Use Oracle Net Services
      1. Network Protocols
      2. Optimize Network Bandwidth
      3. Connections
      4. Maintain Connections
      5. Define a Location
    2. Critical Skill 3.2 Learn the Difference Between Dedicated and Shared Server Architectures
      1. Dedicated Server
      2. Shared Server
      3. Set Dispatchers
      4. Views to Monitor the Shared Server
    3. Critical Skill 3.3 Define Connections
      1. A Connect Descriptor
      2. Define a Connect Descriptor
      3. The Oracle Connection Manager
      4. Session Multiplexing
      5. Firewall Access Control
    4. Critical Skill 3.4 Use the Oracle Net Listener
      1. Password Authentication
      2. Multiple Listeners
      3. Connection Pooling
    5. Critical Skill 3.5 Learn Naming Methods
      1. Directory Naming Method
      2. Directory Information Trees
      3. Distinguished Names
      4. How to Find the Directory Naming Information
      5. Net Service Alias Entries
      6. The Local Naming Method
      7. The Easy Naming Method
      8. The External Naming Method
      9. Which Naming Method to Use
    6. Critical Skill 3.6 Use Oracle Configuration Files
    7. Critical Skill 3.7 Use Administration Tools
      1. The Oracle Enterprise Manager/Grid Control
      2. The Oracle Net Manager
      3. The OEM Console
      4. The OEM Components
      5. The Oracle Net Configuration Assistant
      6. The Oracle Internet Directory Configuration Assistant
      7. Command-Line Utilities
      8. The Oracle Advanced Security Option
      9. Dispatchers
      10. Project 3-1 Test a Connection
    8. Critical Skill 3.8 Use Profiles
    9. Critical Skill 3.9 Network in a Multi-tiered Environment
    10. Critical Skill 3.10 Install the Oracle 11g Client Software
    11. Chapter 3 Mastery Check
  10. 4 SQL: Structured Query Language
    1. Critical Skill 4.1 Learn the SQL Statement Components
      1. DDL
      2. DML
    2. Critical Skill 4.2 Use Basic Insert and Select Statements
      1. Insert
      2. Select
    3. Critical Skill 4.3 Use Simple Where Clauses
      1. A Where Clause with and/or
      2. The Where Clause with NOT
      3. The Where Clause with a Range Search
      4. The Where Clause with a Search List
      5. The Where Clause with a Pattern Search
      6. The Where Clause: Common Operators
    4. Critical Skill 4.4 Use Basic Update and Delete Statements
      1. Update
      2. Delete
    5. Critical Skill 4.5 Order Data
    6. Critical Skill 4.6 Employ Functions: String, Numeric, Aggregate (No Grouping)
      1. String Functions
      2. Numeric Functions
      3. Aggregate Functions
    7. Critical Skill 4.7 Use Dates and Data Functions (Formatting and Chronological)
      1. Date Functions
      2. Special Formats with the Date Data Type
      3. Nested Functions
    8. Critical Skill 4.8 Employ Joins (ANSI vs. Oracle): Inner, Outer, Self
      1. Inner Joins
      2. Outer Joins
      3. Project 4-1 Join Data Using Inner and Outer Joins
      4. Project 4-2 Join Data Using ANSI SQL Joins
        1. Self-Joins
    9. Critical Skill 4.9 Learn the Group By and Having Clauses
      1. Group By
      2. Having
      3. Project 4-3 Group Data in Your Select Statements
    10. Critical Skill 4.10 Learn Subqueries: Simple and Correlated Comparison with Joins
      1. Simple Subquery
      2. Correlated Subqueries with Joins
    11. Critical Skill 4.11 Use Set Operators: Union, Intersect, Minus
      1. Union
      2. Union All
      3. Intersect
      4. Minus
      5. Project 4-4 Use the Union Function in Your SQL
    12. Critical Skill 4.12 Use Views
    13. Critical Skill 4.13 Learn Sequences: Just Simple Stuff
    14. Critical Skill 4.14 Employ Constraints: Linkage to Entity Models, Types, Deferred, Enforced, Gathering Exceptions
      1. Linkage to Entity Models
      2. Types
      3. Deferred
    15. Critical Skill 4.15 Format Your Output with SQL *Plus
      1. Page and Line Size
      2. Page Titles
      3. Page Footers
      4. Formatting Columns
      5. Project 4-5 Format Your SQL Output
        1. Writing SQL*Plus Output to a File
    16. Chapter 4 Mastery Check
  11. 5 PL/SQL
    1. Critical Skill 5.1 Define PL/SQL and Learn Why We Use It
    2. Critical Skill 5.2 Describe the Basic PL/SQL Program Structure
    3. Critical Skill 5.3 Define PL/SQL Data Types
      1. Valid Characters
      2. Arithmetic Operators
      3. The varchar2 Data Type
      4. The Number Data Type
      5. The Date Data Type
      6. The Boolean Data Type
    4. Critical Skill 5.4 Write PL/SQL Programs in SQL*Plus
      1. Project 5-1 Create a PL/SQL Program
        1. SQL in Your PL/SQL Programs
        2. PL/SQL Cursors
        3. The Cursor FOR Loop
    5. Critical Skill 5.5 Handle Error Conditions in PL/SQL
      1. Error Handling Using Oracle-Supplied Variables
    6. Critical Skill 5.6 Include Conditions in Your Programs
      1. Program Control
    7. Project 5-2 Use Conditions and Loops in PL/SQL
    8. Critical Skill 5.7 Create Stored Procedures—How and Why
    9. Critical Skill 5.8 Create and Use Functions
      1. Project 5-3 Create and Use a Function
    10. Critical Skill 5.9 Call PL/SQL Programs
    11. Chapter 5 Mastery Check
  12. 6 The Database Administrator
    1. Critical Skill 6.1 Learn What a DBA Does
    2. Critical Skill 6.2 Perform Day-to-Day Operations
      1. Architecture and Design
      2. Capacity Planning
      3. Backup and Recovery
      4. Security
      5. Performance and Tuning
      6. Managing Database Objects
      7. Storage Management
      8. Change Management
      9. Schedule Jobs
      10. Network Management
      11. Troubleshooting
    3. Critical Skill 6.3 Understand the Oracle Database 11g Infrastructure
      1. Schemas
      2. Storage Structures
    4. Critical Skill 6.4 Operate Modes of an Oracle Database 11g
      1. Modes of Operation
      2. Database and Instance Shutdown
    5. Critical Skill 6.5 Get Started with Oracle Enterprise Manager
      1. Instance Configuration
      2. User Sessions
      3. Resource Consumer Groups
      4. Schema, Security, and Storage Management
      5. Distributed Management
      6. Warehouse Features
      7. Other Tools
    6. Critical Skill 6.6 Manage Database Objects
      1. Control Files
      2. Redo Logs
      3. Undo Management
      4. Schema Objects
    7. Critical Skill 6.7 Manage Space
      1. Archive Logs
      2. Tablespaces and Data Files
    8. Critical Skill 6.8 Manage Users
      1. Create a User
      2. Edit Users
    9. Critical Skill 6.9 Manage Privileges for Database Users
      1. Grant Authority
      2. Roles
      3. Profiles
    10. Project 6-1 Create Essential Objects
    11. Chapter 6 Mastery Check
  13. 7 Backup and Recovery
    1. Critical Skill 7.1 Understand Oracle Backup and Recovery Fundamentals
      1. Where Do I Start?
      2. Backup Architecture
      3. Oracle Binaries
      4. Parameter Files
      5. Control Files
      6. Redo Logs
      7. Undo Segments
      8. Checkpoints
      9. Archive Logs
      10. Data Files, Tablespaces, Segments, Extents, and Blocks
      11. Dump Files
    2. Critical Skill 7.2 Learn about Oracle User-Managed Backup and Recovery
      1. Types of User-Managed Backups
      2. Cold Backups
      3. Hot Backups
      4. Recovery from a Cold Backup
      5. Recovery from a Hot Backup
      6. Seven Steps to Recovery
      7. Recovery Using Backup Control Files
    3. Critical Skill 7.3 Write a Database Backup
    4. Critical Skill 7.4 Back Up Archived Redo Logs
    5. Critical Skill 7.5 Get Started with Oracle Data Pump
    6. Critical Skill 7.6 Use Oracle Data Pump Export
    7. Critical Skill 7.7 Work with Oracle Data Pump Import
    8. Critical Skill 7.8 Use Traditional Export and Import
    9. Critical Skill 7.9 Get Started with Recovery Manager
      1. RMAN Architecture
      2. Set Up a Recovery Catalog and Target Database
      3. Key RMAN Features
      4. Backups
      5. RMAN Using Enterprise Manager
      6. Performing Backups
      7. Restore and Recovery
    10. Project 7-1 RMAN End to End
    11. Chapter 7 Mastery Check
  14. 8 High Availability: RAC, ASM, and Data Guard
    1. Critical Skill 8.1 Define High Availability
    2. Critical Skill 8.2 Understand Real Application Clusters
    3. Critical Skill 8.3 Install RAC
    4. Critical Skill 8.4 Test RAC
      1. Workload Manager
      2. ASM
    5. Critical Skill 8.5 Set Up the ASM Instance
      1. Project 8-1 Install ASMLib
    6. Critical Skill 8.6 Create ASM Disk Groups
      1. Project 8-2 Create Disk Groups
    7. Critical Skill 8.7 Use ASMCMD and ASMLIB
    8. Critical Skill 8.8 Convert an Existing Database to ASM
    9. Critical Skill 8.9 Understand Data Guard
    10. Critical Skill 8.10 Explain Data Guard Protection Modes
    11. Critical Skill 8.11 Create a Physical Standby Server
      1. Project 8-3 Create a Physical Standby Server
    12. Chapter 8 Mastery Check
  15. 9 Large Database Features
    1. Critical Skill 9.1 Learn to Identify a Very Large Database
    2. Critical Skill 9.2 Why and How to Use Data Partitioning
      1. Why Use Data Partitioning
      2. Implement Data Partitioning
    3. Project 9-1 Create a Range-Partitioned Table and a Local-Partitioned Index
    4. Critical Skill 9.3 Compress Your Data
      1. Data Compression
      2. Index Key Compression
    5. Critical Skill 9.4 Use Parallel Processing to Improve Performance
      1. Parallel Processing Database Components
      2. Parallel Processing Configuration
      3. Invoke Parallel Execution
    6. Critical Skill 9.5 Use Materialized Views
      1. Uses for Materialized Views
      2. Query Rewrite
      3. When to Create Materialized Views
      4. Create Materialized Views
    7. Critical Skill 9.6 Use SQL Aggregate and Analysis Functions
      1. Aggregation Functions
      2. Analysis Functions
      3. Other Functions
    8. Critical Skill 9.7 Create SQL Models
      1. Project 9-2 Use Analytic SQL Functions and Models
    9. Chapter 9 Mastery Check
  16. A Mastery Check Answers
    1. Chapter 1: Database Fundamentals
    2. Chapter 2: Installing Oracle
    3. Chapter 3: Connecting to Oracle
    4. Chapter 4: SQL: Structured Query Language
    5. Chapter 5: PL/SQL
    6. Chapter 6: The Database Administrator
    7. Chapter 7: Backup and Recovery
    8. Chapter 8: High Availability: RAC, ASM, and Data Guard
    9. Chapter 9: Large Database Features
  17. Index