Skip to content

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.

Published inOEM Cloud Control 12c

Be First to Comment

Leave a Reply

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