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.