AWR Warehouse Repository Cleanup

Recently I was testing AWR Warehouse functionality at work and had to re-configure the AWR Repository database due to a firewall issue which caused me to change the database port number. Anyways, when I re-configured the AWR Warehouse database, I didn’t cleanup or deleted the source databases.

After configuring the AWR Warehouse database in OEM Cloud Control, I noticed that all my source databases were missing and space was not released. After doing some research and reading the DBSNMP.MGMT_CAW_LOAD package, I found that OEM Cloud Control assigned a new EM_ID when I re-configured the AWR Warehouse repository database. To find out your AWR Warehouse EM_ID run the following query in OEM Cloud Control repository database or OMR. Before I re-configured the output was “1.”

SQL> SELECT EM_ID FROM sysman.DB_CAW_REPOS
 
     EM_ID
----------
         2

1 row selected.

On AWR Warehouse repository database, I queried some %CAW% tables in DBSNMP schema and found out that remnants of EM_ID 1 still exist.

Following query showed two AWR repositories and their properties:

 
SQL> SELECT DISTINCT EM_ID
  FROM DBSNMP.CAW_PROPERTIES
WHERE EM_ID IS NOT NULL
 
     EM_ID
----------
         1
         2
 
2 rows selected.

There were two source databases that belonged to EM_ID 1.

SQL> SELECT COUNT(*)
FROM DBSNMP.CAW_SRC_DBS
WHERE EM_ID = 1
 
  COUNT(*)
----------
         2

1 row selected.

The data in CAW_SPACE_USAGE table is used to show how much space is being used by AWR Warehouse repository database in OEM Cloud Control dash board. And since I just re-configured the AWR repository database with having zero source database, the 5GB of used space didn’t make any sense.

SQL> SELECT TOTAL_SIZE_MB
FROM DBSNMP.CAW_SPACE_USAGE
WHERE UPDATE_DATE > SYSDATE - 2
 
TOTAL_SIZE_MB
-------------
         5143

1 row selected.

The DELETED Column in CAW_SRC_DBS tables get updated whenever you delete a source database. And since EM_ID = 1 is the old AWR Repository, I needed to have following two databases marked deleted.

SQL> COL TARGET_TYPE FORMAT A15
SQL> SELECT CAW_SRC_ID, TARGET_TYPE, DELETED
FROM DBSNMP.CAW_SRC_DBS
WHERE EM_ID = 1
 
CAW_SRC_ID TARGET_TYPE     DELETED
---------- --------------- -------
         4 oracle_database N     
         5 oracle_database N     
 
2 rows selected.

I didn’t want to manually update all these tables and break something. And I couldn’t find any clear answer on the web, so I start reading DBSNMP.MGMT_CAW_LOAD package and found a procedure called “CELANUP.” This procedure truncates the CAW_SPACE_USAGE table, updates CAW_SRC_DBS and deletes rows from CAW_PROPERTIES table. Exactly what I was looking for 🙂

 
SQL> EXEC DBSNMP.MGMT_CAW_LOAD.CLEANUP(EM_ID => 1)

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.16

SQL> COMMIT

Commit complete.

Elapsed: 00:00:00.11

Now there are properties for only one AWR Warehouse:

 
SQL> SELECT DISTINCT EM_ID
  FROM DBSNMP.CAW_PROPERTIES
WHERE EM_ID IS NOT NULL
 
     EM_ID
----------
         2

1 row selected.

CAW_SPACE_USAGE table was truncated. But don’t worry, it gets populated within 5mins by a scheduled job. Only one row gets inserted in 24 hours and UPDATE_DATE column gets sysdate-1 value. I am not sure why, but don’t be surprised if it gets updated with yesterday date.

SQL> SELECT TOTAL_SIZE_MB
FROM DBSNMP.CAW_SPACE_USAGE;

no rows selected.

AWR Warehouse Dashboard

Now the two tables have been updated with DELETED = Y:

SQL> COL TARGET_TYPE FORMAT A15
SQL> SELECT CAW_SRC_ID, TARGET_TYPE, DELETED
FROM DBSNMP.CAW_SRC_DBS
WHERE EM_ID = 1
 
CAW_SRC_ID TARGET_TYPE     DELETED
---------- --------------- -------
         4 oracle_database Y     
         5 oracle_database Y     
 
2 rows selected.

To find out current space allocated by source databases in AWR Repository, run the following query. This will give space usage in MB.

 
SELECT ROUND (SPACE_USAGE_KBYTES / 1024, 2)
  FROM V$SYSAUX_OCCUPANTS
WHERE OCCUPANT_NAME = 'SM/AWR';

If you ever need to re-configure AWR Repository database, make sure to delete all source databases and run the CLEANUP procedure after removing it from OEM Cloud Control.

Related Posts

2 thoughts on “AWR Warehouse Repository Cleanup

  1. Hello
    I ran the cleanup package on the OEM12, and now when I try to clic on “AWR Warehouse”, I get the java.lang.NumberFormatException: null (ADF_FACE-60097)…
    Do you have any idea ?

Leave a Reply

Your email address will not be published. Required fields are marked *