Oracle Database 12c DBA Handbook

Oracle Database 12c DBA Handbook

by Bob Bryla
Released July 2015
Publisher(s): Oracle Press
ISBN: 9780071798792

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

The definitive reference for every Oracle DBA—completely updated for Oracle Database 12c

Oracle Database 12c DBA Handbook is the quintessential tool for the DBA with an emphasis on the big picture—enabling administrators to achieve effective and efficient database management. Fully revised to cover every new feature and utility, this Oracle Press guide shows how to harness cloud capability, perform a new installation, upgrade from previous versions, configure hardware and software, handle backup and recovery, and provide failover capability. The newly revised material features high-level and practical content on cloud integration, storage management, performance tuning, information management, and the latest on a completely revised security program.

  • Shows how to administer a scalable, flexible Oracle enterprise database
  • Includes new chapters on cloud integration, new security capabilities, and other cutting-edge features
  • All code and examples available online

Table of contents

  1. Cover
  2. Title Page
  3. Copyright Page
  4. Dedication
  5. Contents at a Glance
  6. Contents
  7. Acknowledgments
  8. Introduction
  9. 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. Summary
    2. 2 Upgrading to Oracle Database 12c
      1. Choosing an Upgrade Method
      2. Before Upgrading
      3. Using the Database Upgrade Assistant
      4. Performing a Manual Direct Upgrade
      5. Using Data Pump Export and Import
        1. Export and Import Versions to Use
        2. Performing the Upgrade
      6. Using the Data-Copying Method
      7. After Upgrading
      8. Summary
    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. Summary
    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
      3. Summary
  10. 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
        1. Implementing the Database Resource Manager
        2. Sizing Database Objects
        3. Using Global 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
      7. Summary
    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_OBJECT_USAGE
        9. DBA_ALERT_HISTORY
        10. V$ALERT_TYPES
        11. V$UNDOSTAT
        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. Managing Alert and Trace Files with ADR
        7. 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. Cloud Control and Monitoring
      10. Summary
    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 Transaction Backout
        4. Flashback Table
        5. Flashback Version Query
        6. Flashback Transaction Query
        7. Flashback Data Archive
        8. Flashback and LOBs
      5. Migrating to Automatic Undo Management
      6. Summary
    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. Managing SGA Pools
        2. Specifying the Size of the SGA
        3. Using the Cost-Based Optimizer
      4. Tuning Data Access
        1. Identifying Chained Rows
        2. Using Index-Organized Tables
        3. Tuning Issues for 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. Reducing Network Traffic
        1. Replication of Data Using Materialized Views
        2. Using Remote Procedure Calls
      7. Using the Automatic Workload Repository
        1. Managing Snapshots
        2. Managing Baselines
        3. Generating AWR Reports
        4. Running the Automatic Database Diagnostic Monitor Reports
        5. Using Automatic SQL Tuning Advisor
      8. Performance Tuning in a Multitenant Environment
        1. Tuning Methodology
        2. Sizing the CDB
        3. Using Memory Advisors
        4. Leveraging AWR Reports
        5. Using the SQL Tuning Advisor
      9. Managing Resource Allocation Within a PDB
        1. Using Shares to Manage Inter-PDB Resources
        2. Creating and Modifying Resource Manager Plans
      10. Performing Database Replay
        1. Analyze the Source Database Workloads
        2. Capture Source Database Workloads
        3. Process Workloads on Target System
        4. Replay Workloads on Target CDB
        5. Verify Replay Results
      11. Summary
    5. 9 In-Memory Option
      1. Overview of Oracle In-Memory Option
        1. System Requirements and Setup
        2. In-Memory Case Study
      2. Data Dictionary Views
        1. V$IM_SEGMENTS
        2. V$INMEMORY_AREA
        3. V$SGA
      3. Summary
    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. 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
        1. DBMS_CRYPTO Package
        2. Transparent Data Encryption
      6. Summary
    7. 11 Multitenant Database Architecture
      1. Understanding the Multitenant Architecture
        1. Leveraging Multitenant Databases
        2. Understanding Multitenant Configurations
      2. Provisioning in a Multitenant Environment
        1. Understanding Pluggable Database Provisioning
        2. Configuring and Creating a CDB
        3. Understanding New Data Dictionary Views: The Sequel
        4. Creating PDBs
        5. Unplugging and Dropping a PDB
      3. Managing CDBs and PDBs
        1. Understanding CDB and PDB Service Names
        2. Connecting to a CDB or PDB Using SQL Developer
        3. Creating Services for CDBs or PDBs
        4. Switching Connections Within a CDB
        5. Starting Up and Shutting Down CDBs and PDBs
        6. Changing Parameters in a CDB
        7. Manage Permanent and Temporary Tablespaces in CDB and PDBs
      4. Multitenant Security
        1. Managing Common and Local Users
        2. Managing Common and Local Privileges
        3. Managing Common and Local Roles
        4. Enabling Common Users to Access Data in Specific PDBs
      5. Backup and Recovery in Multitenant Environments
        1. Performing Backups of a CDB and All PDBs
        2. Backing Up CDBs
        3. Backing Up PDBs
        4. Recovering from PDB Datafile Loss
        5. Using the Data Recovery Advisor
        6. Identifying Block Corruption
        7. Duplicating PDBs Using RMAN
      6. Summary
  11. Part III High Availability
    1. 12 Real Application Clusters
      1. Overview of Real Application Clusters
        1. Hardware Configuration
        2. Software Configuration
        3. Network Configuration
        4. Disk Storage
      2. RAC Characteristics
        1. Server Parameter File Characteristics
        2. RAC-Related Initialization Parameters
        3. Dynamic Performance Views
      3. RAC Maintenance
        1. Starting Up a RAC
        2. Redo Logs in a RAC Environment
        3. Undo Tablespaces in a RAC Environment
        4. Failover Scenarios and TAF
        5. Tuning a RAC Node
      4. Summary
    2. 13 Backup and Recovery Options
      1. Backup Capabilities
      2. Logical Backups
      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
        4. Data Pump Import Options
      5. Implementing Offline Backups
      6. Implementing Online Backups
        1. Getting Started
        2. Performing Online Database Backups
      7. Integration of Backup Procedures
        1. Integration of Logical and Physical Backups
        2. Integration of Database and Operating System Backups
      8. Summary
    3. 14 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. Running SQL Commands in RMAN
        2. Frequently Used Commands
        3. Setting Up a Repository
        4. Registering a Database
        5. Persisting RMAN Settings
        6. Initialization Parameters
        7. Data Dictionary and Dynamic Performance Views
      3. Backup Operations
        1. Full Database Backups
        2. Tablespace Backups
        3. Datafile Backups
        4. Image Copy Backups
        5. Control File, SPFILE Backup
        6. Archived Redo Log Backup
        7. Incremental Backups
        8. Incrementally Updated Backups
        9. Incremental Backup Block Change Tracking
        10. Using a Fast Recovery Area
        11. Validating Backups
      4. Recovery Operations
        1. Block Media Recovery
        2. Restoring a Control File
        3. Restoring a Tablespace
        4. Restoring a Table
        5. Restoring a Datafile
        6. Restoring an Entire Database
        7. Validating Restore Operations
        8. Point-in-Time Recovery
        9. Data Recovery Advisor
      5. Miscellaneous Operations
        1. Cataloging Other Backups
        2. Catalog Maintenance
        3. REPORT and LIST
      6. Summary
    4. 15 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
      5. Managing Gaps in Archive Log Sequences
      6. Managing Roles: Switchovers and Failovers
        1. Switchovers
        2. Failovers
      7. Administering the Databases
        1. Startup and Shutdown of Physical Standby Databases
        2. Opening Physical Standby Databases in Read-Only Mode
        3. Managing Datafiles in Data Guard Environments
        4. Performing DDL on a Logical Standby Database
      8. Summary
    5. 16 Miscellaneous High Availability Features
      1. Recovering Dropped Tables Using Flashback Drop
      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. Online Object Reorganization
        1. Creating Indexes Online
        2. Rebuilding Indexes Online
        3. Coalescing Indexes Online
        4. Rebuilding Index-Organized Tables Online
        5. Redefining Tables Online
        6. Moving a Datafile Online
      5. Summary
  12. Part IV Networked Oracle
    1. 17 Oracle Net
      1. Overview of Oracle Net
        1. Connect Descriptors
        2. Net Service Names
        3. Replacing tnsnames.ora with Oracle Internet Directory
        4. Listeners
      2. Using the Oracle Net Configuration Assistant
        1. Configuring the Listener
        2. Naming Methods Configuration
        3. Local Net Service Name Configuration
        4. Directory Usage Configuration
      3. Using the Oracle Net Manager
      4. Starting the Listener Server Process
      5. Controlling the Listener Server Process
      6. The Oracle Connection Manager
        1. Using the Oracle Connection Manager
        2. Configuring the Oracle Connection Manager
        3. Using the Connection Manager Control Utility (CMCTL)
      7. Directory Naming with Oracle Internet Directory
        1. Oracle Internet Directory Architecture
        2. Setting Up an Oracle Internet Directory
      8. Using Easy Connect Naming
      9. Using Database Links
      10. Tuning Oracle Net
        1. Limiting Resource Usage
        2. Using Compression
        3. Debugging Connection Problems
      11. Summary
    2. 18 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. Summary
    3. 19 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
      8. Summary
  13. Index

Product information

  • Title: Oracle Database 12c DBA Handbook
  • Author(s): Bob Bryla
  • Release date: July 2015
  • Publisher(s): Oracle Press
  • ISBN: 9780071798792