An updated guide for an updated certification exam!
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.
Table of Contents:
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
About the Author :
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.
