Generating AWR Report for PDB

While tried to generate AWR report for a PDB database, faced an issue. On searching the net and gone through some oracle documents came to know some pre steps needs to done at PDB level to generate a successful AWR report from a PDB database.

Issue Faced:  AWR_PDB_DATABASE_INSTANCE     ORA-06512: at line 27

After some search, found the issue was raised as there was no PDB database snapshot was available for AWR report to run. So decided to create the snapshot for PDB. Now the snapshot can be created manually or automatically.

1. Creating PDB Snapshot Manually
2. Creating PDB Snapshot Automatically

Creating PDB Snapshot Manually

Connect to the PDB database and execute the below procedure to create a snapshot manually.

Once created, check the snapshot information.

Note: In a normal database snapshot information will be available in the table DBA_HIST_SNAPSHOT but in a PDB database you need to check the table AWR_PDB_SNAPSHOT

Now we've created one snapshot but it'll not be sufficient to run an AWR report as the report needs begin and end snaps. So now you can wait for some time and again create a snapshot manually to use as end snap.

Creating PDB Snapshot Automatically

To avoid the inconvenience of  creating snapshots manually, we can tune the database to create the snapshots automatically at regular intervals.

Now by following below steps we've enable to create the snapshots automatically. 

If more than 1 PDB is running, you can change the parameter 'AWR_SNAPSHOT_TIME_OFFSET' to avoid performance issue when AWR report is running parallelly from multiple PDB's.

alter system set AWR_SNAPSHOT_TIME_OFFSET=1000000

Next we need to set the time interval for the snapshot creation. 

Now snapshots will be generated automatically for every 1 hour. This interval timing can be changed as per the need.

After waited for 1 hour new snapshot is created. 

Now we can run AWR report to capture the data.

Ref: https://wikidba.com/how-to-create-awr-for-pdb-level-on-12-2-step-by-steps/

****************************நன்றி****************************