Focus On Oracle

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

Oracle Engineered System


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

How to generate AWR for Standby Databases

在Oracle数据库12.2中,AWR框架得到了增强,支持从任何通用数据库(包括Active Data Guard)捕获远程快照。这个框架称为RMF(Remote Management Framework)。通过这个特性,我们还可以分析与备库相关的性能问题。

下面的例子是在RAC的primary和standby上操作的
primary(db_unique_name=racdb),lnxrac12c1、lnxrac12c2
standby(db_unique_name=racdbs),lnxmem2、lnxmem3

本文中,在主库上执行的,显示为PRIM>,在备库上执行显示为STBY>,也就是图片中的这几个步骤


1.确认备库的open mode和database role
  STBY> select inst_id, open_mode, database_role from gv$database order by 1;
  INST_ID    OPEN_MODE            DATABASE_ROLE
  ---------- -------------------- ----------------
           1 READ ONLY WITH APPLY PHYSICAL STANDBY
           2 READ ONLY WITH APPLY PHYSICAL STANDBY

2.在Primary上解锁用户
  SYS$UMF用户是默认的数据库用户,拥有访问RMF视图和表的所有权限。RMF中的所有AWR相关操作只能由SYS$UMF用户执行。默认情况下,SYS$UMF用户是被锁定的,在部署RMF Toplogy之前必须解锁。
  PRIM> alter user sys$umf identified by sysumf account unlock;
3.在Primary上创建主备库上的database link
  PRIM>create database link dbl_racdb_to_racdbs CONNECT TO sys$umf IDENTIFIED BY sysumf using 'racdbs';
  PRIM>create database link dbl_racdbs_to_racdb CONNECT TO sys$umf IDENTIFIED BY sysumf using 'racdb';
4.配置数据库,然后增加到Topology
  在primary上执行
  PRIM>exec dbms_umf.configure_node ('prim');#如果执行exec dbms_umf.configure_node(),默认会使用db_unique_name
  在standby上执行(在备库上,相对来说,主库是远端的库,我们可以通过dblink来注册)
  STBY>exec dbms_umf.configure_node ('stby','dbl_racdbs_to_racdb');
5.创建RMF Topology
  在Primary上执行  
  PRIM>exec dbms_umf.create_topology ('Topology_1');
6.注册standby数据库到Toplogy
  在primary上执行
  PRIM>exec dbms_umf.register_node ('Topology_1', 'stby', 'dbl_racdb_to_racdbs', 'dbl_racdbs_to_racdb', 'FALSE', 'FALSE');
  PL/SQL procedure successfully completed.
  启用adg上的awr服务
  PRIM>exec dbms_workload_repository.register_remote_database(node_name=>'stby');
  PL/SQL procedure successfully completed.
  如果想停用,可以执行下面的命令
  SQL> exec DBMS_WORKLOAD_REPOSITORY.unregister_remote_database(node_name=>'stby');
可能会碰到的问题
If you encounter "ORA-15766: already registered in an RMF topology" unregister the node as below and then rerun "DBMS_UMF.register_node":
exec DBMS_UMF.unregister_node ('Topology_1', 'stby');
PL/SQL procedure successfully completed.
If you encounter "ORA-13519: Database id (1730117407) exists in the workload repository" unregister the remote database as below and then rerun "DBMS_WORKLOAD_REPOSITORY.register_remote_database":
exec DBMS_WORKLOAD_REPOSITORY.unregister_remote_database('stby','Topology_1',TRUE);
PL/SQL procedure successfully completed.
7.校验配置信息
set line 132
col topology_name format a15
col node_name format a15
select * from dba_umf_topology;
select * from dba_umf_registration;
select * from dba_umf_service;
select * from dba_umf_link;
SQL> select * from dba_umf_topology;
TOPOLOGY_NAME TARGET_ID TOPOLOGY_VERSION TOPOLOGY
--------------- ---------- ---------------- --------
Topology_1 2014871576 6 ACTIVE

SQL> select * from dba_umf_registration;
TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SO AS_CA STATE
--------------- --------------- ---------- ---------- ----- ----- --------------------
Topology_1 prim 2014871576 0 FALSE FALSE OK
Topology_1 stby 1730117407 0 FALSE FALSE OK

SQL> select * from dba_umf_service;
TOPOLOGY_NAME NODE_ID SERVICE
--------------- ---------- -------
Topology_1 1730117407 AWR
8.通过RMF创建快照
 在primary上执行

 PRIM>exec dbms_workload_repository.create_remote_snapshot('stby');

可能会碰到的问题

We need to run at least two to get the begin_snap and end_snap.
If you encounter "ORA-13516: AWR Operation failed: Remote source not registered for AWR" then manually switch a few (2-3) logfiles on primary:
alter system switch logfile;
9.创建AWR报告
  PRIM>@?/rdbms/admin/awrrpti.sql

10.如果ADG角色发生改变,我们还可以通过来切换(前提是已完成步骤A和步骤B的操作)

   A.在源端数据库上创建Link
   SQL> EXEC DBMS_UMF.CREATE_LINK (topology name,
                                source name,
                                candidate destination name,
                                source to candidate destination database link,
                                candidate destination to source database link);
   B.在角色改变之前,至少为备库创建一个snapshot
   C.角色切换后通过下面的命令即可
   SQL> EXEC DBMS_UMF.SWITCH_DESTINATION(topology name, force_switch=>FALSE);

Reference
How to Generate AWRs in Active Data Guard Standby Databases (Doc ID 2409808.1)

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgdba/gathering-database-statistics.html#GUID-309C107F-DC42-4119-9904-9504E9748B84

https://www.oracle.com/technetwork/database/availability/con6531-oracle-active-data-guard-3334919.pdf

https://docs.oracle.com/en/database/oracle/oracle-database/18/tgdba/gathering-database-statistics.html#GUID-8DEFFB31-87F0-45D4-9E3E-C788AE88C5E7
https://docs.oracle.com/database/122/TGDBA/gathering-database-statistics.htm#TGDBA232
https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_WORKLOAD_REPOSITORY.html
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_UMF.html


关键词:awr 18c oracle 

相关文章

Oracle自治数据仓库云
expdp on physical standby
Oracle Database 12c之后DataGuard环境中的PDB操作
How to generate AWR for Standby Databases
Oracle DataGuard in 18c & 19c
Oracle ASM from 10g to 18c
通过gDBClone快速的构建开发测试环境
Oracle Database Express Edition 18c发布了
在18c中通过ASM Flex DiskGroup克隆PDB
Oracle ASM Storage Limits
通过rpm包安装Oracle Database 18c
一文彻底搞懂Oracle RHP(Rapid Home Provisioning)
Top
Cell186-9589-1286
QQ 375349564
E-mail ohsdba@qq.com