Introduction
At the outset, I would like to say that this is not a perfect book. There are deadlines in the book publishing business and books have to go to print whether or not they are perfect. And, there is no living in writing technical books so authors like me have day jobs which means that we cannot devote as much time to perfecting our writing as we would like.
This book is really for beginners in database administration. If you are not a beginner in database administration, I recommend that you get Pro Oracle Database 12c Administration by my fellow Apress author Darl Kuhn instead. Also, did you know that Oracle Corp. provides excellent free reference materials at http://docs.oracle.com? For beginners in database administration, I particularly recommend Oracle Database 2 Day DBA in the “2 Day” series of publications. In fact, I suggest that you take a moment to check out 2 Day DBA right now; it may be all that you want or need.
In general, I have found most Oracle Database professionals do not take enough advantage of the free Oracle Corp. publications. A plausible defense is that reference manuals are harder to read than books from Apress and other fine publishers. But, in addition to reference manuals, Oracle Corp. also provides many publications that are written in an extremely readable style. Throughout this book, I will be frequently referring you to the free Oracle Corp. publications for more information because I want you to get into the habit of referring to the best source of detailed and reliable information. Besides, I view myself as an interpreter and a teacher, not a regurgitator of free material.
The ancient Chinese classic text Tao Te Ching, Lao Tzu (“Old Master”) says: “The tree which fills the arms grew from the tiniest sprout; the tower of nine stories rose from a (small) heap of earth; the journey of a thousand [miles] commenced with a single step.” I wrote this book to help you take the first steps of your Oracle Database journey. It’s the book I wish I’d had when I first started using Oracle Database so many years ago. It’s the book that I would have liked to have given to the many IT colleagues and friends who, over the years, have asked me to teach them the basics of Oracle Database.
I started my own journey almost two decades ago when my then manager, Bill Schwimmer, gave me the chance to become an Oracle Database administrator back in the days of Oracle 7. Books on Oracle Database were fewer then, and I relied on printed copies of the manuals, which I had to share with the rest of the team; this was in the days before Google, when Netscape Navigator had just appeared on the scene.
Today the Oracle Database manuals can be downloaded for free from the Oracle website. But their size has grown tremendously over the years. The Oracle Database 7.3 SQL reference manual had about 750 pages; the 12c version has almost 2000 pages. You definitely don’t want to be carrying a printed copy of that in your backpack!
The book that you have in your hands is not an exhaustive reference manual by any stretch of the term; it is a more manageable introduction to key Oracle Database administration topics, including planning, installation, monitoring, troubleshooting, maintenance, backups, and performance tuning—to name just a few. You’ll be getting the benefit of my experience not just the party line found in the manuals. For example, for reasons explained inside, I give equal time to both Statspack as well as Automatic Workload Repository (AWR).
In this book, you’ll find information that you won’t find in other books on Oracle Database. Here you’ll find not just technical information but guidance on the work practices that are as vital to your success as technical skills. The most important chapter in the book is The Big Picture and the Ten Deliverables. If you take the lessons in that chapter to heart, you can quickly become a much better Oracle database administrator than you ever thought possible.
Who This Book Is For
I was a C programmer before I became a database administrator. For lack of a text like this, it took me quite a while to adjust to my new role. If you are an IT professional who has been thrust into an Oracle Database administration role without the benefit of formal training, or just want to understand how Oracle Database works, then I wrote this book for you.
How This Book Is Structured
The chapters of this book are logically organized into four parts that closely track the way your database administration career will naturally evolve. Part I is a necessary backgrounder in relational database theory and Oracle Database concepts, Part II will teach you how to implement an Oracle Database correctly, Part III will expose you to the daily routine of a database administrator, and Part IV will introduce you to the fine art of performance tuning. Each chapter has a section of exercises that are designed to help you apply the lessons of the chapter. Each chapter also includes a list of reference works that contain more information on the topic of the chapter.
Part I: Database Concepts
You may be in a hurry to learn how to create a database but I hope you will take the time to first understand the underlying theory. You won’t regret it.
Chapter 1: Relational Database Management Systems
Leonardo da Vinci said: “Those who are in love with practice without knowledge are like the sailor who gets into a ship without rudder or compass and who never can be certain [where] he is going. Practice must always be founded on sound theory.” How can you competently administer a relational database management system like Oracle if you don’t really know what makes a “relational” database relational or what a database management system manages for you? This chapter will help you find your bearings and prepare you for what is to come in the rest of the book. However, you probably won’t be satisfied until you’ve seen an Oracle database management system. You will therefore connect to the fully-functional database in a “virtual machine” provided by Oracle Corporation.
Chapter 2: Structured Query Language
All database user activity is conducted in Structured Query Language (SQL), and therefore database administrators need to be intimately familiar with it. The greatest potential for performance improvement usually lies within the software application, not within the database where the application stores its data or within the physical infrastructure where the database is housed. An equally important reason why database administrators need to be intimately familiar with SQL is that all database administration activities such as database maintenance and user management are also conducted in SQL. A third reason is that SQL has deficiencies that must be guarded against. These deficiencies include redundancy, problems introduced by nullable data items, and the absence of prohibitions on duplicate data records.
Chapter 3: Oracle Architecture
Just as an automobile engine has a lot of interconnected parts that must all work well together, and just as an automobile mechanic must understand the individual parts and how they relate to the whole, the Oracle database engine has a lot of interconnected parts, and the database administrator must understand the individual parts and how they relate to the whole. This chapter provides a short overview of the Oracle engine.
Part II: Database Implementation
After spending some time on database theory, you’ll be eager to create your first database. I hope that you take the opportunity to install Oracle on your own XP or Vista laptop—the best way to learn is by doing.
Chapter 4: Planning
Your goal as Oracle administrator is not simply to create a database but to be on time, on budget, and to meet the availability and performance targets of the business. As with any goal, careful planning is the key to success. You have little control over a number of factors that affect the success of your database; for example, application design and testing. This chapter discusses three important issues that are definitely within your circle of influence and that you cannot afford to ignore: licensing, architecture, and sizing.
Chapter 5: Software Installation
In this chapter, I’ll go over a few prerequisites such as obtaining the software, installation guides, and reference manuals. I’ll also discuss the installation of software that precedes the creation of a database. I’ll show you how I installed the Oracle software on my laptop running Windows XP Professional.
Chapter 6: Database Creation
Database creation is easier that you would think; it’s the tasks that come before and after that take a lot of time. In this chapter, I’ll first discuss the “Next-Next-Next; click Finish” method of creating a database. I’ll then briefly discuss some tasks that you should consider performing after you create a database; specifically, installing the RDA and Statspack tools and disabling database features that have not been licensed. Finally, I’ll introduce the manual method of database creation and some basic administrative tasks.
Chapter 7: Physical Database Design
Performance considerations can come to the forefront at any time during the life of the database; new queries can be introduced at any time. Database administrators must therefore understand the mechanisms that can be used to improve performance, and this chapter discusses three broad categories. Indexes can be used to quickly find the data. Partitions and clusters can be used to organize the data. Finally, materialized views and denormalized tables can be used to perform expensive operations like Joins ahead of time.
Chapter 8: User Management and Data Loading
Your job does not end when you create a database; you still have to get the data into it and ensure that those who have a need to use it can do so. This chapter discusses how to control users and how to get large amounts of data in and out of databases. User management and data loading are two common chores performed by database administrators.
Part III: Database Support
The easy part is over. You have created a database and loaded it with data. Now you have to turn your attention to the care and feeding of it.
Chapter 9: Taking Control
If you are going to be responsible for a database, you need to know what it contains and how it is being used. Which are the biggest tables? How are the data files, control files, and log files laid out? How many people have database accounts? How many people use the database at a time? Your first action when you acquire responsibility for a database should be to thoroughly explore it.
In this chapter, you’ll learn about form-based tools such as Enterprise Manager, SQL Developer, and Remote Diagnostic Agent which make it easy to explore the database and simplify the task of database administration.
Chapter 10: Monitoring
When I was growing up, I was sometimes awoken at night by the sound of a walking stick tapping on the ground—it was the night watchman patrolling the neighborhood. He would have had a better chance of surprising any burglars if he’d crept up on them quietly, but I never questioned why he advertised his presence so loudly. Armed only with a walking stick, he would have to rely on strong lungs to wake up the neighborhood if he saw any burglars, so perhaps it was best to advertise his presence and hope that burglars would flee when they heard him coming. Nevertheless, the sound of his stick was comforting—it was good to know that someone trustworthy was watching the neighborhood while we slept.
The database administrator is responsible for watching the database. If something goes wrong with the database that could have been prevented, there is nobody else to blame. As you’ll learn in this chapter, database availability, changes, security, growth, backups, workload, performance, and capacity are some of the areas that should be monitored.
Chapter 11: Fixing Problems
In this chapter, you will watch a real-life problem as it progresses from detection to resolution. You will learn a five-step systematic approach to problem-fixing and the difference between incident management and problem management. I will cover the variety of Internet resources that are available to you, introduce an Oracle knowledge base called MetaLink, and explain how to get technical support from Oracle Corporation. Finally, I will discuss some common database problems.
Chapter 12: Backups
American national hero Benjamin Franklin often wrote anonymous letters to the Pennsylvania Gazette, a prominent newspaper that he himself owned and edited. In one such letter he coined the famous phrase “an ounce of prevention is worth a pound of cure” and, in addition to making several suggestions for the prevention of fires, he suggested that Philadelphia imitate his native Boston in establishing fire stations and employing firefighters; not only should all efforts be made to prevent fires but the city should be adequately prepared to handle the next inevitable fire.
Backups are to a database what fire stations and fire fighters are to a city; we may protect the database against damage the best we can, but we must be prepared if the database ever gets damaged, through user or operator error or hardware failure, and needs to be repaired. In this chapter you’ll learn about the different kinds of backups and the tools used to create them.
Chapter 13: Recovery
In the previous chapter, you learned how to make backup copies of the database; you will now turn your attention to repairing the database if it gets damaged.
Chapter 14: Maintenance
In The Little Prince by Antoine de Saint-Exupéry, the protagonist meets a little prince whose home was on an asteroid. In one of their discussions, the little prince talked about the importance of proper maintenance, saying “Sometimes, there is no harm in putting off a piece of work until another day. But when it is a matter of baobabs, that always means a catastrophe. I knew a planet that was inhabited by a lazy man. He neglected three little bushes …” You can quite imagine what might happen to an asteroid if three little bushes are allowed to grow into immense baobab trees.
In this chapter, we go over the maintenance that is needed to keep your database in peak operating condition.
Chapter 15: The Big Picture and the Ten Deliverables
This is the most important chapter in this book—I discuss the big IT picture and offer very specific guidance in the form of the database administration role’s ten deliverables. Few, if any, other books address this topic. If you take the lessons in this one chapter to heart, you can quickly become a better Oracle Database administrator than you thought possible.
Competency in Oracle technology is only half of the challenge of being a database administrator. If you had very little knowledge of Oracle technology but knew exactly what needed to be done, you could always find out how to do it—there is Google and there are online manuals a-plenty. Too many Oracle database administrators don’t know what to do and what they have when they are through is “just a mess without a clue.”
Part IV: Database Tuning
There’s no such thing as a completely self-tuning car and there’s no such thing as a completely self-tuning database. Performance tuning can often be a puzzle that requires a creative solution.
Chapter 16: Database Tuning
Database tuning can be a complex exercise but it can be facilitated by a systematic approach. This chapter describes a systematic five-step approach to performance tuning. It also presents the most important tools provided by Oracle to help with performance tuning; Statspack is emphasized because newer tools such as AWR and ADDM require costly licenses and are not available at most sites. In particular, you will learn a powerful method of mining the Statspack repository for data on performance trends. A highlight of this chapter is the very detailed performance tuning exercise at the end; it will reinforce the lessons of the chapter.
Chapter 17: SQL Tuning
Perhaps the most complex problem in database administration is SQL tuning, and it is not a coincidence that I left it for the very end. The paucity of books devoted to SQL tuning is evidence of the difficulty of the topic. The only way to interact with Oracle, to retrieve data, to change data, to administer the database, is via SQL. Oracle itself uses SQL to perform all the work that it does behind the scenes. SQL performance is therefore the key to database performance; all database performance problems are really SQL performance problems even if they express themselves as contention for resources.
In this chapter, I will present some of the causes of inefficient SQL and some of the common techniques of making SQL more efficient. Most of the time will be spent working through a case study; I will show you a fairly typical SQL statement and improve it in stages until it hits the theoretical maximum level of performance that is possible to achieve.
Source Code and Updates
As you work through the examples in this book, you may decide that you prefer to type in all the code by hand. You may want to do this because it is a good way to get familiar with the coding techniques that are being used.
Whether you want to type the code in or not, all the source code for this book is available in the Source Code section of the Apress web site (http://www.apress.com). If you like to type in the code, you can use the source code files to check the results you should be getting—they should be your first stop if you think you might have typed in an error. If you don’t like typing, then downloading the source code from the Apress web site is a must! Either way, the code files will help you with updates and debugging.
Errata
Apress makes every effort to make sure that there are no errors in the text or the code. However, to err is human, and as such we recognize the need to keep you informed of any mistakes as they’re discovered and corrected. Errata sheets are available for all our books at http://www.apress.com. If you find an error that hasn’t already been reported, please let us know.
The Apress web site acts as a focus for other information and support, including the code from all Apress books, sample chapters, previews of forthcoming titles, and articles on related topics.
Contacting the Author
Join the Google group Beginning Oracle Database 12c Administration at https://groups.google.com/forum/#!forum/beginning-oracle-database-12c-administration. There you will find additional materials and can discuss the material in this book. If you have private comments, you may send them to [email protected].