Beginning Oracle Database 12c Administration: From Novice to Professional, Second Edition

Beginning Oracle Database 12c Administration: From Novice to Professional, Second Edition

by Ignatius Fernandez
Released July 2015
Publisher(s): Apress
ISBN: 9781484201930

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

Beginning Oracle Database 12c Administration is your entry point into a successful and satisfying career as an Oracle Database Administrator.

The chapters of this book are logically organized into four parts closely tracking the way your database administration career will naturally evolve. Part 1 "Database Concepts" gives necessary background in relational database theory and Oracle Database concepts, Part 2 "Database Implementation" teaches how to implement an Oracle database correctly, Part 3 "Database Support" exposes you to the daily routine of a database administrator, and Part 4 "Database Tuning" introduces the fine art of performance tuning.

Beginning Oracle Database 12c Administration provides information that you won't find in other books on Oracle Database. You'll discover not only technical information, but also guidance on work practices that are as vital to your success as are your technical skills. The author's favorite chapter is "The Big Picture and the Ten Deliverables." (It is the editor’s favorite chapter too!) If you take the lessons in that chapter to heart, you can quickly become a much better Oracle database administrator than you ever thought possible.

You will grasp the key aspects of theory behind relational database management systems and learn how to:

  • Install and configure an Oracle database, and ensure that it’s properly licensed;

  • Execute common management tasks in a Linux environment;

  • Defend against data loss by implementing sound backup and recovery practices; and

  • Improve database and query performance.

Table of contents

  1. Cover
  2. Title
  3. Copyright
  4. Dedication
  5. About IOUG Press
  6. Contents at a Glance
  7. Contents
  8. About the Author
  9. About the Technical Reviewer
  10. Acknowledgments
  11. Foreword to the First Edition
  12. Introduction
  13. Part I: Database Concepts
    1. Chapter 1: Relational Database Management Systems
      1. A First Look at Oracle Database 12c
      2. What Is a Database?
      3. What Is a Relational Database?
        1. The Secret Sauce
        2. Examples of Relational Operations
        3. Efficiency of Relational Operators
        4. Query Optimization
      4. What Is a Database Management System?
        1. Transaction Management
        2. The Atomicity Property of Transactions
        3. The Consistency Property of Transactions
        4. The Isolation Property of Transactions
        5. The Durability Property of Transactions
        6. Data Integrity
        7. Data Security
      5. What Makes a Relational Database Management System Relational?
      6. Summary
      7. Further Reading
    2. Chapter 2: SQL and PL/SQL by Example
      1. Railroad Diagrams
      2. Types of SQL
        1. Data Definition Language
        2. Database Manipulation Language
      3. Embedded SQL
      4. SQL*Plus and SQL Developer
      5. Criticisms of SQL
        1. Duplicates
        2. Redundancy
        3. Nullable Data Items
      6. Introduction to PL/SQL
      7. Summary
      8. Exercises
      9. Further Reading
    3. Chapter 3: Oracle Architecture
      1. Database vs. Instance
      2. Database
        1. Software
        2. Configuration Files
        3. Data Files
        4. Temporary Files
        5. Redo Log Files
        6. Archived Redo Log Files
        7. Control File
        8. Event Logs
        9. Database Backups
      3. Instance
        1. System Global Area
        2. Buffer Cache
        3. Shared Pool
        4. Log Buffer
        5. Server Processes
        6. Background Processes
      4. One-to-Many Configuration
      5. Many-to-One Architecture
      6. Life Cycle of a Database Session
      7. Summary
      8. Exercises
      9. Further Reading
  14. Part II: Database Implementation
    1. Chapter 4: Planning
      1. Licensing
        1. Practical Example
        2. Free to Download, Free to Learn, Unlimited Evaluation
        3. Database Editions
      2. Architectural Choices
        1. Dedicated Server
        2. Shared Server
        3. Real Application Clusters
        4. Standby Database
        5. Maximum Availability Architecture (MAA)
      3. Sizing
        1. Disk Sizing
        2. Other Disk Considerations
        3. Memory Sizing
        4. CPU Sizing
        5. Network Sizing
      4. Modern Options
        1. Engineered Systems
        2. Cloud Computing
      5. Summary
      6. Further Reading
    2. Chapter 5: Software Installation
      1. Oracle Technology Network
      2. The Forgotten Manuals
      3. Prerequisites and Preinstallation Requirements
      4. Client Installations
      5. Instant Client
      6. SQL Developer
      7. Server Installations
      8. Oracle Database Examples
      9. Summary
    3. Chapter 6: Database Creation
      1. Creating and Configuring a Listener
      2. Creating and Configuring a Database
        1. Common Options
        2. Initialization Parameters
        3. Data Files
        4. Control Files
        5. Redo Log Groups
      3. Basic Database Administration Tasks
        1. Stopping the Database
        2. Stopping the Listener
        3. Starting the Listener
        4. Starting the Database
        5. Enterprise Manager Database Express
      4. Summary
    4. Chapter 7: Physical Database Design
      1. Indexes
        1. Unique vs. Non-Unique Indexes
        2. Concatenated Indexes
        3. Function-Based Indexes
        4. Structure of an Index
        5. What Indexes to Create?
        6. Index-Organized Tables
        7. Advanced Topics
      2. Partitioning
        1. Advantages of Partitioning
        2. List Partitioning
        3. Range Partitioning
        4. Interval Partitioning
        5. Hash Partitioning
        6. Reference Partitioning
        7. Composite Partitioning
        8. Local and Global Indexes
        9. Partitioned Views
      3. Denormalization and Materialized Views
      4. Clusters
      5. Summary
    5. Chapter 8: User Management and Data Loading
      1. Schemas
      2. User Management
        1. Creating Users
        2. Giving Permissions to Users
        3. Revoking Permissions Granted to Users
        4. Modifying User Attributes
        5. Removing Users
      3. Data Loading
        1. The Export and Import Utilities
        2. The Data Pump Utilities
        3. SQL*Loader
      4. Summary
  15. Part III: Database Support
    1. Chapter 9: Taking Control
      1. Enterprise Manager
      2. SQL Developer
      3. Remote Diagnostic Agent
      4. Dictionary Tables and Views
      5. Third-Party Tools
      6. Summary
      7. Exercises
    2. Chapter 10: Monitoring
      1. Monitoring Database Availability
      2. Monitoring Changes
      3. Monitoring Security
      4. Monitoring Backups
      5. Monitoring Growth
      6. Monitoring Workload
      7. Monitoring Performance
      8. Monitoring Capacity
        1. Third-Party Tools
      9. Summary
      10. Exercises
      11. Further Reading
    3. Chapter 11: Fixing Problems
      1. Systematic Five-Step Problem-Solving Method
      2. The Book We All Want and Best Practices for Problem Management
      3. Real-Life Example: Unresponsive Listener
        1. Define the Problem
        2. Investigate and Analyze the Problem
        3. Solve and Implement the Problem
        4. One Week Later
        5. Opportunities for Improvement
      4. Incident Management vs. Problem Management
      5. Internet Resources
      6. Working with Oracle Support
        1. Remote Diagnostic Agent (RDA)
        2. ORAchk Oracle Configuration Audit Tool
        3. Automatic Diagnostic Repository (ADR)
      7. Error Codes
      8. Four Errors
        1. ORA-01555: Snapshot Too Old
        2. ORA-00060: Deadlock Detected
        3. ORA-00600: Internal Error Code
        4. ORA-07445: Exception Encountered
      9. Summary
      10. Exercises
    4. Chapter 12: Backups
      1. Why Do You Need Backups?
      2. Types of Backup
        1. Tape Backups vs. Disk Backups
        2. Full Backups vs. Partial Backups
        3. Level 0 Backups vs. Level 1 Backups
        4. Physical Backups vs. Logical Backups
        5. Consistent Backups vs. Inconsistent Backups
        6. Hot vs. Cold Backups
        7. Oracle-Managed Backups vs. User-Managed Backups
      3. Practical Demonstration: Physical Backups
      4. Practical Demonstration: Logical Backups
      5. Common RMAN Commands
      6. Horror Stories
      7. Summary
      8. Exercises
    5. Chapter 13: Recovery
      1. Horror Stories
      2. Types of Recovery
        1. Restore vs. Recover
        2. Full Recovery vs. Partial Recovery
        3. Complete Recovery vs. Incomplete Recovery
        4. Traditional vs. Flashback
        5. Physical Recovery vs. Logical Recovery
      3. Flashback Technology
        1. Flashback Query
        2. Flashback Versions
        3. Flashback Transaction
        4. Flashback Table
        5. Flashback Drop
        6. Flashback Data Archive
        7. Flashback Database
      4. LogMiner
      5. Data Recovery Advisor
      6. Documentation and Testing
      7. Summary
      8. Exercises
    6. Chapter 14: Database Maintenance
      1. The Maintenance Plan
      2. Backups
        1. Generic Best Practices for Database Backups
        2. Best Practices for Oracle Database Backups
      3. Statistics
      4. Archiving and Purging
        1. Rebuilding
      5. Log File Maintenance
      6. Auditing
      7. User Management
      8. Capacity Management
        1. Time Series
      9. Patching
      10. Summary
      11. Exercises
    7. Chapter 15: The Big Picture and the Ten Deliverables
      1. An Instructive Job Interview
      2. How I Became a DBA
      3. ITIL
        1. The Big Picture
        2. IT Service Management Processes
      4. Start with the End in Mind: The Ten Deliverables
      5. The Book You Really Need and the Art of the SOP
        1. Benefits of SOPs
        2. Structure of an SOP
        3. Suggested SOPs
      6. Summary
      7. Exercises
      8. Further Reading
  16. Part IV: Database Tuning
    1. Chapter 16: Database Tuning
      1. Using a Systematic Five-Step Tuning Method
      2. Analyzing DB Time
      3. Understanding the Oracle Wait Interface
        1. V$SYSSTAT and V$SESSTAT
        2. V$SESSION_EVENT and V$SYSTEM_EVENT
      4. Mining the Statspack Repository
      5. Using the Statspack Report
      6. Summary
      7. Exercises
      8. Further Reading
    2. Chapter 17: SQL Tuning
      1. Defining Efficiency
      2. Identifying Inefficient SQL Statements
      3. Understanding the Causes of Inefficient SQL
      4. Ways to Improve SQL
        1. Indexes
        2. Hints
        3. Statistics
      5. Tuning by Example
        1. Creating and Populating the Tables
        2. Establishing a Baseline
        3. Examining the Query Plan
        4. Indexes and Statistics
        5. Using SQL Access Advisor
        6. Optimizer Hints
        7. Extreme Tuning
        8. But Wait, There’s More!
      6. Summary
      7. Exercises
  17. Index
  18. General

Product information

  • Title: Beginning Oracle Database 12c Administration: From Novice to Professional, Second Edition
  • Author(s): Ignatius Fernandez
  • Release date: July 2015
  • Publisher(s): Apress
  • ISBN: 9781484201930