[20230220][20230110]生成相關備庫的awr報表

[20230220][20230110]生成相關備庫的awr報表

--//前一陣子,我才知道我們一套生產系統一些sql語句在備庫執行,據說這些語句執行緩慢.我當時的想法就是定位有問題的sql語句,透過
--//sql profile解決該問題. 我執行tpt ashtop.sql定位語句沒有問題的,因為查詢的是gv$active_session_history.但是要在備庫生成
--//相關備庫的awr報表是不行的.我以前看過一些檔案透過建立db link,job定時收集備庫的awr資訊.看了許多檔案,嘗試自己實現看看.
--//主要參考連結:

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