You are previewing Pro Oracle Database 12c Administration, Second Edition.
O'Reilly logo
Pro Oracle Database 12c Administration, Second Edition

Book Description

Pro Oracle Database 12c Administration is a book focused on results. Author Darl Kuhn draws from a well of experience over a decade deep to lay out real-world techniques that lead to success as an Oracle Database administrator. He gives clear explanations on how to perform critical tasks. He weaves in theory where necessary without bogging you down in unneeded detail. He is not afraid to take a stand on how things should be done. He won't leave you adrift in a sea of choices, showing you three ways to do something and then walking away.

Database administration isn't about passing a certified exam, or about pointing-and-clicking your way through a crisis. Database administration is about applying the right solution at the right time, about avoiding risk, about making robust choices that get you home each night in time for dinner with your family. If you have "buck stops here" responsibility for an Oracle database, then Pro Oracle Database 12c Administration is the book you need to help elevate yourself to the level of Professional Oracle Database Administrator.

  • Covers multi-tenant container and pluggable database implementation and management

  • Condenses and organizes the core job of a database administrator into one volume.

  • Takes a results-oriented approach to getting things done.

  • Lays a foundation upon which to build a senior level of expertise

What you'll learn

  • Create a stable environment consistent across all databases that you manage

  • Manage pluggable and multi-tenant databases

  • Take care of job #1: backing up, and then recovering when needed

  • Manage users and objects, and the security between them

  • Do battle with "large"—large databases and large objects

  • Move and distribute data using Data Pump, materialized views, external tables

  • Automate critical jobs and tackle database troubleshooting problems

Who this book is for

Pro Oracle Database 12c Administration is aimed at new database administrators who aspire to senior positions in which employers and customers trust you to work independently, and with a "buck stops here" attitude.

Table of Contents

  1. Title Page
  2. Dedication
  3. Contents at a Glance
  4. Contents
  5. About the Author
  6. About the Technical Reviewers
  7. Acknowledgments
  8. Introduction
  9. CHAPTER 1: Installing the Oracle Binaries
    1. Understanding the OFA
    2. Installing Oracle
    3. Installing with a Copy of an Existing Installation
    4. Upgrading Oracle Software
    5. Reinstalling After Failed Installation
    6. Applying Interim Patches
    7. Installing Remotely with the Graphical Installer
    8. Summary
  10. CHAPTER 2: Implementing a Database
    1. Setting OS Variables
    2. Creating a Database
    3. Configuring and Implementing the Listener
    4. Creating a Password File
    5. Starting and Stopping the Database
    6. Using the dbca to Create a Database
    7. Dropping a Database
    8. How Many Databases on One Server?
    9. Understanding Oracle Architecture
    10. Summary
  11. CHAPTER 3: Configuring an Efficient Environment
    1. Customizing Your OS Command Prompt
    2. Customizing Your SQL Prompt
    3. Creating Shortcuts for Frequently Used Commands
    4. Rerunning Commands Quickly
    5. Developing Standard Scripts
    6. Organizing Scripts
    7. Summary
  12. CHAPTER 4: Tablespaces and Data Files
    1. Understanding the First Five
    2. Understanding the Need for More
    3. Creating Tablespaces
    4. Renaming a Tablespace
    5. Controlling the Generation of Redo
    6. Changing a Tablespace’s Write Mode
    7. Dropping a Tablespace
    8. Using Oracle Managed Files
    9. Creating a Bigfile Tablespace
    10. Enabling Default Table Compression within a Tablespace
    11. Displaying Tablespace Size
    12. Altering Tablespace Size
    13. Toggling Data Files Offline and Online
    14. Renaming or Relocating a Data File
    15. Summary
  13. CHAPTER 5: Managing Control Files, Online Redo Logs, and Archiving
    1. Managing Control Files
    2. Managing Online Redo Logs
    3. Implementing Archivelog Mode
    4. Summary
  14. CHAPTER 6: Users and Basic Security
    1. Managing Default Users
    2. Creating Users
    3. Modifying Passwords
    4. Logging In as a Different User
    5. Modifying Users
    6. Dropping Users
    7. Enforcing Password Security and Resource Limits
    8. Managing Privileges
    9. Summary
  15. CHAPTER 7: Tables and Constraints
    1. Understanding Table Types
    2. Understanding Data Types
    3. Creating a Table
    4. Modifying a Table
    5. Displaying Table DDL
    6. Dropping a Table
    7. Undropping a Table
    8. Removing Data from a Table
    9. Viewing and Adjusting the High-Water Mark
    10. You need to be aware of a couple of performance-related issues regarding the high-water mark
    11. Creating a Temporary Table
    12. Creating an Index-Organized Table
    13. Managing Constraints
    14. Summary
  16. CHAPTER 8: Indexes
    1. Deciding When to Create an Index
    2. Planning for Robustness
    3. Creating Indexes
    4. Maintaining Indexes
    5. Indexing Foreign Key Columns
    6. Summary
  17. CHAPTER 9: Views, Synonyms, and Sequences
    1. Implementing Views
    2. Managing Synonyms
    3. Managing Sequences
    4. Summary
  18. CHAPTER 10: Data Dictionary Fundamentals
    1. Data Dictionary Architecture
    2. A Different View of Metadata
    3. A Few Creative Uses of the Data Dictionary
    4. Summary
  19. CHAPTER 11: Large Objects
    1. Describing LOB Types
    2. Illustrating LOB Locators, Indexes, and Chunks
    3. Distinguishing Between BasicFiles and SecureFiles
    4. Creating a Table with a LOB Column
    5. Maintaining LOB Columns
    6. Implementing SecureFiles Advanced Features
    7. Loading LOBs
    8. Measuring LOB Space Consumed
    9. Reading BFILEs
    10. Summary
  20. CHAPTER 12: Partitioning: Divide and Conquer
    1. What Tables Should Be Partitioned?
    2. Creating Partitioned Tables
    3. Maintaining Partitions
    4. Partitioning Indexes
    5. Partition Pruning
    6. Summary
  21. CHAPTER 13: Data Pump
    1. Data Pump Architecture
    2. Getting Started
    3. Exporting and Importing with Granularity
    4. Transferring Data
    5. Features for Manipulating Storage
    6. Filtering Data and Objects
    7. Common Data Pump Tasks
    8. Interactive Command Mode
    9. Monitoring Data Pump Jobs
    10. Data Pump Legacy Mode
    11. Summary
  22. CHAPTER 14: External Tables
    1. SQL*Loader vs. External Tables
    2. Loading CSV Files into the Database
    3. Performing Advanced Transformations
    4. Viewing Text Files from SQL
    5. Unloading and Loading Data Using an External Table
    6. Summary
  23. CHAPTER 15: Materialized Views
    1. Understanding MVs
    2. Creating Basic Materialized Views
    3. Going Beyond the Basics
    4. Modifying MVs
    5. Managing MV Logs
    6. Refreshing MVs
    7. Monitoring MV Refreshes
    8. Creating Remote MV Refreshes
    9. Managing MVs in Groups
    10. Summary
  24. CHAPTER 16: User-Managed Backup and Recovery
    1. Implementing a Cold-Backup Strategy for a Noarchivelog Mode Database
    2. Making a Cold Backup of an Archivelog Mode Database
    3. Implementing a Hot Backup Strategy
    4. Performing a Complete Recovery of an Archivelog Mode Database
    5. Performing an Incomplete Recovery of an Archivelog Mode Database
    6. Summary
  25. CHAPTER 17: Configuring RMAN
    1. Understanding RMAN
    2. Starting RMAN
    3. RMAN Architectural Decisions
    4. Segueing from Decision to Action
    5. Summary
  26. CHAPTER 18: RMAN Backups and Reporting
    1. Preparing to Run RMAN Backup Commands
    2. Running Backups
    3. Taking Backups of Pluggable Databases
    4. Creating Incremental Backups
    5. Checking for Corruption in Data Files and Backups
    6. Using a Recovery Catalog
    7. Logging RMAN Output
    8. RMAN Reporting
    9. Summary
  27. CHAPTER 19: RMAN Restore and Recovery
    1. Determining if Media Recovery Is Required
    2. Determining What to Restore
    3. Using RMAN to Stop/Start Oracle
    4. Complete Recovery
    5. Restoring Archive Redo Log Files
    6. Restoring a Control File
    7. Restoring the spfile
    8. Incomplete Recovery
    9. Flashing Back a Table
    10. Flashing Back a Database
    11. Restoring and Recovering to Different Server
    12. Summary
  28. CHAPTER 20: Oracle Secure Backup
    1. OSB Editions and Features
    2. OSB Terminology
    3. Download and Installation
    4. Command-Line Access to OSB
    5. OSB Configuration
    6. Database Backup
    7. Database Restore
    8. Filesystem Backup
    9. Filesystem Restore
    10. OSB Job Monitoring
    11. Virtual Test Devices
    12. Oracle Database Backup in the Cloud
    13. OSB Software Upgrades
    14. Summary
  29. CHAPTER 21: Automating Jobs
    1. Automating Jobs with Oracle Scheduler
    2. Oracle Scheduler vs. cron
    3. Automating Jobs via cron
    4. Examples of Automated DBA Jobs
    5. Summary
  30. CHAPTER 22: Database Troubleshooting
    1. Quickly Triaging
    2. Identifying Bottlenecks via OS Utilities
    3. Finding Resource-Intensive SQL Statements
    4. Detecting and Resolving Locking Issues
    5. Resolving Open-Cursor Issues
    6. Troubleshooting Undo Tablespace Issues
    7. Handling Temporary Tablespace Issues
    8. Summary
  31. CHAPTER 23: Pluggable Databases
    1. Understanding Pluggable Architecture
    2. Creating a CDB
    3. Administrating the Root Container
    4. Creating a Pluggable Database within a CDB
    5. Administrating Pluggable Databases
    6. Summary
  32. Index