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.
Control Files
It is critical to the database that you have at least one valid control file for your database. These are small binary files that contain the records of the physical structure of the database and are important for database recovery. They can be multiplexed by the Oracle instance. Ensuring that you have at least three copies of the control files (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 toward ensuring that your control files are in good shape. Control files are discussed in more detail in Chapter 6.
Redo Logs
Redo logs are necessary to ensure database integrity and should be duplexed in Oracle. More than one group of logs are needed, and duplexing them will keep another mirrored copy of the logs. Oracle mirroring helps even if your redo logs are mirrored by your storage subsystem because Oracle will use the alternative 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 typically 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. Checkpointing writes information out to the data files from the logs and is important for data consistency. Checkpoints need to happen on a regular basis so that all of the buffers get written out to data files. 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 Oracle Enterprise Manager.
Undo Management
The before images of changed rows are stored in the Undo segment. 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. There are parameters to manage the retention in the Undo tablespace and the size of the Undo. UNDO_MANAGEMENT set to AUTO turns on automatic undo management, UNDO_TABLESPACE sets the tablespace, and UNDO_RETENTION sets the time retention for keeping the information in the Undo tablespace.
Schema Objects
As discussed earlier in this chapter, you can manage schema objects through SQL Developer. 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. These include tables, indexes, clusters, and partitioned tables. Manage these objects through sizing of the tablespaces where they are implemented. Just because an object may have hundreds of extents doesn’t mean that a reorganization of the object is not necessary. You need to reorg only if there are a large number of chained or migrated rows. It is also possible for the reorgs to gain back unused space. Indexes, on the other hand, will need to be rebuilt more frequently and do provide some performance benefits. These rebuilds and reorganizations of indexes can typically be done online and are easy to take care of at a time when there is not much going on in the database. You find out more about managing space in the next section.
It is important to keep up-to-date statistics on your tables and indexes. This will assist the optimizer in making better decisions when choosing access paths for your queries and can be used to validate the structures. Besides statistics about the object, there are also system statistics that help determine the resources available. The DBMS_STATS package will gather statistics for the overall system (DBMS_STATS.GATHER_SYSTEM_STATS) and for the database-level statistics (DBMS_STATS.GATHER_DATABASE_STATS). In Oracle Database 12c, a scheduler job called gather_stats_job will 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 (statistics that are old due to recent changes in data). There is a table, DBA_AUTOTASK_TASK, to check what maintenance jobs have been scheduled. Setting the Oracle Database 12c initialization parameter STATISTIC_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 11g releases, the DBMS_STATS package should be run manually. Especially after upgrade of a database, it is important to manually run object, database, and system statistics.
Triggers, views, synonyms, procedures, functions, and packages are logical schema objects that do not take up a lot of space in the tablespace; however, these objects need to be watched to ensure they are not invalid. They can become invalid with an ALTER table statement or changes in structure to any dependent objects. These objects should be valid, and you can check this with the SQL statement that follows:
Leave a Reply