如何生成ADG备库上的AWR报告

[oracle@vm2 ~]$ sqlplus  as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 6 16:36:21 2021
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Connected to an idle instance.

SYS@ora19> startup
ORACLE instance started.

Total System Global Area 2516582144 bytes
Fixed Size                    9137920 bytes
Variable Size                  536870912 bytes
Database Buffers         1962934272 bytes
Redo Buffers                    7639040 bytes
Database mounted.
Database opened.
SYS@ora19> show pdbs

    CON_ID CON_NAME                          OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                          READ ONLY  NO
         3 PDB1                           MOUNTED
SYS@ora19> alter pluggable database pdb1 open read only;

Pluggable database altered.

SYS@ora19> recover managed standby database disconnect from session;
Media recovery complete.

    SYS@ora19> alter user sys$umf identified by sysumf account unlock;

    User altered.

    SYS@ora19> create database link ora19b2ora19 connect to sys$umf identified by sysumf using 'ora19';

    Database link created.

    SYS@ora19> create database link ora192ora19b connect to sys$umf identified by sysumf using 'ora19b';

    Database link created.

SYS@ora19>  alter system set "_umf_remote_enabled"=TRUE scope=BOTH;

System altered.

SYS@ora19> exec dbms_umf.configure_node ('prim');

PL/SQL procedure successfully completed.

SYS@ora19> exec dbms_umf.configure_node ('stby','ora19b2ora19');

PL/SQL procedure successfully completed.

SYS@ora19> exec DBMS_UMF.create_topology ('Topology_1');

PL/SQL procedure successfully completed.

SYS@ora19> select * from dba_umf_topology;

TOPOLOGY_NAME          TARGET_ID           TOPOLOGY_VERSION       TOPOLOGY
-------------------------------------------------------------------------------------------------------------------------------- ---------- ----------------
Topology_1                                                                      2014871576          4                                        ACTIVE

SYS@ora19> exec DBMS_UMF.register_node ('Topology_1', 'stby', 'ora192ora19b', 'ora19b2ora19', 'FALSE', 'FALSE');

PL/SQL procedure successfully completed.

SYS@ora19> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'stby');

PL/SQL procedure successfully completed.

SYS@ora19> col node_name format a15
SYS@ora19> select * from dba_umf_topology;

TOPOLOGY_NAME         TARGET_ID TOPOLOGY_VERSION TOPOLOGY
--------------- ---------- ---------------- --------
Topology_1        2014871576                  4 ACTIVE

SYS@ora19> 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

SYS@ora19> select * from dba_umf_service;

TOPOLOGY_NAME           NODE_ID SERVICE
--------------- ---------- -------
Topology_1        1730117407 AWR

SYS@ora19> exec dbms_workload_repository.create_remote_snapshot('stby');

PL/SQL procedure successfully completed.

    SYS@ora19> @?/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
    ------------ ---------- ---------    ----------   ------
      1730117407         1        ORA19             ora19          vm2
    * 576945090         1        ORA19             ora19          vm1

    Enter value for dbid: 1730117407
    Using 1730117407 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
    ------------ ------------ ---------- ------------------ ----------

    ora19             ORA19                  1  06 Aug 2021 17:22          1


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

Login as sysdba in new primary and run the below:

exec DBMS_UMF.SWITCH_DESTINATION(topology_name IN VARCHAR2,force_switch IN BOOLEAN DEFAULT TRUE);

BUG 21046490 - UMF-ADG: FAILED TO TAKE SNAPSHOTS AFTER SWITCHOVERS IN ADG[This section is not visible to customers.]


Per non-published Bug 28930258, if  in a hurry and has not constructed a topology, then check the value of _remote_awr_enabled on the current primary database.

SELECT b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM sys.x$ksppi a,
sys.x$ksppcv b,
sys.x$ksppsv c
WHERE
a.indx = b.indx
AND
a.indx = c.indx
AND a.ksppinm = '_remote_awr_enabled';

If it is equal to TRUE, then simply set it to FALSE.

ALTER SYSTEM SET "_remote_awr_enabled" = FALSE SCOPE = BOTH SID = '*';

This will cause AWR snapshots to be generated automatically again.