OCP: Oracle8i DBA Architecture & Administration and Backup & Recovery Study Guide
Copyright ©2000 SYBEX , Inc., Alameda, CA
www.sybex.com
OCP: Oracle8i™ DBA Architecture & Administration and Backup & Recovery Study Guide Doug Stuns Biju Thomas
San Francisco • Paris • Düsseldorf • Soest • London Copyright ©2000 SYBEX , Inc., Alameda, CA
www.sybex.com
Associate Publisher: Richard Mills Contracts and Licensing Manager: Kristine O’Callaghan Acquisitions and Developmental Editors: Kim Goodfriend, Richard Mills Editor: Sharon Wilkey Production Editor: Leslie E. H. Light Technical Editors: Bob Bryla, Betty MacEwen Book Designer: Bill Gibson Graphic Illustrator: epic Electronic Publishing Specialists: Bill Gibson, Jill Niles, Judy Fung, Nila Nichols, Susie Hendrickson Proofreaders: Laurie OConnell, Nancy Riddiough, Camera Obscura Indexer: Ted Laux CD Coordinator: Kara Eve Schwartz CD Technicians: Keith McNeil, Siobhan Dowling Cover Designer: Archer Design Cover Illustrator/Photographer: Photo Researchers Copyright © 2001 SYBEX Inc., 1151 Marina Village Parkway, Alameda, CA 94501. World rights reserved. No part of this publication may be stored in a retrieval system, transmitted, or reproduced in any way, including but not limited to photocopy, photograph, magnetic, or other record, without the prior agreement and written permission of the publisher. Library of Congress Card Number: 01-106432 ISBN: 0-7821-2683-9 SYBEX and the SYBEX logo are trademarks of SYBEX Inc. in the USA and other countries. Screen reproductions produced with FullShot 99. FullShot 99 © 1991-1999 Inbit Incorporated. All rights reserved. FullShot is a trademark of Inbit Incorporated. The CD interface was created using Macromedia Director, COPYRIGHT 1994, 1997-1999 Macromedia Inc. For more information on Macromedia and Macromedia Director, visit http://www.macromedia.com. SYBEX is an independent entity from Oracle Corporation and is not affiliated with Oracle Corporation in any manner. This publication may be used in assisting students to prepare for an Oracle Certified Professional exam. Neither Oracle Corporation nor SYBEX warrants that use of this publication will ensure passing the relevant exam. Oracle is either a registered trademark or a trademark of Oracle Corporation in the United States and/or other countries. TRADEMARKS: SYBEX has attempted throughout this book to distinguish proprietary trademarks from descriptive terms by following the capitalization style used by the manufacturer. The author and publisher have made their best efforts to prepare this book, and the content is based upon final release software whenever possible. Portions of the manuscript may be based upon pre-release versions supplied by software manufacturer(s). The author and the publisher make no representation or warranties of any kind with regard to the completeness or accuracy of the contents herein and accept no liability of any kind including but not limited to performance, merchantability, fitness for any particular purpose, or any losses or damages of any kind caused or alleged to be caused directly or indirectly from this book. Manufactured in the United States of America 10 9 8 7 6 5 4 3 2 1
Copyright ©2000 SYBEX , Inc., Alameda, CA
www.sybex.com
Software License Agreement: Terms and Conditions The media and/or any online materials accompanying this book that are available now or in the future contain programs and/or text files (the "Software") to be used in connection with the book. SYBEX hereby grants to you a license to use the Software, subject to the terms that follow. Your purchase, acceptance, or use of the Software will constitute your acceptance of such terms. The Software compilation is the property of SYBEX unless otherwise indicated and is protected by copyright to SYBEX or other copyright owner(s) as indicated in the media files (the "Owner(s)"). You are hereby granted a single-user license to use the Software for your personal, noncommercial use only. You may not reproduce, sell, distribute, publish, circulate, or commercially exploit the Software, or any portion thereof, without the written consent of SYBEX and the specific copyright owner(s) of any component software included on this media. In the event that the Software or components include specific license requirements or end-user agreements, statements of condition, disclaimers, limitations or warranties ("End-User License"), those End-User Licenses supersede the terms and conditions herein as to that particular Software component. Your purchase, acceptance, or use of the Software will constitute your acceptance of such End-User Licenses. By purchase, use or acceptance of the Software you further agree to comply with all export laws and regulations of the United States as such laws and regulations may exist from time to time. Reusable Code in This Book The authors created reusable code in this publication expressly for reuse for readers. Sybex grants readers permission to reuse for any purpose the code found in this publication or its accompanying CD-ROM so long as all three authors are attributed in any application containing the reusable code, and the code itself is never sold or commercially exploited as a stand-alone product. Software Support Components of the supplemental Software and any offers associated with them may be supported by the specific Owner(s) of that material but they are not supported by SYBEX. Information regarding any available support may be obtained from the Owner(s) using the information provided in the appropriate read.me files or listed elsewhere on the media. Should the manufacturer(s) or other Owner(s) cease to offer support or decline to honor any offer, SYBEX bears no responsibility. This notice concerning support for the Software is provided for your information only. SYBEX is not the agent or principal of the Owner(s), and SYBEX is in no way responsible for providing any support for the Software, nor is it liable or responsible for any support provided, or not provided, by the Owner(s). Warranty SYBEX warrants the enclosed media to be free of physical defects for a period of ninety (90) days after purchase. The Software is not available from SYBEX in any other form or media
than that enclosed herein or posted to www.sybex.com. If you discover a defect in the media during this warranty period, you may obtain a replacement of identical format at no charge by sending the defective media, postage prepaid, with proof of purchase to: SYBEX Inc. Customer Service Department 1151 Marina Village Parkway Alameda, CA 94501 (510) 523-8233 Fax: (510) 523-2373 e-mail:
[email protected] Web: HTTP://WWW.SYBEX.COM After the 90-day period, you can obtain replacement media of identical format by sending us the defective disk, proof of purchase, and a check or money order for $10, payable to SYBEX. Disclaimer SYBEX makes no warranty or representation, either expressed or implied, with respect to the Software or its contents, quality, performance, merchantability, or fitness for a particular purpose. In no event will SYBEX, its distributors, or dealers be liable to you or any other party for direct, indirect, special, incidental, consequential, or other damages arising out of the use of or inability to use the Software or its contents even if advised of the possibility of such damage. In the event that the Software includes an online update feature, SYBEX further disclaims any obligation to provide this feature for any specific duration other than the initial posting. The exclusion of implied warranties is not permitted by some states. Therefore, the above exclusion may not apply to you. This warranty provides you with specific legal rights; there may be other rights that you may have that vary from state to state. The pricing of the book with the Software by SYBEX reflects the allocation of risk and limitations on liability contained in this agreement of Terms and Conditions. Shareware Distribution This Software may contain various programs that are distributed as shareware. Copyright laws apply to both shareware and ordinary commercial software, and the copyright Owner(s) retains all rights. If you try a shareware program and continue using it, you are expected to register it. Individual programs differ on details of trial periods, registration, and payment. Please observe the requirements stated in appropriate files. Copy Protection The Software in whole or in part may or may not be copyprotected or encrypted. However, in all cases, reselling or redistributing these files without authorization is expressly forbidden except as specifically provided for by the Owner(s) therein.
Copyright ©2000 SYBEX , Inc., Alameda, CA
www.sybex.com
To Cathy—Doug Stuns To Shiji—Biju Thomas
Copyright ©2000 SYBEX , Inc., Alameda, CA
www.sybex.com
Acknowledgments
First, I want to thank the Lord, my savior, for making this all possible. Next, my parents, Ron and Jan Stuns, for without them I would not be who I am and would not have had the fortitude necessary to write this book. My parents have been there for me throughout my life and have supported everything that I have done. My lovely wife of 11 years, Cathy, has been my major support in this process and encouraged me to start this task knowing full well of the time commitment. She is always my biggest and best supporter in every circumstance. I am so happy that she has been there to support me throughout. My wonderful children, Brant and Brea, 8 and 6 years of age, have supported and tolerated me through this process as well. The many hours away from them has been difficult for each of us, but their understanding has been incredible. Brant and Brea went so far as to join me in the den pretending to help write and review sections so that we could spend more time together. Without their understanding and support, I would not have been able to complete this task. I am so proud of them both. Numerous other people have helped to contribute to the completion and ultimate success of this project, and I owe them sincere appreciation. I want to thank Rick and Matt for reviewing questions and other technical materials. I am greatly thankful to the staff at Sybex. First, Technical Editors Bob Bryla and Betty MacEwen, for their diligent work and attention to detail. Next, Editor Sharon Wilkey, whose great efforts to correct and smooth out some rough edges made this process much easier. Production Editor Leslie E. H. Light was always there to help in any way possible. I also thank Associate Publisher Richard Mills for his support and follow-through. Unseen, but not unappreciated, Bill Gibson, Jill Niles, and the rest of the Electronic Publishing Specialists at Sybex created the look you see here and made sure each page was a marriage of form and function.It has been my pleasure to work with all of you. Finally, I want to thank all of the many professional people whom I have had the privilege to work with in my career. I have learned much and owe many thanks! —Doug Stuns
Copyright ©2000 SYBEX , Inc., Alameda, CA
www.sybex.com
viii
Acknowledgments
I would like to thank Richard and Leslie for their help, guidance, and patience throughout the process of writing this book. Thank you, Sharon, for the edits and suggestions—your hard work certainly helped to complete the project on time. Bob and Betty, your technical reviews helped enormously to raise the quality of this book—thank you. Bill and Jill, you made this book look great. Thank you also. I also thank Wendy and all my colleagues for their never-failing support— you are a great team to work with. I thank my friends for inspiring and encouraging me. Finally, I owe big thanks to my wife, Shiji, who gave me all the support when I needed it the most. Thank you for giving up your weekends and evenings when I was working on this book. —Biju Thomas
Copyright ©2000 SYBEX , Inc., Alameda, CA
www.sybex.com
Introduction
There is high demand and competition for professionals in the Information Technology (IT) industry, and the Oracle Certified Professional (OCP) certification is the hottest credential in the database realm. You have made the right decision to pursue certification. Being an OCP will give you a distinct advantage in this highly competitive market. Many readers may already be familiar with the Oracle Corporation and its products and services. For those who aren’t familiar with the company, Oracle Corporation, founded in 1977, is the world’s leading database company and second largest independent software company, with revenues of more than $9.7 billion and clients in more than 145 countries. Oracle’s CEO, Lawrence J. Ellison, saw the future of information technology in Internet computing, and the Oracle8i database was created to meet the needs of this technological evolution. This book is intended to help you continue on your exciting new path toward obtaining the Oracle8i certified database administrator certification. The book will give you the necessary knowledge of the Oracle Server architecture and the hands-on skills you need to pass Exams 1Z0-023 and 1Z0025. Although the OCP exams for Database Administration can be taken in any order, it is generally recommended that the Oracle8i OCP certification exam for Architecture and Administration and the Oracle8i OCP certification exam for Backup and Recovery be the final exams taken in the series.
Why Become an Oracle Certified Professional? The number one reason to become an Oracle Certified Professional is to gain more visibility and greater access to the industry’s most challenging opportunities. The OCP program is Oracle’s commitment to provide top-quality resources for technical professionals who want to become Oracle specialists in specific job roles. The certification tests are scenario based, which is the most effective way to access your hands-on expertise and critical problemsolving skills. Certification is proof of your knowledge and shows that you have the skills required to support Oracle’s core products according to the standards established by Oracle. The OCP program can help a company identify proven performers who have demonstrated their skills to support the company’s
Copyright ©2000 SYBEX , Inc., Alameda, CA
www.sybex.com
xxvi
Introduction
investment in Oracle technology. It demonstrates that you have a solid understanding of your job role and the Oracle products used in that role. 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 has several certification tracks designed to meet different skill levels. Each track consists of several tests that can be taken in any order. The following tracks are available:
Oracle Database Administrator
Oracle Application Developer
Oracle Database Operator
Oracle Java Developer
Oracle Financial Applications Consultant
Oracle Database Administrator (DBA) 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, and maintain databases to ensure that the databases meet the data management needs of the business. DBAs also monitor the databases for performance issues and work to prevent unscheduled downtime. Being an effective DBA requires a broad understanding of the architecture of Oracle databases and expertise in solving system-related problems. The Oracle8i certified administrator track consists of the following five tests:
1Z0-001: Introduction to Oracle—SQL and PL/SQL
1Z0-023: Oracle8i—Architecture and Administration
1Z0-024: Oracle8i—Performance and Tuning
1Z0-025: Oracle8i—Backup and Recovery
1Z0-026: Oracle8i—Network Administration
Copyright ©2000 SYBEX , Inc., Alameda, CA
www.sybex.com
Introduction
xxvii
Oracle Application Developer This track tests your skills in client-server and Web-based application development using Oracle application development tools such as Developer/2000, SQL, PL/SQL, and SQL*Plus. The following five tests comprise this track:
1Z0-001: Introduction to Oracle—SQL and PL/SQL
1Z0-101: Develop PL/SQL Program Units
1Z0-111: Developer/2000 Forms 4.5 I
1Z0-112: Developer/2000 Forms 4.5 II
1Z0-113: Developer/2000 Reports 2.5
Oracle Database Operator (DBO) A Database Operator (DBO) performs simple operational tasks on Oracle databases in a support role to the DBA. DBOs need an introductory knowledge of the commands and utilities associated with managing a database. DBOs also install and set up databases, create users, and perform routine backups. You need to take the following test to be certified as a Database Operator:
1Z0-401: Database Operator
Oracle Java Developer This certification track is part of the Certification Initiative for Enterprise Development, a multi-vendor collaboration with Sun Microsystems, IBM, Novell, and the Sun-Netscape Alliance to establish standards for knowledge and skill levels for enterprise developers in the Java technology. The Initiative recognizes three levels of certification requiring five tests. At each skill level, a certificate is awarded to candidates who successfully pass the required exams in that level.
Level 1: Sun Certified Programmer
1Z0-501: Sun Certified Programmer for the Java 2 Platform
Level 2: Certified Solution Developer
1Z1-502: Oracle JDeveloper: Develop Database Applications with Java (Oracle JDeveloper, Release 2)
Copyright ©2000 SYBEX , Inc., Alameda, CA
www.sybex.com
xxviii
Introduction
or 1Z1-512: Oracle JDeveloper: Develop Database Applications with Java (Oracle JDeveloper, Release 3)
1Z0-503: Object-Oriented Analysis and Design with UML
Level 3: Certified Enterprise Developer
1Z0-504: Enterprise Connectivity with J2EE
1Z0-505: Enterprise Development on the Oracle Internet Platform
Oracle Financial Applications Consultant This certification tests your expertise in Oracle Financial applications. These exams are designed to test your knowledge of the business processes incorporated into the Oracle Financial applications software. The following three tests comprise this track, and the third exam offers a specialization in either Procurement or Order Fulfillment:
1Z0-210: Financial Management R11
1Z0-220: Applied Technology R11
1Z0-230: Procurement R11
or
1Z0-240: Order Fulfillment
More Information The most current information about Oracle certification can be found at http://education.oracle.com. Follow the Certification Home Page link and choose the track you are interested in. Read the Candidate Guide for the test objectives and test contents, and keep in mind that they can change at any time without notice.
OCP: Database Administrator Track The Oracle8i Database Administrator certification consists of five tests, and Sybex offers several study guides to help you achieve the OCP Database Administrator Certification. There are three books in this series:
OCP: Oracle8i™ DBA SQL and PL/SQL Study Guide
Copyright ©2000 SYBEX , Inc., Alameda, CA
www.sybex.com
Introduction
xxix
OCP: Oracle8i™ DBA Architecture & Administration and Backup & Recovery Study Guide
OCP: Oracle8i™ DBA Performance Tuning and Network Administration Study Guide
Additionally, these three books are offered in a boxed set:
OCP: Oracle8i™ DBA Certification Kit
Table F.1 lists the five exams for the DBA track, their scoring, and the Sybex study guides that will help you pass each exam. TABLE F.1
OCP Database Administrator Tests and Passing Scores Total Questions
Questions Correct
Passing %
Introduction to Oracle: SQL & PL/SQL
57
39
68%
OCP: Oracle8i™ DBA SQL and PL/SQL Study Guide
1Z0-023
Oracle8i: Architecture and Administration
65
38
58%
OCP: Oracle8i™ DBA Architecture & Administration and Backup & Recovery Study Guide
1Z0-024
Oracle8i: Performance and Tuning
57
38
67%
OCP: Oracle8i™ DBA Performance Tuning and Network Administration Study Guide
1Z0-025
Oracle8i: Backup and Recovery
60
34
57%
OCP: Oracle8i™ DBA Architecture & Administration and Backup & Recovery Study Guide
1Z0-026
Oracle8i: Network Administration
59
41
71%
OCP: Oracle8i™ DBA Performance Tuning and Network Administration Study Guide
Exam #
Title
1Z0-001
Copyright ©2000 SYBEX , Inc., Alameda, CA
www.sybex.com
Sybex Study Guide
xxx
Introduction
Skills Required for DBA Certification
Understanding RDBMS concepts
Writing queries and manipulating data
Creating and managing users and database objects
Understanding PL/SQL programming and constructs
Understanding Oracle Server architecture—Database and Instance
Completely understanding physical and logical database storage concepts
Managing data—storage, loading, and reorganization
Managing roles, privileges, passwords, and resources
Understanding backup and recovery options
Archiving redo log files and hot backups
Using Recovery Manager (RMAN) to perform backup and recovery operations
Creating and managing standby database
Identifying and tuning database and SQL performance
Interpreting data dictionary views and database parameters
Configuring Net8 on the server side and client side
Using multi-threaded server, connection manager, and Oracle Names
Understanding graphical and character mode backup, recovery, and administration utilities
Tips for Taking OCP Exams
Each OCP test contains about 60–80 questions to be completed in about 90 minutes. Answer the questions you know first, so that you do not run out of time.
Many questions on the exam have answer choices that at first glance look identical. Read the questions carefully. Don’t just jump to conclusions. Make sure that you are clear about exactly what each question asks.
Copyright ©2000 SYBEX , Inc., Alameda, CA
www.sybex.com
Introduction
xxxi
Many of the test questions are scenario based. Some of the scenarios contain non-essential 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. You can mark a difficult question or a question you are unsure about and come back to it later.
When answering questions that 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.
What Does This Book Cover? This book covers everything you need to know to pass both the OCP: Oracle8i DBA Architecture & Administration and OCP: Oracle8i DBA Backup & Recovery exams. The first part of the book covers the configuration, architecture, and administration of an Oracle database. The second part covers the topics of backup and recovery, including using traditional techniques as well as RMAN.
Part One: OCP: Oracle8i DBA Architecture & Administration Chapter 1 starts with an overview of the Oracle database configuration and its architecture. It discusses what constitutes an instance and a database, and the various background processes that communicate with the Oracle database. Chapter 2 discusses the administrator authentication methods and the stages in starting and stopping the Oracle database. This chapter also introduces the Oracle Enterprise Manager utilities. Chapter 3 introduces you to the steps required in database creation and how to prepare OS environment and parameter files. The database administrative packages and database event triggers are also discussed. Chapter 4 covers two important constituents of the Oracle database—the control file and redo log file. You will learn the importance and use of these files in this chapter. Chapter 5 is dedicated to managing tablespaces. The types of tablespaces and their configuration and storage are discussed. Chapter 6 discusses the logical storage structures such as blocks, extents, and segments. Creating and managing rollback segments are discussed. Chapter 7 covers creating and managing tables and its associated structures such as indexes and constraints.
Copyright ©2000 SYBEX , Inc., Alameda, CA
www.sybex.com
xxxii
Introduction
Chapter 8 introduces database and data security. Setting up users, privileges, and roles are discussed. Chapter 9 discusses the database utilities such as SQL*Loader and Export/Import. It also discusses the direct-load insert and National Language Support.
Part Two: Oracle8i DBA Backup & Recovery Chapter 10 starts with an overview of the backup and recovery process and configurations. Chapter 11 discusses the recovery structures and processes as well as the types of failures. Chapter 12 introduces how to configure the database for backup and recovery. This chapter also describes the differences between ARCHIVELOG and NOARCHIVELOG modes. Chapter 13 covers how to perform physical backups without Recovery Manager. It includes implications of closed and opened backups, Nologging and Logging options, control file backups, and special issues associated with read-only tablespaces. Chapter 14 covers how to perform complete recovery without Recovery Manager. This chapter focuses primarily on the differences between ARCHIVELOG and NOARCHIVELOG mode recoveries. Chapter 15 discusses how to perform incomplete recovery without Recovery Manager. This chapter has a special section on how to recover after losing current and inactive nonarchived redo log files. Chapter 16 introduces the Export and Import utilities and how these utilities complement the backup process. It includes an example of the tablespace point-in-time recovery (TSPITR). Chapter 17 introduces additional recovery issues, such as parallel recovery and recovering a database with missing data files. There is also a section on issues associated with read-only tablespaces. Chapter 18 covers more Oracle utilities for troubleshooting the database. This chapter focuses on detecting corruption, diagnosing and solving problems via trace files, and using the LogMiner utility to reconstruct redo log transactions. Chapter 19 introduces Oracle Recovery Manager, providing an overview that includes its components and capabilities.
Copyright ©2000 SYBEX , Inc., Alameda, CA
www.sybex.com
Introduction
xxxiii
Chapter 20 demonstrates Recovery Manager’s catalog creation and maintenance. It covers how to query the recovery catalog and create, run, and store RMAN scripts. Chapter 21 covers backups using Recovery Manager. It discusses the types of RMAN backups, tuning backups, and performing incremental and cumulative backups. Chapter 22 discusses restoration and recovery using Recovery Manager and has specific examples of types of recoveries. Chapter 23 covers the Oracle standby database, including its complete configuration and setup. It provides examples of using the standby database in specific scenarios. Each chapter ends with review questions that are specifically designed to help you retain the knowledge presented. To really nail down your skills, read each question carefully and answer the questions.
Where Do You Take the Exam? You may take the exams at any of the more than 800 Sylvan Prometric Authorized Testing Centers around the world. For the location of a testing center near you, call 1-800-891-3926. Outside of the United States and Canada, contact your local Sylvan Prometric Registration Center. The tests can be taken in any order. To register for an Oracle Certified Professional exam:
Determine the number of the exam you want to take.
Register with the nearest Sylvan Prometric Registration Center. At this point, you will be asked to pay in advance for the exam. At the time of this writing, the exams are $125 each and must be taken within one year of payment. You can schedule exams up to six weeks in advance or as soon as one working day prior to the day you wish to take it. If something comes up and you need to cancel or reschedule your exam appointment, contact Sylvan Prometric at least 24 hours in advance.
When you schedule the exam, you’ll get instructions regarding all appointment and cancellation procedures, the ID requirements, and information about the testing-center location.
Copyright ©2000 SYBEX , Inc., Alameda, CA
www.sybex.com
xxxiv
Introduction
You can also register for the test online at http://www.2test.com/ register/frameset.htm. If you live outside the United States, register online at http://www.2test.com/register/testcenterlocator/ ERN_intl_IT&FAA.htm.
How to Use This Book This book can provide a solid foundation for the serious effort of preparing for the Oracle Certified Professional Architecture & Administration and Backup & Recovery exams. To best benefit from this book, use the following study method: 1. Take the Assessment Tests immediately following this introduction.
(The answers are at the end of each test.) Carefully read over the explanations for any questions you get wrong, and note which chapters the material comes from. This information should help you plan your study strategy. 2. Study each chapter carefully, making sure that you fully understand
the information and the test objectives listed at the beginning of each chapter. Pay extra close attention to any chapter for which you missed questions in the Assessment Tests. 3. Closely examine the sample queries that are used throughout the
book. You may find it helpful to type in the samples and compare the results shown in the book to those on your system. Once you’re comfortable with the content in the chapter, answer the review questions related to that chapter. (The answers appear at the end of the chapter, after the review questions.)
When typing in examples from the book, do not type the line numbers that appear in the sample output; the Oracle query tools automatically number lines for you.
4. Note the questions that confuse you, and study those sections of the
book again. 5. Take the Practice Exams in this book. You’ll find them in Appendix A
and Appendix B. The answers appear at the end of the exams.
Copyright ©2000 SYBEX , Inc., Alameda, CA
www.sybex.com
Introduction
xxxv
6. Before taking the exam, try your hand at the bonus exams that are
included on the CD that comes with this book. The questions in these exams appear only on the CD. This will give you a complete overview of what you can expect to see on the real thing. 7. Remember to use the products on the CD that is included with this
book. The electronic flashcards and the EdgeTest exam preparation software have all been specifically picked to help you study for and pass your exams. Oracle also offers sample exams on their certification Web site: http://education.oracle.com/certification/. The electronic flashcards can be used on your Windows computer or on your Palm device. To learn all the material covered in this book, you’ll have to apply yourself regularly and with discipline. Try to set aside the same time period every day to study, and select a comfortable and quiet place to do so. If you work hard, you will be surprised at how quickly you learn this material. All the best!
What’s on the CD? We worked hard to provide some really great tools to help you with your certification process. All of the following tools should be loaded on your workstation when studying for the test.
The EdgeTest for Oracle Certified DBA Preparation Software Provided by EdgeTek Learning Systems, this test preparation software prepares you to successfully pass the OCP: Oracle8i DBA Architecture & Administration and Backup & Recovery exams. In this test engine, you will find all of the questions from the book, plus additional Practice Exams that appear exclusively on the CD. You can take the Assessment Tests, test yourself by chapter, take the Practice Exams that appear in the book or on the CD, or take an exam randomly generated from any of the questions.
Electronic Flashcards for PC and Palm Devices After you read the book, read the review questions at the end of each chapter and study the practice exams included in the book and on the CD. But wait, there’s more! Test yourself with the flashcards included on the CD. If you can get through these difficult questions, and understand the answers, you’ll
Copyright ©2000 SYBEX , Inc., Alameda, CA
www.sybex.com
xxxvi
Introduction
know you’re ready for the OCP: Oracle8i DBA Architecture & Administration and Backup & Recovery exams. The flashcards include over 150 questions specifically written to hit you hard and make sure you are ready for the exam. Between the review questions, practice exam, and flashcards, you’ll be more than prepared for the exam.
OCP: Oracle8i DBA Architecture & Administration and Backup & Recovery Study Guide Ebook Sybex is now offering the Oracle Certification books on CD, so you can read them on your PC or laptop. They are in Adobe Acrobat format. Acrobat Reader 4 is also included on the CD. This will be extremely helpful to readers who fly and don’t want to carry a book, as well as to readers who find it more comfortable reading from their computer.
How to Contact the Authors To contact Biju Thomas, you can e-mail him at
[email protected] or visit his Web site for DBAs at www.bijoos.com/oracle. You can reach Doug Stuns via e-mail at
[email protected] . Doug Stuns recommends that you register at technet.oracle.com to get access to the Oracle8i documentation, including information for all of the RMAN commands as well as other useful information about Oracle8i.
About the Authors Biju Thomas is an Oracle Certified Professional with more than six years of Oracle database administration and application development experience. He has written articles for Oracle Magazine and Oracle Internals. Doug Stuns is an Oracle Certified Professional with more than 10 years of experience with Oracle databases. He is currently president and founder of SCS, Inc., an Oracle-based consulting company in Scottsdale, Arizona. Formerly Doug worked for the Oracle Corporation for five years, serving as senior principal technical consultant focusing on DBA consulting and customized education projects.
Copyright ©2000 SYBEX , Inc., Alameda, CA
www.sybex.com
OCP: Oracle8i Architecture & Administration Assessment Test 1. What happens when one of the redo members of the next group is
unavailable when LGWR has finished writing the current log file? A. Database operation will continue uninterrupted. B. The database will hang; do an ALTER DATABASE SWITCH LOGFILE
to skip the unavailable redo log. C. The instance will be shut down. D. LGWR will create a new redo log member, and the database will
continue to be in operation. 2. How do you change the status of a database to restricted availability,
if the database is already up and running? Choose the best answer. A. Shut down the database and start the database using STARTUP
RESTRICT. B. Use the ALTER DATABASE RESTRICT SESSIONS command. C. Use the ALTER SYSTEM ENABLE RESTRICTED SESSION command. D. Use the ALTER SESSION ENABLE RESTRICTED USERS command. 3. Which background process updates the online redo log files with the
redo log buffer entries when a COMMIT occurs in the database? A. DBWn B. LGWR C. CKPT D. CMMT
Copyright ©2000 SYBEX , Inc., Alameda, CA
www.sybex.com
xxxviii
Assessment Test
4. Analyze the following statement. On which tablespace will the roll-
back segment R01 be created? CREATE ROLLBACK SEGMENT R01;
A. The default tablespace of the user creating the rollback segment. B. In the RBS tablespace. C. In the SYSTEM tablespace. D. The statement will return an error. You must specify a tablespace
when creating a rollback segment. 5. Choose two extent management options available for tablespaces. A. Dictionary managed B. Data file managed C. Locally managed D. Remote managed E. System managed 6. Which component in the following list is not part of the SGA? A. Database buffer cache B. Library cache C. Sort area D. Shared pool 7. How do you enable complex password verification such as having at
least one numeric or special character in the password? A. Use the ALTER USER command to specify the PASSWORD_VERIFY_
FUNCTION clause B. Define a profile with the PASSWORD_VERIFY_FUNCTION and assign
the profile to the user C. Create a trigger on the password change event, which fires when
the user changes a password and verifies the criteria D. Set the initialization parameter PASSWORD_VERIFY_FUNCTION
Copyright ©2000 SYBEX , Inc., Alameda, CA
www.sybex.com
OCP: Oracle8i Architecture & Administration Assessment Test
xxxix
8. The ALTER INDEX … REBUILD command cannot ____________. A. Move index to a new tablespace B. Change the INITIAL extent size of the index C. Collect statistics on the index D. Specify a new name for the index 9. Choose a file that is not used by the SQL*Loader utility for input or
output. A. Control file B. Parameter file C. Bad file D. Text file E. Data file 10. Which is the correct order of steps in executing a query? A. Parse, execute B. Execute, parse, fetch C. Parse, execute, fetch D. Parse, fetch, execute 11. Which script creates the data dictionary tables? A. catalog.sql B. catproc.sql C. sql.bsq D. dictionary.sql 12. How do you collect statistics for a table? A. ALTER TABLE
COMPUTE STATISTICS B. ANALYZE TABLE COMPUTE STATISTICS C. ALTER TABLE COLLECT STATISTICS D. ANALYZE TABLE COLLECT STATISTICS, but you cannot combine a rename with any other index operation. When rebuilding an index, you can specify a new tablespace and new storage parameters. The index can be rebuilt in parallel, and you can specify COMPUTE STATISTICS to collect statistics. Indexes are discussed in Chapter 7. 9. D. The text file is not a specific file type used by SQL*Loader per BEGIN BACKUP, and ALTER TABLESPACE END BACKUP is the command to remove the tablespace from backup mode. See Chapter 13. 26. C. The init.ora parameter DB_BLOCK_CHECKING = TRUE will password=<password> entries=<users> where file - name of password file (mand), password - password for SYS and INTERNAL (mand), entries - maximum number of distinct DBAs and OPERs (opt), There are no spaces around the equal-to (=) character. DISABLE. COMPILE command. 13. D. DML event triggers should always be associated with a table or’ REUSE; Oracle recommends backing up the control file whenever you make a change to the database structure, such as adding data files, renaming files, or dropping redo log files.’ TO ‘’; 5. Open the database (ALTER DATABASE OPEN). 6. Back up the control file.’; LOG_ARCHIVE_DUPLEX_DEST Specifies a second destination to write the archive log files. This must be a location on the server where the database is located. This destination can be either a must-succeed or a best-effort archive destination, depending on how many archive destinations must succeed. The minimum successful number of archive destinations is specified in the parameter LOG_ARCHIVE_MIN_SUCCEED_DEST. You can change the archiving location specified by this parameter by using ALTER SYSTEM SET LOG_ARCHIVE_DUPLEX_DEST = ‘’; LOG_ARCHIVE_DEST_N Using this parameter, you can specify up to five archiving destinations. These archive locations can be either on the local machine or on a remote machine where the standby database is located. When these parameters are used, you cannot use the LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST parameters to specify the archiving location. The syntax for specifying this parameter in the initialization file is: LOG_ARCHIVE_DEST_n = “” | ((SERVICE = | LOCATION = ‘’) [MANDATORY | OPTIONAL] [REOPEN [= ]]) ADD DATAFILE SIZE B. ALTER DATABASE DATAFILE RESIZE C. ALTER DATAFILE RESIZE D. ALTER TABLESPACE RESIZE 6. If the DB_BLOCK_SIZE of the database is 8KB, what will be the size of CASCADE. D. Use DROP TABLESPACE INCLUDING COALESCE. 10. B. A table can be dropped from a read-only tablespace. When a table AUTOEXTEND ON NEXT MAXSIZE . 13. D. When you create a tablespace with no default storage parameters, RESIZE . A temporary file cannot be renamed. 15. C. The data files belonging to the SYSTEM tablespace can be renamed DEALLOCATE UNUSED (discussed in Chapter 7, “Managing Tables, Indexes, and Constraints”). command; this statement should be the first statement of the transaction. A transaction begins when you start a new session or when you issue a COMMIT or ROLLBACK. The rollback segment is used only when the first DML statement is issued. A transaction can use only one rollback segment. Like any other segment, the rollback segment also consists of many extents. The extents in the rollback segment are used in a circular fashion. There should be at least two extents for each rollback segment. When Oracle completes writing to an extent in the rollback segment, it checks whether the next extent is free. When the last extent of the rollback segment becomes full,). 3. B. The STATUS column of the DBA_CONSTRAINTS view shows ( <userid_variable> IN VARCHAR2 (30), <password_variable> IN VARCHAR2 (30), IN VARCHAR2 (30) ) RETURN BOOLEAN.log, .bad and .dsc. For example, if your control file name is load.ctl, Oracle creates the log file as load.log, the bad file as load.bad, and the discard file as load.dsc._ .. The language specifies the language to be used for displaying Oracle error messages, day names, month names, etc. The territory specifies the default date format, numeric formats, and monetary formats. The character set specifies the character set to be used by the client machine—for example, AMERICAN_AMERICA.WE8ISO8859P1, where AMERICAN is the language, AMERICA is the territory, and WE8ISO8859P1 is the character set. NLS_LANGUAGE Specified at the session level (using ALTER SESSION) or as an initialization parameter. Sets the language to be used. The session value overrides the NLS_LANG setting. The default values for the NLS_ DATE_LANGUAGE and NLS_SORT parameters are derived from NLS_ LANGUAGE. NLS_TERRITORY Specified at the session level or as an initialization parameter. Sets the territory. The session value overrides the NLS_LANG setting. The default values for parameters such as NLS_CURRENCY, NLS_ ISO_CURRENCY, NLS_DATE_FORMAT, and NLS_NUMERIC_CHARACTERS are derived from NLS_TERRITORY. NLS_DATE_FORMAT Specified at the session level, or as an environment variable, or as an initialization parameter. Sets a default format for date displays. NLS_DATE_LANGUAGE Specified at the session level, or as an environment variable, or as an initialization parameter. Sets a language explicitly for day and month names in date values. NLS_CALENDAR Specified at the session level, or as an environment variable, or as an initialization parameter. Sets the calendar Oracle uses. END BACKUP while the database is mounted. command was issued. Another technique in more static tables is nightly exports—logical extracts from a database that are stored in a binary dump file. Individual tables or user schemas can be rebuilt by using the Import utility with the binary dump file generated by the Export utility. Chapter 16, “Oracle Export and Import Utilities,” explains these utilities in detail. Media (disk) failure Results when files needed by the database, such as data files, cannot be accessed. This usually occurs because a disk drive fails or a controller accessing a disk fails. Oracle provides several ways to recover from media failure. Each method is dependent on the type of backups that are performed. If you perform backups in ARCHIVELOG mode, complete recovery is possible. If you perform backups in NOARCHIVELOG mode, complete recovery might not be possible, or some data loss usually occurs.‘FILENAME’ command. 5. A, B, C, and D. All the listed failures are types of failures that can BEGIN BACKUP command is issued to perform hot backups. After this command is issued, an OS copy can take place on each tablespace. When the OS copy is complete, an ALTER TABLESPACE END BACKUP command must be issued. These commands must be executed for every tablespace in the database. BEGIN BACKUP, an OS copy command, and the command ALTER TABLEPACE END BACKUP. Refer back to Chapter 12, “Oracle Backup and Recovery Configuration,” for a more detailed discussion of hot backups. Figure 13.2 is an example of an opened backup. FIGURE 13.2 BEGIN BACKUP; SVRMGRL > ! cp /Disk3/datafile* /staging SVRMGRL > ALTER TABLESPACE END BACKUP; BEGIN/END BACKUP was run to /staging Disk6. BEGIN BACKUP command and an ALTER TABLESPACE END BACKUP command. The opened backup requires only a copy of the data files. The ALTER TABLESPACE BEGIN BACKUP command causes a checkpoint to the data file or data files in the tablespace. This causes all dirty blocks to be written to the data file, and the data-file header is stamped with the SCN consistent with those data blocks. All other changes occurring in the data file from Data Manipulation Language (DML), such as INSERT, UPDATE, and DELETE statements, get recorded in the data files and redo logs in almost the same way as during normal database operations. However, the data-file header SCN does not get updated until the ALTER TABLESPACE END BACKUP command gets executed and another checkpoint occurs. To distinguish what blocks are needed in a recovery situation, Oracle writes more information in the redo logs during the period that the ALTER TABLESPACE BEGIN BACKUP command is executed. This is one reason why data files are fundamentally different from other Oracle database files, such as redo logs, control files, and init.ora files when it comes to backups. Redo logs, control files, and init.ora files can be copied with standard OS copy commands without performing any preparatory steps such as the ALTER TABLESPACE commands. BEGIN and END backup commands. Typically, this type of backup is done with a scripting language in Unix or a third-party GUI utility in the Windows environment. Using Unix shell scripts, a list of data files and tablespaces are dumped to a file listing and parsed into svrmgr and cp commands so that all tablespaces and data files are backed up together.=suggested | filename | AUTO | CANCEL} Log applied. ORA-00279: change 48325 generated at 03/29/00 22:05:25 needed for thread 1=suggested | filename | AUTO | CANCEL} Log applied. ORA-00279: change 48330 generated at 03/29/00 22:08:41 needed for thread 1 ORA-00289: suggestion : /oracle/admin/brdb/arch1/archbrdb_86.log ORA-00280: change 48330 for thread 1 is in sequence #86 ORA-00278: log file '/oracle/admin/brdb/arch1/archbrdb_85.log' no longer needed for this recovery Specify log: {=suggested | filename | AUTO | CANCEL} ORA-00279: change 48330 generated at 03/29/00 22:08:41 needed for thread 1 ORA-00289: suggestion : /oracle/admin/brdb/arch1/archbrdb_86.log ORA-00280: change 48330 for thread 1 is in sequence #86 ORA-00278: log file '/oracle/admin/brdb/arch1/archbrdb_85.log' no longer needed for this recovery Specify log: {=suggested | filename | AUTO | CANCEL} Log applied. Media recovery complete. SVRMGR>=suggested | filename | AUTO | CANCEL} AUTO Log applied. ORA-00279: change 68449 generated at 03/30/00 19:27:11 needed for thread 1 ORA-00289: suggestion : /oracle/admin/brdb/arch1/archbrdb_92.log ORA-00280: change 68449 for thread 1 is in sequence #92 ORA-00278: log file '/oracle/admin/brdb/arch1/archbrdb_91.log' no longer needed for this recovery=suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled. Statement processed. SVRMGR> 5. You should verify the new location of the data files after the database=suggested | filename | AUTO | CANCEL} AUTO Log applied.=suggested | filename | AUTO | CANCEL} /oracle/admin/brdb/arch1/archbrdb_10.log Log applied. Media recovery complete. SVRMGR> alter database open resetlogs; Statement processed. SVRMGR> 16. Take an export TSPITR of the clone database and apply it to the pri-=suggested | filename | AUTO | CANCEL} auto Log applied. ORA-00279: change 170736 generated at 04/10/00 23:10:14 needed for thread 1 ORA-00289: suggestion : /oracle/admin/brdb/arch1/archbrdb_18.log ORA-00280: change 170736 for thread 1 is in sequence #18 ORA-00278: log file '/oracle/admin/brdb/arch1/archbrdb_17.log' no longer needed for this recovery Log applied. ORA-00279: change 170863 generated at 04/10/00 23:19:33 needed for thread 1 Copyright ©2000 SYBEX , Inc., Alameda, CA=suggested | filename | AUTO | CANCEL} AUTO Log applied. ORA-00279: change 170736 generated at 04/10/00 23:10:14 needed for thread 1 ORA-00289: suggestion : /oracle/admin/brdb/arch1/archbrdb_18.log ORA-00280: change 170736 for thread 1 is in sequence #18 ORA-00278: log file '/oracle/admin/brdb/arch1/archbrdb_ 17.log' no longer needed for this recovery Log applied. ORA-00279: change 170863 generated at 04/10/00 23:19:33 needed for thread 1 ORA-00289: suggestion : /oracle/admin/brdb/arch1/archbrdb_19.log ORA-00280: change 170863 for thread 1 is in sequence #19 ORA-00278: log file '/oracle/admin/brdb/arch1/archbrdb_18.log' no longer needed for this recovery Log applied. ORA-00279: change 170973 generated at 04/10/00 23:19:46 needed for thread 1 ORA-00289: suggestion : /oracle/admin/brdb/arch1/archbrdb_20.log ORA-00280: change 170973 for thread 1 is in sequence #20 ORA-00278: log file '/oracle/admin/brdb/arch1/archbrdb_19.log' no longer needed for this recovery Log applied. ORA-00279: change 171052 generated at 04/10/00 23:20:00 needed for thread 1 ORA-00289: suggestion : /oracle/admin/brdb/arch1/archbrdb_21.log ORA-00280: change 171052 for thread 1 is in sequence #21=suggested | filename | AUTO | CANCEL} auto Log applied. ORA-00279: change 170736 generated at 04/10/00 23:10:14 needed for thread 1 ORA-00289: suggestion : /oracle/admin/brdb/arch1/archbrdb_18.log ORA-00280: change 170736 for thread 1 is in sequence #18 ORA-00278: log file '/oracle/admin/brdb/arch1/archbrdb_17.log' no longer needed for this recovery Log applied. ORA-00279: change 170863 generated at 04/10/00 23:19:33 needed for thread 1 ORA-00289: suggestion : /oracle/admin/brdb/arch1/archbrdb_19.log ORA-00280: change 170863 for thread 1 is in sequence #19 ORA-00278: log file '/oracle/admin/brdb/arch1/archbrdb_18.log' no longer needed for this recovery Log applied. ORA-00279: change 170973 generated at 04/10/00 23:19:46 needed for thread 1 ORA-00289: suggestion : /oracle/admin/brdb/arch1/archbrdb_20.log ORA-00280: change 170973 for thread 1 is in sequence #20 ORA-00278: log file '/oracle/admin/brdb/arch1/archbrdb_19.log' no longer needed for this recovery=suggested | filename | AUTO | CANCEL} AUTO Log applied. ORA-00279: change 68449 generated at 03/30/00 19:27:11 needed for thread 1 ORA-00289: suggestion : /oracle/admin/brdb/arch1/archbrdb_92.log ORA-00280: change 68449 for thread 1 is in sequence #92 ORA-00278: log file '/oracle/admin/brdb/arch1/archbrdb_91.log' no longer needed for this recovery Log applied. ORA-00279: change 68450 generated at 03/30/00 19:27:18 needed for thread 1 ORA-00289: suggestion : /oracle/admin/brdb/arch1/archbrdb_93.log=suggested | filename | AUTO | CANCEL} auto Log applied. ORA-00279: change 231897 generated at 04/13/00 23:02:31 needed for thread 1 ORA-00289: suggestion : /oracle/admin/brdb/arch1/archbrdb_40.log ORA-00280: change 231897 for thread 1 is in sequence #40 ORA-00278: log file '/oracle/admin/brdb/arch1/archbrdb_39.log' no longer needed for this recovery Log applied. Media recovery complete. SVRMGR> alter database open; Statement processed. 6. Validate that the data file users01.dbf has read-write status.=suggested | filename | AUTO | CANCEL} auto Log applied. Media recovery complete. SVRMGR> SVRMGR> alter database open; Statement processed. SVRMGR> 6. Validate that the status of users01.dbf is read-only.=suggested | filename | AUTO | CANCEL} Log applied. ORA-00279: change 252039 generated at 04/13/00 23:37:13 needed for thread 1 ORA-00289: suggestion : /oracle/admin/brdb/arch1/archbrdb_45.log ORA-00280: change 252039 for thread 1 is in sequence #45 ORA-00278: log file '/oracle/admin/brdb/arch1/archbrdb_44.log' no longer needed for this recovery Specify log: {=suggested | filename | AUTO | CANCEL} CANCEL. 5. B. The init.ora parameter for minimum parallel servers is VALIDATE STRUCTURE command, the DBVERIFY utility, and the init.ora parameter DB_ BLOCK_CHECKING. Finally, this chapter shows you how to configure and use the Oracle LogMiner utility to undo changes in the database. You do this by extracting the SQL statements from the archive logs. These statements can then be redone or undone in the database, depending on the desired outcome..log, generally referred to as the alert log. The is the Oracle system identifier for the instance in question. In our examples, this filename will be alert_brdb.log. The alert log file records all start-ups, shutdowns, log switches, some ALTER commands, storage problems, and a myriad of error-related statements. VALIDATE STRUCTURE command VALIDATE STRUCTURE The ANALYZE TABLE VALIDATE STRUCTURE command validates the integrity of the structure of the object being analyzed. This command is either successful or not successful at the object level. Therefore, if this command returns an error for the object being analyzed, you would need to completely rebuild it. If no error is returned, the object is free of corruption and would not need to be re-created. The following is an example of the command with and without error: SVRMGR> analyze table test.t3 validate structure; * ERROR at line 1: ORA-01498: block check failure - see trace file SVRMGR> analyze table t1 validate structure; Statement processed. SVRMGR> VALIDATE STRUCTURE command, you need this information saved in the REPAIR_TABLE. SVRMGR> ! more check_obj.sql --determine what block is corrupt in a table set serveroutput on size 100000; declare rpr_count int; begin rpr_count := 0; dbms_repair.check_object( schema_name => 'TEST', object_name => 'T3', repair_table_name => 'REPAIR_TABLE', corrupt_count => rpr_count); dbms_output.put_line('repair block count: ' ||to_char(rpr_count)); end; VALIDATE STRUCTURE command, the DBVERIFY utility, and the DB_BLOCK_CHECKING init.ora parameter. This chapter also showed you how to use the DBMS_REPAIR package and the DBVERIFY utility to identify block corruption. The DBMS_ REPAIR example demonstrated how to fix corruption. You also saw the use of the Oracle LogMiner utility to fix logical corruption. VALIDATE STRUCTURE DBVERIFY DB_BLOCK_CHECKING LogMiner V$LOGMNR_CONTENTS[email protected] RMAN-08002: starting full resync of recovery catalog RMAN-08004: full resync complete RMAN> RMAN> views. There are 24 of these views in the 8i recovery catalog. To get a list of these views, you can point to the recovery catalog database rcdb and perform the following query: [oracle@DS-LINUX /oracle]$ . oraenv ORACLE_SID = [rcdb] ? [oracle@DS-LINUX /oracle]$ sqlplus system/manager SQL*Plus: Release 8.1.5.0.0 - Production on Mon Jul 24 21:29:16 2000 (c) Copyright 1999 Oracle Corporation. reserved. OFFLINE IMMEDIATE'; OFFLINE D. Change the initialization parameter file 36. When you create a profile, if you do not specify values for a certain RENAME FILE TO for each data file. See Chapter 5 for more information. 45. C. NLS parameters can be specified for a session by using the ALTER BEGIN BACKUP. The database must be in ARCHIVELOG mode; individual tablespaces cannot be in ARCHIVELOG mode. See Chapter 12 for more information. VALIDATE STRUCTURE A command that checks a table for corrupt blocks. archive logs Logs that are copies of the online redo logs and that are saved to another location before the online copies are reused. ARCHIVELOG A mode of database operation. When the Oracle database is run in ARCHIVELOG mode, the online redo log files are copied to another location before they are overwritten. These archived log files can be used for point-in-time recovery of the database. They can also be used for analysis. archiver process (ARCn) to archive log files. 
