AWR Warehouse and Data Guard (Part Five)

Continuing the investigation, I wanted to check the effect of deleting one of the two data guard instances from AWRW; remember I have updated the mapping table such that both DGA and DGA both map to the same DBID.

We can check from load metadata and AWR itself which snapshots have been loaded

SQL> SELECT
    dump_id,
    dbid,
    target_name,
    begin_snap_id,
    end_snap_id
FROM
    dbsnmp.caw_load_metadata
ORDER BY dump_id;  

   DUMP_ID     DBID TARGET_NAM BEGIN_SNAP_ID END_SNAP_ID
---------- ---------- ---------- ------------- -----------
    14 1852644603 DGA            12        38
    15 1852644603 DGB            39        41
    16 1852644603 DGB            42        44

SQL> SELECT
    MIN(snap_id),
    MAX(snap_id),
    COUNT(*)
FROM
    dba_hist_snapshot
WHERE
    dbid = 1852644603
ORDER BY snap_id;  

MIN(SNAP_ID) MAX(SNAP_ID)   COUNT(*)
------------ ------------ ----------
      12           44      33

So we have 33 snapshots, loaded from both DGA and DGB.
I then delete DGB from AWRW via the GUI. After waiting a few minutes for the scheduler task that actually does the deletion to kick in, I recheck the AWR data.

SQL> SELECT
    MIN(snap_id),
    MAX(snap_id),
    COUNT(*)
FROM
    dba_hist_snapshot
WHERE
    dbid = 1852644603
ORDER BY snap_id;
  
MIN(SNAP_ID) MAX(SNAP_ID)   COUNT(*)
------------ ------------ ----------
                   0

Because databases are mapped to the same DBID, all AWR history for both databases has been removed from AWR Warehouse.

One final thing I wanted to check was that with the updated mapping record, AWRW data was really accessible via OEM.

I re-added DGB back to AWRW, mapped to its real DBID using the steps before.
From the AWRW page I highlight DGA and select “Compare Periods->Compare Periods Report”. (Note this is a powerful feature of AWRW probably deserving of it’s own entry):

I make sure AWR Data Source is “Historical – AWR Warehouse”:

I then check I can pick a pair of uploaded snapshots, in this case 49 and 50.
For the second period I can select a different database. Note in particular how this screen shows the AWRW mappings  I am then able to pick the exact same snapshots, 49 and 50, for DGB.
I am able to run the AWR comparison report for the same snapshots, but associated with the different databases.