[20230220][20230110]生成相关备库的awr报表

--//前一阵子,我才知道我们一套生产系统一些sql语句在备库运行,据说这些语句运行缓慢.我当时的想法就是定位有问题的sql语句,通过
--//sql profile解决该问题. 我运行tpt ashtop.sql定位语句没有问题的,因为查询的是gv$active_session_history.但是要在备库生成
--//相关备库的awr报表是不行的.我以前看过一些文档通过建立db link,job定时收集备库的awr信息.看了许多文档,尝试自己实现看看.
--//主要参考链接:https://fatdba.com/2022/02/07/part-4-how-to-generate-an-awr-report-for-a-data-guard-physical-standby-database/

1.环境:
SYS@192.168.100.235:1521/orcl> @ pr
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

--//配置如下:
--//192.168.100.235 主库 oracle_sid=orcl
--//192.168.100.235 备库 oracle_sid=orcldg

2.前期准备工作:
--//在主库建立用户SYS$UMF.在19c该用户实际上存在的.
SYS@192.168.100.235:1521/orcl> select username,common,account_status from dba_users where username ='SYS$UMF';
USERNAME COM ACCOUNT_STATUS
-------- --- --------------
SYS$UMF  YES LOCKED

SYS@192.168.100.235:1521/orcl> alter user SYS$UMF identified by o1r2c3l4 account unlock ;
User altered.

SYS@192.168.100.235:1521/orcl> select username,common,account_status from dba_users where username ='SYS$UMF';
USERNAME COM ACCOUNT_STATUS
-------- --- --------------
SYS$UMF  YES OPEN

--//在主库和备库设置"_umf_remote_enabled"=TRUE.我看到缺省是"_umf_remote_enabled"=TRUE.
alter system set "_umf_remote_enabled"=TRUE scope=BOTH;

--//在主库建立db_link.连接主库以及不备库的dblink:
create database link link_to_primary connect to "SYS$UMF" identified by "o1r2c3l4" using '192.168.100.235:1521/orcl';
create database link link_to_standby connect to "SYS$UMF" identified by "o1r2c3l4" using '192.168.100.237:1521/orcldg';

SYS@192.168.100.235:1521/orcl> column HOST format a30
SYS@192.168.100.235:1521/orcl> select db_link, username, host from dba_db_links;
DB_LINK         USERNAME HOST
--------------- -------- ------------------------------
SYS_HUB                  SEEDDATA
LINK_TO_PRIMARY SYS$UMF  192.168.100.235:1521/orcl
LINK_TO_STANDBY SYS$UMF  192.168.100.237:1521/orcldg

--//测试dblink连接情况,在主备库上分别执行.贴出主库的执行情况.
SYS@192.168.100.235:1521/orcl> select db_unique_name from v$database@link_to_primary;
DB_UNIQUE_NAME
------------------------------
orcl

SYS@192.168.100.235:1521/orcl> select db_unique_name from v$database@link_to_standby;
DB_UNIQUE_NAME
------------------------------
orcldg

--//Next we have to add the primary database node to the UMF repository, for that you have to run below command on both
--//the primary & standby databases.
--//指派primary site name ='primary_site' 和 standby database site name ='standby_site'.
--//主库:
SYS@192.168.100.235:1521/orcl> exec dbms_umf.configure_node ('primary_site');
PL/SQL procedure successfully completed.

--//备库:
SYS@192.168.100.237:1521/orcldg> exec dbms_umf.configure_node('standby_site','LINK_TO_PRIMARY');
PL/SQL procedure successfully completed.

--//如果取消配置节点
--//SQL> exec DBMS_UMF.UNCONFIGURE_NODE;

--//建立UMF topology
--//On PRIMARY Database create topology:
SYS@192.168.100.235:1521/orcl> exec DBMS_UMF.create_topology ('Topology_1');
PL/SQL procedure successfully completed.

--// Lets query if the toplogy is created with no errors and is ACTIVE.
SYS@192.168.100.235:1521/orcl> select * from dba_umf_topology;
TOPOLOGY_NAME  TARGET_ID TOPOLOGY_VERSION TOPOLOGY
------------- ---------- ---------------- --------
Topology_1    3588577726                1 ACTIVE

SYS@192.168.100.235:1521/orcl> column TOPOLOGY_NAME format a20
SYS@192.168.100.235:1521/orcl> select * from dba_umf_registration;
TOPOLOGY_NAME NODE_NAME       NODE_ID  NODE_TYPE AS_SO AS_CA STATE
------------- ------------ ---------- ---------- ----- ----- -----
Topology_1    primary_site 3588577726          0 FALSE FALSE OK
 
--//Run in case want to DROP the topology
--//SQL> exec DBMS_UMF.drop_topology('Topology_1');

--//Next you have to add the standby node to the topology, for that you need to use register_node procedure and need to
--//mention both of the DBLinks that we have created earlier. This you have to run on PRIMARY node.
    
--//On PRIMARY database
SYS@192.168.100.235:1521/orcl> exec DBMS_UMF.register_node ('Topology_1', 'standby_site', 'LINK_TO_STANDBY', 'LINK_TO_PRIMARY', 'FALSE', 'FALSE');
PL/SQL procedure successfully completed.

SYS@192.168.100.235:1521/orcl> select * from dba_umf_registration;
TOPOLOGY_NAME NODE_NAME       NODE_ID  NODE_TYPE AS_SO AS_CA STATE
------------- ------------ ---------- ---------- ----- ----- -----
Topology_1    primary_site 3588577726          0 FALSE FALSE OK
Topology_1    standby_site   18526484          0 FALSE FALSE OK

--//Now when both of the nodes 'primary_site' and 'standby_site' are added to the topology, we have to register the
--//standby node for the AWR service.

SYS@192.168.100.235:1521/orcl> @ desc dba_umf_registration
Name                Null?    Type
------------------- -------- -------------
TOPOLOGY_NAME       NOT NULL VARCHAR2(128)
NODE_NAME           NOT NULL VARCHAR2(128)
NODE_ID             NOT NULL NUMBER
NODE_TYPE           NOT NULL NUMBER
AS_SOURCE                    VARCHAR2(5)
AS_CANDIDATE_TARGET          VARCHAR2(5)
STATE                        VARCHAR2(20)

--//On PRIMARY database.
SYS@192.168.100.235:1521/orcl> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'standby_site');
PL/SQL procedure successfully completed.

SYS@192.168.100.235:1521/orcl> select * from dba_umf_service;
TOPOLOGY_NAME           NODE_ID SERVICE
-------------------- ---------- -------
Topology_1             18526484 AWR

SYS@192.168.100.235:1521/orcl> select * from dba_umf_link;
TOPOLOGY_NAME        FROM_NODE_ID TO_NODE_ID LINK_NAME
-------------------- ------------ ---------- ----------------
Topology_1             3588577726   18526484 LINK_TO_STANDBY
Topology_1               18526484 3588577726 LINK_TO_PRIMARY

--//生成备库的awr snapshot.
--//On PRIMARY database.
SYS@192.168.100.235:1521/orcl> exec dbms_workload_repository.create_remote_snapshot('standby_site');
PL/SQL procedure successfully completed.

--//等一分钟,继续....
SYS@192.168.100.235:1521/orcl> host sleep 60
SYS@192.168.100.235:1521/orcl> exec dbms_workload_repository.create_remote_snapshot('standby_site');
PL/SQL procedure successfully completed.

3.看看是否产生备库的awr报表:
SYS@orcl>  @?/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: text

Type Specified: text

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  18526484       1      ORCL         orcldg       LISDG
* 1585360079     1      ORCL         orcl         LIS-DB

Enter value for dbid: 18526484
Using 18526484 for database Id
Enter value for inst_num: 1
Using 1 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 <return> 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
------------ ------------ ---------- ------------------ ----------

orcldg       ORCL                 1  20 Feb 2023 10:37    1
                                  2  20 Feb 2023 10:39    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
..
--//在toad下也可以.首先主要选择db_id(即NODE_ID),操作步骤略.

--//如果需要定时收集awr信息,可以建立schedule,步骤略.
--//定期执行exec dbms_workload_repository.create_remote_snapshot('standby_site');命令.
--//另外参数是db_link的最好选择大写,我遇到小写出问题的情况,参考连接:
--//[20230110]sql profile run standby database.txt

4.总结大致步骤:
configure node -> create topology -> register node -> DBMS_WORKLOAD_REPOSITORY.register_remote_database.

--//主库,configure_node:
exec dbms_umf.configure_node ('primary_site');
--//备库,configure node:
exec dbms_umf.configure_node('standby_site','LINK_TO_PRIMARY');
--//如果取消配置节点
--//SQL> exec DBMS_UMF.UNCONFIGURE_NODE;

--//主库,create topology:
exec DBMS_UMF.create_topology ('Topology_1');
--//DROP topology
--//SQL> exec DBMS_UMF.drop_topology('Topology_1');

--//主库,register node:
exec DBMS_UMF.register_node ('Topology_1', 'standby_site', 'LINK_TO_STANDBY', 'LINK_TO_PRIMARY', 'FALSE', 'FALSE');

--//主库,使用DBMS_WORKLOAD_REPOSITORY.register_remote_database register remote database:
exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'standby_site');