How to Enable & Create AWR Reports at PDB level?

This article will help you to enable AWR reports to be generated at PDB level. At times, this will be required for you to generate the AWR report only from the concerned PDB and not from the entire CDB level. So, the below steps will help you to configure the same.

First verify whether the Database Initialization Parameter “AWR_PDB_AUTOFLUSH_ENABLED” is set to TRUE or not from the concerned PDB. I am connecting to my PDB container.

As you see from above output, the parameter “AWR_PDB_AUTOFLUSH_ENABLED” is not enabled. So, let’s enable it by setting it to TRUE.

Once you set this parameter to TRUE, ensure that you are also setting the AWR Interval duration. Otherwise, the AWR report will not be generated even though you enabled this parameter.

So, let’s set the AWR Interval duration.

In the below command, I have actually set the no of days to retain the AWR reports to 43200 minutes which is equal to 30 days. Please be noted that the default AWR retention period is 8 days (11520 minutes). The best practice is to keep 30 days retention period for the critical databases. You can even set more than 30 days if required.

And, also, in the below command, I have set the AWR interval duration to 30 minutes only, not the default 60 minutes (1 hour) duration. You can set it to default 60 minutes duration or you can keep it to 30 minutes or 15 minutes depending upon your business need.

Let’s check the AWR settings whether the retention and duration are set correctly or not.

As you see from the below output, you can confirm that the AWR retention is set to 30 days and the AWR Snap Interval is set to 30 minutes for my PDB container (con_id = 8).

Please be noted that you may set different AWR settings for different PDBs. It doesn’t need to be same with CDB settings or other PDB settings.

Once you enable the AWR parameter and set the AWR retention and snap interval, you can observe that the PDB will start generating the AWR reports at its specified duration. Please be noted that the AWR SNAP ID will start from 1 as you have freshly enabled the AWR at PDB level. So, the PDB level AWR SNAP ID will be different from your CDB level AWR SNAP ID.

So, to check the AWR SNAP ID at PDB level, use the dictionary view called “AWR_PDB_SNAPSHOT” from the PDB container.

If you could wait for the specified interval duration, then you will see the next generated AWR SNAP ID.

If you want to create the AWR snapshot manually in your PDB, then you can create it by calling the package “DBMS_WORKLOAD_REPOSITORY” with its procedure “CREATE_SNAPSHOT()”.

Now, if you see the last generated SNAP ID, you can observe that it would be 3 as we have manually generated the AWR snapshot above.

Hope, the article was helpful to progress your work!