No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.
Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Web site is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Web site may provide or recommendations it may make. Further, readers should be aware that Internet Web sites listed in this work may have changed or disappeared between when this work was written and when it is read.
For general information on our other products and services or to obtain technical support, please contact our Customer Care Department within the U.S. at (877) 762-2974, outside the U.S. at (317) 572-3993 or fax (317) 572-4002.
Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.
Library of Congress Control Number: 2014930413
TRADEMARKS: Wiley and the Sybex logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. Oracle is a registered trademark of Oracle, Inc. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.
10 9 8 7 6 5 4 3 2 1
Dear Reader,
Thank you for choosing OCA: Oracle Database 12c Administrator Certified Associate Study Guide.This book is part of a family of premium-quality Sybex books, all of which are written by outstanding authors who combine practical experience with a gift for teaching.
Sybex was founded in 1976. More than 30 years later, we’re still committed to producing consistently exceptional books. With each of our titles, we’re working hard to set a new standard for the industry. From the paper we print on, to the authors we work with, our goal is to bring you the best books available.
I hope you see all that reflected in these pages. I’d be very interested to hear your comments and get your feedback on how we’re doing. Feel free to let me know what you think about this or any other Sybex book by sending me an email at contactus@sybex.com. If you think you’ve found a technical error in this book, please visit http://sybex.custhelp.com. Customer feedback is critical to our efforts at Sybex.
Best regards,
Chris Webb
Associate Publisher
Sybex, an Imprint of Wiley
Dedication
To the ladies in my family: my lovely wife, loving mom, and lovable sisters. Thanks for always being there for me.
To my children: Joshua and Jeanette. You brighten my day, every day!
Acknowledgments
I thank the great publishing team at Wiley for helping me throughout the writing process. I am grateful to Jeff Kellum, acquisitions editor at Wiley, for initiating this project and having faith in me. I am indebted to Lisa Bishop, developmental editor, for offering valuable suggestions to improve the readability and organization of the book. Thank you both for your patience and hard work throughout this project.
Thank you Kathy Grider-Carlyle, copy editor, for meticulously going through the chapters and making sure there is no ambiguity and that all the pieces tie together. I thank Dassi Zeidel, production editor, for ensuring consistency and accuracy and coordinating the production process. I would like to thank Pete Gaughan, Connor O’Brien, Jenni Housh, Rayna Erlick, and everyone at Wiley who helped in the making of this book.
A technical book is judged by not only who wrote it, but also who reviewed it. I am much honored to have two great Oracle experts as technical reviewers, both Oracle ACE Directors and recipients of the DBA of the Year award. I thank Arup Nanda for reviewing each paragraph of every chapter and for the invaluable comments, tips, and edits. I thank Syed Jaffar Hussain for reading and ensuring the technical accuracy. The input from two Oracle stalwarts certainly improved the content and reliability of the book.
I sincerely thank Gavin Powell for initially signing up as a coauthor to this book. Due to unforeseen events, Gavin could complete only one chapter. I thank Gavin for working with me and helping with the book.
I have many friends to thank at OneNeck IT Solutions for their friendship and encouragement. My heartfelt thanks to Chuck Vermillion (SVP & General Manager) for his support and confidence in me. Thank you Danny Carrizosa for the motivation and your willingness to help out wherever needed. I thank Phil Pearson and the entire team for their trust in my technical abilities. It sure is great working with you all.
Last but not least, no words can express the ceaseless support and encouragement given by my dear wife. Thank you, Shiji. You are the best. I owe my kids several weekends and thank them for their patience and tolerance of so much of my attention being given to this book.
About the Author
Biju Thomas is an Oracle ACE, Oracle Certified Professional, and Certified Oracle Database SQL Expert. Biju has been developing and administering Oracle databases since 1993. He now spends time mentoring DBAs, performance tuning, and designing Oracle solutions. He is Principal Solutions Architect at OneNeck IT Solutions (http://www.oneneck.com). He is a frequent presenter at Oracle conferences and a contributor to Oracle technical journals. He blogs at http://www.bijoos.com/oraclenotes, and you can follow his tweets @biju_thomas.
About the Contributor
Gavin Powell spent 25 years as an IT professional in positions such as Oracle DBA, performance tuner, data architect, and developer. In the last 10 years, he has branched out into technical writing (many titles in print) in between contract and consulting jobs. He also enjoys music, songwriting, performing, singing, playing too many instruments, home recording/producing, and studying music at Berklee.
About the Technical Reviewers
Arup Nanda has been an Oracle DBA for the last 20 years, working on all aspects of Oracle technology from modeling to performance tuning to disaster recovery. He has written over 500 published articles, presented over 300 sessions in 22 countries, and coauthored 5 books. He is an Oracle ACE Director, Oak Table Network member, editor for SELECT Journal, and a board member of Exadata SIG of IOUG. In recognition, Oracle conferred on him the coveted DBA of the Year and Architect of the Year awards in 2003 and 2012, respectively. He lives in Danbury, CT.
Syed Jaffar Hussain has over 21 years IT experience that includes more than 14 years of production Oracle database administration. Oracle has honored him with the prestigious Oracle ACE Director role and named him DBA of the Year for 2011, both for his excellent knowledge and contributions to the Oracle community. He is an Oracle Certified Master (OCM) for Oracle Database 10g, a status granted only after passing extensive challenges in a hands-on environment. He is also an Oracle Database 10g RAC Certified Expert. Syed Jaffar is a well-known Oracle speaker, and he coauthored Expert Oracle RAC12 and Oracle 11g R1/R2 Real Application Clusters Essentials. He blogs regularly at http://jaffardba.blogspot.com.
Introduction
There is high demand for professionals in the information technology (IT) industry, and Oracle certifications are the hottest credential in the database world. You have made the right decision to pursue certification, because being certified in the latest version of Oracle, Oracle Database 12c, will give you a distinct advantage in this highly competitive market.
Many readers may already be familiar with Oracle and do not need an introduction to Oracle databases. For those who aren’t familiar with the company, Oracle, founded in 1977, sold the first commercial relational database and is now the world’s leading database company and second-largest independent software company with revenues of more than $37 billion, and is headquartered in Redwood City, California.
Oracle databases are the de facto standard for large Internet sites, mission-critical enterprise applications, and cloud solutions. With the acquisition of Sun Microsystems, Oracle offers complete enterprise business solutions with engineered systems capable of running world-class databases and applications. Enterprise Resource Planning (ERP) application suites, data warehouses, and business applications at many large and medium-sized companies rely on Oracle. The demand for DBA resources remains higher than others during weak economic times.
This book is intended to help you on your exciting path toward becoming an Oracle Database 12c Administrator Certified Associate (OCA), which is the first step on the path toward Oracle Certified Professional (OCP) and Oracle Certified Master (OCM) certification. This book covers two of the exams required for the OCA certification. Using this book and a practice database, you can start learning Oracle Database 12c and pass the 1Z0-061 “Oracle Database 12c: SQL Fundamentals” and 1Z0-062 “Oracle Database 12c: Installation and Administration” exams.
Why Become Oracle Certified?
The number one reason to become OCA or OCP certified is to gain more visibility and greater access to the industry’s most challenging opportunities. Oracle certification is the best way to demonstrate your knowledge and skills in Oracle database systems. Preparing for the certification exam may be the best time spent on your career because you learn the tasks that are necessary to be successful as a DBA.
Certification is proof of your knowledge and shows that you have the skills required to support Oracle core products. The Oracle certification program can help a company identify proven performers who have demonstrated their skills and who can support the company’s investment in Oracle technology. It demonstrates that you have a solid understanding of your job role and the Oracle products used in that role.
The certification tests are scenario-based, which is the most effective way to assess your hands-on expertise and critical problem-solving skills. OCPs are among the best paid in the IT industry. Salary surveys consistently show the OCP certification to yield higher salaries than the other certifications, including Microsoft, Novell, and Cisco.
So, whether you are beginning a career, changing careers, securing your present position, or seeking to refine and promote your position, this book is for you!
Oracle Certifications
Oracle certifications follow a track that is oriented toward a job role. The primary certification tracks are Database, Applications, Java, Enterprise Management, Virtualization, and Operating Systems. Within each track, Oracle has a tiered certification program of OCA and OCP. Only the Database track has OCM. The Database track is clearly for the Database Administrator job role.
The role of database administrator (DBA) has become a key to success in today’s highly complex database systems. The best DBAs work behind the scenes, but are in the spotlight when critical issues arise. They plan, create, maintain, and ensure that the database is available for the business, most importantly, DBAs troubleshoot, diagnose, and resolve issues. They have tools to proactively monitor the database for performance issues and to prevent unscheduled downtime. The DBA’s job requires a broad understanding of the architecture of an Oracle database and expertise in solving problems.
Sybex has Oracle certification study guides for the Database track. The following sections will introduce you to the different tiers in the Oracle Database 12c certification track.
The Oracle Certified Associate (OCA) credential is the first step toward achieving the Oracle Certified Professional (OCP) certification. OCA shows that you have the fundamental knowledge and skills to support an Oracle Database 12c database. This certification requires you to pass two exams that demonstrate your Oracle basics:
1Z0-061: Oracle Database 12c: SQL Fundamentals
1Z0-062: Oracle Database 12c: Installation and Administration
If you have already passed any one of the following tests, you need not take the 1Z0-061 exam. You only need to pass the 1Z0-062.
1Z0-051: Oracle Database 11g SQL Fundamentals I
1Z0-047: Oracle Database SQL Expert
The 1Z0-061 exam can be taken at a testing location or from your home using the Internet. The 1Z0-062 test is offered at a Pearson Vue facility.
Oracle Database 12c Administrator Certified Professional
The Oracle Certified Professional credential shows that you have the skill and technical expertise to manage and implement enterprise databases. The OCP tier challenges you to demonstrate your continuing experience and knowledge of Oracle technologies. The OCP test will measure your knowledge in setting up and managing multitenant architecture databases and in backup and recovery. The Oracle Database 12c Administrator Certified Professional certification requires you to have the OCA certification as well as to pass the following exam.
The highest level of certification available in any track is the Oracle Certified Master. The OCM certification credential shows that you have the highest level of expertise in an Oracle product. To become a certified master, you must first achieve OCP status and then complete two advanced instructor-led classes at an Oracle Education facility. You must also pass a hands-on examination at an Oracle Education facility. At the time of writing this book, the Oracle Database 12c Certified Master Exam is not released.
More Information and Resources
You can find most current information about Oracle certification at http://education.oracle.com/certification. You may be asked to choose the country of residence before being directed to the site. Follow the links under Certifications to choose the track and learn more.
Choose the Database track to view the different certification versions available. Choose Oracle Database 12c Administrator Certified Associate, and then click on the test to find out more about the test contents, the objectives covered in the test, the passing score, and to register for the test.
The Oracle documentation is available online at http://tahiti.oracle.com. Oracle documentation contains a wealth of information, which can be used to supplement what you learn from this book.
Oracle provides training series with step-by-step instructions to perform a variety of Oracle Database 12c tasks. The Oracle by Example (OBE) tutorial can be found at http://apex.oracle.com/pls/apex/f?p=44785:1.
Oracle Technology Network (http://www.oracle.com/technology/index.html) is a great resource for database administrators and developers. You can read articles; view sample code; access documentation; participate in forums; and most importantly, download Oracle Database 12c, Oracle Enterprise Manager Cloud Control 12c, and other Oracle products.
OCA/OCP Study Guides
The Oracle Database 12c Administration track certification consists of three tests: two for OCA and one for OCP. Sybex offers study guides to help you achieve OCA and OCP certification.
OCA: Oracle Database 12c Administrator Certified Associate Study Guide (9781118643952) – Covers exams 1Z0-061: Oracle Database 12c: SQL Fundamentals and 1Z0-062: Oracle Database 12c: Installation and Administration.
OCP: Oracle Database 12c Administrator Certified Professional Study Guide (9781118644072) – Covers exam 1Z0-063: Oracle Database 12c: Advanced Administration.
These two books are offered in a boxed set as OCP: Oracle Certified Professional on Oracle Database 12c Certification Kit (9781118957684).
Oracle Exam Requirements
The Oracle Database 12c Database Certified Associate certification tests your basic SQL skills for the SQL exam and database architecture and administration skills for the DBA exam. The SQL exam tests your knowledge of writing SQL and using the functions available in Oracle Database 12c. The Installation and Administration exam concentrates on the architecture and the basic administration of Oracle 12c databases. The following sections detail the skills needed to pass the SQL Fundamentals and the Installation and Administration exams.
OCA SQL (1Z0-061) Requirements
To pass the Oracle Database 12c SQL Fundamentals exam, you must have the following skills:
Write SQL SELECT statements that display data from one or more tables
Join tables using ANSI syntax and Oracle traditional syntax
Restrict, sort, and aggregate data using single-row, conversion, and group functions
Write subqueries and queries using SET operators
Manipulate data: insert, update, delete
Create and manage tables and other database objects
OCA Installation and Administration (1Z0-062) Requirements
To pass the Oracle Database 12c Installation and Administration exam, you must have the following skills:
Understand the Oracle server architecture (database and instance)
Install Oracle Database 12c software and create a database
Use Database Configuration Assistant and Enterprise Manager tools
Understand the physical and logical storage of the database, and be able to manage space allocation and growth
Use the data dictionary views and set database parameters
Manage and manipulate data, including its storage, loading, and reorganization
Create and manage tables, constraints, and indexes
Manage redo logs, archive logs, and automatic undo
Configure Oracle Net on the server side and client side
Understand backup and recovery architecture
Secure the database and audit database usage
Use advisors to tune and manage the database
Upgrade the database
Move data between databases
Install and manage Grid Infrastructure, Oracle Restart, and Automatic Storage Management
Tips for Taking the OCA Exams
The following tips will help you prepare for and pass each exam.
Each test consists of between 70 and 95 questions to be completed in 120 to 150 minutes (depending on the exam). Answer the questions you are sure of first, before you run out of time.
Many questions on the exam have answer choices that at first glance look identical. Read the questions carefully. Do not jump to conclusions. Make sure you clearly understand what each question asks.
Most questions are based on scenarios. Some of the scenarios contain nonessential information and exhibits. You need to be able to identify what’s important and what’s not.
Do not leave any questions unanswered. There is no negative scoring. After selecting an answer, you can mark difficult questions or the ones you are unsure of and come back to them later.
When answering questions you are not sure about, use a process of elimination to get rid of the obviously incorrect answers first. Doing this greatly improves your odds if you need to make an educated guess.
If you are not sure of your answer, mark it for review and then look for other questions that may help you eliminate any incorrect answers. At the end of the test, you can review the questions you marked earlier.
What Is Covered in This Book
This book covers everything you need to pass the Oracle Database 12c Certified Associate exams. Part I includes the first seven chapters that cover the objectives for the Oracle Database 12c SQL Fundamentals exam. Part II of the book includes the remaining 11 chapters that cover the objectives for the Oracle Database 12c Installation and Administration exam.
Part I: Oracle Database 12c SQL Fundamentals
Chapter 1: Introducing Oracle Database 12c RDBMS introduces you to the features of Oracle Database 12c and the aspects of a relational database.
Chapter 2: Introducing SQL introduces you to writing simple queries using SELECT statements. It also introduces you to filtering and sorting data.
Chapter 3: Using Single-Row Functions discusses the single-row functions and conversion functions available, with details on how and where to use them.
Chapter 4: Using Group Functions explains data aggregations, Oracle’s built-in group function, and nesting of functions.
Chapter 5: Using Joins and Subqueries explains how data from multiple tables can be related via joins, subqueries, and by using SET operators.
Chapter 6: Manipulating Data explores how to manipulate data: adding, removing, and updating data. The chapter also covers how transaction control works.
Chapter 7: Creating Tables and Constraints explains how to create and manage tables and constraints. It also discusses the various datatypes available in Oracle Database 12c to store data.
Part II: Oracle Database 12c Installation and Administration
Chapter 8: Introducing Oracle Database 12c Components and Architecture is the first chapter to start if you’re reading for the Oracle Database 12c Installation and Administration exam. This chapter introduces you to the Oracle Database 12c database architecture and the relationship between logical and physical storage structures.
Chapter 9: Creating and Operating an Oracle Database 12c explains how you can install the Oracle Database 12c software and create a database. It discusses the initialization parameters, stages of database startup and shutdown, where to find log and trace files, and how to use the data dictionary.
Chapter 10: Understanding Storage and Space Management explores the logical and physical storage of the database. You will learn space management and the various types of tablespaces. This chapter also discusses monitoring space and reclaiming wasted space.
Chapter 11: Managing Data Concurrency and Undo shows you how you can add, update, and remove data from tables, as well as how transactions work. It introduces you to undo data and undo management. This chapter also discusses how Oracle manages locks to ensure data concurrency. Be sure to read Chapter 6 before you read this chapter.
Chapter 12: Understanding Oracle Network Architecture introduces you to the Oracle Net configuration and setup. You will learn to set up network architecture on the server and client.
Chapter 13: Implementing Security and Auditing shows how you can secure your database using privileges, profiles, and roles. You will also learn to audit database usage.
Chapter 14: Maintaining the Database and Managing Performance explores the tools available in Oracle Database 12c to manage the performance of the database. You will learn about optimizer statistics, Automatic Workload Repository, various advisors, and Automatic Memory Management.
Chapter 15: Using Backup and Recovery introduces you to the backup architecture concepts. It discusses the various backup modes and using RMAN. It also delves into the various recovery scenarios and how best to get the data back. It shows you how to use the Data Recovery Advisor, which can help find and recover the database, as well as offer advice.
Chapter 16: Controlling Resources and Jobs shows you how to manage the resources available on the database server using the Resource Manager. You will also learn about the Oracle Scheduler job management system.
Chapter 17: Upgrading to Oracle Database 12cfamiliarizes you with the process of upgrading a database to Oracle Database 12c. You also learn the various upgrade and migration methods.
Chapter 18: Using Grid Infrastructure and Data Movement Tools introduces you to Data Pump and SQL*Loader, the tools available in Oracle Database 12c to move and load data. This chapter also covers the Grid Infrastructure installation and configuration, including setting up Automatic Storage Management disks.
Each chapter ends with Review Questions that are specifically designed to help you retain the knowledge presented. To really nail down your skills, read and answer each question carefully.
What’s Available Online?
The book includes a number of companion study tools, which can be downloaded from www.sybex.com/go/oca12sg. See Appendix B, “About the Additional Study Tools,” for information on how to access and install these tools:
Test Preparation Software The test preparation software prepares you to pass both the 1Z0-061 and 1Z0-062 exams. You’ll find all the review and assessment questions from the book plus an additional four practice exams (two for each exam) that appear exclusively from the downloadable study tools.
Electronic Flashcards The companion study tools include over 400 flashcards specifically written to hit you hard, so don’t get discouraged if you don’t ace your way through them at first! They’re there to ensure that you’re really ready for the exam. And no worries—armed with the review questions, practice exams, and flashcards, you’ll be more than prepared when exam day comes!
Author Bonus Material I’ve included all of the code from the book, as well as three bonus Whitepapers that show you how to install an Oracle 12c database on Windows, how to create non-CDB Oracle Database 12c on Windows, and how to install Oracle Enterprise Manager 12c on Virtual Box.
Glossary A complete glossary of common terms is available at www.sybex.com/go/oca12sg.
How to Use This Book
This book provides a solid foundation for the serious effort of preparing for the Oracle Database 12c Certified Associate exams. To best benefit from the book, use the following study method:
Take the assessment tests immediately following this introduction. (The answers are at the end of the tests.) Carefully read the explanations for any questions you get wrong, and note in which chapters the material is covered. This information should help you plan your study strategy.
Study each chapter carefully, making sure you fully understand the information and the test objectives listed at the beginning of each chapter. Pay close attention to any chapter related to questions you missed in the assessment test.
Complete all hands-on activities in the chapter, referring to the chapter so that you understand the reason for each step you take. It is best to have an Oracle Database 12c database available to try out the examples and the code provided in the book. All the code is also included with the additional study tools.
Answer the review questions related to that chapter. Note the review questions that confuse or trick you, and study those sections of the book again.
The two bonus exams for each exam are included with the accompanying study tools. They will give you a complete overview of what you can expect to see on the real test.
Answer all the flashcard questions included with the study tools.
Remember to use the study tools included with this book. The electronic flash cards and the Sybex test engine exam-preparation software has been specifically designed to help you study and pass your exams.
To learn all the material covered in this book, you will need to apply yourself regularly and with discipline. Try to set the same time period every day to study, and select a comfortable and quite place to do so. If you work hard, you will be surprised at how quickly you will learn this material. You can also find supplemental reading material and Oracle documentation references on my blog that will deepen your knowledge of what you read in this book. All the best!
How to Contact the Author
I welcome feedback from you about this book or about books you’d like to see from me in the future. You can reach me by writing to biju.thomas.sybex@gmail.com. For more information about database administration and Oracle Database 12c certification, please visit my blog at www.bijoos.com/oraclenotes. You may follow me on social media through Twitter (@biju_thomas) and Facebook (Oracle Notes www.facebook.com/oraclenotes).
Sybex strives to keep you supplied with the latest tools and information you need for your work. Please check their website at www.sybex.com, where we’ll post additional content, errata, and updates that supplement this book if the need arises. Enter search terms in the Search box (or type the book’s ISBN— 9781118643952), and click Go to get to the book’s update page.
