Ships from: acton, MA
Usually ships in 1-2 business days
Foreword by Dr. Arthur M. Langer.
Experience Oracle database administration now! This integrated book-and-Web learning solution teaches core Oracle DBA skills you need, hands-on, through real-world labs, exercises, Q&As, and a Web-based training site. You'll experience aspects of day-to-day Oracle database administration, including managing the database and its physical layout, ensuring security and data integrity, providing for backups, and more. Your free Web-based training module includes a Virtual Study Lounge where you can interact with other learners, discover new interactive Q&As, and get book updates.
Every Prentice Hall Interactive Workbook is fully integrated with its own exclusive Web site, giving you all this and more:
Experience the skills you need! Melanie Caffrey and Douglas Scherer are experienced Oracle database professionals who together have taught hundreds of students in Oracle database technology. Benefit from their combined knowledge and ehands-on, through practical exercises, self-review questions, and real-world answers. Exclusive "Test Your Thinking" exercises lead you to a practiced understanding of the subject.
The Oracle DBA Interactive Workbook presents 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.
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 ComputerTechnology 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 the Oracle SQL Interactive Workbook in the Prentice Hall Oracle Interactive Workbook series.
There are software as well as knowledge requirements necessary to complete the exercise sections of the workbook.
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.
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.
You will need access to the Internet and the WWW so that you can access the companion Web site for this book through--
...
| About the Authors | xiii | |
| Foreword | xv | |
| Acknowledgments | xvii | |
| Introduction | xxi | |
| Chapter 1 | The Database Administrator's Job | 1 |
| LAB 1.1 | The Role of the Oracle DBA | 2 |
| 1.1.1 | Understand About Becoming a DBA | 3 |
| 1.1.2 | Understand the Role of the DBA | 3 |
| LAB 1.2 | Sources for Oracle DBA Help | 6 |
| 1.2.1 | Prepare for Tasks | 8 |
| 1.2.2 | Find Oracle Help | 9 |
| Chapter 1 Test Your Thinking | 13 | |
| Chapter 2 | Creating Your Database | 15 |
| LAB 2.1 | OFA Basics | 16 |
| 2.1.1 | Identify your ORACLE_BASE Location | 17 |
| 2.1.2 | Identify your ORACLE_HOME Directory | 18 |
| 2.1.3 | Identify and Configure your Admin Directory | 19 |
| 2.1.4 | Identify the Location for your Database Files | 20 |
| LAB 2.2 | Creating and Configuring an Oracle Database | 25 |
| 2.2.1 | Edit the Instance Configuration File | 28 |
| 2.2.2 | Write Scripts to Create the Database | 34 |
| 2.2.3 | Create the Database Using the Create Scripts | 39 |
| 2.2.4 | Test Database Viability | 44 |
| 2.2.5 | Set the ROLLBACK_SEGMENTS Parameter | 46 |
| 2.2.6 | Review the Scripts That Create the Data Dictionary | 47 |
| Chapter 2 Test Your Thinking | 56 | |
| Chapter 3 | Oracle Networking: Configuring Basic Net8/SQL*NET Components | 57 |
| LAB 3.1 | Setting up Network Configuration Files | 58 |
| 3.1.1 | Manually Configure the tnsnames.ora File | 58 |
| 3.1.2 | Manually Configure the listener.ora File | 62 |
| LAB 3.2 | Managing the Oracle Listener | 67 |
| 3.2.1 | Start and Stop the Listener | 68 |
| 3.2.2 | Issue Other Listener Commands | 70 |
| 3.2.3 | Perform a Loopback Test | 71 |
| LAB 3.3 | Configuring the Multi-Threaded Server | 76 |
| 3.3.1 | Configure and View Information about the MTS Environment | 76 |
| Chapter 3 Test Your Thinking | 81 | |
| Chapter 4 | Tablespaces | 83 |
| LAB 4.1 | Tablespaces: Logical Areas of Storage | 85 |
| 4.1.1 | What a Tablespace is | 87 |
| 4.1.2 | What a Tablespace is Used For | 87 |
| LAB 4.2 | How Tablespaces Relate to the Oracle Database and How to Manipulate Them | 91 |
| 4.2.1 | Understand The Relationship of Tablespaces to Physical Files | 93 |
| 4.2.2 | Create a Tablespace | 94 |
| 4.2.3 | Delete a Tablespace | 95 |
| Chapter 4 Test Your Thinking | 102 | |
| Chapter 5 | Segments and Extents | 103 |
| LAB 5.1 | Segments | 104 |
| 5.1.1 | Define a Segment | 104 |
| 5.1.2 | Identify Common Segment Types | 105 |
| LAB 5.2 | Extents | 111 |
| 5.2.1 | Define an Extent | 111 |
| 5.2.2 | Understand Segment and Extent Parameters | 113 |
| 5.2.3 | Understand Free Space | 115 |
| Chapter 5 Test Your Thinking | 127 | |
| Chapter 6 | Data Blocks | 129 |
| LAB 6.1 | Units of Storage Called Data Blocks | 130 |
| 6.1.1 | Define a Block | 130 |
| 6.1.2 | Understand the Makeup of a Block | 134 |
| 6.1.3 | Recognize the Physical Record Format | 135 |
| LAB 6.2 | Block-Level Storage Parameters | 145 |
| 6.2.1 | Understand Block-Level Storage Parameters | 145 |
| 6.2.2 | Understand Row Migration and Row Chaining | 149 |
| 6.2.3 | Understand Free Lists | 151 |
| Chapter 6 Test Your Thinking | 156 | |
| Chapter 7 | Redo Logs | 157 |
| LAB 7.1 | Redo Logs | 158 |
| 7.1.1 | Understand Why Redo Logs Are Important | 158 |
| 7.1.2 | Understand How LGWR Writes To Redo Logs | 161 |
| LAB 7.2 | Recovering Data with Redo Logs | 170 |
| 7.2.1 | Understand How Redo Logs Are Used In Recovering Lost Data | 170 |
| Chapter 7 Test Your Thinking | 178 | |
| Chapter 8 | Datafiles | 179 |
| LAB 8.1 | Datafiles | 180 |
| 8.1.1 | Understand Datafiles | 180 |
| 8.1.2 | Understand the Relationship between Datafiles and an Instance | 182 |
| LAB 8.2 | Manipulating Your Datafiles | 189 |
| 8.2.1 | Create, Manipulate, and Delete a Datafile | 189 |
| Chapter 8 Test Your Thinking | 199 | |
| Chapter 9 | User Creation | 201 |
| LAB 9.1 | Creating and Manipulating Users | 202 |
| 9.1.1 | Create and Manipulate Users | 202 |
| Chapter 9 Test Your Thinking | 215 | |
| Chapter 10 | Privilege and Resource Groups | 217 |
| LAB 10.1 | Creating and Manipulating Privileges | 218 |
| 10.1.1 | Create and Manipulate Privileges | 218 |
| LAB 10.2 | Creating and Manipulating Roles | 230 |
| 10.2.1 | Create and Manipulate Roles | 230 |
| LAB 10.3 | Creating and Manipulating Profiles | 237 |
| 10.3.1 | Create and Manipulate Profiles | 237 |
| Chapter 10 Test Your Thinking | 249 | |
| Chapter 11 | Auditing | 251 |
| LAB 11.1 | Auditing Sessions, Auditing Database Actions, and Auditing Objects | 252 |
| 11.1.1 | Audit Sessions, Audit Database Actions, and Audit Objects | 252 |
| Chapter 11 Test Your Thinking | 269 | |
| Chapter 12 | Rollback Segments | 271 |
| LAB 12.1 | Rollback Segment Management | 272 |
| 12.1.1 | Configure and Maintain Rollback Segments | 272 |
| Chapter 12 Test Your Thinking | 294 | |
| Chapter 13 | Locking | 295 |
| LAB 13.1 | Locking and Data Consistency and Concurrency | 296 |
| 13.1.1 | Create, Review, and Eliminate Locks | 296 |
| Chapter 13 Test Your Thinking | 310 | |
| Chapter 14 | Logical Backup and Recovery | 311 |
| LAB 14.1 | Backup and Recovery Using the Import and Export Utilities | 312 |
| 14.1.1 | Perform a Logical Backup Using the Export Utility | 312 |
| 14.1.2 | Perform a Logical Recovery Using the Import Utility | 315 |
| Chapter 14 Test Your Thinking | 325 | |
| Chapter 15 | Physical Backup and Recovery | 327 |
| LAB 15.1 | Backup and Recovery Using Archivelog Mode | 328 |
| 15.1.1 | Perform a Physical Backup and Perform Different Types of Archiving | 328 |
| 15.1.2 | Simulate a Media Failure | 338 |
| 15.1.3 | Perform a Complete Offline Recovery | 341 |
| 15.1.4 | Perform a Complete Online Recovery | 344 |
| Chapter 15 Test Your Thinking | 364 | |
| Chapter 16 | Application and SQL Optimization | 365 |
| LAB 16.1 | Using the Autotrace Command and TKPROF Utility | 366 |
| 16.1.1 | Set up SQL*Plus's AUTOTRACE Utility | 373 |
| 16.1.2 | Determine the Optimizer Mode | 374 |
| 16.1.3 | Analyze Tables and Indexes | 375 |
| 16.1.4 | Interpret the Output of the Execution Plan | 376 |
| 16.1.5 | Use the TKPROF Utility | 377 |
| Chapter 16 Test Your Thinking | 401 | |
| Chapter 17 | Database Tuning and Optimization | 403 |
| LAB 17.1 | Optimizing the Use of Memory and Disk Resources | 404 |
| 17.1.1 | Gather Performance Statistics | 404 |
| 17.1.2 | Monitor and Enhance Memory Usage | 407 |
| 17.1.3 | Determine I/O Distribution and Level of Contention | 413 |
| 17.1.4 | Analyze Performance Statistics | 414 |
| Chapter 17 Test Your Thinking | 425 | |
| Appendix A | Answers to Self-Review Questions | 427 |
| Appendix B | Recommended Reading List | 439 |
| Appendix C | Raid Synopsis | 441 |
| Appendix D | Environment Settings to Remember | 443 |
| Index | 449 |
The Oracle DBA Interactive Workbook presents 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 forThis 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 the Oracle SQL Interactive Workbook in the Prentice Hall Oracle Interactive Workbook series.
What you'll needThere are software as well as knowledge requirements necessary to complete the exercise sections of the workbook.
SoftwareNote 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 8iYou 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 WWWYou 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/schererHere you will find the files that are necessary for completing the exercises.
KnowledgeTo 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 OrganizedThe 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:
Depending on the bread 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 siteThe companion Web site is located at
http://www.phptr.com/scherer
Here you will find two very important items:
Also, check the Web site for periodically updated information about the book.
IMPORTANT notes about COMPLETING THE exercises
Overview