Oracle Database 10g DBA Handbook

Oracle Database 10g DBA Handbook

by Kevin Loney, Bob Bryla
Released April 2005
Publisher(s): McGraw Hill Computing
ISBN: 9780071770439

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

Everything a DBA needs to know in one volume--this is the must-have reference for anyone working with the Oracle database, and it’s been fully revised and updated for Oracle Database 10g. Co-author Kevin Loney is the all-time, best-selling Oracle Press author.

Table of contents

  1. Cover Page
  2. Oracle Database 10g DBA Handbook
  3. Copyright Page
  4. Dedication
  5. Contents
  6. Acknowledgments
  7. Introduction
  8. Part I Database Architecture
    1. 1 Getting Started with the Oracle Architecture
      1. An Overview of Databases and Instances
        1. Databases
        2. Instances
      2. Oracle Logical Storage Structures
        1. Tablespaces
        2. Blocks
        3. Extents
        4. Segments
      3. Oracle Logical Database Structures
        1. Tables
        2. Constraints
        3. Indexes
        4. Views
        5. Users and Schemas
        6. Profiles
        7. Sequences
        8. Synonyms
        9. PL/SQL
        10. External File Access
        11. Database Links and Remote Databases
      4. Oracle Physical Storage Structures
        1. Datafiles
        2. Redo Log Files
        3. Control Files
        4. Archived Log Files
        5. Initialization Parameter Files
        6. Alert and Trace Log Files
        7. Backup Files
        8. Oracle Managed Files
        9. Password Files
      5. Multiplexing Database Files
        1. Automatic Storage Management
        2. Manual Multiplexing
      6. Oracle Memory Structures
        1. System Global Area
        2. Program Global Area
        3. Software Code Area
        4. Background Processes
      7. Backup/Recovery Overview
        1. Export/Import
        2. Offline Backups
        3. Online Backups
        4. Rman
      8. Security Capabilities
        1. Privileges and Roles
        2. Auditing
        3. Fine-grained Auditing
        4. Virtual Private Database
        5. Label Security
      9. Real Application Clusters
      10. Oracle Streams
      11. Oracle Enterprise Manager
      12. Oracle Initialization Parameters
        1. Basic Initialization Parameters
        2. Advanced Initialization Parameters
      13. Software Installation
        1. Overview of Licensing and Installation Options
        2. Using OUI to Install the Oracle Software
        3. Using the DBCA to Create a Database
        4. Manually Creating a Database
    2. 2 Upgrading to Oracle Database 10g
      1. Choosing an Upgrade Method
      2. Before Upgrading
      3. Using the Database Upgrade Assistant
      4. Performing a Manual Direct Upgrade
      5. Using Export and Import
        1. Export and Import Versions to Use
        2. Performing the Upgrade
      6. Using the Data-Copying Method
      7. After Upgrading
    3. 3 Planning and Managing Tablespaces
      1. Tablespace Architecture
        1. Tablespace Types
        2. Optimal Flexible Architecture
      2. Oracle Installation Tablespaces
        1. System
        2. Sysaux
        3. Temp
        4. Undotbs1
        5. Users
        6. Example
      3. Segment Segregation
    4. 4 Physical Database Layouts and Storage Management
      1. Traditional Disk Space Storage
        1. Resizing Tablespaces and Datafiles
        2. Moving Datafiles
        3. Moving Online Redo Log Files
        4. Moving Control Files
      2. Automatic Storage Management
        1. ASM Architecture
        2. Creating an ASM Instance
        3. ASM Instance Components
        4. ASM Dynamic Performance Views
        5. ASM Filename Formats
        6. ASM File Types and Templates
        7. Administering ASM Disk Groups
  9. Part II Database Management
    1. 5 Developing and Implementing Applications
      1. Tuning by Design: Best Practices
        1. Do As Little As Possible
        2. Do It As Simply As Possible
        3. Tell the Database What It Needs to Know
        4. Maximize the Throughput in the Environment
        5. Divide and Conquer Your Data
        6. Test Correctly
        7. Standard Deliverables
      2. Resource Management and Stored Outlines
        1. Implementing the Database Resource Manager
        2. Implementing Stored Outlines
        3. Sizing Database Objects
        4. Using Temporary Tables
      3. Supporting Tables Based on Abstract Datatypes
        1. Using Object Views
        2. Security for Abstract Datatypes
        3. Indexing Abstract Datatype Attributes
      4. Quiescing and Suspending the Database
      5. Supporting Iterative Development
        1. Iterative Column Definitions
        2. Forcing Cursor Sharing
      6. Managing Package Development
        1. Generating Diagrams
        2. Space Requirements
        3. Tuning Goals
        4. Security Requirements
        5. Data Requirements
        6. Version Requirements
        7. Execution Plans
        8. Acceptance Test Procedures
        9. The Testing Environment
    2. 6 Monitoring Space Usage
      1. Common Space Management Problems
        1. Running Out of Free Space in a Tablespace
        2. Insufficient Space for Temporary Segments
        3. Too Much or Too Little Undo Space Allocated
        4. Fragmented Tablespaces and Segments
      2. Oracle Segments, Extents, and Blocks
        1. Data Blocks
        2. Extents
        3. Segments
      3. Data Dictionary Views and Dynamic Performance Views
        1. DBA_TABLESPACES
        2. DBA_SEGMENTS
        3. DBA_EXTENTS
        4. DBA_FREE_SPACE
        5. DBA_LMT_FREE_SPACE
        6. DBA_THRESHOLDS
        7. DBA_OUTSTANDING_ALERTS
        8. DBA_ALERT_HISTORY
        9. V$ALERT_TYPES
        10. V$UNDOSTAT
        11. V$OBJECT_USAGE
        12. V$SORT_SEGMENT
        13. V$TEMPSEG_USAGE
      4. Space Management Methodologies
        1. Locally Managed Tablespaces
        2. Using OMF to Manage Space
        3. Bigfile Tablespaces
        4. Automatic Storage Management
        5. Undo Management Considerations
      5. SYSAUX Monitoring and Usage
      6. Archived Redo Log File Management
      7. Built-in Space Management Tools
        1. Segment Advisor
        2. Undo Advisor and the Automatic Workload Repository
        3. Index Usage
        4. Space Usage Warning Levels
        5. Resumable Space Allocation
        6. OS Space Management
      8. Space Management Scripts
        1. Segments That Cannot Allocate Additional Extents
        2. Used and Free Space by Tablespace and Datafile
      9. Automating and Streamlining the Notification Process
        1. Using DBMS_SCHEDULER
        2. OEM Job Control and Monitoring
    3. 7 Managing Transactions with Undo Tablespaces
      1. Transaction Basics
      2. Undo Basics
        1. Rollback
        2. Read Consistency
        3. Database Recovery
        4. Flashback Operations
      3. Managing Undo Tablespaces
        1. Creating Undo Tablespaces
        2. Undo Tablespace Dynamic Performance Views
        3. Undo Tablespace Initialization Parameters
        4. Multiple Undo Tablespaces
        5. Sizing and Monitoring the Undo Tablespace
        6. Read Consistency vs. Successful DML
      4. Flashback Features
        1. Flashback Query
        2. DBMS_FLASHBACK
        3. Flashback Table
        4. Flashback Version Query
        5. Flashback Transaction Query
      5. Migrating to Automatic Undo Management
    4. 8 Database Tuning
      1. Tuning Application Design
        1. Effective Table Design
        2. Distribution of CPU Requirements
        3. Effective Application Design
      2. Tuning SQL
        1. Impact of Order on Load Rates
        2. Additional Indexing Options
        3. Generating Explain Plans
      3. Tuning Memory Usage
        1. Specifying the Size of the SGA
        2. Using the Cost-Based Optimizer
      4. Tuning Data Access
        1. Locally Managed Tablespaces
        2. Identifying Chained Rows
        3. Increasing the Oracle Block Size
        4. Using Index-Organized Tables
      5. Tuning Data Manipulation
        1. Bulk Inserts: Using the SQL*Loader Direct Path Option
        2. Bulk Data Moves—Using External Tables
        3. Bulk Inserts: Common Traps and Successful Tricks
        4. Bulk Deletes: The truncate Command
        5. Using Partitions
      6. Tuning Physical Storage
        1. Using Raw Devices
      7. Reducing Network Traffic
        1. Replication of Data
        2. Using Remote Procedure Calls
      8. Using STATSPACK and the Automatic Workload Repository
        1. Managing Snapshots
        2. Managing Baselines
        3. Generating AWR Reports
        4. Running the Automatic Database Diagnostic Monitor Reports
      9. Tuning Solutions
    5. 9 Using STATSPACK
      1. Installing STATSPACK
        1. Security of the PERFSTAT Account
        2. Post-installation
      2. Gathering Statistics
      3. Running the Statistics Report
      4. Managing the STATSPACK Data
      5. Deinstalling STATSPACK
    6. 10 Database Security and Auditing
      1. Non-Database Security
      2. Database Authentication Methods
        1. Database Authentication
        2. Database Administrator Authentication
        3. Operating System Authentication
        4. Network Authentication
        5. 3-Tier Authentication
        6. Client-Side Authentication
        7. Oracle Identity Management
        8. User Accounts
      3. Database Authorization Methods
        1. Profile Management
        2. System Privileges
        3. Object Privileges
        4. Creating, Assigning, and Maintaining Roles
        5. Using a VPD to Implement Application Security Policies
      4. Auditing
        1. Auditing Locations
        2. Statement Auditing
        3. Privilege Auditing
        4. Schema Object Auditing
        5. Fine-Grained Auditing
        6. Auditing-Related Data Dictionary Views
        7. Protecting the Audit Trail
      5. Data Encryption Techniques
  10. Part III High Availability
    1. 11 Real Application Clusters
      1. Overview of Real Application Clusters
        1. Hardware Configuration
        2. Software Configuration
        3. Network Configuration
        4. Disk Storage
      2. Installation and Setup
        1. Operating System Configuration
        2. Software Installation
      3. RAC Database Characteristics
        1. Server Parameter File Characteristics
        2. RAC-Related Initialization Parameters
        3. Dynamic Performance Views
      4. RAC Maintenance
        1. Starting Up a RAC Database
        2. Redo Logs in a RAC Environment
        3. Undo Tablespaces in a RAC Environment
        4. Failover Scenarios and TAF
        5. RAC Node Failure Scenario
        6. Tuning a RAC Node Database
        7. Tablespace Management
    2. 12 Backup and Recovery Options
      1. Capabilities
      2. Logical Backups
        1. The Data Pump Export/Import Process
      3. Physical Backups
        1. Offline Backups
        2. Online Backups
      4. Using Data Pump Export and Import
        1. Creating a Directory
        2. Data Pump Export Options
        3. Starting a Data Pump Export Job
      5. Data Pump Import Options
        1. Starting a Data Pump Import Job
        2. Comparing Data Pump Export/Import to Export/Import
        3. Implementing Offline Backups
        4. Implementing Online Backups
      6. Integration of Backup Procedures
        1. Integration of Logical and Physical Backups
        2. Integration of Database and Operating System Backups
    3. 13 Using Recovery Manager (RMAN)
      1. RMAN Features and Components
        1. RMAN Components
        2. RMAN vs. Traditional Backup Methods
        3. Backup Types
      2. Overview of RMAN Commands and Options
        1. Frequently Used Commands
        2. Setting Up a Repository
        3. Registering a Database
        4. Persisting RMAN Settings
        5. Initialization Parameters
        6. Data Dictionary and Dynamic Performance Views
      3. Backup Operations
        1. Full Database Backups
        2. Tablespace
        3. Datafiles
        4. Image Copies
        5. Control File, SPFILE Backup
        6. Archived Redo Logs
        7. Incremental Backups
        8. Incrementally Updated Backups
        9. Incremental Backup Block Change Tracking
        10. Backup Compression
        11. Using a Flash Recovery Area
        12. Validating Backups
      4. Recovery Operations
        1. Block Media Recovery
        2. Restoring a Control File
        3. Restoring a Tablespace
        4. Restoring a Datafile
        5. Restoring an Entire Database
        6. Validating Restore Operations
        7. Point in Time Recovery
      5. Miscellaneous Operations
        1. Cataloging Other Backups
        2. Catalog Maintenance
        3. REPORT and LIST
    4. 14 Oracle Data Guard
      1. Data Guard Architecture
        1. Physical vs. Logical Standby Databases
        2. Data Protection Modes
      2. LOG_ARCHIVE_DEST_n Parameter Attributes
      3. Creating the Standby Database Configuration
        1. Preparing the Primary Database
        2. Creating Logical Standby Databases
      4. Using Real-time Apply
        1. Managing Gaps in Archive Log Sequences
      5. Managing Roles—Switchovers and Failovers
        1. Switchovers
        2. Switchovers to Physical Standby Databases
        3. Switchovers to Logical Standby Databases
        4. Failovers to Physical Standby Databases
        5. Failovers to Logical Standby Databases
      6. Administering the Databases
        1. Startup and Shutdown of Physical Standby Databases
        2. Managing Datafiles in Data Guard Environments
        3. Performing DDL on a Logical Standby Database
    5. 15 Miscellaneous High Availability Features
      1. The flashback table Command
        1. Privileges Required
        2. Recovering Dropped Tables
        3. Flashing Back to SCN or Timestamp
      2. The flashback database Command
      3. Using LogMiner
        1. How LogMiner Works
        2. Extracting the Data Dictionary
        3. Analyzing One or More Redo Log Files
        4. LogMiner Features Introduced in Oracle Database 10g
      4. Online Object Reorganizations
        1. Creating Indexes Online
        2. Rebuilding Indexes Online
        3. Coalescing Indexes Online
        4. Rebuilding Index-Organized Tables Online
        5. Redefining Tables Online
  11. Part IV Networked Oracle
    1. 16 Oracle Net
      1. Overview of Oracle Net
        1. Connect Descriptors
        2. Service Names
        3. Replacing tnsnames.ora with the Oracle Internet Directory
        4. Listeners
      2. Using the Oracle Net Configuration Assistant
        1. Configuring the Listener
      3. Using the Oracle Net Manager
      4. Starting the Listener Server Process
      5. Controlling the Listener Server Process
        1. The Oracle Connection Manager
        2. Using Connection Manager
        3. Directory Naming with Oracle Internet Directory
      6. Using Easy Connect Naming
      7. Using Database Links
      8. Tuning Oracle Net
        1. Limiting Resource Usage
        2. Debugging Connection Problems
    2. 17 Managing Large Databases
      1. Creating Tablespaces in a VLDB Environment
        1. Bigfile Tablespace Basics
        2. Creating and Modifying Bigfile Tablespaces
        3. Bigfile Tablespace ROWID Format
        4. DBMS_ROWID and Bigfile Tablespaces
        5. Using DBVERIFY with Bigfile Tablespaces
        6. Bigfile Tablespace Initialization Parameter Considerations
        7. Bigfile Tablespace Data Dictionary Changes
      2. Advanced Oracle Table Types
        1. Index Organized Tables
        2. Global Temporary Tables
        3. External Tables
        4. Partitioned Tables
        5. Materialized Views
      3. Using Bitmap Indexes
        1. Understanding Bitmap Indexes
        2. Using Bitmap Indexes
        3. Using Bitmap Join Indexes
      4. Oracle Data Pump
        1. Data Pump Export
        2. Data Pump Import
        3. Using Transportable Tablespaces
    3. 18 Managing Distributed Databases
      1. Remote Queries
      2. Remote Data Manipulation: Two-Phase Commit
      3. Dynamic Data Replication
      4. Managing Distributed Data
        1. The Infrastructure: Enforcing Location Transparency
        2. Managing Database Links
        3. Managing Database Triggers
        4. Managing Materialized Views
        5. Using DBMS_MVIEW and DBMS_ADVISOR
        6. What Kind of Refreshes Can Be Performed?
        7. Using Materialized Views to Alter Query Execution Paths
      5. Managing Distributed Transactions
        1. Resolving In-Doubt Transactions
        2. Commit Point Strength
      6. Monitoring Distributed Databases
      7. Tuning Distributed Databases
  12. A Password Verify Function
  13. Index

Product information

  • Title: Oracle Database 10g DBA Handbook
  • Author(s): Kevin Loney, Bob Bryla
  • Release date: April 2005
  • Publisher(s): McGraw Hill Computing
  • ISBN: 9780071770439