OCP: Oracle Database 12c Administrator Certified Professional Study Guide

Robert G. Freeman, OCA, OCP, has worked with Oracle database products for over 20 years. He is the author of Oracle Recovery Manager and the Oracle OCP Exam. He frequently speaks on Oracle database topics. Charles A. Pack, OCA, OCP, has over 20 years of IT experience. He taught Oracle DBA classes at Florida Community College, Jacksonville, and presents at universities and professional organizations. Both were coauthors of Sybex s OCP: Oracle Database 11g Administrator Certified Professional Study Guide.

Introduction xxi Assessment Test xxxiii Chapter 1 Performing Oracle User-Managed Backups 1 Oracle Database Data Protection Options 2 What Kind of Failures Can Happen to a Database 2 Physical and Logical Backups 3 Tools for Backup and Recovery 3 Oracle MAA Recommendations 4 Understanding the Oracle Database as It Relates to Backup and Recovery 5 Oracle Processes Related to Backup and Recovery 6 Oracle Memory Structures Related to Backup and Recovery 8 The Oracle Data Dictionary 8 Oracle Data Files and Tablespaces 11 Redo Logs 12 Control File 14 Parameter Files 15 NOARCHIVELOG and ARCHIVELOG Modes 17 The Oracle Instance and the Oracle Database 18 Configuring the Database for Backup and Recovery 26 Configuring for ARCHIVELOG Mode 26 Putting the Database in ARCHIVELOG Mode 29 Using ARCHIVELOG Mode Data Dictionary Views 33 Performing Oracle Offline Backups 36 Performing Oracle Online Backups 40 The Mechanics of Online Backups 40 Backing Up the Control File 46 Creating a Backup Control File 46 Creating a Trace File with the Create CONTROLFILE Command in It 47 Summary 49 Exam Essentials 50 Review Questions 51 Chapter 2 Performing Oracle User-Managed Database Recoveries 57 Performing a Recovery in NOARCHIVELOG Mode 58 Performing a Full Database Recovery in ARCHIVELOG Mode 62 Preparing for the Recovery 62 Recovering the Database 64 Performing User-Based Incomplete Recoveries 77 Requirements for and Mechanics of an Incomplete Recovery 77 Preparing for an Incomplete Recovery 77 Performing an Incomplete Recovery 79 Performing Other Types of Recoveries 85 Recovering from the Loss of a Tempfile 85 Recovering from the Loss of an Online Redo Log Group 85 Recovering from the Loss of a Control File 89 Recovering from the Loss of the Password File 93 Recovering from the Loss of Everything 93 Summary 94 Exam Essentials 95 Review Questions 96 Chapter 3 Configuring and Backing Up Oracle Databases Using RMAN 105 Why Use RMAN? 107 Exploring the RMAN Architecture 108 Getting Started with RMAN 110 Starting the RMAN Interface 110 The SYSBACKUP Privilege 111 The RMAN Command Line 113 The RMAN Command Prompt 114 Calling SQL from the RMAN Command Prompt 114 Configuring RMAN 115 The Fast Recovery Area 116 RMAN Persistent Configuration Settings 119 Unique RMAN Configuration Settings 120 Preparing RMAN for Use 121 Backing Up Your Database with RMAN 136 Using the RMAN Command Line 136 Types of RMAN Backups 141 RMAN Offline Backups 145 RMAN Online Backups 145 RMAN Incremental Backups 151 RMAN Incrementally Updated Backups 154 RMAN Multisection Backups 155 RMAN Backup of Archived Redo Logs 156 RMAN Backup of the Spfile and Control Files 157 Backing Up RMAN Backup Sets 158 Backing Up the RMAN FRA 158 Creating Archival Backups 159 Summary 159 Exam Essentials 160 Review Questions 161 Chapter 4 Using the RMAN Recovery Catalog 167 Introducing the Recovery Catalog 168 Creating the Recovery Catalog User and Schema Objects 170 Using a Recovery Catalog 172 Connecting to the Recovery Catalog from RMAN 173 Registering the Target Database with the Recovery Catalog 174 Unregistering a Database 174 Using Scripts in the RMAN Recovery Catalog 175 Executing External Scripts 175 Creating Stored Scripts 176 Using Script Substitution Variables 177 Maintaining the Recovery Catalog 177 Dropping the Recovery Catalog 178 Upgrading the Recovery Catalog 178 Synchronizing the Recovery Catalog 178 Export and Import the Recovery Catalog 178 Backing Up the Recovery Catalog 179 Recreating an Unrecoverable Recovery Catalog 179 Using the RMAN Virtual Private Catalog 181 Creating the RMAN Virtual Private Catalog 181 Administering the RMAN Virtual Private Catalog 182 Summary 183 Exam Essentials 183 Review Questions 185 Chapter 5 Recovering Databases with RMAN 189 RMAN Database-Recovery Basics 191 Recovering a Database in NOARCHIVELOG Mode 192 Recovering a Database in ARCHIVELOG Mode 194 Complete Database Recovery in ARCHIVELOG Mode 195 Data File or Tablespace Recovery in ARCHIVELOG Mode 200 Recovering a Database Using Incomplete Recovery 209 Types of Point-in-Time Recovery 209 Point-in-Time Recovery Mechanics 214 Using Image Copies to Recover Your Database 217 Other Basic Recovery Topics 218 Block Media Recovery 218 Recovering the Control File 219 Recovering the Spfile 224 Recovering the Database Password File 229 Clearing Redo Logs 229 Summary 230 Exam Essentials 230 Review Questions 232 Chapter 6 Tuning and Monitoring RMAN and the Automatic Diagnostic Workflow 239 Overview of the RMAN Report and List Commands 240 Using the RMAN REPORT Command 240 Using the RMAN LIST Command 247 Monitoring, Administering, and Tuning RMAN 256 Monitoring RMAN Operations 257 Administering RMAN Operations 260 Tuning RMAN Operations 263 The Oracle Database Fault Diagnostic Framework 264 Overview of the Oracle Database Fault Diagnostic Framework 265 The Oracle ADR 267 Problems and Incidents 269 The Automatic Diagnostic Workflow Managing Problems and Incidents 270 Components Related to the Fault Diagnosability Framework 271 Enterprise Manager Support Workbench 272 The ADRCI Command-Line Utility 273 Summary 275 Exam Essentials 275 Review Questions 277 Chapter 7 Performing Oracle Advanced Recovery 283 Switching between RMAN Incarnations 285 Overview of RMAN Database Duplication 287 RMAN Database Duplication Basics 287 Performing an RMAN Database Duplication 289 Performing an RMAN Tablespace Point-in-Time Recovery 299 TSPITR Overview 300 Checking the Transport Set 302 Lost Objects 303 Rules, Rules, and More Rules 304 TSPITR Aftereffects 304 Restoring a Database to a New Host 308 Using RMAN to Move a Database 308 Oracle Data Pump, Oracle GoldenGate and Oracle Data Guard 310 Complete Disaster 310 Backup Best Practices for Data Warehouses 311 The First Execution of the Command 312 The Second Execution of the Command 313 The Third Execution of the Command 313 Restoring from Incrementally Updated Backups 315 Performing Table and Partition Recovery from Backups 315 Things to Check Before Trying to Restore and Recover Database Tables and Partitions 316 Restrictions When Restoring and Recovering Database Tables and Partitions 317 Options to Consider When Performing Table or Table Partition Restores 317 An Example of Using RMAN to Restore and Recover a Database Table 318 ASM-Related Backups 320 Quick ASM Overview 321 ASM Diskgroup Metadata Backups 321 Restoring ASM Disk Groups 323 Configuring and Using Oracle Secure Backup 323 What Is OSB? 324 How Does OSB Interface with RMAN? 325 The OSB Architecture 325 Installing and Configuring OSB 327 Oracle Database 12c Multitenant Architecture and RMAN 329 Architecture Overview 329 Backing Up CDBs with RMAN 330 Backing Up PDBs with RMAN 331 Recovering CDBs and PDBs with RMAN 332 Duplicating a PDB with RMAN 333 Oracle Data Pump and Oracle Multitenant 334 The Data Recovery Advisor 335 Summary 339 Exam Essentials 340 Review Questions 341 Chapter 8 Understanding Flashback Technology 349 Describe the Flashback Technologies 351 Managing Undo Automatically 352 Uncovering Undo 352 Working with Automatic Undo Management 354 Preserving Data with Undo Retention 355 Employing Flashback Technologies 357 Recovering Tables and Objects with Flashback Drop and the Recycle Bin 357 Use Flashback to Query Data 368 Recovering Data with Flashback Version Query 373 Detecting Changes with Flashback Transaction Query 376 Perform Flashback Table Operations 379 Describe and Use Flashback Data Archive 383 Perform Flashback Database Operations 385 Summary 390 Exam Essentials 391 Review Questions 392 Chapter 9 Diagnosing the Database and Managing Performance 399 Diagnosing Failures 400 Setting Up the Automatic Diagnostic Repository 400 Using the Support Workbench 404 Performing Block Media Recovery 414 Managing Database Performance 416 Using the SQL Tuning Advisor 417 Using the SQL Access Advisor to Tune a Workload 424 Perform Database Replay 430 Summary 448 Exam Essentials 449 Review Questions 450 Chapter 10 Managing Database Resources 455 Managing Storage 456 Managing Resumable Space Allocation 456 Managing Transportable Tablespaces 466 Managing Transportable Databases 479 Shrinking Segments 486 Managing Storage in a CDB and PDBs 494 Manage Permanent and Temporary Tablespaces in CDB and PDBs 495 Summary 497 Exam Essentials 497 Review Questions 498 Chapter 11 Creating Oracle Multitenant Databases 503 Multitenant Container and Pluggable Database Architecture 504 Describing the Multitenant Architecture 504 Explaining Pluggable Database Provisioning 507 Creating Multitenant Container Databases and Pluggable Databases 508 Creating and Configuring a CDB 508 Creating a PDB Using Different Methods 517 Unplugging and Dropping a PDB 534 Migrating a Pre-12.1 Non-CDB Database to a CDB 542 Summary 544 Exam Essentials 544 Review Questions 545 Chapter 12 Managing Oracle Multitenant Databases 551 Establishing Connections to CDB/PDB 552 User Accounts in a CDB 553 Establishing Connections to a CDB 554 Establishing Connections to a PDB 558 Start Up and Shut Down a CDB and Open and Close PDBs 564 Starting Up the CDB Using SQL*Plus 564 Shutting Down the CDB 567 Opening a PDB 570 Shutting Down or Closing a PDB 572 Evaluate the Impact of Parameter Value Changes 574 AUDIT-FILE-DEST 574 AUDIT-SYS-OPERATIONS 575 AUDIT-SYSLOG-LEVEL 575 AUDIT-TRAIL 575 ENABLE-PLUGGABLE-DATABASE 576 LDAP-DIRECTORY-ACCESS 576 MAX-STRING-SIZE 576 NONCDB-COMPATIBLE 576 PDB-FILE-NAME-CONVERT 577 RESOURCE-MANAGER-PLAN 577 SESSIONS 577 Summary 578 Exam Essentials 578 Review Questions 580 Chapter 13 Oracle Utilities 585 Moving Data, Performing Security Operations, and Interacting with Other Oracle Products 586 Using Data Pump 587 Understanding and Using SQL*Loader 595 Audit Operations 603 Using Other Products with CDBs and PDBs Database Vault, Data Guard, LogMiner 606 Summary 616 Exam Essentials 616 Review Questions 618 Chapter 14 Oracle Security in CDBs and PDBs 623 Managing Security in a CDB and PDBs 624 Managing Common and Local Users 625 Managing Common and Local Privileges 636 Managing Common and Local Roles 642 Enabling Common Users to Access Data in Specific PDBs 648 Summary 653 Exam Essentials 654 Review Questions 655 Appendix A Answers to Review Questions 661 Chapter 1: Performing Oracle User-Managed Backups 662 Chapter 2: Performing Oracle User-Managed Database Recoveries 664 Chapter 3: Configuring and Backing Up Oracle Databases Using RMAN 666 Chapter 4: Using the RMAN Recovery Catalog 668 Chapter 5: Recovering Databases with RMAN 669 Chapter 6: Tuning and Monitoring RMAN and the Automatic Diagnostic Workflow 671 Chapter 7: Performing Oracle Advanced Recovery 672 Chapter 8: Understanding Flashback Technology 674 Chapter 9: Diagnosing the Database and Managing Performance 676 Chapter 10: Managing Database Resources 678 Chapter 11: Creating Oracle Multitenant Databases 680 Chapter 12: Managing Oracle Multitenant Databases 681 Chapter 13: Oracle Utilities 682 Chapter 14: Oracle Security in CDBs and PDBs 684 Appendix B About the Additional Study Tools 687 Additional Study Tools 688 Sybex Test Engine 688 Electronic Flashcards 688 Bonus Lab Exercises 688 Glossary 688 Adobe Reader 689 System Requirements 689 Using the Study Tools 689 Troubleshooting 689 Customer Care 690 Index 691