Cyclic maintenance
To ensure that the database environment is optimally tuned, the move must be away from reacting to events, and instead actively planning and then tuning the database. There is a balance between constantly performing maintenance on the database and not interfering with the database. At times, it is true when it is said that problems occur only after the maintenance has been done on the database. So, a good period to aim for, where no work is done on the database, is about 6 months (depending on the volatility of a database application, the period can range from 4 to 8 months).
Maintenance is introduced and performed on a cyclic basis. The cycle involves reviewing the database, performing maintenance, and then leaving the database alone (see the next diagram). Once the review is performed two to three months after the database has been created, the newly created databases are prone to change.
The aim is to perform a full database reorganization and tune every 6 months. Performing this maintenance more frequently will be disruptive to the users and unnecessary. Performing it less frequently will result in the database becoming out of tune and the danger that objects will grow beyond their original storage allocations. If, for example, a 2-year period was used, then it would be quite difficult to predict storage requirements. There are too many factors to take into consideration and uncertainties that can occur.
Once the maintenance has been performed, the database is not touched except if an emergency occurs. So, the role of the DBA changes further, and they must now become an expert in forecasting to calculate storage and CPU requirements for a 6-month period.
Emergency maintenance is only performed when something drastic occurs, and the stability of the database is threatened. The examples include a table not being able to grow or the PROCESS parameter being exceeded in the INIT.ORA file. In these cases, emergency maintenance has to be performed, because an event unforeseen in the initial planning was missed. These events happen but should occur rarely. If they occur frequently, then the review has not been performed correctly, and procedures should be adjusted accordingly.
