Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » Oracle 12c+

How to generate AWR on PDB and ADG(12.2 afterwards)

AWR在性能分析时非常重要,通过AWR报告,大致上也能看出系统的繁忙程度,是否存在性能问题等。从12.2开始,AWR有了新的改进。本文只关注两个点,也是比较常用的。


12.2开始支持pdb级别的AWR(12.1只支持CDB级别)。下面摘抄自官方文档

The multitenant database architecture was introduced starting with Oracle Database 12c Release 1 (12.1.0.1). In the multitenant architecture, a container database (CDB) can include multiple pluggable databases (PDBs).

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

In Oracle Database 12c Release 2 (12.2), CDB root as well as individual PDBs store, view, and manage AWR data. You can take an AWR snapshot at a CDB-level, that is, on the CDB root, as well as at a PDB-level, that is, on the individual PDBs. 

在12.2上生成pdb的awr报过,有两种方式
1.自动生成,通过设置AWR_PDB_AUTOFLUSH_ENABLED=TRUE自动生成(默认是FALSE)
alter session set container=PDB1;
alter system set awr_pdb_autoflush_enabled=true;
2.手动生成,这个也是推荐的做法(如果采用手动生成的方式,CDB和PDB的snapshot id是不一样的)
alter session set container=PDB1;
exec dbms_workload_repository.create_snapshot();

3.生成AWR报告

@?/rdbms/admin/awrrpt
Specify the location of AWR Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AWR_ROOT - Use AWR data from root (default)
AWR_PDB - Use AWR data from PDB


特别提醒一定要修改AWR生成时间偏移,防止因为同时生成AWR报告造成的性能问题如果有很多的PDB,一般是0-3599,单位是秒。1000000表示是根据pdb的名字自动调节

alter system set AWR_SNAPSHOT_TIME_OFFSET=1000000 scope=both;


从12.2开始可以生成备库的AWR报过,用了分析备份的性能问题。下面摘抄自官方文档
Starting with Oracle Database 12c Release 2 (12.2), Automatic Workload Repository (AWR) data can be captured for Active Data Guard (ADG) standby databases. This feature enables analyzing any performance-related issues for ADG standby databases.

详细过程可参考https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgdba/gathering-database-statistics.html#GUID-E1369092-DA6B-4CF4-B286-69D4A3BDFA1E


Reference
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgdba/gathering-database-statistics.html#GUID-309C107F-DC42-4119-9904-9504E9748B84
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgdba/gathering-database-statistics.html#GUID-D64AEB01-18FF-47EF-BB5C-A0611117D180


关键词:db 12c oracle 

相关文章

Getting started with Oracle JDBC
How to generate AWR on PDB and ADG(12.2 afterwards)
Oracle ZDLRA (Zero Data Lost Recovery Appliance) Features
Oracle Database 18c On Exadata
Oracle Database 18c New Feature Guide
Migrate Oracle Database on AIX to Exadata
ORA-12514 During DataPump Export/Import In RAC
在12c上使用wm_concat
Oracle Active DataGuard 12.2新特性揭秘
Active Data Guard新功能演示
Oracle MAA汇总
Oracle Audit Vault and Database Firewall

联系我们

韩朝阳



    Phone:186-9589-1286

    E-mail:ohsdba@qq.com

    微  信: ohsdba


电话(186-9589-1286)

QQ(375349564)

微信(ohsdba)


常用链接

Oracle
eDelivery
My Oracle Support

Exadata 12.1 Document

Exadata 12.2 Document

Oracle 11gR2 Document

Oracle 12cR1 Document

Oracle 12cR2 Document

GoldenGate 12c

Oracle Fusion Middleware

Mysql Document

PostgreSQL

Python

github

sourceforge



Top
Cell186-9589-1286
QQ 375349564
E-mail ohsdba@qq.com