AWR in Multitenant Environment : Oracle 12cR2

AWR in Multitenant Environment : Oracle 12cR2 

Starting with Oracle Database 12c Release 1, Multitenancy is the core part of Database, where a container database (CDB) can include multiple pluggable database (PDBs).

Oracle Database 12c Release 1 (12.1.01) is having centralized Automatic Workload Repository (AWR) repository to store performance data related to CDB and PDBs. One can take an AWR snapshot only at a CDB-level, i.e CDB root. This AWR snapshot is for the whole database system, i.e it contains the statistical information about the CDB as well as all the PDBs in a multitenant environment.

Starting with Oracle Database 12c Release 2 (12.2), CDB root as well as individual PDBs store performance related data, view, and manage AWR data. One can take an AWR snapshot at a CDB-level, i.e on the CDB root, as well as at a PDB-level, i.e. on the individual PDBs.

The CDB-level snapshot data is stored in the SYSAUX tablespace of a CDB root.
The PDB-level snapshot data is stored in the SYSAUX tablespace of a PDB.

The AWR_PDB_AUTOFLUSH_ENABLED initialization parameter enables you to specify whether to enable or disable automatic snapshots for all the PDBs or for individual PDBs. The automatic snapshot operations are enabled by default for a CDB, but are disabled by default for a PDB.

To enable automatic snapshots for a PDB, the PDB administrator must connect to that PDB, set the value for the AWR_PDB_AUTOFLUSH_ENABLED parameter to true, and set the snapshot generation interval to a value greater than 0.

NOTE : PDB snapshot ID's can be different from CDB snapshot ID's

Let's see an example of how it looks like when running AWR report in 12c Release 2:

Step 1: Login sqlplus as command line.

Step 2: Run awrrpt.sql file as shown in below screen shot.

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

 
Step 3: Select AWR Type

Choose AWR_PDB to generate PDB level AWR report.

It will ask few things as below,

Enter value for report_type: html

Enter value for awr_location: AWR_PDB

Step 4: Input snap id and other parameters, example shown in screenshot.

Enter value for num_days: 1

Enter value for begin_snap: 30

Enter value for end_snap: 31

How PDB AWR looks like  ?

Here you can see, container name, Instance name, Unique name, CDB.

Hope this article help you to generate PDB level AWR report.

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert

Oracle Autonomous Database, Oracle 18c

Oracle Autonomous Database, Oracle 18c

At Oracle OpenWorld 2017, Oracle Chairman of the Board and CTO Larry Ellison unveiled his vision for the world’s first autonomous database cloud. Powered by Oracle Database 18c.

Larry  said, “This is the most important thing we’ve done in a long, long time. The automation does everything. We can guarantee availability of 99.995 percent, less than 30 minutes of planned or unplanned downtime.”

Oracle OpenWorld 2017: Keynote by Larry Ellison Click Here

Next generation Oracle Database 18c, is world's first autonomous database cloud. This autonomous database cloud integrates adaptive machine learning to deliver self-driving, self-tuning, self-recovering, and self-scaling administration—without human intervention—resulting in streamlined operations, more efficient consumption of resources, and higher security and reliability.

Key features of Next Generation Oracle Database.

Self-Driving: Provides continuous adaptive performance tuning based on machine learning. Automatically upgrades and patches itself while running. Automatically applies security updates while running to protect against cyberattacks. 

Self-Scaling: Instantly resizes compute and storage without downtime. Cost savings are multiplied because Oracle Autonomous Database Cloud consumes less compute and storage than Amazon, with lower manual administration costs. 

Self-Repairing: Provides automated protection from downtime. SLA guarantees 99.995 percent reliability and availability, which reduces costly planned and unplanned downtime to less than 30-minutes per year.

Oracle Autonomous Data Warehouse Cloud (PDF) by Oracle

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert