SO WHAT DOES AN ORACLE DBA DO? AND DO I NEED ONE?

Steve Lemme
PLATINUM technology, inc.
steve.lemme@platinum.com

OVERVIEW
The exact job definition of an Oracle Database Administrator's (DBA's) responsibilities are hard to find in print.
It would great if those responsibilities could be easily bundled into a nice package say like the role of an
accountant. However, uniqueness within companies and the distributed enterprise has made that seemingly
impossible. As one career description defines it: DBA - Administers and controls an organizations data
resources. Use Data Dictionary software packages to ensure data integrity and security, recover corrupted data
and eliminate data redundancy, and uses tuning tools to improve database performance. Can it be this easy to
define the role of a DBA?

WHAT EXACTLY IS A DBA?

The position of Database Administrator seems to mean different things to many organizations. In smaller
development organizations, the duties are much broader than in a major corporation. Whether a company is
performing development or utilizing third party packages, will further define the role of the DBA position.
Knowing what is expected of the role and how the position fits within the company business operations is
critical.
For many companies, the DBA role may begin with one or two servers running small workgroup or
departmental applications. Within a few months, the organization can suddenly find themselves supporting
applications around the clock without the proper support or staffed resources. Lack of resources then escalates
into customer dissatisfaction, over-work, and staff frustration that leads to employee retainment problems.
An important factor is to define the roles & responsibilities, is ensuring there will be an infrastructure in place to
support existing and new business processes. For most organizations, confusion arises when the skills of the IT
staff normally overlap without a clear delineation of responsibility. To be most productive, the skills of the staff
should intersect with the responsibilities being clearly defined. It is important to define, establish, and
communicate the roles and responsibilities.
Qualifications for the role of DBA are quite diverse. The DBA must possess technical knowledge in the sense of
the interpretation of database design models. A DBA must also possess a knowledge of the security, data
retention and disaster recovery requirements of the company. Knowledge of the database performance and
administration requirements of company application systems is also a requirement. Business skills such as
multitasking, analytical skills, active listening, oral and written communication are significant requirements as
are technical skills in operating systems, networking and database management software. A DBA should also be
familiar with software tools and languages for use by the organization to produce stored procedures. A DBA
must also have an understanding of tools used to test, monitor, tune and administer the company databases.
In a mainframe environment, the typical duties of a DBA consisted of talking with a few analysts supporting a
single operating system along with a couple programmers who supported the transaction processing
applications. In a distributed open systems environment, the role of the DBA is expanded by various operating
systems, applications, combined with a greater number and diversity of people. A typical DBA must deal each
day with groups in many functional areas, including network management, systems management, training and
application development. DBAs must be knowledgeable enough to determine where in the system the problem
actually occurs. DBAs should assist with incorporating consistent adherence to standards procedures, and
naming conventions. Not having standards and procedures leaves a project more susceptible to risks and adds
Paper #811 Page 1

additional complications when no one remaining on the team understands the code left behind by previous
employees.

KEY DBA ENABLERS

Several factors are critical for the success of the DBA role:
Adaptability:
Configuring systems and software for the ability to change as business needs dictate while maintaining
the production environment and servicing the user community
Availability:
DBAs must be prepared to respond rapidly to issues
Control:
DBAs must control the metadata, data definition process, and access
Tools:
Utilizing fully featured tools are critical to supporting fast moving projects.
Management of the Oracle database requires a variety of knowledge and analytical skill. Architecture of the
database, performance tuning and high availability are just a few issues which face the Oracle DBA. This paper
will outline critical DBA skills, typical responsibilities and roles. But first, we have to describe the type of job
responsibilities outside the role of DBA, but within the IT team, that empower the DBA to be fully effective in
their job role and career development.

System Administrator
The System Administrator is responsible for server maintenance and configuration. They install, upgrade and
tune the operating system. Responsibility for implementing the backup/recovery plan falls into Systems
Administration as does the development and implementation of a Disaster Recovery plan. The System
Administrator works closely with the DBA and the Network Administrator.

Network Administrator
The Network Administrator is responsible for the network architecture, maintenance, performance and
monitoring of the network. The Network Administrator works with WANS, routers, subnets, etc. The Network
Administrator works closely with the DBA, System Administrator and Desktop Administrator.

Desktop Administrator
The Desktop Administrator is responsible for the PC network at the workgroup level, setting standards for LAN
software and hardware, and testing and configuring the network software on the client and file servers. The
Desktop Administrator maintains file servers, terminal servers, and troubleshoots PC and LAN issues. The
Desktop Administrator works closely with the Systems Administrator, DBA and the Network Administrators.

Developer
The development staff is responsible for the design, and creation of the various programs making up the
business application. Developers should be familiar with forms, PL/SQL, 3GL languages, and Client/Server
development skills. Developers work closely with the Business Analysts, the Application Administrators, and
the Application DBA.
Paper #811 Page 2

Application Administrator
The Application Administrator is an individual with an in-depth knowledge of the use of the applications. The
Application Administrator is responsible for the configuration of the application from the users viewpoint,
enrolling end-users, and trouble shooting problems for the help desk. The Application Administrator role works
closely with the Business Analysts, Developers, DBA'S, and Desktop Administrators.

Business Analysts
The business analyst is responsible for implementing the applications and facilitating any transition required
from legacy systems. The Business Analyst has in-depth knowledge in the business functions of the corporation
and other areas of the system. The Business Analyst works with the project managers, Developers, end users and
the Application Administrators.

TYPES OF ORACLE DBAs

As scope, size, and complexity of systems expand, the DBA role can be broken into several areas of
responsibility or specialties. For smaller organizations, this may not be necessary, but in larger organizations
DBA's tend to be broken out into areas of specialty.

Database Operator
The database operator provides manual monitoring of the database console, tape mounting, and critical jobs. The
database operator verifies backup or archives ran correctly, restarts the backup or archives if necessary, and
monitors the available freespace in the backup and archive directories. The Database Operator can be supported
by existing staff or filled utilizing a third party tool.

System DBA
The System DBA's role is critical for the initial database architecture, for new projects or application upgrade
analysis, and capacity planning. A System DBA not only has experience with Oracle and advanced features such
as replication, parallel server, and partitioning, but also a background in system and network architecture.
Without a System DBA, many organizations later find themselves against a wall with I/O, performance, storage
management, backup, and capacity problems.

Application DBA
The Application DBA provides database modeling support and development of the logical database. The
Application DBA develops applications architecture, performs SQL tuning and supports the applications. The
Application DBA works closely with Developers, the System DBA and Business Analysts.

DEMANDS OF A DBA

According to a recent Computerworld survey, the average, information technology professional

works over 50 hours per week.

Over half of the surveyed IT workers said they "occasionally" miss getting home for dinner; with
28% saying "frequently."

Almost half, work an average of six hours on Saturdays and Sundays and said they work while on
vacation. 61 % of IT professionals interrupt their vacations to call and check on things at work with
25% of them bringing along a laptop computer, pager or cellular phone to stay in touch with the
Paper #811 Page 3

office.

WHAT TYPE OF DBA IS REQUIRED FOR AN ORGANIZATION?

DBA's skill types range from operators to Senior System or Applications DBA'S. What type of DBA, and how
many are needed? If an experienced DBA is hired only to monitor Oracle and ensure backups and jobs are
completing, they will probably become bored and leave. On the other hand, if you hire a single DBA not only to
administer the databases, but the systems and applications for the 20+ servers, they will soon burnout and leave.
If all that an organization requires is an additional resource to monitor a database application via a monitoring
tool, reacting when the tool tells them there is a problem, then employ a junior DBA or outsource the function. If
the DBA is a quick study, you will be lucky if they stay a year in a hot job market.
A junior DBA is indicative of a resume that is full of Oracle projects but most involved third party applications
that happened to have Oracle installed as their backend repository. Junior DBA's are too inexperienced to
address recovery issues or database internals, but are competent with monitoring database health.
For larger companies, a more senior individual(s) may be needed to participate fully in the companies IT
lifecycle. A senior DBA will have a broad range of skillsets to map to business operations and architectures. The
Senior DBA may focus entirely on one subject matter such as performance tuning, while another team member
is responsible for backups and recovery. In either scenario, the DBA role should follow these fundamental
processes:

Identify the root cause of the problem, resolve database problems, and develop methods for
preventing the problems

Review databases with developers, users, and management monthly to detect system or operational
problems
Coordinate on a timely basis to resolve problems discovered or brought to the attention of the DBA
Implement and maintain methods and procedures for monitoring and maintaining database systems

Maintain and improve technical skills, environment and application knowledge

Identify training available in areas of needed improvement and attend training as directed
Four hours monthly in reviewing available World Wide Web sources or trade publications
Review existing applications by study of documentation and discussions with application
developers
Develop a knowledgebase of the database, environment and associated software
Assist with support for all application development
Review reports/logs/dumps and other data to recommend corrective actions to maintain database
performance and efficiency
Coordinate performance and tuning actions with database developers

Review and evaluate status of assigned projects and their impact upon the database systems

Monitor project progress, discusses major problems with other analysts and management
When discovered, resolve delays affecting project completion
Provide weekly status reports to Management
Conduct database walkthroughs with programmers, analysts, and users prior to finalization of major
recommendations
Paper #811 Page 4

Review databases thoroughly before recommending new systems or system changes

Provide user support through meetings, presentations, and preparation of documentation

Establish and maintain effective communication with user community

Participate actively in meeting with staff and users, demonstrate ability to interpret user needs by
preparing thorough summaries and recommendations
Develop and update user documentation

Prepare and review database specifications, including testing and controls

Review database recommendations thoroughly with users and management, resolve problems or
differences before final recommendations are made
Study new database design principles, and developing knowledge of database principles by applying
them in projects
Gather information from users and staff when required by project tasks, to be evaluated by
supervisor through review of work, discussion with users, and review with other analysts

Prepare budgets, planning documents and maintain service contracts

Prepare requested material on time. Prepare requested material for fiscal year by stated deadlines

Design, create, and maintain physical and logical databases

Participates in logical model design or walkthroughs of the logical model design to gain an
understanding of the logical model design. Present physical implementation concerns to the design
teams and management
Create physical databases using sound design principles including documentation of the creation
and access procedures
Create, tests, and monitors backup procedures for the physical or logical databases
Maintain the physical database by monitoring and recommending changes as needed to
management. Create quarterly reports on physical storage needs, memory requirements, and
database upgrades projected for a two year time span

Paper #811 Page 5

TRAITS AND RESPONSIBILITIES OF A DBA

Many organizations tend to concentrate on technical knowledge and overlook personality. Like most IT roles,
the role of DBA is customer-service oriented. The DBA will have to interface with a variety of people: users,
developers, administrators, vendors, and managers.
Consequently, these traits should be consider when filling the DBA role:

Communication skills
Confidence
Curiosity
Detail oriented
Determination

Base Responsibilities
Architecture

The DBA needs be familiar with sizing, file placement and specification of storage media. Expertise
in RAID, failover, networking, disk shadowing, solid state disks, and their application to the Oracle
database environment are important. The DBA needs to be familiar with installation and updates for
the database server platform. For the UNIX operating system, DBAs should be familiar with the
drawbacks and benefits associated with use of raw devices.

Availability & Maintenance

One of the jobs of a DBA is monitoring and tuning of the database. Monitoring and tuning requires
a detailed understanding of the Oracle data dictionary, TKprof, Explain Plan, cost based, rule based
optimizers indexes, use of hints, and SQL statement tuning.

Backup and Recovery

A DBA must understand all of the backup and recovery options. Use of the import and export
utilities, cold and hot backups. Recovery scenarios are subject matter that a DBA must understand.

Business Processes & Requirements

A DBA should possess a sound understanding of the business requirements and how they map to
database methods and systems

Security

An understanding of Oracle security is critical to the DBA role. Knowledge of roles, profiles,
system, and object level privileges is essential for a DBA. In addition, understanding how the
operating system security interacts with Oracle is equally important.

Paper #811 Page 6

COMPREHENSIVE LIST OF DBA RESPONSIBILITES

The following outline is a comprehensive listing of the DBAs responsibilities. The intent is not to assign al se
responsibilities as the role of the DBA, but for an organization to choose and staff the activities that fit their
business requirements.

Database Architecture Duties

Planning future storage requirements for the database

Define database availability and Fault Management architecture
Defining and creating environments for development and new release installation
Creating physical database storage structures once developers have designed an application
Constructing the database
Determine, set sizing and physical locations of datafiles
Evaluation of new hardware and software purchases
Research, test, and recommend tools for Oracle development, modeling, database administration and
backup and recovery
Provide database design and implementation
Provide technical direction and vision of Oracle technologies (what's coming next i.e. parallelism,
replication, partitioning)
Understand and employ the Optimal Flexible Architecture to ease administration, allow flexibility in
managing 1/0, and to increase the ability to scale the system
Work with application developers to determine and define proper partitioning

Backup and Recovery

Determine and implement the backup/recovery plan for each database while in development and as the
application moves through test and onto production
Establish and maintain sound backup and recovery policies and procedures
Knowledge and practice of Oracle backup and recovery scenarios
Perform Oracle cold backups when the database is completely shutdown to ensure consistency of the
data
Perform Oracle hot backups while the database is up and operational
Perform Oracle import/exports as a method of recovering data or individual objects
Provide retention of data to satisfy ethical and legal responsibilities of the company
Restoration of database services for disaster recovery
Recovery of database in the event of a hardware or software failure
Utilize partitioning and transportable tablespaces to reduce downtime

Maintenance and Daily Tasks

Adjust INIT.ORA parameters as needed

Adjust next extent size of rapidly growing tables and indexes
Administer database management software and related utilities
Automate database startup and shutdown
Automate repetitive operations
DBA should ensure there is enough space for growth and that the space is being utilized efficiently
Determine and set critical thresholds for disk, tablespaces, extents, and fragmentation
Enroll new users while maintaining system security
Paper #811 Page 7

Filter of database alarm and alert information

Install, configure and upgrade Oracle server software and related products installation
Log Technical Action Reports (TARs); apply patches
Maintain "Database Administrator's Handbook"
Maintain an ongoing configurations for database links to other databases
Maintain archived Oracle data
Manage contractual agreements with providers of database management software
Manage Service Level Agreements with Oracle consultants or vendors
Measure and report attainment of database team objectives
Monitor and advise management on licensing issues while ensuring compliance with Oracle License
agreements
Monitor and coordinate update of the database recoverability plan with the site's disaster recovery plan
Monitor and optimizing the performance of the database
Monitor rollback segment use
Monitor temporary tablespace use
Monitor the status of database instances
Perform "house cleaning" tasks as required, purging old files from the Oracle database
Perform database troubleshooting
Perform modifications of database structure from information provided by application developers
Perform monthly and annual performance reports for trend analysis and Capacity Planning
Install new and maintain existing client configurations
Perform ongoing Configuration Management
Perform ongoing Oracle security management
Perform routine audit of user and developer accounts
Perform translation of developer modeled designs for managing data into physical implementation
Perform correlation of database errors, alerts, and events
Plan and coordinate the testing of the new database, software, and application releases
Provide focal point on calls to Oracle Corporation for technical support
Work as part of a team and provide 7x24 support when required

Methodology and Business Process

Coordinate and execute of database upgrades

Coordinate upgrades of system software products to resolve any Oracle/operating system
issues/conflicts
Create error and alert processes and procedures
Create naming conventions for SQLNet files (LISTENER.ORA, TNSNAMES.ORA, etc.)
Create process and procedures for functional and stress testing of database applications
Create process and procedures of application transport from DEV, to TEST, to PROD
Define and maintain database standards for the organization to ensure consistency in database creation
Define database standards and procedures cover the instance parameters, object sizing, storage, and
naming. The procedures define the process for install/upgrade, corporate database requirements,
security, backup/recovery, applications environment, source code control, change control, naming
conventions, and table/index creation
Define the database service levels necessary for application availability
Defining methodology tasks for database software integration
Define methodology for developing and improving business applications

Paper #811 Page 8

Create a process to determine if a new release is "stable" enough for consideration to be placed on the
development system
Develop data conversion processes for customization, testing and production
Develop database test plans
Develop database administration procedures and responsibilities for production systems
Develop production migration procedures
Establish and provide schema definitions, and tablespace, table, constraint, trigger, package, procedure,
and index naming conventions
Facilitate design sessions for requirements gathering and defining system requirements
Provide database problem reporting, management, and resolution
Provide final approval for all technical architecture components that manage and exchange data
including database management software, server hardware, data distribution management software,
transaction processing monitors, and connecting client applications software
Provide process for setup of new database environments.
Provide risk and impact analysis of maintenance or new releases of code
Provide standards and methods for database software purchasing
Provide standards and naming conventions
Handle multiple projects and deadlines
Provide configuration management of INIT.ORA parameters

Education and Training

Attend training classes and user group conferences to stay on top of latest information and latest Oracle
product offers
Evaluate Oracle features and Oracle related products
The DBA should have a good understanding of the Oracle database, related utilities and tools
The DBA should have a good understanding of the underlying operating system
The DBA should have a good understanding of physical database design
The DBA should have a good understanding of Oracle data integrity
The DBA should have a sound knowledge of the organizations applications and how they map to the
business
The DBA should have a good knowledge of how Oracle acquires and manages resources
The DBA should know enough about the Oracle tools' normal functional behavior to be able to
determine if a problem is the tool or the database
Possess sound knowledge in database and system performance tuning
Provide in-house technical consulting and training
Stay abreast of the most current releases of Oracle software and compatibility issues
Subscribe to database trade journals and WWW sources

Communication

Interface with vendors

Disseminate Oracle information to the developers, users, and staff
Mentor Jr. DBAs
Perform training of application developers to understand and use Oracle concepts, techniques and tools
that model and access managed data
Assist developers with database design issues and problem resolution including knowledge on how to
run and understand the output from both TKProf and the Explain Plan utilities
Train backup DBAs,
Paper #811 Page 9

Documentation

Creation and maintain a Database Operations handbook for frequently performed tasks
Define standards for database documentation
Manage center staff and other appropriate personnel
Create documentation of database environment

Paper #811 Page 10

CONCLUSION
It is so easy for a DBA to become overwhelmed with tasks in which they were unprepared. The DBA must
always be prepared to promptly address issues and requests. With the rate of technology changing every six
months, DBAs need to have clearly defined training. Oracle training is not effective if attended all at once. A
DBA's training needs to occur prior to installation of the hardware, database or applications. T6 stay proficient,
the DBA needs to practice and refine those skills learned in training,
The initial design, planning and layout of the physical architecture of a database are the first and most important
tasks. If an experienced database system architect were unavailable, it would be best to consult with an
experienced technical architect prior to implementation of a production system. When designing a database
server, issues such as: transaction rate, amount of expected data, number of users, characteristics of use, backup
and recovery requirements, disaster recovery requirements, service level agreements and future use and growth
all contribute to the database design. An architect will utilize this information to determine the number and size
of disks, the number and capacity of processors, the required backup technology, which network medium, and
the amount and allocation of memory.
This paper has presented a comprehensive set of tasks that a DBA may perform within a company. Tasks may be
unique to a company's environment that may not be listed in this paper. In addition, there are other tasks listed in
this paper that maybe assigned to another job role. In either case, it is important that the tasks are performed.
When examining a DBA's tasks, there are normally two different perspectives that can be used: from an
"applications" or "systems" perspective. An Application DBA works close with the developers, users of the
application, and places emphasis on application optimization. A Systems DBA is more involved at the hardware
level, looking at 1/0, system, and network database performance issues, backups and recovery, in addition to
technologies such as parallel server and replication.

About the Author:

Steve Lemme is an accomplished System Architect and Oracle Master DBA, with 9+ years experience in Mission Critical
VLDB System Architecture and Distributed Multi-Tier Enterprise Computing. Mr. Lemme also holds the role of President
in the Arizona Oracle Users Group (AZORA).
Mr. Lemme is a regular speaker at IOUG, OracleWorld, and has published and presented throughout the country in the
areas of:
Oracle Licensing
Database Monitoring
Database Replication
SAP on Oracle
Database Tool Selection
WWW Oracle Resources
The Role of the DBA
Data Mining, Modeling and Warehousing Systems for Computer Integrated Manufacturing

Paper #811 Page 11

Bibliography:
Cary V. Milisap, The OFA Standard * Oracle7 for Open Systems, Oracle Corporation, Part Number Al 9308- 1, September
24,1995
Ed Whalen, Teach Yourself Oracle 8 in 21 Days, Sams Publishing, ISBN 0-672-31159-3
Eric Slockburger, How Many DBAs Does it take to Screw in a Lightbulb, Carolina Medicorp, Inc., IOUW/OOW 1996
Frank Naude, DBA Tasks, Responsibilities and Skills Required, Oracle FAQ 1.1 3/3/98
Marlene Theriault, Database Administrator - The Definitive Job Description, Paper #20 IOUW/OOW 1996, The Johns
Hopkins University Applied Physics Laboratory
Michael R. Ault, How to Hire (Or be Hired as) an Oracle DBA, DMR-TreCom / RevealNet, Inc.
Steven Bobrowski and others, ORACLE7 Server Administrator's Guide, Oracle Corporation, Part Number 6694*70*1292,
December, 1992
Steven J, Recsky, The Life and Times of a DBA in an Oracle7 Client/Server Environment, Alberta Energy Company Ltd.,
Paper #20 IOUG-A 1995

Paper #811 Page 12