Enabling Automatic AWR Snapshots on PDB

Oracle 12.2 allows AWR snapshots to be taken both at CDB and PDB level. CDB level snapshots will contain both CDB and PDB statistics.

AWR report will list the containers whose statistics are available in the AWR report. Below screenshot shows part of SQL statistics section of a CDB level AWR report. It could be seen it has SQL stats for both PDB and the root CDB.

On the other-hand PDB level snapshots will have statistics local to that PDB and some global statistics. Read here for further information on these.

Automatic snapshot is only enabled on the CDB level and by default is disabled on PDB. Oracle recommendation is "to generally use manual snapshots for a PDB. You should enable automatic snapshots only selectively for a PDB for performance reasons".
However, if required automatic AWR snapshots could be enabled on PDB by setting awr_pdb_autoflush_enabled to true. This could be set on CDB root level or PDB level. If set to true on CDB root level then automatic snapshots will be enabled for all the PDBs. If set for PDB then automatic AWR snapshot is enabled only for that PDB. Once awr_pdb_autoflush_enabled is set to true login to the PDB and run DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS with an interval value greater than 0.
Below steps shows enable automatic AWR snapshots for all the PDBs in a CDB.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APDB                           READ WRITE NO

Enable awr_pdb_autoflush to true on cdb

SQL> show con_name

CON_NAME
---------
CDB$ROOT

SQL>  alter system set awr_pdb_autoflush_enabled=true scope=both sid='*';
System altered.

Login to PDB and run the MODIFY_SNAPSHOT_SETTINGS with interval > 0. using PDB's DBID.

SQL> show con_name;

CON_NAME
---------
APDB

SQL> select * from awr_pdb_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL        CON_ID
---------- -------------------- -------------------- ---------- ----------
3913427709 +40150 00:01:00.0    +00008 00:00:00.0    DEFAULT             3


BEGIN
 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 11520, 
                                                           interval => 60,
                                                           topnsql => 34,
                                                           dbid => 3913427709);
END;
/

SQL> select * from awr_pdb_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL        CON_ID
---------- -------------------- -------------------- ---------- ----------
3913427709 +00000 01:00:00.0    +00008 00:00:00.0            34          3

AWR_PDB_WR_CONTROL view shows that interval has been set to every one hour.



When the first snapshot is generated, the AWR_PDB_DATABASE_INSTANCE view will be populated with PDB data.

SQL> select con_id,dbid,INSTANCE_NUMBER,INSTANCE_NAME,CDB,LAST_ASH_SAMPLE_ID from AWR_PDB_DATABASE_INSTANCE;

    CON_ID       DBID INSTANCE_NUMBER INSTANCE_NAME    CDB LAST_ASH_SAMPLE_ID
---------- ---------- --------------- ---------------- --- ------------------
         3 3913427709               2 cdb12c22         YES            1102638
         3 3913427709               1 cdb12c21         YES            1105261

Running awrrpt.sql before a record for PDB exists in AWR_PDB_DATABASE_INSTANCE view will result in "ORA-20200: Database/Instance 3913427709/1 does not exist in AWR_PDB_DATABASE_INSTANCE" (refer 2267849.1). When running PDB AWR reports use AWR_PDB as the location for the AWR_DATA.

Specify the location of AWR Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AWR_ROOT - Use AWR data from root (default)
AWR_PDB - Use AWR data from PDB
Enter value for awr_location: AWR_PDB

There is another optional parameter to consider when enable automatic AWR snapshots for PDBs. AWR_SNAPSHOT_TIME_OFFSET specifies an off-set for snapshot start time. When there are large number of PDBs in a CDB, setting same snapshot time for all of them could result in CPU spikes. Setting this to special value of 1000000 will result in an off-set based on the DB name, reducing CPU spikes due to multiple PDBs taking snapshot at the same time.

SQL> alter system set awr_snapshot_time_offset=1000000 scope=both sid='*';
System altered.

In summary to enable automatic snapshot taking on PDB, set awr_pdb_autoflush_enabled to true and set snapshot interval to value greater than 0.

Useful metalink notes
ORA-20200 Error When Generating AWR or ADDM Report as a PDB DBA User From a 12.2.0.1 CDB Database [ID 2267849.1]