Oracle Database 11g : Underground Advice for Database Administrators: Beyond the basics

The story above shows that protecting the database is considered one of the ongoing responsibilities of a DBA. So, how does one accomplish this goal? You do it with the right knowledge and tools. At the same time you can't make everyone else an enemy while staunchly protecting the database. There are security decisions that you will have to stick to, but you should try not to stand in the way of getting the work done. It is a position of respect that you are striving for, not a position based on fear.

How does one gain this knowledge? Obviously, one of the first answers is to read the supplied documentation, but that isn't enough. Other less obvious ways are interacting with your peers at conferences, joining Oracle User Groups, conversations with other DBAs, office meetings with IT personnel, writing papers and giving presentations, interacting with technical support sites, and e-mailing lists. Try gathering information about a particular issue or problem from as many sources as possible before implementing a possible solution. Always take advice with a pinch of salt (including this book). You will be able to make a better and more informed decision by doing as much research and using as many resources as possible.

With that being said, acquiring information is only one of the initial steps to resolving a problem. These investigate different scenarios, as there will usually be more than one approach to fix a complex issue or problem. It is only by working through an issue with hands-on experience that a DBA gains the confidence along with the ability to survive a production-crisis situation. To accomplish this, you need adequate resources, such as the correct hardware, software, and most importantly, scheduled time to do a practice run through various scenarios.

A DBA needs a box that no one else is dependent on so that they can create and destroy databases at will. Adequate hardware for testing purposes is almost as important as the production hardware for database administration. The software side is significant as well and requires much more work to keep up with all the technological and security updates. Change is constant in the software industry. You will always be migrating to the next software version (Oracle software or operating system), patch set, Critical Patch Update (CPU), Patch Set Updates (PSU), one-off patch, or hardware replacements.

One of the questions most frequently asked by DBA managers is: what type of person should I hire to be the DBA? This usually means they weren't happy with the previous candidates. To be good at the job takes a tough but not mean attitude, getting it just right, and paying careful attention to the smallest of details without losing sight of the big picture. You must know everything about the Oracle software and if you are not able to find someone who does, step out of the office, communicate with other IT professionals, and pass on the information you learn to others who need help as well. Teaching someone else is the best test to see if you really know it well. Above all, don't be afraid to do the right thing when it comes to database security and privacy issues—in most situations, a DBA is all powerful, but needs a certain amount of self-restraint.

This book assumes that you could be any type of DBA, working on a single, small database, right up to many large databases. A Very Large Database (known as VLDB) will take several DBAs to run, so teamwork is important. Don't be surprised, as the newcomer, if you are relegated to minor duties for several years. Also, don't assume that if your database is not large, its tuning isn't as important: Oracle Maximum Availability Architecture (MAA) and Optimal Flexible Architecture (OFA) standards will apply to all Oracle customers, not just VLDBs. Both MAA and OFA are discussed later in this book.

There are general practitioners (GPs) who perform routine health exams and treat common, everyday problems such as the flu or a muscle strain. You go to a specialist when the GP has reached his or her limit of expertise. If your organization is large, then there will be an opportunity for you to become a specialist as you gain experience. For now you should become familiar with all aspects of the database as a generalist DBA. Don't ignore or discount certain areas of database administration as being unimportant; if you are responsible for this database, then all areas will be important. Your customers will often see their requirements as more important than yours, so adapting to meet everyone's needs will require flexibility on your part.

Your tools should be extensible, flexible, and ubiquitous. Now that you have some knowledge, what tools are needed? As you go through these chapters, there will be mention of what tools are available time and again. Reasons for choosing a certain tool will be provided: make your decisions based on availability, cost, and adaptation to your particular need or environment, and personal preferences. Be open and willing to change to a new tool because it may just make you more efficient. Don't let the flashy software with high price tags and marketing hype influence purchasing decisions. There are a lot of great open source tools that are available, which may require a little more investment in time to configure, install, or maintain, but which are well worth it. You will only be as good as the tools you pick for this job. It is time to pick the programs you will become best at—command line or GUI-based or command line-based Unix scripting in combination with SQL*Plus are excellent for manipulating both the database and operating system for almost any task that needs to be done. Graphic-based tools have their place, but there will be times that you will need to revert to command-line versions of those tools, especially for troubleshooting. Proficiency will be the only way to be effective, efficient (across the enterprise) and ubiquitous—common among all of the systems or databases you will have to administer.

What is a dinosaur? It is someone who is stuck in their ways and too comfortable where they are to move (change things). You can spot a dinosaur if the answers to the following questions are positive.

Now that you know how to spot a dinosaur, the reasons for avoiding them may not be obvious. It is the sign of an insecure, cloistered individual who refuses to change because of fear of the unknown. Realize that there is no real way to know everything about Oracle and that you will need to be open and willing to change in order to keep ahead of the steep learning curve ahead of you.

It is normal that you might be a bit overwhelmed as a new DBA for your first year. If this situation extends for much longer than that, then something is wrong and it is obviously time to ask for long-term help. This situation is especially true if you are the only DBA or the first Oracle DBA in your organization. Help can come in the form of changing your phone number to non-published within your organization, changing who you directly answer to, or adding another DBA (on-site, remote, or temporary consultant). Compare and evaluate the regular tasks you perform with other DBAs. It doesn't matter if you are only a backup or junior DBA at this point, it is the best time to learn these valuable concepts and still be protected from disrupting the production environment. Everyone wants you to succeed as a DBA because it is a position with a huge amount of responsibility, long hours, and it requires ongoing training to stay competent.

Are you barely able to answer requests as they pour in? Ask that those requests be filtered or first reviewed by a front-line tech support personnel. The exception would be if that is your job—a front-line DBA to serve as part of a tech support organization. On a side note, third-party vendors will put tech people in support positions who give DBA-like advice, but be very wary as they often have little to no actual experience. Remember, a lot of technical helpers only play with databases all day. They don't actually have to manage a live production one with your critical data in it. Learn to say no to these individuals.

A DBA is most often placed underneath the head of IT (most often the CIO) because the database is often central to the entire IT department. If you are one of the many DBAs, your organization will most often have an immediate DBA supervisor that you will answer to first, before the head of IT.

The DBA is not the sole source for any issue related to Oracle, especially if your entire IT department is new to Oracle. Their job is not to train everyone on the basics of Oracle—they don't have the time and probably aren't the best people for that job. Most DBAs don't have the experience of teaching others; they are usually best in a one-on-one situation training a backup or junior DBA. They also shouldn't be subjected to the whims of upper management when there is a technical issue that can't be resolved by the standard front-line personnel, or when the perception is that the issue won't be handled quickly enough.

After a year on the job, you should be proficient enough with My Oracle Support (MOS, Oracle's support site renamed from Metalink) to find out most of the answers on your own and be comfortable enough to implement the solution without help. Using an Internet search engine may be another source of information (beware that the information may not be timely, complete, or even accurate), but it is a source you turn to when other more dependable ones aren't productive.

When you create a MOS Service Request, don't expect an answer that day unless the severity level and contract agreement dictates that level of support. Oracle Engineer support help may be hours or even days from now. Reach out to the Oracle community for help at the same time you enter a Service Request, which often will get a quicker if not better answer than Oracle support, because these people will have encountered the same problem at some point. Not everyone will give reliable advice; learn to recognize those that do. Most often a test case with reproducible results is the best indicator of expertise, or at least a well-thought-out process. E-mail lists and forums have been known in the past for shooting down people who don't read the documentation first. The exception would be if you are at a total loss on how to fix a problem or are experiencing a disaster at a particular moment.

Responsibilities include installing, configuring, and managing the database, and these responsibilities can be divided into tasks scheduled to occur at certain intervals. This is a generalized list and, depending on your environment, may or may not be applicable. Most of the outlined tasks will be investigated further in later chapters in the book.

Monitoring and Log Rotation tasks can be done with Enterprise Manager, Grid Control, Unix shell scripting, DBMS_Scheduler, Perl, third-party database tools, or a combination of any of these.

A few years ago having the database down on a regular basis was normal and considered necessary just for backups. But it is no longer needed in these days of 24x7 IT operations and expanded Service Level Agreements (SLAs).

The database most often will only have to be down for patches or upgrades, which can be either Oracle or application-specific. You should no longer need to have the database down to do backups. If cold backups are a norm at your workplace, then this is a sign of a dinosaur. Little to no downtime applies to production instances, but non-production should be mostly up during working hours with only intermittent outages.

Each organization has its own Outage Handling Procedures—depending on whether it is planned or unplanned downtime. Most DBAs are assigned a database to be the primary contact when there is an outage issue on call. Outage handling usually includes something similar to the following:

Outage handling is an important process and includes quite a few non-DBA team members who must coordinate efforts, and not just point fingers to get this issue resolved. These types of procedures should be well documented (in both print and online form for disasters) with a definite line of authority as to who can execute the procedures with administrative approval.

There are many things that could cause the database to crash or become unavailable to end users:

Speaking of the human side of things, the following list details how to avoid the really bad things that can happen to even experienced DBAs. Remember if you are a novice or new DBA, you shouldn't have access to certain servers or databases because your superior understands how easy it is to do the wrong thing at the wrong time. The following list may seem harsh, full of should and don't statements, but I felt it was important to state exactly what others have experienced or witnessed personally. Think of it as an experienced DBA giving someone under them some good advice about what to avoid.

As a new DBA, one of the hardest things to figure out is the philosophy behind the position. The best DBAs in the business seem to have an underlying sense or gut feeling when something is wrong, and when to speak up and say no.

Treat others as you would like to be treated with a certain amount of respect. There should be lines drawn based on your position in the organization. Those boundaries are there for a reason—responsibilities and duties are attributed to the team player based on those boundaries. You will be working closely with the team and you will depend on them doing their job. A database is dependent on a reliable framework of underlying software and hardware, which most often is not your responsibility; this makes your job very dependent on your fellow team members' expertise.

This chapter helped to show what a DBA does all day. All rules may need to be bent or modified to suit your organization's needs. Only experience and testing will allow your team to decide how best to proceed on any single rule or suggestion in this chapter.