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 

相关文章

关于Java的那些事
Vagrant with oracle
Install Oracle Database 19c on X86
Install oracle products on docker
一图了解Oracle GoldenGate实现Oracle到Oracle复制的前世今生
Exadata最权威最完整的学习资料
Oracle数据库C函数解析
MySQL Cookbook for Oracle DBA
Full Transportable Export/Import(fxtts)
Oracle自治数据仓库云
expdp on physical standby
Oracle Database 12c之后DataGuard环境中的PDB操作
Top