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

[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');