While learning the job of DBA cannot be done entirely in a few short months, a great place to start learning is this chapter. It provides an overview of the main tasks of a DBA such as the daily operations and how to manage database objects, users and space. (From the book Oracle Database 10g: A Beginner's Guide, by Ian Abramson, Michael Abbey, and Michael Corey, McGraw-Hill/Osborne, 0072230789.)
In order to properly perform the role of Database Administrator, you will need to develop and implement solutions that cover all areas of this discipline. The amazing part of this job is that you may be asked to do many, or perhaps all, aspects of your job on any given day. Your daily tasks will vary from doing high-level architecture and design to performing low-level tasks. Let’s take a look at the things that you will be getting involved in.
Architecture and Design
DBAs should be involved with the architecture and design of new applications, databases, and even technical infrastructure changes. Decisions made here will have a large impact on database performance and scalability and database knowledge will help choose a better technical implementation. Data design tools such as Oracle Designer can assist the DBA.
Short and long range planning needs to be performed on your databases and applications. This will focus on performance and sizing characteristics of your systems that will help to determine upcoming storage, CPU, memory, and network needs. This is an area that is often neglected and can lead to big problems if it is not done properly.
Backup and Recovery
A backup and recovery plan is, of course, critical in order to protect your corporate data. You need to ensure that data can be recovered quickly to the nearest point in time as possible. There is also a performance aspect to this since backups must be performed using minimal resources while the database is up and running and recoveries need to be performed within a time limit predefined by Service Level Agreements (sla) that are developed to meet customers’ requirements. A complete backup and recovery implementation should include local recovery and remote recovery that is also referred to as disaster recovery planning (drp). You will see more on backup and recovery later in Chapter 5.
This is an area that has become very sensitive due to the number of users that can access our databases and the amount of external, web-based access. Database users need to be authenticated so that we know with certainty who is accessing our database. They must then be given authorization to use the resources that they need to do their job by granting access to the objects in Oracle. This can be managed with Oracle Enterprise Manager, and we will show examples of this later in this chapter. External users require extra web-based security that is beyond the scope of this book.
Performance and Tuning
Performance and tuning is arguably the most exciting area of database management. Changes here are noticed almost immediately and every experienced DBA has stories about small changes they’ve made that resulted in large performance gains. On the other hand, every performance glitch in the environment will be blamed on the database and you will need to learn how to deal with this. Statspack, OEM Performance Management, and third-party tools will assist you in this area. There is a lot to learn here, but the proper tools will simplify this considerably.
Managing Database Objects
We need to manage all schema objects such as tables, indexes, views, synonyms, sequences, clusters, and source types such as packages, procedures, functions, and triggers to ensure they are valid and organized in a fashion that will deliver adequate performance and have adequate space. The space requirements of schema objects are directly related to tablespaces and datafiles that are growing at incredible rates. Using OEM, this can be simplified, something we will see examples of later in this chapter.
Databases are growing at incredible rates. We need to carefully manage space and pay particular attention to the space used by datafiles and archive logs. Online utilities are supported to help reorg objects while they remain online. Reorgs use considerable resources, however, so do not perform these operations unless it is necessary. See the section “Managing Space” for more on this.
TIP Do not reorg unless you absolutely need to.
Being able to upgrade or change the database is a discipline that includes many areas. Upgrades to the database schema, procedural logic in the database, and database software must all be performed in a controlled manner. Change control procedures and tools such as Oracle’s Change Manager and third-party offerings will assist you.
Oracle Database 10gcomes with a new scheduler that allows you to schedule a job for a specific date and time, and to categorize jobs into job classes that can be prioritized. So, resources can be controlled by job class. Of course, other native scheduling systems such as “at” in Windows and crontab in UNIX can be used as well as other third-party offerings.
Oracle Networking is a fundamental component of the database that you will need to become comfortable with. Database connectivity options like Tnsnames, the Oracle Internet Directory (OID), and the Oracle Listener require planning to ensure that performance and security requirements are met in a way that is simple to manage. You will see more of this in the next chapter.
Though troubleshooting may not be what you’d consider a classic area of Database Management, it is one area that you will encounter daily. You will need tools to help you with this. Oracle MetaLink technical support, available to customers who purchase the service, is invaluable. Oracle alert logs and dump files will also help you greatly. Experience will be your biggest ally here and the sooner you dive into database support, the faster you will progress.
You’ve seen the areas of database management that need to be handled, now it’s time to look at the Oracle schema and storage infrastructure.
This chapter is from Oracle Database 10g: A Beginner's Guide, by Abramson, Abbey and Corey. (McGraw-Hill/Osborne, 2004, ISBN: 0072230789). Check it out at your favorite bookstore today. Buy this book now.