How to Generate AWR snaps for Multitenant Database at CDB and PDB level

The AWR snaps are usually generated at the CDB level by default. They can be created both at the CDB and PDB level.

 

To generate AWR report at CDB level,

SQL> alter session set container=CDB$ROOT;

SQL> @?/rdbms/admin/awrrpt

 

To generate AWR report at PDB level,

SQL> alter session set container=PDB1;

SQL> @?/rdbms/admin/awrrpt

 

To manually create a AWR snapshot from a PDB database,

SQL> connect <username>/<password> as sysdba

SQL> alter session set container=PDB1;

SQL> exec dbms_workload_repository.create_snapshot();

 

To automatically create AWR snapshots in the PDB,

SQL> alter session set container = CDB$ROOT;

SQL> alter system set AWR_PDB_AUTOFLUSH_ENABLED = TRUE;

SQL> alter system set AWR_SNAPSHOT_TIME_OFFSET=1000000;

SQL> select * from cdb_hist_wr_control;   ### use this to look at the awr snap information

 

If you want to modify the snap interval,

SQL> alter session set container=PDB1;

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval => 30, dbid => 4182556862);

 

To view the AWR snp information for both CDB and PDB use the following,

SQL> alter session set container=CDB$ROOT;

SQL> select con_id, instance_number, snap_id, begin_interval_time, end_interval_time from cdb_hist_snapshot order by 1,2,3;

 

These are few useful scripts and queries that comes handy when you want to generate AWR reports in a multitenant database.