Creating AWR report for PDB database on 12.2 Oracle

Creating AWR report for PDB database on 12.2 Oracle

Following are steps to enable AWR report for PDB database on 12.2 or later version of Oracle:

1. Connect to the PDB database with admin option:

SQLPLUS sys@pdb2 as sysdba
Password

2. Check the AWR parameter set at PDB level.

SQL> show parameter awr

NAME                          TYPE     VALUE
----------------------------- -------- --------
awr_pdb_autoflush_enabled     boolean  FALSE
awr_pdb_max_parallel_slaves   integer  10
awr_snapshot_time_offset      integer  0

3. Enabled the AWR PDB AUTOFLUSH ENABLED parameter.

alter system set awr_pdb_autoflush_enabled=true;

4. Check AWR Snapshot time and set it for 60 minutes:

-- Check
select * from cdb_hist_wr_control;

     DBID  SNAP_INTERVAL     RETENTION         TOPNSQL  CON_ID SRC_DBID   SRC_DBNAME
---------- ----------------- ----------------- -------- ------ ---------- ----------
3687486700 +40150 00:01:00.0 +00008 00:00:00.0 DEFAULT  5      3687486700 

--Set it for 60 minutes
execute dbms_workload_repository.modify_snapshot_settings(interval => 60);

-- Check
select * from cdb_hist_wr_control;

     DBID  SNAP_INTERVAL     RETENTION         TOPNSQL  CON_ID SRC_DBID   SRC_DBNAME
---------- ----------------- ----------------- -------- ------ ---------- ----------
3687486700 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT  5      3687486700 

5. Note for avoid performance issue when multiple PDBs creating snapshots in same time.
Set the following parameter:

-- For avoid performance issues
alter system set AWR_SNAPSHOT_TIME_OFFSET=1000000 scope=both;

6. For testing the AWR Report manually create the snapshot and fetch the report:

-- Manually create snapshots:
exec dbms_workload_repository.create_snapshot();

--Fetch AWR report:
@?/rdbms/admin/awrrpt.sql;

Note: For PDB Snapshot view for check snapshot:

select * from awr_pdb_snapshot;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.