Oracle DBA Interactive Workbook
by Melanie Caffrey, Douglas Scherer
Category: Computers

Introduction TheOracle DBA Interactive Workbookpresents an introduction to Oracle database administration in a unique and highly effective format. It is organized more like a teaching aid than a reference manual in that to learn basic administration techniques, you are challenged to work through a set of guided tasks rather than to read through descriptions. There is a list of additional reading material in Appendix B that you can reference if you wish to delve deeper into any of the topics covered in this book. This book is intended for beginners in the world of Oracle database administration. It will take you through a set of tasks, starting with creating a database to using some of the Oracle database tuning, and backup and recovery utilities. Those experienced with Oracle databases will recognize that a workbook covering the topic of database administration will be an extremely useful introduction for the beginner, but it cannot be used as a deep study of the Oracle database environment. After working through this book, you will have been launched into the exciting and interesting (sometimes nerve-racking) world of database administration, but it will take years of experience to become a senior database administrator. Who this book is for This book is intended for anyone who needs a quick and detailed introduction to Oracle database administration tasks. It is not the intention of this book to provide an in-depth study of the Oracle database server. The foundation for this book was developed to supplement the Introduction to Oracle Database Administration class in the Database Track at Columbia University's Computer Technology and Applications (CTA) program in New York City. The book's sidebars and answers often reflect questions that students have asked in class. The student body entering the CTA program range from those who have years of experience in the IT field but no experience with Oracle, to those with absolutely no experience in IT and even no experience with PCs. The Introduction to Oracle Database Administration class is offered as an elective in the fourth and final term of the CTA program's curriculum. So, while it is geared for beginners with little or no previous experience with Oracle database administration, it is expected that the reader will have some experience working with an Oracle database. The reader should be comfortable with relational database concepts as well as SQL. If you are unfamiliar with any of these subjects, refer to theOracle SQL Interactive Workbookin the Prentice Hall Oracle Interactive Workbook series. What you'll need There are software as well as knowledge requirements necessary to complete the exercise sections of the workbook. Software Oracle 7.3.4, 8.0, or 8i. Access to the WWW. Note that prior to beginning the workbook labs, you should already have installed the Oracle Server software (it will make it easier to get through the labs if you do not install the default database, however) and the Net8 or SQL*Net components. Since the instructions vary greatly depending on the platform, you should consult your Oracle documentation (Installation Guide and Getting Started) for installation information. Oracle 7.3.4, 8.0, or 8i You can use either Oracle Standard Edition or Oracle Enterprise Edition. Since you will be performing actions that may affect other databases, you should not work through the tasks in this book in an environment where there are critical data, programs, etc.! Since Oracle frequently improves and changes its products, new versions are released all the time. However, the concepts covered in this book are fundamental to the Oracle RDBMS (hereafter called the Oracle Server) and are unlikely to change significantly in the near future. Even if your version of the database is slightly different than the one listed here, you should still be able to make use of this book. The tasks and descriptions represent core issues of Oracle Server architecture and are therefore universal to all three versions of the Oracle Server. There are occasional notes to point out a specific issue regarding a specific version. Additionally, you should be familiar with SQL*Plus, the tool supplied with the database to interact with it in a command-line fashion. Access to the WWW You will need access to the Internet and the WWW so that you can access the companion Web site for this book through-- http://www.phptr.com/scherer Here you will find the files that are necessary for completing the exercises. Knowledge To complete the exercises, you should be familiar with relational database concepts and understand what an Entity Relationship Diagram is. You should also be comfortable using SQL to access and manipulate database objects such as tables, constraints, sequences, and so on. If you are not familiar or comfortable with these subjects, then it is recommended that you refer to the Oracle SQL Interactive Workbook in the Prentice Hall Oracle Interactive Workbook series. You should also feel comfortable working in your operating system's command-line environment. How this Book Is Organized The intent of this workbook is to teach you about Oracle database administration by presenting you with a series of tasks and challenges, followed by detailed solutions to those challenges. At times, an individual task acts as a complete unit of work, while other tasks are developed through a series of simple actions. Each chapter has a series of labs that introduce topics. The labs are composed of tasks, which are typical to the daily work of a database administrator. Beyond the labs, topics are further explored through questions and answers. What follows is the common layout of the chapters: Chapter Objectives A list of subjects the chapter will cover. Chapter Text An overview of chapter subjects. Lab Objectives A list of subjects the lab will cover. Lab Text An introduction to the lab subject, occasionally including steps that prepare the Oracle Server for the exercises. Exercises Questions that require hands-on interaction with the Oracle Server. The exercises will guide you through learning the subject introduced in the lab text. Exercise Answers Answers and discussion of the exercise subject. Self-Review Questions Multiple-choice questions to review lab material. Test Your Thinking Project questions to supplement the lab material. Depending on the breadth of the subject, a given chapter may have more than one lab, and a given lab may have more than one set of exercises. The exercises are not meant to be closed-book quizzes to test your knowledge. On the contrary, they are intended to act as your guide and walk you through a task. You are encouraged to flip back and forth from the question section to the answer section so that, if need be, you can read the discussions as you go along. The chapters and their exercises must be completed in sequence as the material in later chapters builds on what is presented earlier. Chapter 1, "The Database Administrator's Job," introduces you to the world of database administration. Chapter 2, "Creating Your Database," walks you through the steps of manually creating your own database. Chapter 3, "Oracle Networking: Configuring Basic Net8/SQL*Net Components," guides you through setting up the components necessary for remote users to connect to your database over a network. Chapter 4, "Tablespaces," teaches you how to create and manipulate tablespaces, and introduces you to logical storage constructs. Chapter 5, "Segments and Extents," provides a more in-depth look at these higher level logical storage constructs, which were introduced in Chapter 4. Chapter 6, "Data Blocks," examines data blocks, the smallest unit of Oracle storage. Chapter 7, "Redo Logs," teaches you the basics of how redo logs operate and how they are written to. Chapter 8, "Datafiles," describes how to view information about and manipulate datafiles. Chapter 9, "User Creation," teaches you how to create and alter user accounts. Chapter 10, "Privilege and Resource Groups," introduces you to the concepts and effects of privileges, roles, and resource limits and shows you how to assign them to user accounts. Chapter 11, "Auditing," examines Oracle's mechanism for recording different types of database activity for purposes of maintaining database security and/or keeping records of billable activity. Chapter 12, "Rollback Segments," presents a study of the use and operation of rollback segments and how they provide an Oracle database with read-consistency and "undo" information. Chapter 13, "Locking," guides you through exercises that demonstrate the importance of Oracle locking mechanisms and introduces you to the concepts of data "consistency" and "concurrency." Chapter 14, "Logical Backup and Recovery," covers logical data backups and recoveries using the Oracle-provided import (IMP) and export (EXP) utilities. Chapter 15, "Physical Backup and Recovery," teaches you about different types of database failures, walks you through a simulation of a physical database failure and recovery, and explains the importance of archiving your redo log files. Chapter 16, "Application and SQL Optimization," provides a look at optimizing standalone and application SQL code through Oracle-provided facilities such as AUTOTRACE, SQLTRACE, and TKPROF. Chapter 17, "Database Tuning and Optimization," presents an introductory look at the other side of tuning an Oracle database by showing you how to gather information about and, if necessary, tune your memory and disk I/O through the use of the Oracle-provided utilities, UTLBTAT.SQL and UTLESTAT.SQL. About the companion web site The companion Web site is located at http://www.phptr.com/scherer Here you will find two very important items: Files you will download for specific tasks in the workbook. Answers to the Test Your Thinking questions. Also, check the Web site for periodically updated information about the book. IMPORTANT notes about COMPLETING THE exercises Complete the chapters and exercises in the order they're given. If the labs, exercises, and steps are not done in their proper sequence, you may have to start from the first chapter and work your way back into the material. This is similar to performing these tasks in a real-world environment. Database administration is less forgiving than application coding. In database administration, you cannot simply recompile your code and try the application again. If you leave out a step, you may lose a portion of the database in a way that is unrecoverable--losing your work. The only exception to this is Chapter 3, "Oracle Networking: Configuring Basic Net8/SQL*Net Components," which can be completed after all of the other chapters. In Chapter 2, you will learn about the command SHUTDOWN IMMEDIATE. After successfully completing Chapter 2, you should, from then on perform a SHUTDOWN IMMEDIATE on your database before rebooting or shutting down your computer. For Chapters 2, 3, 13, 15, and 17, you should allocate enough time to finish from beginning to end. It can take from 45 minutes to a couple of hours to complete the exercises in each chapter. The other chapters will flow better if you complete them in one sitting, but it is not as critical to your database if you are unable to do so. Check off the steps as you complete them. The most frequent error students make in the CTA course is not following each step, no matter how insignificant it seems. Learn to walk before you run. If you think there's a better way to accomplish an activity, don't try it out until you've completed the entire Oracle DBA Interactive Workbook. Some of the steps are in the text and some are explicitly in the exercises. If you check off each step as you go along, you will be more inclined to perform each task, and will have a way to trace whether you've missed a step if you run into trouble. The questions require you to think like an Oracle DBA. For example, you may be asked to look into an Oracle-provided script and report what you think it will do based on the information in the script. In another example, you may perform an exercise to make the database freeze. You're then asked to figure out what the problem is and what the next step should be to fix the problem, based on the knowledge that you've gained by that point in the book. Of course, the solution is given to you, but do your best to consider the problem before reading the provided answer. In either case, make your solution to the problem fall within the scope of the problem set in the chapter. This will be good practice for your work as a DBA, when you will be required to stay focused under pressure. For those completing the tasks in Windows, you should have administrative rights to the machine where the database is located.