Two days to DBA – reality or fiction

In continuing our theme of what is a DBA, I thought I would go to otn.oracle.com and start down the Getting Started link. The idea is that I should be able to look here and find out what it takes to be a DBA. Having been a Unix admin, how tough should it be to pick up a new application to manage? A database is just another application, right?


I started with  http://download.oracle.com/docs/cd/B19306_01/server.102/b14196.pdf which is the latest release of the Oracle Database, 2 Day DBA document updated on December 2005. This document covers 10gR2 which isn�t the latest version but close to the latest version. The primary author is Colin McGregor. A quick search on the internet shows that Colin has also published books associated with administering Oracle 8i as wellas Oracle 9i. This book is an excellent introduction to get you started and helps you understand what you don’t understand. By following the obe links and recommendations, it does lead you to the relevant reference material.


The book is 208 pages long with the usual disclaimers, table of contents, and misc publishing stuff that most people skip over. The purpose of the book is to be an introduction to the base requirements of becoming a successful DBA. If we look at the table of contents, we see the major topics that are covered and the number of pages on each topic. It took me half a day to read the 200 plus pages and understand the concepts. If I incorporated the obe references and actually installed the software and a database instance, it would take two full days. 


1.      Introduction


2.      Installation and creation of a database (22 pages)


3.      Enterprise Manager (7 pages)


4.      Network Configuration (5 pages)


5.      Managing an Instance (9 pages)


6.      Managing Storage Structures (20 pages)


7.      Users and Security (11 pages)


8.      Managing Objects (26 pages)


9.      Backup and Recovery (22 pages)


1.      Performance and Tuning (17 pages)


2.      Patch and Configuration Management (6 pages)


With an appendix on ASM and clustering. The intended audience is anyone who wants to dig a little deeper into database administration. Some common tasks performed by a DBA includes


              o        Installing Oracle software


o        Creating Oracle databases


o        Performing upgrades of the database and software to new release levels


o        Starting up and shutting down the database


o        Managing the database�s storage structures


o        Managing users and security


o        Managing schema objects, such as tables, indexes, and views


o        Making database backups and performing recovery when necessary


o        Proactively monitoring the database�s health and taking preventive or corrective action as required


o        Monitoring and tuning performance


 


The book covers installing, creating, and upgrading the database in 22 pages. The descriptions are good with external references to additional tutorials on how to install a database. My recommendation is to start with the Express Edition, which is a personal edition, to become familiar with the database constructs and install sqlDeveloper as well to look at the data. Once you have become comfortable doing this, move onto the Standard Edition or Enterprise Edition. I recommend choosing what will be used for your work be the choice that you play with. The management and operation of the two version are similar but there are subtle differences in extensions like Partitioning, Replication, and Enterprise Manager management pack extensions.


 


The next 7 pages cover Enterprise Manager. In my opinion, this is very light and you really need to read the supplemental readings suggested. It is important to note that the installation of OEM is different than the installation of the database and both must be something that you need to be comfortable with. It is also important to note that the 10gR3 edition of OEM is substantially more mature than the 9i version and it is recommended that you install the latest and greatest version of OEM. You can administer older versions, I believe back to 8i, of the database using the newest version of OEM. The two versions do not need to be the same because one is a monitoring tool to measure the other and run independently.


 


The next 5 pages cover how to manage the database Listner. The listener is truly a complex concept if you want to support high availability. If you want a simple connection, the examples given are very good and you can get by without having to follow the references in this document. The definition of the Naming Methods is a good introduction but does not go into enough depth on how or why you would use one option over the other. Most people just use DNS or an LDAP naming service, based on your coporation, to define the database server name. The connection is typically handled with host and port connections as is typically done with other applications. A network adminsitration group and not the database group typically do management of these names. For a basic configuration, it only important to know how to start, stop, configure, and check the status of a basic listener. This can be done from the command line or through OEM.


 


The next 9 pages talk about managing the database instance. With 10g, management of the SGA and PGA have become less and less of an issue. Management of the background processes has also become less of an issue with advance job control in OEM. This section does cover how to start and stop a database. It is important to note that the procedures are different on Windows and all other platforms. If you want to learn one way to manage these services, you will need to use OEM to make the management the same. This section even talks about the differences between the command line and the OEM management approaches. You will probably need to follow the links to the Oracle by Example tutorials that take you through the administration screens to understand how to truly start and stop a database. Most places that I have visited have automated scripts to perform these tasks. Shutting down a database typically requires more than stopping an instance. These scripts are unique to each department and developed in association with business requirements and limitations.


 


The next 20 pages focus on managing storage structures. What this means is growing a database, redistributing your storage across disk devices, and changing log and configuration parameters. A strong understanding of the redo logs is important. When a system crashes, the redo log is one if the first things looked at to figure out how long it will take to recover transactions and re-synchronize the database. It is also important to know the differences between segments, extents, datafiles, and tablespaces. This section gives good explanations but more reading would substantially help. You will need to follow the links detailed in this section. The common database storage tasks are as follows:



  • Viewing tablespaces
  • Creating a tablespace
  • Modifying a tablespace
  • Dropping a tablespace
  • Reclaiming wasted space

The next 11 pages cover users and security. This chapter talks extensively about roles and profiles and the implication on security but dances around the advanced security options that are just as important. Other features like DataVault take user administration to a new level and add another layer of complexity on top of roles. Adding and deleting a user is easy. Integrating user creation with Active Directory of LDAP is much more complex. Most database deployments are integrated with naming services because user provisioning and deprovisioning has become a more and more complex issue. In my opinion, this chapter is a little light but configuration on how users are created differs greatly from company to company. Universities, for example, create users in batch mode because the user community changes from semester to semester based on tuition payment. Employment systems typically create users and provision services like email, blog servers, and access to self service applications. These services do not change very much over the life of the user and updates are typically done by a program and not through OEM or a command line structure. This adminstration topic is typically dictated by corporate culture and business requirements and do not change very much over time.


 


The next 26 pages talk about managing schema objects. This is the place where most senior DBAs spend their time. They manage tables, tweak and tune indexes, manage views as well as stored procedures. A good DBA should know this area of the book backwards and forwards. Most of the other sections are dictated by corporate policies or procedures and restrict what can and can�t be done. Managing objects is less of a science and more of an art form and DBAs are given creative license to make the database operate optimumly. I would recommend reading the links recommended in this section, specifically Database Concepts, Administration Guide, and the Performance and Tuning Guide. This section also talks about the different Index types. I recommend understanding the differences as well as where and why one might be used over the other. For some applications, it will also be important to understand Partitioning as well as Materialized Views, neither of which are discussed in this section. Triggers and stored procedures are also important and are discussed in this section. I would also recommend looking at concepts like Flashback for fast recovery and DataGuard for splitting operations between multiple computers. Many customers look at putting a transactional database like order entry or procurement on one system and reporting and business analytics on a replica read-only machine. Combining these two functions on the same box makes it very difficult to tune the database since one is a transactional system while the other is more of a data warehouse function. It is important to note that a standby DataGuard replica can have different indexes, materialized views, and triggers than the primary transactional system. Reports and analysis can be initialized when data reaches a specific point or at a given time.


 


The next 22 pages focus on backup and recovery. Flash recovery as well as RMAN is covered in depth in this chapter. This function probably what most junior adminstrators get tasked with. It is a thankless job but one that is critical when something fails. It is imprtant to test, test, and re-test recovery. As with user creation, this process and procedure is typically defined by corporate policy and culture. It is not necessarily important to understand how Oracle recommends doing backup and recovery, more how your company wants to perform the operations. The main tasks that I would focus on in this seciton are how to backup tables, rows, or cells from a flashback area and understand the advanced concepts of secure backup. I would also make sure that you know how to figure out if a backup finished successfully and how to restore from a backup. Both will be important to know because there is nothing worse than having to recover from a failed backup with three levels of management looking over your shoulder asking if you know what you are doing.


 


The next 17 pages are probably the most important section of this book. Proper monitoring and tuning makes the difference between and good junior administrator and a superior senior adminsitrator. If you can pinpoint a problem and give a recommended fix that is reasonable, cost effective, and accurate, people will start trusting you and relying upon you for more important projects. Alerts and ADDM are two tools that can give you valuable information or overload you with too much data. It is important to constantly tweak and tune both outputs. This section discusses both of these topics in depth. It is important to note that all of these examples use OEM and not command line management tools. I would recommend understanding OEM as well as the Tuning, Diagnostic, Configuration Management, and Change Management packs. These options come at an additional cost but are very valuable tools  that a senior DBA will need.


The next 6 pages talk about managing patches and installed software. This topic is one of those areas that you will need to revisit once a year or every other year. It is important to keep your software up to date and security patches installed. Fortunately, you will have a test and development system to play with and remind you how to upgrade a system or update the patches before having to do this on a production system. My recommendation is to plan updates and upgrades months in advance. During this time you should test your development and test systems to make sure there are no unknowns or surprises when your production system comes back up.

What is a DBA

The lines between a system administrator and DBA have gotten blurred of late. The question of that a UNIX or Windows admin does and what a DBA does has gotten a little confusing. Let�s take a look at the differences and see what is required. We will base the analysis first on job requirements as posted on monster.com. We will then look at what it takes to be certified for a DBA and Linux admin.


 


DBA capabilities


 


First, what does monster.com list as the requirements for an Oracle DBA. We started this process by searching for an Oracle DBA in the keyword description. Fortunately, there were over 1500 matches that indicate that there is a broad spectrum of descriptions to choose from. It also shows that this is a viable job opportunity with a relatively high demand.


 


Some of the responsibilities that are commonly listed are (ranked by importance):



  • Make sure existing database instances are running and backed up
  • Plan growth of database infrastructure
  • Respond to database support requests (Build/Alter tables, run stats, load tables, data recovery)
  • Provide third level support for help desk
  • Assist resolution of production issues
  • Clone existing production environments for test and development
  • Mentor system admins and operators
  • Lead projects and implementations
  • Evaluate, define, and document standards, processes, and procedures
  • Document network topology, database configuration, and site configurations
  • Evaluate tools
  • Evaluate and recommend purchases
  • Assist in development of disaster recovery and business continuity plan

The skills that are required are



  • Good communication skills
  • Time and multiple project management
  • Advanced knowledge of Oracle 9i and newer versions
  • Comfort with all aspect of database administration (installation, configuration, performance tuning, and security)
  • College degree of some type (typically engineering, bachelor or masters)
  • Familiarity with data modeling, entity relationships, referential integrity, and cardinality
  • Familiarity with creating schemas, triggers, stored procedures, sequences, synonyms, and user defined data types
  • Experience with database backup and restore
  • Experience with database replication
  • Experience with SQL, PL/SQL, Java/JDBC, ODBC
  • Working knowledge of Microsoft SQL Server, MySQL
  • Ability to write batch jobs and scripts
  • Experience with database log shipping and standby databases
  • Experience with service packs and security updates as well as rolling upgrades
  • Experience with troubleshooting HA and highly redundant configurations
  • Five or more years experience as DBA

Supplemental skills desired



  • Knowledge of IIS and Apache configurations and adminsitration
  • Knowledge of operating systems (Windows, Unix)
  • Knowledge of network hardware, firewalls, switches, and routers
  • Knowledge of business applications (SAP, Oracle E-Business Suite, PeopleSoft)
  • Experience with a programming language (Perl, Java, C##, ASP, .NET)
  • Experience with operation center software (HP OpenView, BMC Remedy, Tivoli, CA Unicenter, Nagios/NetSaint)
  • Experience with storage network hardware vendors
  • Knowledge of data warehousing tools (like ETL and Warehouse builder)
  • Knowledge of business analytics software
  • Knowledge of federal and state regulations (FERPA, HIPPA, SOX) and how it relates to data in the database

DBA Certification


 


If we shift our view to the certification process and look at what is being tested we see a slightly different list of topics. The 10g Admin Workshop 1 focuses on the following topics:



  • Installation
  • Creating a database
  • Managing a database instance
  • Managing database storage structures
  • Administering user security
  • Managing schema objects
  • Managing data and concurrency
  • Managing undo data
  • Implementing security
  • Configuring networks
  • Proactive maintenance (stats, AWR, ADDM, alerts)
  • Performance management
  • Backup and recovery
  • Flashback
  • Moving data (loading and migrating)

The 10g Admin Workshop 2 shifts the focus a little deeper into recovery and less of basic administration and user creation.



  • Configuring recovery manager
  • Recovering from user errors
  • Dealing with database corruption
  • Automating management (SQL Tuning Advisor, AWR, ADDM)
  • Using rman
  • Recovering temp tables, redo logs, index, read-only tables, and passwords
  • Monitoring and managing storage
  • Monitoring and managing memory
  • Database recovery
  • Flashback database
  • Managing resources
  • Job scheduling and management

Additional certification can be obtaines for high availability



  • Overview of RAC
  • Installing clusterware
  • Installing RAC
  • Database creation
  • RAC DB administration
  • Backup and recovery of RAC
  • Monitoring and tuning RAC
  • Managing services in HA solutions
  • Managing connections in HA solutions
  • Administering clusterware
  • Troubleshooting clusterware
  • Designing for maximum availability architecture

System Administrator requirements


 


If we shift our focus to what is a systems administrator the job description found on monster.com is a little different



  • Setup and configure hardware boxes with operating systems
  • Patch management for operating systems and applications
  • Support customer requests for operating system and application loads
  • Tune application and operating system configurations
  • College degree (computer science or equivalent)
  • 5 years experience
  • Focus on network, systems, and application security
  • Support and maintain web sites, intranet, and project web sites


  • Provide third level support for help desk
  • Assist resolution of production issues
  • Clone existing production environments for test and development
  • Mentor system admins and operators
  • Lead projects and implementations
  • Evaluate, define, and document standards, processes, and procedures
  • Document network topology, database configuration, and site configurations
  • Evaluate tools
  • Evaluate and recommend purchases


  • Assist in development of disaster recovery and business continuity plan
  • Support network team

Must have proficiency in



  • Good communication skills
  • Time and multiple project management


  • Operating system (Linux, Windows, pick one)
  • DNS, DHCP, IMAP, POP, SMTP, RADIUS, PHP, LDAP, Active Directory, Samba
  • Programming and scripting languages (Perl, csh, sh, java, c, c##)
  • Backup and restore procedures for operating system
  • Experience with VMWare


  • Experience with blades or partitioning of hardware


  • Phone systems (POTS and VoiP)
  • Printer and copier maintenance
  • Experience with firewall and network security

System Admin certification


 


If we shift our focus to what is part of the certification process we see:



  • User environements and logging in
  • Command line basics
  • File system management (disk partitioning, file system types and management, LVM, RADI, Quotas)
  • Users, groups, and permissions (NIS, LDAP, Active Directory, files, ACL)
  • Shell basics
  • Standard I/O and pipes
  • Shell scripting and text processing
  • Managing processes
  • Network administration (device recognition, kernel drivers, DHCP, DNS, TCP, IP, filtering, firewalls)
  • System installation and configuration (BOOTP, jumpstart, kickstart, flash install)
  • System initialization and services (boot, BIOS, kernel procedures, init scripts)
  • Kernel services (/proc, kernel modules, system monitoring)
  • Package and application management (RPM, patch management)
  • Printer administration
  • Backup and restore basics (dump/restore, tape, cpio)
  • X window configuration and security
  • Fault analysis (rougue processes, priorities, memory leaks)
  • Network services (FTP, NFS, Samba)
  • Web services (Web server, CGI, SSL, certificates, email, SMTP)
  • Encryption technologies and key management, PAM, Kerberos

Commonalities between DBA and System Administrator


 


The two jobs do have some overlap. Mainly education, communication skills, and support of help desk. If we list the job functions that are the same we see:



  • Provide third level support for help desk
  • Assist resolution of production issues
  • Clone existing production environments for test and development
  • Mentor system admins and operators
  • Lead projects and implementations
  • Evaluate, define, and document standards, processes, and procedures
  • Document network topology, database configuration, and site configurations
  • Evaluate tools
  • Evaluate and recommend purchases
  • Assist in development of disaster recovery and business continuity plan

Skills that are similar



  • Good communication skills
  • Time and multiple project management
  • College degree of some type (typically engineering, bachelor or masters)


  • Programming knowledge
  • Experience with firewall and network security

Differences between DBA and System Administrator


 




























DBA


System Administrator


Make sure existing database instances are running and backed up


Make sure existing operating systems and applications are running and backed up


Plan growth of database infrastructure


Plan growth of hardware and network required to run applications


Respond to database support requests (Build/Alter tables, run stats, load tables, data recovery)


Respond to user support requests (load operating systems, add applications, printer problems)


Patch management for database


Patch management for operating system and applications


Tune database


Tune operating system and applications running on system


Focus on database and application security


Focus on network, system, and applicaion security


Support developers in accessing data stored in database


Support and maintain web sites, intranet, and project web sites


 


The training required for both jobs are radically different. The DBA focuses more on administration of data and users. The sys admin focuses more on components of the operating system and applications. The DBA is aligned a little more with the business units while the sys admin is more aligned with the help desk. Both are important relevant jobs but few people cross the boundaries between the two because the training required to become an expert in both is time consuming.

storage selection for a database

It is amazing how friendships help business relations. I have a friend who is working at NetApps and we are both calling on the same account. We started to recommend conflicting information to the customer based on the knowledge of our own worlds. Once we talked, I understand his perspective and I think that he understands mine. We are now talking about how we can jointly present to this customer and even coordinate our messages with each other and through our recommended consulting organization.

Here’s the situation. We have a customer that is running an old version of Oracle (9i) on an old operating system (Solaris 8) on old hardware (Fujitsu some older model) attached to a NetApps filer (three generations ago). The customer is experiencing significant performance problems. They have two applications that they are running against the database. One is an ERP system that has a relatively low transaction count. The other is a business objects application that is consuming all of the resources. Normally this would not be a problem but the business objects app is consuming all of the resources and causing the ERP system to lock up and interrupt business.

Simple solution right? Find the bottleneck and fix it. Well, it isn’t that simple. NetApps did a detailed analysis and found that the disk is 90% busy. The network is 60% busy. If they double the disk speed and reduce the latency by half, the network will become a problem. Fortunately, the business objects app is configured to be a read only analysis of the database. If we can split the database out, we can significantly improve the ERP performance.

Given a computer science/electrical engineering background, my first impression was that NFS mounting the data was the biggest problem. If they could move to direct attach disks, it would improve performance. This would get rid of the network loading and allow them to purchase faster disks at the same time. What I didn’t realize was that they would also need to purchase a fiber channel switch and learn how to manage this as well. I did know that NFS management was easy, I’ve been doing it for years. I didn’t realize that SAN management is an art to itself and is significantly more complex and expensive to manage. Looking into this subject a little more I was amazed to find that NFS does not substantially effect performance if tuned properly. An NFS system is only 12% slower than a fiber attached system and 2% slower than iSCSI.

The recommendation that we are moving forward with is a second database system that replicates the primary data using DataGuard with physical replication. Once this is done, redirect the business object app to hit the standby database and purchase a second netapp filer to offload the original system. The added benefit is that this configuration is the foundation for disaster recovery since both apps are currently running on one machine. Even though the standby system will be in the same datacenter it will provide redundancy for the primary system.

The performance results and complexity of SAN storage amazed me. Both were contrary to my thinking. Once I looked at it from this perspective, it makes sense. Live and learn…..