To change the frequency of AWR in OEM & Cloud Console

To change the frequency of AWR (Automatic Workload Repository) snapshot collection in Oracle Enterprise Manager (OEM) or Oracle Cloud Console, you can follow the steps outlined below. This process involves adjusting the snapshot interval, which is the time between AWR snapshots. By default, AWR snapshots are taken every hour (60 minutes), but this can be modified.

Steps to Change AWR Snapshot Frequency in Oracle Enterprise Manager (OEM)

1. Log into Oracle Enterprise Manager:

   • Open your browser and navigate to the Oracle Enterprise Manager (OEM) console.

   • Log in using your credentials as a DBA or admin user.

2. Navigate to the Database Target:

   • From the Enterprise menu, select Targets and then choose Databases.

   • Choose the database for which you want to change the AWR snapshot frequency.

3. Access AWR Settings:

   • Once you are inside the database's performance page, click on the Administration tab.

   • Under Workload, click on Automatic Workload Repository (AWR).

4. Modify Snapshot Settings:

   • In the AWR page, you will see the current snapshot interval and retention period settings.

   • Click on Edit (or Modify in some versions) to adjust the settings.

5. Change the Snapshot Interval:

   • In the Snapshot Settings window, you can change the Snapshot Interval. For example, if you want AWR snapshots to be taken every 30 minutes, set the interval to 30 minutes.

   • You can also adjust the Retention Period to keep snapshots for a longer or shorter period based on your needs.

6. Save the Changes:

   • After adjusting the snapshot interval, click OK or Apply to save the changes.

   • OEM will now update the snapshot collection interval for AWR reports.

Steps to Change AWR Snapshot Frequency in Oracle Cloud Console (OCI)

1. Log into Oracle Cloud Console:

   • Open the Oracle Cloud Infrastructure (OCI) console in your browser.

   • Log in using your Oracle Cloud credentials.

2. Navigate to the Autonomous or DB System Database:

   • From the left navigation menu, select Databases.

   • Choose either an Autonomous Database or a DB System (depending on where your Oracle database is hosted).

3. Open the Performance Hub:

   • For Autonomous Databases, you can access Performance Hub directly.

   • For DB Systems, select your database, and then navigate to the Performance Hub under the Monitoring section.

4. Modify AWR Snapshot Settings:

   • In the Performance Hub, look for an option related to AWR Snapshots.

   • Click on Settings or AWR Options.

   • Here, you will find the current snapshot interval setting.

   • Change the Snapshot Interval (in minutes) to your desired frequency. For example, you can set it to 30 minutes or any other frequency as needed.

5. Save Changes:

   • After making the changes, confirm and save your settings.

   • The system will now take snapshots at the newly defined frequency.

Verifying Snapshot Changes

To verify that the snapshot frequency has been updated, you can:

1. Check the Snapshot Frequency in OEM or Cloud Console:

   • In both OEM and the OCI console, navigate back to the AWR settings to ensure the snapshot interval is updated.

2. Run a Query in SQL: You can also check the new interval via SQL using the following query:

   sql

   SELECT snap_interval, retention FROM dba_hist_wr_control;

This query will show the updated interval (in minutes) and retention period for AWR snapshots.

Conclusion

Both Oracle Enterprise Manager (OEM) and Oracle Cloud Console (OCI) provide an intuitive interface for modifying the AWR snapshot interval. By changing the snapshot frequency, you can collect more frequent snapshots (e.g., every 30 minutes) or reduce the frequency based on your performance monitoring needs.