Oracle Database 11g—Underground Advice for Database Administrators

Oracle Database 11g—Underground Advice for Database Administrators

by April C. Sims
Released April 2010
Publisher(s): Packt Publishing
ISBN: 9781849680004

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

A real-world DBA survival guide for Oracle 11g database implementations

  • A comprehensive handbook aimed at reducing the day-to-day struggle of Oracle 11g Database newcomers

  • Real-world reflections from an experienced DBA—what novice DBAs should really know

  • Implement Oracle's Maximum Availability Architecture with expert guidance

  • Extensive information on providing high availability for Grid Control

    • In Detail

    Today DBAs are expected to deploy and manage large databases with quality service and little to no downtime. The DBA's main focus is on increasing productivity and eliminating idle redundancy throughout the enterprise. However, there is no magic set of best practices or hard and fast rules that DBAs need to follow, and this can make life difficult. But if DBAs follow some basic approaches and best practices, tasks can be performed more efficiently and effectively.

    This survival guide offers previously unwritten underground advice for DBAs. The author provides extensive information to illuminate where you fit in, and runs through many of the tasks that you need to be watchful of, extensively covering solutions to the most common problems encountered by newcomers to the world of Oracle databases.

    The book will quickly introduce you to your job responsibilities, as well as the skills, and abilities needed to be successful as a DBA. It will show you how to overcome common problems and proactively prevent disasters by implementing distributed grid computing—scalable and robust—with the ability to redeploy or rearchitect when business needs change. Reduce downtime across your enterprise by standardizing hardware, software, tools, utilities, commands, and architectural components.

    This book will also help you in situations where you need to install Oracle Database 11g or migrate to new hardware making it compliant with a Maximum Availability Architecture. By the end of this book you will have learned a lot and gained confidence in your abilities. You will be armed with knowledge as to which tools are best used to accomplis h tasks while proactively moving towards an automated environment.

    An example-oriented guide to optimize a DBA's performance on 11g databases

    Table of contents

    1. Oracle Database 11g–Underground Advice for Database Administrators
      1. Table of Contents
      2. Oracle Database 11g—Underground Advice for Database Administrators
      3. Credits
      4. About the author
      5. About the reviewers
      6. Preface
        1. What this book covers
        2. What you need for this book
        3. Who this book is for
        4. Conventions
        5. Reader feedback
        6. Customer support
        7. Errata
        8. Piracy
        9. Questions
      7. 1. When to Step Away from the Keyboard
        1. Protecting and defending
        2. Choosing your tools
          1. Graphic-based, command-line Oracle tools and usage
        3. Staying away from dinosaurs
        4. Insisting on help
        5. What does a DBA do all day?
          1. Prioritizing tasks—daily, weekly, monthly, quarterly, or yearly
            1. Daily
            2. Weekly
            3. Monthly
            4. Quarterly
            5. Yearly
        6. SLAs: Why isn't the database down anymore?
        7. Avoiding major blunders
        8. Summary
      8. 2. Maintaining Oracle Standards
        1. Adapting to constant change
          1. Database concepts
          2. Multiple ORACLE_HOME(s)
          3. Keeping the environment clean
        2. Oracle's Optimal Flexible Architecture (OFA)
          1. 11g differences in the OFA standard
          2. XWINDOWS and GUI displays
        3. Automating day-to-day tasks
          1. DBMS_SCHEDULER
          2. OS cron utility executing a scheduled task on a Unix server
          3. OEM Console plus the Intelligent Agent
        4. 11g Diagnosability Framework
          1. Advisors and checkers
            1. Missing temp file resolution
        5. Environmental variables and scripting
          1. Guidelines for scripting
            1. Separating the configuration file
              1. Host commands relative location
            2. Separating the variable part of the script into its own configuration file
            3. Don't hardcode values; reference a configuration file and password file at runtime
            4. Putting variables at the top of the script with curly braces
            5. Moving functions to a centralized file to be reused
            6. Validating the use of the script
            7. Using SQL to generate code
            8. Helpful Unix commands
        6. Reducing operating system differences with common tools
        7. Configuration management, release management, and change control
          1. Configuration management
            1. Using OCM in disconnected mode with masking
            2. Mass deployment utility
          2. Release management
            1. DBA issues with patching
            2. Applying a patch without integrating MOS with OCM
            3. Using the new patch plan functionality with OCM installed and uploaded to MOS
            4. Change control
        8. Where, when, and who to call for help
          1. My Oracle Support
          2. Documentation library
        9. Summary
      9. 3. Tracking the Bits and Bytes
        1. Dump block
          1. Demonstration of data travel path
            1. Location of trace files
            2. Running dump block SQL statements
            3. Identifying files and blocks
              1. Legend for Trace Files:
            4. Tracking the SCN through trace files
              1. Single Row Insert
              2. Commit of a single row insert
              3. Single row update and corresponding undo block
              4. Commit of a single row update
        2. Oracle's RDBMS Log Miner utility
          1. Turn on archivelog mode
          2. Add supplemental logging
            1. Identification key logging
            2. Table-level supplemental logging
        3. Flash(back) Recovery Area (FRA)
        4. Automatic Undo Management (AUM)
          1. Identifying data in undo segments by flashing back to timestamp
        5. When to use Log Miner
          1. Identifying the data needed to restore
            1. SCN, timestamp, or log sequence number
            2. Pseudo column ORA_ROWSCN
            3. Flashback Transaction Query and Backout
          2. Enabling flashback logs
            1. Flashback Table
            2. Flashback Transaction Query with pseudo columns
            3. Flashback Transaction Backout
          3. Using strings to remove binary components
        6. Summary
      10. 4. Achieving Maximum Uptime
        1. Maximum Availability Architecture (MAA)
          1. Downtime—planned or unplanned
            1. MAA with commodity hardware: Case study
        2. Optimizing Oracle Database High Availability
          1. To archive or not to archive, you pick the mode
            1. Multiple archive destinations
            2. Moving the archive destination in an emergency
            3. Using a different disk device or disk mount
            4. Monitoring all hard drive space and Archivelog space
          2. Database compatibility parameter with spfile, pfile management
          3. Dealing with storage—RAID, SAME, ASM, and OMF
            1. RAID—Redundant Arrays of Inexpensive Disks
            2. SAME—Stripe and Mirror Everything
            3. ASM—Automatic Storage Management
              1. Recommendations for implementing ASM
          4. Mirrored files—control files and online redo logs
          5. Autoextending data files
          6. Auditing, log files, and max dump file size
            1. What is currently being audited?
            2. Auditing Session Activity
            3. Other logs to monitor
          7. Data dictionary healthcheck
          8. SQL*Net hardening, tuning, and troubleshooting
            1. Troubleshooting
            2. What can go wrong?
        3. Grid Control High Availability and Disaster Recovery
          1. Recommended installation for GC 10.2.0.5+
            1. Why should I install a separate database?
            2. Cookbook for silent install and configuring later
            3. Migrating GC repositories
            4. Transportable tablespace migrations
          2. Keeping the repository highly available
          3. Repository backups, restores, or imports
          4. MAA—repository on a physical standby database
          5. OMS and agents' high availability
            1. Cloning Management agents
          6. GC at a very large site
        4. Summary
      11. 5. Data Guard and Flashback
        1. Physical, snapshot, and logical standbys
          1. Physical standby database
          2. Snapshot standby database
          3. Logical standby database
          4. Commodity hardware and mixed environments
          5. What is Data Guard broker?
            1. What controls the Data Guard broker?
          6. Which tool is best?
            1. Start with the default configuration—maximum performance
          7. Utilizing multiple standby sites
          8. Protection modes and real-time apply
            1. Maximum performance (default)
            2. Maximum performance recommendations
            3. Maximum availability
            4. Maximum availability recommendations
            5. Maximum protection and recommendations
          9. Database states
          10. Manual failover with physical standby
          11. Manual failover with DGMGRL
          12. Flashback and guaranteed restore points
          13. Possible testing/recovery scenarios for Flashback and Data Guard
          14. Lost-write detection using a physical standby database
            1. Corruption, patch reversal, upgrades, or testing scenarios
            2. Reinstate failed primary scenario
            3. Troubleshooting the logical standby
            4. Options for resolving errors that stop the SQL Apply process
            5. How to skip a single transaction
          15. Active Data Guard and RMAN
          16. Other Data Guard notes and features
        2. Summary
      12. 6. Extended RMAN
        1. Recovery goals determine backup configuration
        2. Backup types and the default configuration
          1. Backup incremental levels
          2. Full backup
          3. Logical backup
          4. Oracle's suggested backup: What is missing?
            1. Controlfiles—an important part of backup and recovery
          5. How often should backups occur?
            1. Default configuration details
        3. Oracle's recommended backup strategy
          1. Issues with incremental merge backups
          2. Restore and recovery comparison
            1. Recommendations for Incremental Merge backup
            2. Calculating the FRA disk space needed
          3. Catalog versus controlfile RMAN recordkeeping and retention policies
            1. RMAN stored script and substitution variables
          4. Retention policies: Recovery window or redundancy?
            1. Not needed (OBSOLETE) versus not found (EXPIRED)
            2. What if I want to keep certain backups?
        4. Corruption detection
          1. Physical corruption
          2. Logical corruption
            1. Commands and utilities that detect corruption
              1. DBVERIFY
              2. RMAN VALIDATE or BACKUP VALIDATE command
              3. CTAS
              4. Export utility or Data Pump
            2. Which utility should be used?
            3. What should I do if corruption is detected?
        5. Data Recovery Adviser
        6. What does RMAN backup, restore, and recover?
          1. Possible interruptions to the recovery process
        7. What doesn't RMAN backup, restore, and recover?
          1. Online redo: Key to consistency
          2. User-managed backups
        8. What do I do before starting a restore and recovery?
          1. Find the most recent controlfile backup
            1. Find the backup you want to restore
            2. Restoring the controlfile
            3. Restoring the database
              1. Full recovery
              2. Point-in-Time Recovery
            4. Verifying that the recovery is complete
          2. Simplified recovery through resetlogs
        9. RMAN cloning and standbys—physical, snapshot, or logical
          1. Clones, DBIDs, and incarnations
          2. Creating a cloned database
          3. Post-cloning tasks
          4. Creating a standby database
            1. Physical standby
            2. Scheduled maintenance/cataloging of archivelogs
            3. Rolling forward a standby using incremental
            4. Rolling incremental for monthly updates to data warehouses
          5. The DBMS_BACKUP_RESTORE package
        10. Summary
      13. 7. Migrating to 11g: A Step-Ordered Approach
        1. Oracle net services
        2. Client compatibility (SQL*Net, JDBC, ODBC)
        3. RMAN binary, virtual/catalog, and database
        4. Grid Control—database repository and agents
        5. ASM, CFS, and RDBMS within an Oracle Grid infrastructure
        6. Recommended order of migration
        7. Installation of major versions, maintenance releases, and patches
          1. Release installation
          2. PatchSet installation—cloned ORACLE_HOME
        8. Database upgrade methods
          1. How long does the database upgrade take?
          2. Database Upgrade Assistant (DBUA)
        9. RMAN
          1. Using RMAN as part of a manual upgrade process
            1. Downgrading with RMAN
        10. Transportable Tablespaces (TTS)
          1. Preparatory steps for TTS migrations
          2. Using TTS for upgrades
          3. TTS cookbook
          4. Recreating an unrecoverable database with TTS
          5. Using TTS to add skipped read-only tablespaces during duplication
          6. Using TTS to merge two ASM databases into one
          7. Sharing read-only tablespaces between different databases with TTS
          8. Cross-platform migrations with a transportable database
          9. Physical and/or snapshot standbys
            1. Failing back to original version
          10. Transient logical standby: Rolling upgrades with minimal downtime
          11. Export/import or data pump migration
          12. Character set selection—UTF8
        11. Post-11g upgrade tasks
        12. Summary
      14. 8. 11g Tuning Tools
        1. Hardware load testing and forecasting
          1. Orion—Oracle I/O numbers calibration tool
          2. Calibrate I/O
          3. jMeter
        2. Monitoring hidden or underlying problems
          1. Proactive monitoring
          2. Automatic Diagnostic Database Monitor (ADDM)
          3. Automatic Workload Repository
          4. Active Session History (ASH)
          5. SQL Advisors
          6. STATSPACK
        3. Reactive diagnostic and tracing tools
        4. Bind peeking and Adaptive Cursor Sharing
        5. Gathering statistics
          1. Comparing statistics
          2. Restoring statistics history
          3. Knowing what needs to be tuned
          4. Tuning a single query
        6. SQL Plan Management (SPM)
          1. SQL Management Base
          2. Tracing and diagnostic events
            1. What is an event ?
            2. When should I set an event?
            3. What are the different event levels?
          3. Specific Trace events for performance problems
          4. Interpreting the resulting Event Trace file
        7. Upgrading the Optimizer
          1. Capturing and backing up execution plans and statistics
          2. SQL Tuning Sets
          3. Stored Outlines
          4. Capturing and backing up Optimizer Statistics
          5. Upgrade the database to 11g
          6. Capturing new execution plans and new statistics
          7. Evolving or verifying new plans that execute better than the 10g versions
        8. Summary
      15. Index

    Product information

    • Title: Oracle Database 11g—Underground Advice for Database Administrators
    • Author(s): April C. Sims
    • Release date: April 2010
    • Publisher(s): Packt Publishing
    • ISBN: 9781849680004