Using Oracle Enterprise Manager to monitor Database Activity

Oracle Enterprise Manager (OEM) is a comprehensive management tool that provides a holistic view and management capabilities for Oracle environments, including databases, applications, middleware, and hardware. Monitoring database activity in Oracle 12c using Oracle Enterprise Manager involves several steps and utilizes various features within OEM to ensure optimal performance and availability of your database systems. Here’s how to effectively use Oracle Enterprise Manager for monitoring database activity in Oracle 12c:

1. Accessing Oracle Enterprise Manager:
   • Ensure that Oracle Enterprise Manager Cloud Control is properly installed and configured in your environment.
   • Access the Oracle Enterprise Manager web interface by navigating to the appropriate URL, which typically follows the format `https://<hostname>:<port>/em`, where `<hostname>` is the name or IP address of the server hosting OEM, and `<port>` is the port number configured for OEM access.
2. Navigating to the Database Home Page:
   • Once logged in, navigate to the "Targets" menu and select "Databases" to view a list of monitored databases.
   • Click on the specific Oracle 12c database instance you wish to monitor to go to the Database Home Page. This page provides a summary of the database's health and performance.
3. Utilizing the Performance Hub:
   • On the Database Home Page, navigate to the "Performance" menu and select "Performance Hub". The Performance Hub provides a consolidated view of real-time and historical performance data.
   • Utilize features such as "ASH Analytics", "SQL Monitor", and "AWR Report" within the Performance Hub to analyze and diagnose performance issues.
4. Monitoring with Metrics and Alerts:
   • Oracle Enterprise Manager uses a comprehensive set of metrics for monitoring various aspects of the database. Access these metrics through the "Metrics and Alert" settings under the "Monitoring" menu.
   • Configure threshold values for critical metrics to receive alerts when certain thresholds are breached, enabling proactive management of potential issues.
5. Using the SQL Monitoring Feature:
   • The SQL Monitoring feature, accessible under the "Performance" menu, allows you to monitor the execution of SQL queries in real-time. This is particularly useful for identifying and analyzing long-running or resource-intensive SQL queries.
6. Analyzing Automatic Workload Repository (AWR) Reports:
   • Generate and analyze AWR reports to get detailed insights into database performance over specific periods. AWR reports can be accessed through the "Performance" menu, under "AWR" > "AWR Reports".
   • Use AWR data to identify trends, performance bottlenecks, and areas for potential optimization.
7. Setting Up Notification Rules:
   • Configure notification rules to ensure that the DBA team is alerted promptly about critical events, performance thresholds, or system outages. Notification rules can be set up under the "Setup" menu, in the "Incidents & Rules" section.
8. Regular Health Checks:
   • Utilize the "Advisor Central" under the "Performance" menu to access various advisors and checkers, such as the SQL Tuning Advisor, Segment Advisor, and Database Health Check, to maintain optimal database performance and health.
9. Leveraging Historical Data and Trend Analysis:
   • Oracle Enterprise Manager stores historical performance data, which can be analyzed to understand past performance trends, helping in capacity planning and long-term performance optimization.
10. Customizing the OEM Dashboard:
    • Customize the Enterprise Manager dashboard to include widgets and shortcuts for frequently accessed reports, tools, or database metrics, ensuring quick access to critical information.

By following these steps and leveraging the features provided by Oracle Enterprise Manager, DBAs can effectively monitor and manage database activity, ensuring the performance, availability, and reliability of Oracle 12c databases. Remember to regularly review the Oracle Enterprise Manager documentation and stay updated with the latest features and best practices for database monitoring and management

One of the handiest tools to use for all types of tasks is Oracle Enterprise Manager (OEM). OEM can be used to examine the current state of the database, but it can also do much more than simply provide a window into the operations of Oracle. In terms of performance monitoring, OEM offers the Tuning Pack, a set of programs that help you to understand and tune your Oracle database. The Tuning Pack includes:

1. Oracle Tablespace Analyzer: This module specializes in identifying fragmentation in tablespaces, tables, and indexes and gives you the ability to correct these problems easily.
2. Auto-Analyze: The ANALYZE command gathers statistics on your database. The Auto-Analyze module lets you schedule ANALYZE jobs so that this process will occur as part of a regular maintenance process.
3. Oracle Expert: Oracle Expert is a comprehensive analysis program that examines the typical usage of your database and recommends changes you could make to improve database performance, such as new indexes that would improve query performance.
4. Oracle Index Tuning Wizard: This wizard analyzes the usage of indexes in your Oracle database and makes recommendations for improving index usage. This wizard also generates scripts to implement suggested improvements.
5. Oracle SQL Analyze: This module analyzes individual SQL statements to display the execution path chosen by the query optimizer. The module will also help you to rewrite SQL queries for better performance.

Continuous monitoring of the system, network, application, and database operations ensures early detection of problems. Early detection improves the user's system experience because problems can be resolved faster. In addition, monitoring captures system metrics to indicate trends in system performance growth and recurring problems. This information can facilitate prevention, enforce security policies, and manage job processing. For the database server, a sound monitoring system needs to measure availability and detect events that can cause the database server to become unavailable and provide immediate notification to responsible parties for critical failures.
The monitoring system itself needs to be highly available and adhere to the same operational best practices and availability practices as the resources it monitors. Failure of the monitoring system leaves all systems that it monitors unable to capture diagnostic data or alert the administrator of problems. Oracle Enterprise Manager provides the management and monitoring capabilities with many different notification options. Recommendations are available for methods of monitoring the environment's availability and performance and for using the tools in response to changes in the environment.
The next lesson concludes this module.