Oracle DBA Interactive Workbook

About

Features

• Comprehensive, clear writing style.

  • Provides students with an understandable introduction to the Oracle Forms.

• Guided hands-on interaction.

  • Provides students with detailed step-by-step instructions on how to use the product.

• Real-world lab exercises with answers.

  • Draws students in and reinforces the material so they can more easily understand the subject.

• Self-Review and Test Your Thinking questions—In each chapter.

  • Enables students to test their understanding and solidify skills learned.

• Companion Website.

  • Provides students with necessary files and answers to Test Your Thinking questions.

• Advice/Quote icons.

  • Focuses students' attention on special advice or guidelines.

• Tips boxes.

  • Provides students with helpful time- and trouble-saving tips and tricks.

• Warning flags.

  • Provides students with precautions and information designed to avoid computer troubles.

• Web flags.

  • Directs students to the book's companion Web site for reference.



Description

• Copyright 2001
• Dimensions: K
• Pages: 496
• Edition: 1st

• Book
• ISBN-10: 0-13-015742-2
• ISBN-13: 978-0-13-015742-3

Oracle DBA Interactive Workbook is the perfect, hands-on course in Oracle database administration for DBAs at all levels of experience -- whether or not they have access to a live Oracle database. It covers the latest version, Oracle 8i -- making it perfect for DBAs upgrading from older versions, Web developers working with database-enabled Internet applications, and many others. This hands-on, book-and-web package introduces every aspect of day-to-day Oracle 8i database administration, including supporting and organizing the database engine, managing its physical layout, ensuring security and data integrity, providing for backups, and much more. A free Web-based training module includes a Virtual Study Lounge where readers can interact with the author; "Test Your Thinking" project solutions and detailed explanations, interactive Q&As and self-review exercises, new projects, book updates, and more. For database administrators at all levels moving to Oracle for the first time.



Sample Content

Table of Contents

(NOTE: All chapters conclude with Test Your Thinking.)

About the Authors.


Forward.


Acknowledgements.


Introduction.


1. The Database Administrator's Job.

The Role of the Oracle DBA. Sources for Oracle DBA Help.

2. Creating Your Database.

OFA Basics. Creating and Configuring an Oracle Database.

3. Oracle Networking: Configuring Basic Net8/SQL*Net Components.

Setting up Network Configuration Files. Managing the Oracle Listener. Configuring the Multi-Threaded Server.

4. Tablespaces.

Tablespaces: Logical Areas of Storage. How Tablespaces Relate to the Oracle Database and How to Manipulate Them.

5. Segments and Extents.

Segments. Extents.

6. Data Blocks.

Units of Storage Called Data Blocks. Block-Level Storage Parameters.

7. Redo Logs.

Redo Logs. Recovering Data with Redo Logs.

8. Datafiles.

Datafiles. Manipulating Your Datafiles.

9. User Creation.

Creating and Manipulating Users.

10. Privilege and Resource Groups.

Creating and Manipulating Privileges. Creating and Manipulating Roles. Creating and Manipulating Profiles.

11. Auditing.

Auditing Sessions, Auditing Database Actions, and Auditing Objects.

12. Rollback Segments.

Rollback Segment Management.

13. Locking.

Locking and Data Consistency and Concurrency.

14. Logical Backup and Recovery.

Backup and Recovery Using the Import and Export Utilities.

15. Physical Backup and Recovery.

Backup and Recovery Using Archivelog Mode.

16. Application and SQL Optimization.

Lab 16.1 Using the AUTOTRACE Command and TKPROF Utility.

17. Database Tuning and Optimization.

Optimizing the Use of Memory and Disk Resources.

Appendix A: Answers to Self-review Questions.


Appendix B: Recommended Reading List.


Appendix C: Raid Synopsis.


Appendix D: Environment Settings to Remember.


Index.

Preface

Introduction

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 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 the Oracle SQL Interactive Workbook in 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:

1. Files you will download for specific tasks in the workbook.
2. 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.



Updates

Submit Errata

