Oracle 12.2 How to Generate AWRs in Active Data Guard Standby Databases

Posted on by YongJing(敬勇)

从Oralce 12.2开始， 可以对Active Data Guard(ADG) standby database捕获AWR数据。这个功能可以对ADG备库的性能问题执行分析。在下面的例子中主库与备库都是两节点的RAC(db_name=cs)。主库(db_unique_name=cs)，备库(db_unique_name=cs_dg)分别运行在cs1,cs2与jytest1,jytest2节点上。

1对备库确认数据库的open mode与database role

SQL> 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.在主库中对sys$umf用户解锁并设置密码，因为sys$umf用户默认是被锁定的。sys$umf用户是缺省的数据库用户它有访问系统级别远程管理框架(RMF)视图与表的所有权限。在RMF中所有AWR相关的操作只能通过sys$umf用户来执行。

SQL> alter user sys$umf account unlock identified by xxzx7817600;

User altered.

3.创建两个dblink，一个是从主库到备库（cs_to_cs_dg），另一个是从备库到主库（cs_dg_to_cs）。但因为ADG库是只读的，所以创建dblink的操作都需要在主库运行

SQL> create database link cs_to_cs_dg connect to sys$umf identified by xxzx7817600 using 'cs_dg';

Database link created.

SQL> create database link cs_dg_to_cs connect to sys$umf identified by xxzx7817600 using 'cs';

Database link created.

4.需要将数据库节点配置到拓朴(topology)结构中，在拓朴结构中的每个数据库节点必须被指定一个唯一名(缺省值为db_unique_name),在这个例子中主库为cs，备库为cs_dg，在主库中执行以下命令

SQL> exec dbms_umf.configure_node ('cs');

PL/SQL procedure successfully completed.

5.在备库中执行以下命令，通过备库到主库的链路名来进行注册

SQL> exec dbms_umf.configure_node ('cs_dg','CS_DG_TO_CS');

PL/SQL procedure successfully completed.

6.创建RMF拓朴，在主库中执行以下命令

SQL> exec dbms_umf.create_topology ('topology_1');

PL/SQL procedure successfully completed.

7.执行以下语句来对上面的操作进行验证

SQL> set line 132
SQL> col topology_name format a15
SQL> col node_name format a15
SQL> select * from dba_umf_topology;

TOPOLOGY_NAME    TARGET_ID TOPOLOGY_VERSION TOPOLOGY
--------------- ---------- ---------------- --------
topology_1      1789571709                1 ACTIVE

SQL> select * from dba_umf_registration;

TOPOLOGY_NAME   NODE_NAME          NODE_ID  NODE_TYPE AS_SO AS_CA STATE
--------------- --------------- ---------- ---------- ----- ----- --------------------
topology_1      cs              1789571709          0 FALSE FALSE OK

8.向拓朴中注册备库，在主库中执行下面的命令

SQL> exec dbms_umf.register_node ('topology_1', 'cs_dg', 'CS_TO_CS_DG', 'CS_DG_TO_CS', 'FALSE', 'FALSE');

PL/SQL procedure successfully completed.

9.在主库上执行下面的操作把拓扑的ADG库cs_dg的AWR service开启：

SQL> exec dbms_workload_repository.register_remote_database(node_name=>'cs_dg');

PL/SQL procedure successfully completed.

如果遇到“ORA-15766: already registered in an RMF topology” 那么要先执行DBMS_UMF.unregister_node清除注册的节点然而再次执行DBMS_UMF.register_node来注册节点

如果遇到”ORA-13519: Database id (1730117407) exists in the workload repository”那么要先执行DBMS_WORKLOAD_REPOSITORY.unregister_remote_database清除远程数据库然后再次执行
DBMS_WORKLOAD_REPOSITORY.register_remote_database注册远程数据库。

10.配置ADG的AWR功能就做完了，可以检查相关视图来验证这个拓扑配置

SQL> set line 132
SQL> col topology_name format a15
SQL> col node_name format a15
SQL> select * from dba_umf_topology;

TOPOLOGY_NAME    TARGET_ID TOPOLOGY_VERSION TOPOLOGY
--------------- ---------- ---------------- --------
topology_1      1789571709                4 ACTIVE

SQL> select * from dba_umf_registration;

TOPOLOGY_NAME   NODE_NAME          NODE_ID  NODE_TYPE AS_SO AS_CA STATE
--------------- --------------- ---------- ---------- ----- ----- --------------------
topology_1      cs              1789571709          0 FALSE FALSE OK
topology_1      cs_dg           2145432700          0 FALSE FALSE OK

SQL> select * from dba_umf_link;

TOPOLOGY_NAME   FROM_NODE_ID TO_NODE_ID LINK_NAME
--------------- ------------ ---------- ----------------------------------------
topology_1        2145432700 1789571709 CS_DG_TO_CS
topology_1        1789571709 2145432700 CS_TO_CS_DG

SQL> select * from dba_umf_service;

TOPOLOGY_NAME      NODE_ID SERVICE
--------------- ---------- -------
topology_1      2145432700 AWR

11.这时候每当默认生成AWR snapshot时，主库与ADG库会同时生成。如果要手工生成ADG的AWR快照，可以在主库中执行下面的命令（参数值为ADG库的node_id或node_name）

SQL> exec dbms_workload_repository.create_remote_snapshot(2145432700);

PL/SQL procedure successfully completed.

SQL> exec dbms_workload_repository.create_remote_snapshot('cs_dg');

PL/SQL procedure successfully completed.

至少要执行两次命令来得到快照的begin_snap和end_snap。如果遇到 “ORA-13516: AWR Operation failed:Remote source not registered for AWR” 那么需要对主库执行alter system switch logfile命令
再切换日志文件两到三次。

12.如果要生成ADG库的AWR report，可以执行下面的操作：

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

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats.  Please enter the
name of the format at the prompt. Default value is 'html'.

   'html'          HTML format (default)
   'text'          Text format
   'active-html'   Includes Performance Hub active report

Enter value for report_type: html



Type Specified: html





Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  1386528187     1      CS           cs1          cs1.jy.net
  1386528187     2      CS           cs2          cs2.jy.net
  2145432700     1      CS           cs1          jytest1.jydb
  2145432700     2      CS           cs2          jytest2.jydb

Enter value for dbid: 2145432700 这里需要指定备库的dbid
Using 2145432700 for database Id
Enter value for inst_num: 2  这里指定备库的实例ID
Using 2 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing  without
specifying a number lists all completed snapshots.


Enter value for num_days: 1

Listing the last day's Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------

cs2          CS                   1  24 Jan 2019 23:50    1
                                  2  24 Jan 2019 23:51    1


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End   Snapshot Id specified: 2



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_2_1_2.html.  To use this name,
press  to continue, otherwise enter an alternative.

Enter value for report_name: /home/oracle/cs_dg_1_2.html

Using the report name /home/oracle/cs_dg_1_2.html

下面是一个ADG库的AWR report的例子，可以看到数据库的Role为PHYSICAL STANDBY。

Oracle 12.2在Active Dataguard上引入AWR功能，可以让Active dataguard的数据库性能诊断更加容易。

发表评论

电子邮件地址不会被公开。

Proudly powered by WordPress | Indrajeet by Sus Hill.