Oracle® 11g For Dummies®

Oracle® 11g For Dummies®

by Chris Zeis, Chris Ruel, Michael Wessler
Released January 2009
Publisher(s): For Dummies
ISBN: 9780470277652

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Are you a seasoned system administrator charged with setting up an Oracle database? Or did you suddenly become a DBA by default? If database administration with Oracle is part of your job, you’ll be glad to have Oracle 11g For Dummies in your cubicle.

This nuts-and-bolts guide walks you through the mysteries of Oracle and database administration. You’ll learn how to understand Oracle database architecture, set up and manage an Oracle database, and keep it running in tiptop form. Oracle 11g For Dummies covers:

  • The building blocks behind the database engine as well as Oracle’s physical and logical structures

  • Hardware, software, system, and storage requirements for implementation

  • How to recognize and accommodate the differences between Oracle installations on Windows and on Linux/UNIX

  • Daily and intermittent tasks necessary to keep your database running properly

  • How to assess potential threats to your database, configure Oracle Recovery Manager, and set up backup and recovery procedures

  • When to use online, offline, controlfile, and archivelog backups

  • Troubleshooting methodology and how to use Oracle database logs and other diagnostic utilities

  • Different ways to manage your database

  • How to automate jobs with the Oracle Scheduler

  • Using SQL in Oracle, and a great deal more

Completely up to date for the newest release of Oracle, Oracle 11g For Dummies will give you both the information and the confidence to set up and maintain an Oracle database for your organization.

Table of contents

  1. Copyright
  2. About the Authors
  3. Authors' Acknowledgments
  4. Publisher's Acknowledgments
  5. Introduction
    1. About This Book
    2. Who Are You?
    3. What's in This Book
      1. Part I: You Don't Have to Go to Delphi to Know Oracle
      2. Part II: Implementing Oracle on Your Own
      3. Part III: Caring for an Oracle Database
      4. Part IV: Inspecting Advanced Oracle Technologies
      5. Part V: The Part of Tens
    4. Icons in This Book
    5. Where to Go from Here
  6. I. You Don't Have to Go to Delphi to Know Oracle
    1. 1. A Pragmatic Introduction to Oracle
      1. 1.1. Introducing a New Kind of Database Management
      2. 1.2. Pooling Resources with Grid Computing
      3. 1.3. Anticipating Technology and Development Trends
      4. 1.4. Meeting Oracle in the Real World
      5. 1.5. Making the Oracle Decision
    2. 2. Understanding Oracle Database Architecture
      1. 2.1. Defining Databases and Instances
      2. 2.2. Deconstructing the Oracle Architecture
      3. 2.3. Walking Down Oracle Memory Structures
      4. 2.4. Trotting around the System Global Area
        1. 2.4.1. Shared pool
          1. 2.4.1.1. The library cache
          2. 2.4.1.2. The dictionary cache
          3. 2.4.1.3. The quickest result cache
          4. 2.4.1.4. Least Recently Used algorithm
        2. 2.4.2. Database buffer cache
          1. 2.4.2.1. Buffer cache state
            1. 2.4.2.1.1. Free blocks
            2. 2.4.2.1.2. Dirty blocks
          2. 2.4.2.2. Block write triggers
        3. 2.4.3. Redo log buffer
        4. 2.4.4. Large pool
        5. 2.4.5. Java pool
      5. 2.5. Program Global Area
      6. 2.6. Managing Memory
        1. 2.6.1. Managing memory automatically
      7. 2.7. Following the Oracle Processes
        1. 2.7.1. Background processes
        2. 2.7.2. User and server processes
          1. 2.7.2.1. Dedicated server architecture
          2. 2.7.2.2. Shared server architecture
      8. 2.8. Getting into Physical Structures
      9. 2.9. Getting Physical with Files
        1. 2.9.1. Data files: Where the data meets the disk
        2. 2.9.2. Control files
        3. 2.9.3. Redo log files
        4. 2.9.4. Moving to the archives
        5. 2.9.5. Server and initialization parameter files
      10. 2.10. Applying Some Logical Structures
        1. 2.10.1. Tablespaces
        2. 2.10.2. Segments
        3. 2.10.3. Extents
        4. 2.10.4. Oracle blocks
    3. 3. Preparing to Implement Oracle in the Real World
      1. 3.1. Understanding Oracle Database Deployment Methodology
        1. 3.1.1. Client-server applications
        2. 3.1.2. Muli-tier applications
        3. 3.1.3. Component configurations
      2. 3.2. Checking on the Requirements
        1. 3.2.1. User and directory requirements
        2. 3.2.2. Hardware requirements
        3. 3.2.3. Software requirements
        4. 3.2.4. Storage requirements
        5. 3.2.5. Other requirements
          1. 3.2.5.1. Oracle version
          2. 3.2.5.2. Oracle patches
          3. 3.2.5.3. Network connectivity
          4. 3.2.5.4. Security
          5. 3.2.5.5. Application
          6. 3.2.5.6. Backups
  7. II. Implementing Oracle on Your Own
    1. 4. Creating an Oracle Database
      1. 4.1. Feeling at Home in Your Environment
        1. 4.1.1. Finding the Oracle software owner
          1. 4.1.1.1. Linux/UNIX
          2. 4.1.1.2. Windows
        2. 4.1.2. Oracle versions
        3. 4.1.3. Getting to home base
        4. 4.1.4. ORACLE_BASE
        5. 4.1.5. ORACLE_HOME
        6. 4.1.6. ORACLE_SID
        7. 4.1.7. PATH
      2. 4.2. Sticking with the Oracle Universal Installer and oraenv
      3. 4.3. Configuring an Instance
        1. 4.3.1. Using PFILE and SPFILES
        2. 4.3.2. Setting parameters in the pfile and spfile
      4. 4.4. Creating Your Oracle Database
      5. 4.5. Bossing the Database Configuration Assistant (DBCA)
        1. 4.5.1. Taking database control
        2. 4.5.2. Taking the DBCA steps
        3. 4.5.3. Sharing (a) memory
      6. 4.6. Feeling the Post-Configuration Glow
    2. 5. Connecting to and Using an Oracle Database
      1. 5.1. Starting and Stopping the Database
        1. 5.1.1. Environmental requirements
          1. 5.1.1.1. Log in to the database server
          2. 5.1.1.2. Log in as the Oracle DBA account
          3. 5.1.1.3. Set up your environment variables
          4. 5.1.1.4. Start SQL*Plus as a DBA
          5. 5.1.1.5. Database parameter file
        2. 5.1.2. Starting the database
        3. 5.1.3. Stopping the database
          1. 5.1.3.1. Shutdown types
          2. 5.1.3.2. Shutdown decisions
      2. 5.2. Connecting to the Database Instance
        1. 5.2.1. Local versus remote connections
        2. 5.2.2. Communication flow
        3. 5.2.3. Setting up tnsnames.ora
        4. 5.2.4. Configuring the database listener with listener.ora
        5. 5.2.5. Starting and stopping the database listener
        6. 5.2.6. Testing the connection
        7. 5.2.7. Oracle Net Configuration Assistant
      3. 5.3. Sidestepping Connection Gotchas
    3. 6. Speaking the SQL Language
      1. 6.1. Using SQL in Oracle
      2. 6.2. Sharpening the SQL*Plus Tool
      3. 6.3. Using SQL Connection Commands
      4. 6.4. SQL*Plus Profile Scripts glogin.sql and login.sql
        1. 6.4.1. SQL*Plus buffer and commands
        2. 6.4.2. SQL*Plus commands
      5. 6.5. Getting Help from SQL*Plus
        1. 6.5.1. SQL language elements
          1. 6.5.1.1. Data Query Language (DQL)
          2. 6.5.1.2. Data Manipulation Language (DML)
          3. 6.5.1.3. Data Definition Language (DDL)
          4. 6.5.1.4. Data Control Language (DCL)
          5. 6.5.1.5. Transaction Control Language (TCL)
        2. 6.5.2. Using the Oracle Data Dictionary
          1. 6.5.2.1. Data dictionary views
          2. 6.5.2.2. Using desc
          3. 6.5.2.3. Using l
          4. 6.5.2.4. Creating a join
      6. 6.6. Getting a PL/SQL Overview
        1. 6.6.1. Blocking PL/SQL
    4. 7. Populating the Database
      1. 7.1. Creating Tablespaces
      2. 7.2. Creating Users and Schemas
      3. 7.3. Creating Database Objects
        1. 7.3.1. Object types
          1. 7.3.1.1. Table
          2. 7.3.1.2. View
          3. 7.3.1.3. Index
          4. 7.3.1.4. Procedure
          5. 7.3.1.5. Function
          6. 7.3.1.6. Package
          7. 7.3.1.7. Trigger
          8. 7.3.1.8. Database link
          9. 7.3.1.9. Synonym
        2. 7.3.2. Object creation methods
  8. III. Caring for an Oracle Database
    1. 8. Protecting Your Oracle Database
      1. 8.1. Assessing Database Threats
        1. 8.1.1. Instance failure
        2. 8.1.2. Oracle code tree
        3. 8.1.3. Dropped objects
        4. 8.1.4. Media failure
        5. 8.1.5. Corruption
        6. 8.1.6. User error
      2. 8.2. Getting Your Oracle Recovery Manager
        1. 8.2.1. Starting RMAN
        2. 8.2.2. Configuring RMAN
        3. 8.2.3. RMAN catalog
          1. 8.2.3.1. Selecting a catalog mode
          2. 8.2.3.2. Creating the catalog
      3. 8.3. Putting It in the Archives
        1. 8.3.1. Turning archiving on and off
        2. 8.3.2. Archive logs
        3. 8.3.3. Enabling archiving
        4. 8.3.4. Enabling the Flash Recovery Area
      4. 8.4. Backup File Types with RMAN
        1. 8.4.1. Backing up with backup sets
          1. 8.4.1.1. Backing up the database or tablespaces
          2. 8.4.1.2. Naming your backups
          3. 8.4.1.3. Compressing Your Backups
          4. 8.4.1.4. Incremental backups
            1. 8.4.1.4.1. Block change tracking
        2. 8.4.2. Making copies
      5. 8.5. Maintaining the Archives
      6. 8.6. Viewing Backup Information
      7. 8.7. Recovering Your Oracle Database
        1. 8.7.1. Verifying the problem
        2. 8.7.2. Complete recovery
          1. 8.7.2.1. Complete recovery: One or more data files
          2. 8.7.2.2. Complete recovery: One or more control files
          3. 8.7.2.3. Complete recovery without the DRA
        3. 8.7.3. Incomplete recovery
        4. 8.7.4. Recovering your database with copies
    2. 9. Protecting Your Oracle Data
      1. 9.1. Authentication
        1. 9.1.1. User authentication
        2. 9.1.2. Password authentication
          1. 9.1.2.1. Enforcing password security with profiles
          2. 9.1.2.2. Creating a password profile
          3. 9.1.2.3. DEFAULT profile
        3. 9.1.3. Operating system authentication
      2. 9.2. Granting the Privileged Few
        1. 9.2.1. System privileges
        2. 9.2.2. Object privileges
          1. 9.2.2.1. Understanding object privileges
          2. 9.2.2.2. Granting object privileges
      3. 9.3. Role Playing
        1. 9.3.1. Oracle-supplied roles
        2. 9.3.2. The SYSDBA role
      4. 9.4. Virtual Private Database Concept
      5. 9.5. Auditing Oracle's Big Brother
        1. 9.5.1. Getting ready to audit
        2. 9.5.2. Enabling and disabling audits
        3. 9.5.3. Auditing system privileges
          1. 9.5.3.1. Auditing defaults
          2. 9.5.3.2. Auditing successful and unsuccessful attempts
        4. 9.5.4. Auditing objects
        5. 9.5.5. Verifying an audit
        6. 9.5.6. Viewing audit information
        7. 9.5.7. Turning off audits
      6. 9.6. Encrypting a Database
    3. 10. Keeping the Database Running
      1. 10.1. Doing Your Chores
        1. 10.1.1. Making way, checking space
          1. 10.1.1.1. Enterprise Manager
          2. 10.1.1.2. SQL
        2. 10.1.2. Monitoring space in your segments
        3. 10.1.3. Growing and shrinking: Tricky tables
          1. 10.1.3.1.
            1. 10.1.3.1.1. Enterprise Manager
            2. 10.1.3.1.2. Plain old arithmetic
        4. 10.1.4. Checking users
          1. 10.1.4.1. Finding stale sessions
          2. 10.1.4.2. Policing for login abuse
          3. 10.1.4.3. Checking on the backup
          4. 10.1.4.4. Checking batch jobs
        5. 10.1.5. Audit records
        6. 10.1.6. System logs
      2. 10.2. Automating Jobs with the Oracle Scheduler
        1. 10.2.1. Scheduler objects
        2. 10.2.2. Creating your first scheduler job
          1. 10.2.2.1. Disabling a job
          2. 10.2.2.2. Removing the job
      3. 10.3. Using Oracle Data Pump
        1. 10.3.1. Data Pump Export
        2. 10.3.2. Data Pump Import
      4. 10.4. Creating Oracle Directories
      5. 10.5. Using Data Pump with a Parameter File
    4. 11. Tuning an Oracle Database
      1. 11.1. Evaluating Tuning Problems
      2. 11.2. Tuning Your Database
      3. 11.3. Gathering Performance Information with Automatic Workload Repository
        1. 11.3.1. SQL*Plus method
        2. 11.3.2. Database Control method
      4. 11.4. Using the Automatic Database Diagnostic Monitor (ADDM)
      5. 11.5. Improving Queries with SQL Tuning
        1. 11.5.1. Explain plan
        2. 11.5.2. Active Session History (ASH)
        3. 11.5.3. SQL Access Advisor
        4. 11.5.4. SQL Tuning Advisor
        5. 11.5.5. SQL Profiling and Plan Management
        6. 11.5.6. 10046 trace event
    5. 12. Troubleshooting an Oracle Database
      1. 12.1. Troubleshooting with System Methodology
        1. 12.1.1. Identifying the real problem
        2. 12.1.2. Performing basic system checks
          1. 12.1.2.1. Network
          2. 12.1.2.2. Server utilization
          3. 12.1.2.3. CPU utilization
          4. 12.1.2.4. Memory
          5. 12.1.2.5. Available disk space
          6. 12.1.2.6. System event logs
        3. 12.1.3. Performing basic database checks
          1. 12.1.3.1. Running database instance
          2. 12.1.3.2. Oracle NET functionality
          3. 12.1.3.3. Database connection
        4. 12.1.4. Analyzing error messages
          1. 12.1.4.1. Knowing database and system anatomy
          2. 12.1.4.2. Error system example
          3. 12.1.4.3. oerr utility
        5. 12.1.5. Developing and applying a solution
          1. 12.1.5.1. Researching
          2. 12.1.5.2. Planning
          3. 12.1.5.3. Ramifications
          4. 12.1.5.4. Testing
          5. 12.1.5.5. Fallback options
          6. 12.1.5.6. Support
          7. 12.1.5.7. Verification
      2. 12.2. Troubleshooting Using Oracle Database Logs
        1. 12.2.1. Database log infrastructure
        2. 12.2.2. Database alert log
        3. 12.2.3. Trace and dump files
        4. 12.2.4. Listener log
      3. 12.3. Benefiting from Other Diagnostic Utilities
        1. 12.3.1. Oracle Enterprise Manager
        2. 12.3.2. Remote Diagnostic Agent
        3. 12.3.3. Diagnostic database scripts
    6. 13. Monitoring and Managing with Enterprise Manager
      1. 13.1. Tasting Oracle Enterprise Manager Flavors
        1. 13.1.1. OEM Java Console
        2. 13.1.2. OEM Database Control
        3. 13.1.3. OEM Grid Control
      2. 13.2. Configuring Enterprise Manager with the DBCA
      3. 13.3. Creating and Managing Database Control Users
      4. 13.4. Working with Metrics and Policies
      5. 13.5. Setting Up Notifications
      6. 13.6. Setting Up User Notifications
      7. 13.7. Navigating Database Control's Main Page
        1. 13.7.1. Inspecting the Database Control main page
        2. 13.7.2. Accessing other targets
  9. IV. Inspecting Advanced Oracle Technologies
    1. 14. Flashing Back and Replaying: Advanced Features
      1. 14.1. Rolling Back with Flashback Database
        1. 14.1.1. Configuring and enabling a flash back
        2. 14.1.2. Using restore points
        3. 14.1.3. Flashing back your database
      2. 14.2. Using Flashback Data Archive
      3. 14.3. Oracle Database Replay
        1. 14.3.1. Using database replay
        2. 14.3.2. Replaying the workload
    2. 15. Using High-Availability Options
      1. 15.1. Gathering Real Application Clusters
      2. 15.2. Exploring RAC Architecture
        1. 15.2.1. Hardware considerations for RAC
          1. 15.2.1.1. Nodes
          2. 15.2.1.2. Central storage
          3. 15.2.1.3. Cluster interconnect
          4. 15.2.1.4. Network interfaces
        2. 15.2.2. Software considerations for RAC
          1. 15.2.2.1. Operating system
          2. 15.2.2.2. Clustering software
          3. 15.2.2.3. Oracle database
          4. 15.2.2.4. Optional software
      3. 15.3. Preparing for a RAC Install
        1. 15.3.1. Tools for managing a RAC installation
          1. 15.3.1.1. Oracle Universal Installer for clusterware
          2. 15.3.1.2. Oracle Universal Installer for other software
          3. 15.3.1.3. Database Configuration Assistant (DBCA)
          4. 15.3.1.4. Network Configuration Assistant (NETCA)
          5. 15.3.1.5. Server Control (srvctl)
          6. 15.3.1.6. Cluster Control (crsctl)
          7. 15.3.1.7. Oracle Interface Configuration Tool (OIFCFG)
        2. 15.3.2. Oracle RAC application for high availability
      4. 15.4. Defending Oracle Data Guard
        1. 15.4.1. Data Guard architecture
          1. 15.4.1.1. Maximum availability
          2. 15.4.1.2. Maximum protection
          3. 15.4.1.3. Maximum performance
        2. 15.4.2. Physical standby database
        3. 15.4.3. Logical standby database
        4. 15.4.4. Performing switchover and failover operations
  10. V. The Part of Tens
    1. 16. Top Ten Oracle Installation Do's
      1. 16.1. Read the Documentation
      2. 16.2. Observe the Optimal Flexible Architecture
      3. 16.3. Configure Your Profile
      4. 16.4. Running the Wrong Bit
      5. 16.5. Set umask
      6. 16.6. Become Oracle
      7. 16.7. Stage It
      8. 16.8. Patch It
      9. 16.9. Mind the User and Group IDs
      10. 16.10. Back It Up
    2. 17. Top Ten Database Design Do's
      1. 17.1. Using Oracle's Built-In Constraints
      2. 17.2. Spreading Out Your IO
      3. 17.3. Knowing Data Normalization
      4. 17.4. Using Naming Conventions
      5. 17.5. Setting Up Roles and Privileges Properly
      6. 17.6. Keeping Ad-Hoc Queries to a Minimum
      7. 17.7. Enforcing Password Security
      8. 17.8. Limiting the Number of DBAs
      9. 17.9. Storing Code in the Database
      10. 17.10. Testing Your Recovery Strategy
    3. A. Quick Start Install of Oracle 11g on Linux
      1. A.1. Setting Up the Operating System
        1. A.1.1. Checking your operating system version
        2. A.1.2. Checking your kernel version
        3. A.1.3. Checking your OS packages
        4. A.1.4. Creating Linux operating system groups and users
      2. A.2. Creating the Oracle Software Owner
      3. A.3. Configuring the Linux Kernel Parameters
        1. A.3.1. Creating the ORACLE_BASE directory
        2. A.3.2. Configuring the Oracle user's environment
        3. A.3.3. Installing the Oracle 11g database software

Product information

  • Title: Oracle® 11g For Dummies®
  • Author(s): Chris Zeis, Chris Ruel, Michael Wessler
  • Release date: January 2009
  • Publisher(s): For Dummies
  • ISBN: 9780470277652