The Oracle DBA’s Role

The main responsibility of a DBA is to make corporate data available to the end users and the decision makers of an organization. All other DBA tasks are subordinate to that single goal, and almost everything DBAs do on a day-to-day basis is aimed at meeting that single target. Without access to data, many companies and organizations would simply cease to function.

That’s not to say that availability of data is the only thing DBAs have to worry about. DBAs are also responsible for other areas, including these:

• Security: Ensuring that the data and access to the data are secure
• Backup: Ensuring that the database can be restored in the event of either human or systems
failure
• Performance: Ensuring that the database and its subsystems are optimized for performance
• Design: Ensuring that the design of the database meets the needs of the organization
• Implementation: Ensuring proper implementation of new database systems and applications
In a small organization a DBA could be managing the entire information technology (IT) infrastructure,
including the databases, whereas in a large organization there could be a number of
DBAs, each charged with managing a particular area of the system.

You can put the tasks you’ll perform as an Oracle DBA in the following three categories:

0• Security
• System management
• Database design

I discuss each of these broad roles in more detail in the following sections, outlining what you
could consider the bare minimum level of performance expected of a DBA. Although the lists in
each section may seem long and daunting, the tasks are really not that difficult in practice if you follow
certain guidelines. Proper planning and testing, as well as automating most of the routine tasks,
keep the drudgery to a minimum. All you’re left with to do on a daily basis are the really enjoyable
things, such as performance tuning or whatever else may appeal to you.
The DBA’s Security Role
As a DBA, you’ll be involved in many different areas of system security, mainly focusing on the database
and its data. Several potential security holes are possible when you implement a new Oracle
system out of the box, and you need to know how to plug these security holes thoroughly before the
databases go live in a production environment. In Chapter 11, which deals with user management,
you’ll find a fuller discussion of standard Oracle security guidelines and other Oracle securityrelated
issues.
Protecting the Database
For an Oracle DBA, no task is more fundamental and critical than protecting the database itself. The
Oracle DBA is the person the information departments entrust with safeguarding the organization’s
data, and this involves preventing unauthorized use of and access to the database. The DBA has
several means to ensure the database’s security, and based on the company’s security guidelines, he
or she needs to maintain the database security policy (and to create the policy if it doesn’t already
exist). A more complex issue is the authorization of users’ actions within the database itself, after
access has already been granted.

Monitoring the System

Once a database is actually in production, the DBA is expected to monitor the system to ensure
uninterrupted service. The tasks involved in monitoring the system include the following:

• Monitoring space in the database to ensure it is sufficient for the system
• Checking to ensure that batch jobs are finishing as expected
• Monitoring log files on a daily basis for evidence of unauthorized attempts to log in
(something DBAs want to keep close tabs on)
Creating and Managing Users
Every database has users, and it’s the DBA’s job to create them based on requests from the appropriate
people. A DBA is expected to guide the users’ use of the database and ensure the database’s
security by using proper authorization schemes, roles, and privileges.

The DBA’s System Management Role
Another of the DBA’s major roles is the day-to-day management of the database and its subsystems.
This daily monitoring is not limited to the database itself. As a DBA, you need to be aware of how
the system as a whole is performing. You need to monitor the performance of the servers that host
the database and of the network that enables connections to the database. The following sections
describe the various facets of the system management part of the Oracle DBA’s job.
Troubleshooting
One of the Oracle DBA’s main job responsibilities is troubleshooting the database to fix problems.
Troubleshooting is a catchall term, and it can involve several of the tasks I discuss in the following
sections. Two important aspects of troubleshooting are knowing how to get the right kind of help
from Oracle support personnel, and how to use other Oracle resources to fix problems quickly.
Ensuring Performance Tuning
Performance tuning is an omnipresent issue. It’s a part of the design stage, the implementation
stage, the testing stage, and the production stage of a database. In fact, performance tuning is an
ongoing task that constantly requires the attention of a good Oracle DBA. Depending on the organizational
setup, the DBA may need to perform database tuning, or application tuning, or both.
Generally, the DBA performs the database tuning and assists in the testing and implementation
stages of the application tuning performed by the application developers.
Performance requirements for a living database change constantly, and the DBA needs to continually
monitor the database performance by applying the right indicators. For example, after my
firm migrated from Oracle8i to the new Oracle Database 10g, I found that several large batch programs
weren’t completing within the allotted time. After much frustration, I realized that this was
because some of the code was using cost-based optimizer hints that were no longer optimal under
the new Oracle version. A quick revision of those hints improved the performance of the programs
dramatically. The moral of the story: make sure you test all the code under the new Oracle version
before you switch over to it.
You can say that all database tuning efforts can be grouped into two classes—proactive and
reactive tuning. Proactive tuning, as the name indicates, means that the DBA heads off potential
trouble by careful monitoring of necessary performance indices. As we all know, prevention is
always better than any cure, so proactive tuning will always trump reactive tuning efforts. However,
most Oracle DBAs in charge of production databases don’t have the luxury of proactively tuning—
they are too busy reacting to complaints about a slow-performing database or some similar problem.
You are likely to encounter both kinds of database tuning efforts in your day-to-day life as an
Oracle DBA.

Minimizing Downtime

Providing uninterrupted service by eliminating (or at least minimizing) downtime is an important
criterion by which you can judge a DBA’s performance. Of course, if the downtime is the result of a
faulty disk, the company’s service-level agreements (SLAs), if any, will determine how quickly the
disk is replaced. DBAs may or may not have control over the maximum time for service provided in
the SLAs. For their part, however, DBAs are expected to be proactive and prevent avoidable downtime

Estimating Requirements

Only the DBA can estimate the operating system, disk, and memory requirements for a new project.
The DBA is also responsible for coming up with growth estimates for the databases he or she is
managing and the consequent increase in resource requirements. Although some of the decisions
regarding physical equipment, such as the number of CPUs per machine and the type of UNIX
server, may be made independently by system administrators and managers, the DBA can help
during the process by providing good estimates of the database requirements.
In addition to estimating initial requirements, the DBA is responsible for planning for future
growth and potential changes in the applications. This is known as capacity planning, and the DBA’s
estimates will be the basis for funding requests by department managers.
Developing Backup and Recovery Strategies
Adequate backups can prevent the catastrophic loss of an organization’s vital business data. The
Oracle DBA needs to come up with a proper backup strategy and test the backups for corruption.
The DBA also needs to have recovery plans in place, and the best way to do this is to simulate several
types of data loss. Proper testing of backup and recovery plans is sorely neglected in many
companies, in spite of its critical importance for the company.
Loss of business data not only leads to immediate monetary damage in the form of lost revenue,
but it also costs customer goodwill in the long run. Unplanned database downtime reflects
poorly on the firm’s technical prowess and the competency of the management. A good example of
this was the repeated stoppage of the successful online auction firm eBay during 1998 and 1999,
which lost the company millions of dollars in revenue and cost them considerable embarrassment.
When disasters or technical malfunctions keep the database from functioning, the DBA can
fall back on backed-up copies of the database to resume functioning at peak efficiency. The DBA is
responsible for the formulation, implementation, and testing of fail-safe backup and restoration
policies for the organization. In fact, no other facet of the DBA’s job is as critical as the successful
and speedy restoration of the company’s database in an emergency. I’ve personally seen careers
made or broken based on one backup- and recovery-related emergency; an emergency can test the
true mettle of an Oracle DBA like no other job requirement can.
During those times when disaster strikes, the seasoned DBA is the one who is confident that
he or she has the necessary technical skills and can remain calm in an emergency. This calmness is
really the outcome of years of painstaking study and testing of the theoretical principles and the
operational commands necessary to perform sensitive tasks, such as the restoration and recovery
of damaged databases.
Loading Data
After the DBA has created database objects, schemas, and users, he or she needs to load the data,
usually from older legacy systems or sometimes from a data warehouse. If the data loads need to be
done on a periodic basis, the DBA needs to design, test, and implement the appropriate loading
programs.

Overseeing Change Management

Every application goes through changes over time to improve features and fix bugs in the software.
There is a constant cycle of development, testing, and implementation, and the DBA plays an
important role in that cycle. Change management is the process of properly migrating new code,
and the Oracle DBA needs to understand the process that’s in place in his or her organization.