Oracle Database 19c -- How to Configure AWR Snapshot Settings for a CDB

Problem

You need to modify snapshot settings of AWR in a Multitenant Environment 

 

Solution

In Oracle Database 19c the AWR snapshots are taken at CDB level by default.

Set AWR_PDB_AUTOFLUSH_ENABLED initialization parameter in CDB root to enable AWR snapshots for all PDBs in a CDB, or in an individual PDB to enable snapshots for that particular PDB:

SQL> alter system set AWR_PDB_AUTOFLUSH_ENABLED=true scope=both;

 

To modify the interval at which snapshots are taken and the retention period, use:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.
   MODIFY_SNAPSHOT_SETTINGS(
    retention => 43200, -- 30 days
    interval  => 10     -- every 10 minutes
    );   
END;

AWR_SNAPSHOT_TIME_OFFSET parameter control offsets for AWR snapshot start times. Special values 1000000 makes offset random based on the PDB name. This parameter is set at CDB level only