Proactive Maintenance of Your Oracle Database – Key Considerations

Why It’s Important

Active monitoring and proactive maintenance are critical in ensuring a healthy Oracle database. Your company’s Oracle environment should be evaluated on an ongoing basis and best practices should be deployed to improve overall database performance and stability.

Proactive Real-Time Monitoring

Monitoring should be implemented on the Oracle database to proactively identify problems before they become serious issues. Some of the key things to monitor on your database and the implications include:

• Database Wait Events: If one or more SQL are waiting for another SQL to complete, the waiting sessions can decrease database performance. You should understand the answers to the following questions: What is the wait? Is this expected behavior for the application running on this database? Is there a blocking session which needs to be killed?
• Load Average: Monitor load average on the server to know if the server is performing well. If load average is high, it means server resources are strained and some application is using more resources or some issue (database wait, hang etc.) might be causing load to increase, which can also hang the database and server.
• Disk Space: If the mount point which contains the data files for the database is full, you cannot write to the database.
• Listener: If the listener is down you cannot make any new connections to the database.
• Database Sessions: If you reach your maximum number sessions, new sessions cannot be established.
• Flash Recovery Area (FRA) / Archive Log Location: If logs become filled the database can hang.
• Tablespace: If Tablespaces are full, transactions will be suspended.

Six Most Important Areas for Active Maintenance

Routine maintenance should be performed on your Oracle database to avoid critical issues that could negatively impact your business. Below are the six most important areas to actively maintain.

1. Flash Recovery Area (FRA) and archive logs should be sized and cleaned regularly. FRA works separately with disk space requirements as the archive, backup, and FRA logs are written in this location. Cleaning these areas properly with correct retention will avoid database hang situations.
2. Review AWR and STATSPACK weekly to identify high impact SQLs and improve overall database performance.
3. Alert Log and Listener Log should be rotated weekly. When an issue arises, it is better to have a smaller, more manageable alert log for search and analysis.
4. Redo Log File Size should be based on the amount of log switching. The key is to check the number of log switches and determine if there are any log switches under ten minutes, and increasing file size accordingly.
5. Audit Files should be stored outside of the database if no report is produced with the audit files.
6. Check data file, Redo Logs, and Control file locations and make sure they are not in the Oracle_home software installation location. It is important to make sure that if Oracle_home is mistakenly deleted, database files are not deleted as a result.

How MiCORE Helps

MiCORE specializes in optimizing, architecting, and managing complex Oracle environments. Read a case study here about the Oracle support we provide to customers.

Related Posts

• Boost Oracle Performance with a Comprehensive Health Check
• Oracle RMAN Myth Busting
• Oracle RMAN Best Practices
• Oracle Control File Recreation
• Top 3 Communication Skills for IT Support Providers