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 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.

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 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

· 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

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.

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