AWR snapshots in vPDBs ( KBA8046 )

Issue

Oracle 12.2 allows for AWR snapshots to be performed at the PDB level, including controlling the snapshot frequency and retention at the PDB level. After provisioning a vPDB, the snapshot and retention levels for AWR will be reset to the Oracle defaults.

SQL> alter session set container=MYPDB;

Session altered.

SQL> col src_dbname for A32
SQL> col snap_interval for A32
SQL> col retention for A32
SQL> select src_dbname, snap_interval,retention
  2  from dba_hist_wr_control;

SRC_DBNAME             SNAP_INTERVAL              RETENTION
-------------------------------- -------------------------------- --------------------------------
CDB$ROOT             +00000 01:00:00.0          +00003 00:00:00.0
MYPDB                 +40150 00:01:00.0          +00008 00:00:00.0

SQL>

Prerequisites

Oracle 12.2 and above.

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies

Major Release	All Sub Releases
6.0	6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0, 6.0.8.0, 6.0.8.1, 6.0.9.0
5.3	5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0, 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0
5.2	5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1
5.1	5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0
5.0	5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1, 5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

Resolution

To set the AWR interval or retention in a target vPDB a Configure Clone hook can be used to set as desired.

sqlplus / as sysdba > "/tmp/set_awr_interval.out" <<EOF
alter session set container=${DELPHIX_PDB_NAME};
execute dbms_workload_repository.modify_snapshot_settings(interval => 120);
exit;
EOF

exit 0

https://jira.delphix.com/browse/DLPX-76908