Ocp Oracle Certified Professional on Oracle 12c Certification Kit

by Thomas, Biju; Freeman, Robert G.; Pack, Charles A.; Powell, Gavin (CON)

• ISBN13: 9781118957684
• ISBN10: 1118957687
• Format: Paperback
• Copyright: 2014-09-22
• Publisher: Sybex Inc

Summary

Meet your certification guide on the journey to Oracle DBA

The Oracle DBA Certification track now consists of three levels – Associate (OCA), Professional (OCP), and Master (OCM). This valuable kit helps you attain success at each level, providing clarification of the complex material along with plenty of practice that covers all exam objectives.

OCP Oracle Certified Professional on Oracle 12c Certification Kit is the ideal tool for preparing for the OCA and OCP exams. With two comprehensive study guides in one package, this kit serves as a helpful companion throughout your journey to Oracle DBA certification. The OCA study guide is designed for those with some Oracle experience and a background in operational management and maintenance, covering one hundred percent of the material on exams 1Z0-061 and 1Z0-062. Once you're OCA certified, the OCP guide prepares you for exam 1Z0-063 by drawing on your experience with backup, recovery, and cloud computing to master database architecture, RMAN, multitenant architecture, and more. Along the way, you'll have access to robust study tools including review questions, bonus practice exam questions, electronic flashcards, and an appendix of additional lab exercises that give you hands-on experience in preparation for exam day.

• Review data handling and the creation/maintenance of Oracle databases
• Understand database architecture and the functionality of ASM
• Configure projects for recoverability and backup with RMAN
• Learn efficient space management, multitenant architecture, and more

The addition of multitenancy to Oracle 12c makes an up-to-date study guide crucial for thorough preparation. You must be able to demonstrate your expertise in all aspects of the Oracle environment within a limited time frame, so studying early and often is the surest route to success. If you're ready to tackle these challenging exams, OCP Oracle Certified Professional on Oracle 12c Certification Kit can give you the confidence you need.

Author Biography

Biju Thomas (Keller, TX), OCA, OCP,?is a Senior Oracle Solutions Architect with more than 20 years of Oracle development and database management experience.?He has co-authored seven Oracle certification books published?for Sybex, including the upcoming OCA: Oracle 12c Study Guide.

Robert Freeman (Henderson, NV), OCA, OCP, Robert G. Freeman has been working with the Oracle database products for over 20 years. He is the author of many books on Oracle database technologies.

Charles A. Pack (Orange Park, FL),?OCA, OCP, has over?20 years of IT experience. In his current role at CSX Technology, Technical Director of Hardware Provisioning, he and his teams are responsible for enterprise storage, backups, capacity planning, and performance. Both were co-authors for Sybex's OCP: Oracle Database 11g Administrator Certified Professional Study Guide and the updated edition for Oracle 12c.

Table of Contents

OCA: Oracle Database 12c Administrator Certified Associate Study Guide: Exams 1Z0-061 and 1Z0-062

Introduction xx

Assessment Test xxxv

Part I Oracle Database 12c: SQL Fundamentals 1

Chapter 1 Introducing Oracle Database 12c RDBMS 3

Relational Database Management Systems 4

Characteristics of a Relational Database 5

Oracle’s Implementation of RDBMS and ORDBMS 8

The Oracle Database 12c 9

Oracle Database 12c Implementations 9

Connecting to Oracle Database 13

Database Management Tools 14

Oracle Database 12c in the Cloud 19

Familiarizing SQL*Plus 19

Summary 30

Exam Essentials 31

Review Questions 32

Chapter 2 Introducing SQL 35

SQL Fundamentals 36

Oracle Datatypes 38

Operators and Literals 42

Writing Simple Queries 46

Using the SELECT Statement 46

Filtering Rows 51

Sorting Rows 62

Sorting NULLs 64

Limiting Rows 67

Using Expressions 68

Accepting Values at Runtime 72

Using Ampersand Substitution Variables 73

Saving a Variable for a Session 75

Using Positional Notation for Variables 77

Summary 78

Exam Essentials 78

Review Questions 80

Chapter 3 Using Single-Row Functions 89

Single-Row Function Fundamentals 90

Functions for NULL Handling 91

Using Single-Row Character Functions 95

Character Function Overview 95

Character Function Descriptions 96

Using Single-Row Numeric Functions 108

Numeric Function Overview 108

Numeric Function Descriptions 109

Using Single-Row Date Functions 119

Date Format Conversion 119

Date Function Overview 120

Date Function Descriptions 121

Using Single-Row Conversion Functions 131

Conversion Function Overview 131

Conversion Function Descriptions 133

Using Other Single-Row Functions 154

Miscellaneous Function Overview 154

Miscellaneous Function Descriptions 155

Summary 167

Exam Essentials 168

Review Questions 169

Chapter 4 Using Group Functions 175

Group Function Fundamentals 176

Utilizing Aggregate Functions 177

Grouping Data with GROUP BY 178

Group Function Overview 182

Group Function Descriptions: Part 1 184

Group Function Descriptions: Part 2 194

Limiting Grouped Data with HAVING 207

Creating Superaggregates with CUBE and ROLLUP 208

Nesting Functions 215

Summary 218

Exam Essentials 218

Review Questions 219

Chapter 5 Using Joins and Subqueries 227

Writing Multiple-Table Queries 228

Inner Joins 229

Cartesian Joins 239

Outer Joins 240

Other Multiple-Table Queries 245

Using Set Operators 247

The UNION Operator 248

The UNION ALL Operator 249

The INTERSECT Operator 250

The MINUS Operator 250

Putting It All Together 250

Using Subqueries 252

Single-Row Subqueries 253

Multiple-Row Subqueries 254

Subquery Returns No Rows 256

Correlated Subqueries 258

Scalar Subqueries 259

Multiple-Column Subqueries 267

Subqueries in Other DML Statements 268

Summary 269

Exam Essentials 270

Review Questions 271

Chapter 6 Manipulating Data 281

Using DML Statements 282

Inserting Rows into a Table 283

Updating Rows in a Table 289

Deleting Rows from a Table 294

Merging Rows 297

Understanding Transaction Control 299

Savepoints and Partial Rollbacks 302

Data Visibility 304

Summary 306

Exam Essentials 307

Review Questions 308

Chapter 7 Creating Tables and Constraints 319

Database Objects Overview 320

Schema Objects 322

Using Sequences 324

Built-in Datatypes 325

Character Datatypes 325

Numeric Datatypes 329

Date and Time Datatypes 330

Date Arithmetic 334

Binary Datatypes 336

Row ID Datatypes 337

Creating Tables 338

Naming Tables and Columns 339

Specifying Default Values for Columns 341

Adding Comments 348

Creating a Table from Another Table 348

Modifying Tables 350

Adding Columns 350

Modifying Columns 353

Renaming Columns 354

Dropping Columns 354

Hiding Columns from Table 356

Dropping Tables 357

Renaming Tables 357

Making Tables Read-Only 358

Managing Constraints 361

Creating Constraints 362

Dropping Constraints 367

Enabling and Disabling Constraints 367

Deferring Constraint Checks 369

Summary 373

Exam Essentials 374

Review Questions 375

Part II Oracle Database 12c: Installation and Administration 381

Chapter 8 Introducing Oracle Database 12c Components and Architecture 383

Oracle Database Fundamentals 385

Relational Databases 385

Oracle Database 12c Objects 386

Interacting with Oracle Database 12c 388

Oracle Database 12c Architecture 391

User and Server Processes 394

The Oracle Instance 396

Oracle Storage Structures 410

Summary 424

Exam Essentials 425

Review Questions 426

Chapter 9 Creating and Operating Oracle Database 12c 431

Oracle Database 12c Software Installation 432

Planning the Oracle Database 12c Software Install 433

Using the Oracle Universal Installer 440

Using DBCA to Create an Oracle 12c Database 447

Invoking the Database Configuration Assistant 447

Configuring an Oracle Database Using the DBCA 472

Deleting an Oracle Database Using the DBCA 472

Managing Database Templates Using the DBCA 473

Working with Oracle Database Metadata 475

Data Dictionary Views 476

Dynamic Performance Views 477

Managing Initialization-Parameter Files 479

Locating the Default Parameter File 484

Modifying Initialization-Parameter Values 485

Starting Up and Shutting Down an Oracle Instance 490

Starting Up an Oracle Database 12c Instance 491

Shutting Down an Oracle Database 12c Instance 495

Monitoring the Database Alert Log 497

Summary 503

Exam Essentials 504

Review Questions 506

Chapter 10 Understanding Storage and Space Management 511

Understanding the Physical and Logical Storage 512

Contents of a Data Block 514

Managing Tablespaces 516

Identifying Default Tablespaces 516

Creating and Maintaining Tablespaces 516

Obtaining Tablespace Information 531

Managing Data Files 534

Performing Operations on Data Files 535

Using the Oracle Managed Files Feature 539

Querying Data File Information 544

Managing Space 547

Automatic Space Management Features 547

Monitoring Tablespace Free Space 551

Using Segment Advisor 554

Avoiding Out-of-Space Errors 559

Summary 562

Exam Essentials 563

Review Questions 565

Chapter 11 Managing Data Concurrency and Undo 569

Managing Data Changes Using DML 570

Understanding “Change” 571

Differentiating Undo and Redo 572

Configuring and Monitoring Undo 576

Managing Data Concurrency 584

Understanding Locks and Transactions 585

Maximizing Data Concurrency 587

Detecting and Resolving Lock Conflicts 590

Summary 595

Exam Essentials 595

Review Questions 597

Chapter 12 Understanding Oracle Network Architecture 603

Introducing Network Configurations 604

Single-Tier Architecture 605

Two-Tier Architecture 605

n-Tier Architecture 606

An Overview of Oracle Net Features 607

Connectivity 608

Manageability 608

Scalability 609

Security 611

Accessibility 613

Configuring Oracle Net on the Server 613

Understanding the Oracle Listener 614

Managing Oracle Listeners 618

Dynamically Registering Services 634

Oracle Net Logging and Tracing on the Server 635

Configuring Oracle Net for the Client 637

Client-Side Names Resolution Options 637

The Host Naming Method 638

The Oracle Easy Connect Method 639

The Local Naming Method 641

Troubleshooting Client-Side Connection Problems 646

An Overview of Oracle Shared Server 648

Dedicated Server vs. Shared Server 648

Advantages and Disadvantages of Shared Server 650

Oracle Shared Server Infrastructure 651

Configuring the Oracle Shared Server 653

Managing a Shared Server 658

Understanding Database Resident Connection Pooling 661

Configuring DRCP 662

Comparing Connection Architectures 663

Communicating Between Databases 666

Introduction to Database Links 666

Creating Database Links 667

Summary 669

Exam Essentials 670

Review Questions 672

Chapter 13 Implementing Security and Auditing 677

Creating and Managing User Accounts 678

Configuring Authentication 679

Assigning Tablespaces and Quotas 681

Assigning a Profile and Account Settings 684

Contents xv

Removing a User from the Database 686

Managing Default User Accounts 687

Granting and Revoking Privileges 687

Granting Object Privileges 688

Granting System Privileges 692

Role Privileges 701

Applying the Principle of Least Privilege 705

Controlling Resource Usage by Users 711

Implementing Password Security Features 714

Auditing Database Activity 718

Managing Statement Auditing 720

Managing Privilege Auditing 724

Managing Object Auditing 725

Using SQL Developer for Audit Management 727

Purging the Audit Trail 729

Managing Fine-Grained Auditing 732

Implementing Unified Auditing 735

Summary 745

Exam Essentials 746

Review Questions 747

Chapter 14 Maintaining the Database and

Managing Performance 753

Proactive Database Maintenance 754

Managing Optimizer Statistics 755

Gathering Performance Statistics 775

Automatic Database Diagnostic Monitoring 782

The Advisory Framework 793

Monitoring Server-Generated Alerts 798

Understanding Automatic Diagnostic Repository 801

Managing Performance 806

Sources of Tuning Information 807

Tuning Memory 812

Automatic Shared Memory Management 813

Automatic SQL Execution Memory Management 815

Managing Memory Using EM Cloud Control 817

Summary 819

Exam Essentials 820

Review Questions 821

Chapter 15 Using Backup and Recovery 825

Understanding and Configuring Recovery Components 827

Understanding Control Files 827

Understanding Checkpoints 831

Understanding Redo Log Files 832

Understanding Archived Redo Log (ARCHIVELOG) Files 839

Understanding the Fast Recovery Area 844

Performing Backups 848

Understanding Backup Terminology 848

Backing Up the Control File 850

Backing Up the Database 851

Using RMAN to Perform Backups 852

Managing Backups 861

Understanding Types of Database Failures 864

Statement Failures 865

User-Process Failures 865

Network Failures 866

User-Error Failures 866

Instance Failures 867

Media Failures 868

Performing Recovery Operations 868

Understanding Instance Startup 868

Keeping an Instance from Failing 870

Recovering from Instance Failure 870

Tuning Instance Recovery 871

Recovering from User Errors 873

Recovering from the Loss of a Control File 881

Using the Data Recovery Advisor 883

Recovering from the Loss of a Redo Log File 884

Recovering from the Loss of a Non-System-Critical

Data File 886

Recovering from the Loss of a System-Critical Data File 892

Table Recovery Using RMAN 893

Summary 894

Exam Essentials 895

Review Questions 897

Chapter 16 Controlling Resources and Jobs 903

Resource Management with the Resource Manager 904

Functions of the Resource Manager 904

Understanding Resource Manager Components 905

Configuring the Database Resource Manager 911

Accessing and Creating Resource Plans 916

Updating and Deleting Resources 924

Monitoring the Resource Manager 927

Task Automation with Oracle Scheduler 930

Scheduler Architecture 930

Using Oracle Scheduler 934

Contents xvii

Relating Tasks with Job Chains 943

Scheduling Jobs on Remote Systems 947

Prioritizing Jobs with Oracle Scheduler 948

Summary 951

Exam Essentials 951

Review Questions 952

Chapter 17 Upgrading to Oracle Database 12c 957

Determining the Database Upgrade Method 958

Using Direct Upgrade 959

Migrating to Oracle Database 12c 961

Preparing for a Database Upgrade 963

Running the Pre-Upgrade Information Tool 964

Preparing the Oracle Home 974

Reducing Upgrade Downtime 975

Upgrading the Database 976

Using Database Upgrade Assistant 976

Performing a Manual Upgrade 987

Completing the Post-Upgrade Tasks 991

Migrating to Unified Auditing 994

Downgrading to an Earlier Release 995

Summary 997

Exam Essentials 997

Review Questions 999

Chapter 18 Using Grid Infrastructure and Data Movement Tools 1005

Tools for Moving Data 1006

Migrating Data Using Data Pump 1008

Data Pump Architecture 1009

Using Data Pump Clients 1012

Using the Data Pump Wizard 1033

Upgrading an 11g R2 Database to 12c

Using Full Transportable Export 1037

Loading Data with SQL*Loader 1039

Specifying SQL*Loader Command-Line Parameters 1040

Specifying Control File Options 1042

Populating External Tables 1046

Loading External Tables Using Data Pump 1046

Loading External Tables Using Loader 1048

Introducing Grid Infrastructure 1050

Installing Oracle Grid Infrastructure 1051

Managing Oracle ASM Storage 1061

Using Oracle Restart 1068

Summary 1076

Exam Essentials 1077

Review Questions 1078

Appendix A Answers to Review Questions 1083

Chapter 1: Introducing Oracle Database 12c RDBMS 1084

Chapter 2: Introducing SQL 1085

Chapter 3: Using Single-Row Functions 1087

Chapter 4: Using Group Functions 1089

Chapter 5: Using Joins and Subqueries 1090

Chapter 6: Manipulating Data 1092

Chapter 7: Creating Tables and Constraints 1094

Chapter 8: Introducing Oracle Database 12c Components

and Architecture 1096

Chapter 9: Creating and Operating Oracle Database 12c 1098

Chapter 10: Understanding Storage and Space Management 1100

Chapter 11: Managing Data Concurrency and Undo 1102

Chapter 12: Understanding Oracle Network Architecture 1104

Chapter 13: Implementing Security and Auditing 1106

Chapter 14: Maintaining the Database and

Managing Performance 1108

Chapter 15: Using Backup and Recovery 1110

Chapter 16: Controlling Resources and Jobs 1112

Chapter 17: Upgrading to Oracle Database 12c 1114

Chapter 18: Using Grid Infrastructure and

Data Movement Tools 1116

Appendix B About the Additional Study Tools 1119

Additional Study Tools 1120

Sybex Test Engine 1120

Electronic Flashcards 1120

Bonus Author Materials 1120

PDF of Glossary of Terms 1120

Adobe Reader 1121

System Requirements 1121

Using the Study Tools 1121

Troubleshooting 1121

Customer Care 1122

Index 1123

OCP: Oracle Database 12c Administrator Certified Professional Study Guide: Exam 1Z0-063

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