OCP Oracle Certified Professional on Oracle 12c Certification Kit
Published on 22. September 2014
Book
Paperback/Softback
1992 pages
978-1-118-95768-4 (ISBN)
Description
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.
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.
More details
Persons
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.
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.
Content
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
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