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.
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.