[ORACLE] Enabling AWR Snapshots and running AWR reports from Oracle Multitentant Architecture - CDBs and PDBs

Hello everyone!!!

How are you doing?

Before the multitenant architecture, we ran the AWR snapshoits and reports in the database and that was it. All the database information was in the AWR report and done.

Today, when DBAs use the multitenant architecture, 99% of them run the AWR snapshots and reports in the CDB level and thats Ok. 

But sometimes we need a bit more accurate information, and for that we can run the AWR snapshots and reports at the PDB level. 

To do this, I'm going to setup the CDB with the steps below:

But always remember:

1. This step-by-step worked for me, but it may not work for you.
2. It's a basic and limited environment. The real life will be different, for sure.
3. This post is for study and testing as well, and has no concern for performance and security best practices.

So let's go to the party!

 

1) List pdbs

SQL> @showpdb

2) Enable awr_pdb_autoflush on cdb.

SQL> SHOW PARAMETER awr_pdb_autoflush_enabled;
SQL> ALTER SYSTEM SET awr_pdb_autoflush_enabled=TRUE SCOPE=both SID='*';
SQL> ALTER SYSTEM SET awr_snapshot_time_offset=1000000 SCOPE=both SID='*';

Important: 

As the Oracle Manual show us:

"When you change the value of AWR_PDB_AUTOFLUSH_ENABLED in the CDB root, the new value takes effect in all the PDBs in the CDB.

Therefore, if you change the value of AWR_PDB_AUTOFLUSH_ENABLED in the CDB root to true, the value of AWR_PDB_AUTOFLUSH_ENABLED is also changed to true in all of the PDBs, so that automatic AWR snapshots are enabled for all the PDBs.

You can also change the value of AWR_PDB_AUTOFLUSH_ENABLED in any of the individual PDBs in a CDB, and the value that is set for each individual PDB will be honored. This enables you to enable or disable automatic AWR snapshots for individual PDBs."

For more information, click here. 

3) I suggest you change the default settings in the PDB:

SQL> ALTER SESSION SET CONTAINER = IRONMANPDB1;

SQL> SELECT 
  DBID, SUBSTR(SNAP_INTERVAL,1,20) as SNAP_INTERVAL, 
  SUBSTR(RETENTION,1,20) as RETENTION, TOPNSQL, CON_ID 
  FROM awr_pdb_wr_control;

SQL> 
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( 

                                                                                           retention => 14400, 
                                                                                           interval   => 30,
                                                                                           topnsql   => 50,
                                                                                           dbid    => 132885978);
END;
/

Retention => 7 days in minutes. The specified value must be in the range of MIN_RETENTION (1 day) to MAX_RETENTION (100 years).

Interval => Interval between each snapshot, in minutes. The specified value must be in the range MIN_INTERVAL (10 minutes) to MAX_INTERVAL (1 year).

topnsql => The number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count). The setting will have a minimum value of 30 and a maximum value of 50,000.

dbid => Database identifier in AWR

For more information, click here. 

SQL> SELECT 
  DBID, SUBSTR(SNAP_INTERVAL,1,20) as SNAP_INTERVAL,  

          SUBSTR(RETENTION,1,20) as RETENTION, TOPNSQL, CON_ID 
  FROM awr_pdb_wr_control;

In my example, I'll change my interval to the minimum value, 10 minutes.

4) And now, I'm just check if the AWRs snapshots are being created.

SQL> SELECT 

                  con_id, instance_number, snap_id, 

                  SUBSTR(begin_interval_time,1,30) as begin_interval_time, 

  SUBSTR(end_interval_time,1,30) as end_interval_time 

  FROM cdb_hist_snapshot 

  ORDER BY 1,2,3;

5) And finally, to create the CDB AWR report or the PDB AWR report, we use the same script.

-- CDB

SQL> @?/rdbms/admin/awrrpt    

-- PDB

SQL> ALTER SESSION SET CONTAINER = IRONMANPDB1;

SQL> @?/rdbms/admin/awrrpt

That's it folks. As simple as that!

I hope this helps you!

Regards

Mario