The review of the database is the most critical step, and approximately two working weeks should be devoted to it. The review involves:
Analyzing information collected about the database from the previous 6 months and forecasting growth and database usage
Liaising with groups (see the next table), and determining potential changes to the environment in the next 6 months, for example, there might be a plan to double the number of users who access the database
Liaising with management to acquire extra storage and capacity based on forecasts; if due to cost constraints, this capacity cannot be acquired and then alternatives must be explored
The following table shows the areas the DBA should liaise with when performing a review:
The key to the review is obtaining information. This is best handled by the DBA coding plus running scripts and then storing information about all the objects in the database. Oracle provides a large number of tools and capabilities to collect this information. The database is the best environment for the DBA and PL/SQL, the best tool. The information extracted can be broken up into coarse and fine grain:
|
Coarse Grain |
Database Focus Area |
|---|---|
|
Tablespace | |
|
Datafile | |
|
UNDO | |
|
Temporary | |
|
SYSTEM | |
|
REDO Logs | |
|
Archives | |
|
Parameters | |
|
Network Load | |
|
Audit trails and logs | |
|
Fine Grain |
Database Focus Area |
|
Tables | |
|
Indexes | |
|
Triggers | |
|
Constraints | |
|
Objects | |
|
External Tables | |
|
Specialized Views | |
|
Replication structures | |
|
Built in packaged apps (Apex, Multimedia, Spatial) | |
|
Optimization figures |
The initial investment required in moving to a proactive environment is for the time to devote to building the scripts and programs required to extract the information from the database and then store it. It is this hurdle that is the hardest one to jump, as it is typically seen as a waste of time and effort. Unfortunately, there are no known tools in the market that perform perform all of this for you, but there is a large number that can assist and simplify the tasks.
It is important that information should be extracted on a daily basis and stored in a central repository (see the last diagram). This repository is rather like a data warehouse. Information extracted from the database is used for two purposes. The first, as discussed already, is used for the 6-monthly review. The second purpose is to test to see if emergency maintenance is required.
The following diagram shows the creation of a DBA warehouse:
It is important that the emergency maintenance report details only objects to those that need to be fixed immediately. The danger, which is common in a lot of environments, is information overload. By presenting too much information, the odds increase of vital pieces being overlooked and missed.