AWR Snapshots and Reports - PDB level in Oracle 12c R2 (12.2.0.1.0)

AWR Snapshots and Reports - PDB level in Oracle 12c R2 (12.2.0.1.0)


1. AWR Snapshots and reports can be created only at the container database (CDB) level in 
   Oracle 12c R1 (12.1.0.1.0 / 12.1.0.2.0)
2. AWR Snapshots and reports can be created at the container database (CDB) level as well as pluggable database (PDB) level 
   in Oracle 12c R2 (12.2.0.1.0)
3. By default, AWR Snapshots and reports can be generated only at the container database (CDB) level
4. If you want to generate AWR Snapshots and reports at PDB level, set the following parameter 
   "awr_pdb_autoflush_enabled=TRUE" (by default FALSE)

oracle@exaserver01 ~]$ sqlplus / as sysdba


SQL*Plus: Release 12.2.0.1.0 Production on Thu May 3 14:29:18 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_name

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

SQL> show pdbs

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

SQL> alter session set container=ORCLPDB;
Session altered.

SQL> show con_name

CON_NAME
--------
ORCLPDB

SQL> show user
USER is "SYS"

SQL> show parameter awr_pdb_autoflush_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
awr_pdb_autoflush_enabled            boolean     FALSE

SQL> alter system set awr_pdb_autoflush_enabled=TRUE;
System altered.

SQL> show parameter awr_pdb_autoflush_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
awr_pdb_autoflush_enabled            boolean     TRUE


SQL> set lines 100
SQL> select * from cdb_hist_wr_control;

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

SQL> execute dbms_workload_repository.modify_snapshot_settings(interval => 60);
PL/SQL procedure successfully completed.

SQL> select * from cdb_hist_wr_control;

      DBID SNAP_INTERVAL
---------- ---------------------------------------------------------------------------
RETENTION                                                                   TOPNSQL        CON_ID
--------------------------------------------------------------------------- ---------- ----------
1826807715 +00000 01:00:00.0
+00008 00:00:00.0                                                           DEFAULT             3

SQL> show parameter AWR_SNAPSHOT_TIME_OFFSET

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
awr_snapshot_time_offset             integer     0

SQL> alter system set AWR_SNAPSHOT_TIME_OFFSET=1000000 scope=both;
alter system set AWR_SNAPSHOT_TIME_OFFSET=1000000 scope=both
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database


SQL> alter session set container=CDB$ROOT;
Session altered.

SQL> SHOW CON_NAME

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

SQL> show user
USER is "SYS"

SQL> show parameter AWR_SNAPSHOT_TIME_OFFSET

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
awr_snapshot_time_offset             integer     0

SQL> alter system set AWR_SNAPSHOT_TIME_OFFSET=1000000 scope=both;
System altered.

SQL> show parameter AWR_SNAPSHOT_TIME_OFFSET

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
awr_snapshot_time_offset             integer     1000000

SQL> select * from awr_pdb_snapshot;


AWR Report at PDB level:






Note: Pluggable Database (PDB) Snapshot ID's can be different from Container Database (CDB)
Snapshot ID's when you generate PDB Snapshot ID's manually.