OCP: Oracle Database 12C Administrator Certified Professional Study Guide

As the most popular database software in the world, Oracle Database 12c has been updated for the first time in nearly six years and the changes are significant. This study guide reviews how Oracle 12c allows multiple instances to be used simultaneously via the cloud. You'll sharpen your skills to prepare for the three levels of certification: Oracle Certified Associate, Oracle Certified Professional, and Oracle Certified Master. Workbook exercise appendix, test engine, chapter review questions, electronic flashcards, searchable PDF glossary, and two bonus practice exams all help to enhance your preparation to take the Oracle 12c exam. Addresses such topics as: database architecture, configuring and recoverability, configuring backup specifications, and performing user-managed backup and recovery. Reviews how to use RMAN to create backups, perform recovery, and duplicate a database

Looks at performing tablespace point-in-time recovery and using flashback technology.

Covers diagnosing the database, managing memory, managing resources, and automating tasks.

Focusing 100 percent on the exam objectives, OCP: Oracle Database 12c Administrator Certified Professional Study Guide is designed for those who feel they are ready to attempt this challenging exam.

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

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.