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.)
A large part of your job as a DBA will be to manage the objects that exist in a database. Let’s look at the objects that you need to concern yourself with and discuss the main management issues that you will have in each of these areas.
It is critical to the database that you have at least one valid control file for your database. These are small files and can be multiplexed by the Oracle instance. Ensuring that you have at least three copies of the controlfiles (remember, they are small), as well as text and binary backups whenever a data file, log file, or tablespace is changed and on a regularly scheduled basis (at least daily) will go a long way towards ensuring that your control files are in good shape. Controlfiles will be discussed in more detail in Chapter 5.
Redo logs are necessary to ensure database integrity and should be duplexed in Oracle. Oracle mirroring helps even if your redo logs are mirrored by your storage subsystem since Oracle will use the alternate redo log if one should become corrupt. You will need to ensure that you have enough redo logs and that they are sized properly to support database performance. How large should your redo logs be? They should be large enough that a log switch does not usually occur more than once every 15 minutes due to the checkpointing that occurs during a log switch and the overhead that is incurred during this operation. How many redo logs should you have? You should have enough redo logs that the system will not wrap around to a log that has not yet completed a checkpoint or completed archiving (for systems in archivelog mode). Redo logs can be added, deleted, and switched through OEM.
The Undo segment is where the before images of changed rows are stored. Oracle will manage your undo segments for you, but you need to determine how large to make the tablespace that the Undo segment is stored in. The size that you make this depends on the length of time that you want the undo information to be available to you. If you look at Figure 3-7, you will see how OEM helps you determine the length of time that Undo can be retained based on the system activity and Undo tablespace. This is in the Configuration section for an instance. If the tablespace is not the correct size, it can be changed using the Storage feature of OEM, which you will see later in this chapter. Undo segments are covered further in Chapter 5.
If you choose to implement user-managed rollback segments, then these can be managed in the Storage section of OEM by choosing Rollback Segments and then selecting the segment name that you want to manage. If you look at Figure 3-7, you will see a Rollback Segment named System. By the way, the System rollback segment will always exist but should never be used as a rollback segment for user processes.
Schema objects were discussed earlier in this chapter, and you saw that we can manage schema objects through OEM. There are also some things that you may want to do with your own SQL scripts that run as scheduled jobs. When managing schemas, you need to ensure that those physical objects that take up a great deal of space, do in fact have enough space to grow. This includes tables, indexes, clusters, and partitioned tables. Manage this space through the tablespace that they are implemented in and ensure that there is enough room to grow in the tablespace. Ensuring that the extent sizes are large enough that you do not need to allocate too many extents is something that you need to monitor. But, do not become reorg-happy. You do not need to reorg a table if it is in hundreds of extents. You only need to reorg if there are a large number of chained or migrated rows. Indexes, on the other hand, will need to be reorged more frequently. We will find out more about managing space in the next section.
Figure 3-2 shows an example of how the SH.Customers table can be managed through OEM. Note the Storage tab that allows you to change the table’s storage parameters. You should also try to maintain statistics on your tables and indexes so they are up-to-date. This will assist the optimizer make better decisions when choosing access paths for your queries and can be used to validate the structures. In Oracle Database 10g,a scheduler job called gather_stats_jobwill run during a maintenance window between 10:00 P.M. and 6:00 A.M., by default, and will run statistics for those objects in cases where they have not been collected yet or are stale. Setting the Oracle Database 10ginitialization parameter statistics_level to typical (the default) will allow Oracle to automatically update statistics as a background task on a regular basis and is the recommended approach for gathering statistics. In pre–Oracle Database 10greleases, the DBMS_STATS package should be run manually or can use the Monitoringkeyword in a CREATE or ALTER table. Monitoring is a deprecated feature in Oracle Database 10gand the keyword (along with “nomonitoring”) will be ignored.
Logical schema objects that do not take up a lot of space need to be watched to ensure they are not invalid. Triggers, views, synonyms, procedures, functions, and packages are examples of the objects that should be valid. You can check this with the SQL statement that follows.
select owner, object_name, object_type from dba_objects where status ^= ‘VALID’;
We’ve looked at many of the database objects that will require your attention. (The next section will) explore one area that requires special attention due to the size of today’s databases.
Ask the Expert Q: Why is it important for DBAs to get involved with the architecture and design of a new system?
A: Decisions made on the technical infrastructure as well as data and application designs here will have a large impact on database performance and scalability. Database knowledge will help choose a better technical implementation. Once chosen, these can be difficult to change.
Q: Which method do you normally use to shut down a database?
A: Although the shutdown normal operation is a recommended approach, it is often impractical since you need users to disconnect themselves. The approach that I prefer is to perform a checkpoint using the command alter system checkpoint which will write data out to data files and speed up the restart. I then perform a shutdown abort, immediately followed by a startup restrict, and shutdown immediate. This is a fast, guaranteed shutdown that leaves the database in a consistent state once all of the steps have been completed.
Q: What is the best way to become a good Oracle DBA quickly and then to keep improving?
A: There are many things that you will need to do and many skills that you’ll need to develop to do this job. First, learning the basic DBA skills, which you can get from books such as this as well as from courses, will give you a head start. Practicing what you see is probably the quickest and most practical way to learn. Getting involved in supporting some databases in development and production will force you to learn very quickly. Then working on development systems for different types of applications will help to round out your skills. Keep reading and learning and never assume that you know it all and you will do very well.
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.